APIs with FastAPI · Chapter 12 of 26

Migrations with Alembic

By the end of this chapter you can change a database that is already full of data without losing any of it: write Alembic migrations that create tables, add columns, and change data, run them forward and back, keep them in order with a team, and rename a column while the old app is still serving requests. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Structure and data
  • 4 tasks
  • FastAPI
  • In the all labs plan

The first 2 chapters of APIs with FastAPI are free. This one comes with the all labs plan.

Why you need this

DakiyaGo's parcels table has been live for a month. Sunita wants two new facts on every parcel: when it was delivered, and how many times a rider tried. That is two new fields on the model. Up to now the app made its tables with create_all when it started, so the obvious move is to add the fields and restart.

That does not work, and it is not a small gap. The production database has every parcel DakiyaGo has ever booked in it. Nobody may drop it and start again, and every change to its shape has to happen in order, the same way on your laptop, on a teammate's, in the tests and in production. Aman keeps those changes as files in the repository: migrations, run by Alembic.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    create_parcels.py: the first migration

    From today DakiyaGo's schema changes only through migrations. Write migration 0001 (revision "0001", down_revision None) whose upgrade() creates two tables. parcels: awb string(12) as the primary key, merchant_id string(3), from_pincode string(6), to_pincode string(6), weight_grams integer, cod_amount integer with the server default 0, status string(20) with the server default booked, booked_at a timestamp with time zone; every column required. events: id an integer primary key, awb string(12) required and a foreign key to parcels.awb, status string(20) required, at a timestamp with time zone required, hub string(8) that may be NULL.

    downgrade() drops both tables. The check runs alembic upgrade head on an empty database, loads DakiyaGo's parcels and events into your tables, saves a parcel without status or cod_amount, then runs alembic downgrade base and expects no tables left.

  2. Task 2

    add_delivery_columns.py: new columns on a full table

    Sunita wants every parcel's delivery time and how many times a rider tried. Write migration 0002 (revision "0002", down_revision "0001") whose upgrade() adds two columns to parcels: delivered_at, a timestamp with time zone that may be NULL, and delivery_attempts, a required integer that is 0 for every parcel already in the table and for every new one saved without it.

    downgrade() drops both columns and keeps every parcel. The check applies its own 0001, saves 41 parcels, runs your upgrade, saves one more parcel without the new columns, then downgrades to 0001.

  3. Task 3

    backfill_delivered_at.py: a data migration

    delivered_at is empty for every parcel delivered before migration 0002. The times are already in the database: each delivered parcel has an event in events whose status is delivered. Write migration 0003 (revision "0003", down_revision "0002") whose upgrade() sets each delivered parcel's delivered_at to the at of its delivered event; parcels that are not delivered keep NULL.

    downgrade() empties delivered_at again (it does not drop the column, which belongs to 0002). Write the change in SQL inside the migration: the check loads a parcel that is not in the data files, so times copied from the JSON files are wrong.

  4. Task 4 · interview question

    Interview: rename_pincode.py, a rename that breaks nothing

    Imran wants to_pincode called delivery_pincode, but the app in production keeps running, reading and writing to_pincode, until the new code is deployed. Write migration 0004 (revision "0004", down_revision "0003"), the first step of the rename: upgrade() adds delivery_pincode (string(6)) to parcels and copies every parcel's to_pincode into it, leaving to_pincode in place; downgrade() drops delivery_pincode.

    After your upgrade the check runs the old app's queries: one reads to_pincode, one books a parcel without knowing delivery_pincode exists. Both must work. The interviewer will ask how you rename a column without breaking the running app, and when to_pincode goes.

What you will learn

  • Why create_all is not enough once a table holds data.
  • Alembic: a folder of migrations, each with upgrade() and downgrade().
  • Adding a column to a table full of rows, and the default it needs.
  • Data migrations: changing values, in SQL, inside a migration.
  • Teams and running apps: two heads and a merge, and a rename the old code survives.

More in Structure and data

Build "Migrations with Alembic" yourself

DakiyaGo, a Jaipur courier startup, takes bookings on WhatsApp. Merchants want an API, and you build it. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of APIs with FastAPI are free. This one comes with the all labs plan.