🐍 Backend with Python · Chapter 7

Migrations, and never editing a table by hand

Your laptop, CI, staging and production each hold a copy of the schema, and the only way they stay identical is if every change is a numbered, reviewed script that runs everywhere in the same order.

Study this lesson free in the app →

The basics

A migration is a small versioned script that changes the database schema — add a column, create an index, rename a field — with an upgrade() and a downgrade(). Alembic is SQLAlchemy's migration tool.

The problem it solves: the same schema lives in many databases (laptops, CI, staging, production) and your code assumes one exact shape; hand-run ALTER TABLEs make those copies drift apart silently until a deploy breaks.

The mental model: a municipal building-plan register. Every change to the building — an extra floor, a new staircase — is filed as a numbered approval that names the approval before it. The building's legal state is "all approvals up to #17". A mason who adds a wall without filing one has made a building that no longer matches its papers.

Explained

Thursday 11:40 PM, a hotfix by hand

Search on the rides admin page is timing out. An engineer opens a psql shell on production and runs:

CREATE INDEX ix_rides_city ON rides (city);

Fixed in 90 seconds. Nobody writes a migration.

Monday, a teammate adds a column, runs alembic revision --autogenerate, and the draft contains her column plus op.drop_index('ix_rides_city') — because the index exists in the database but not in the models. She doesn't read the draft closely. It ships. Search times out again on Monday night, and this time nobody remembers why it was ever fast.

Staging never had the index at all, so every load test since Thursday measured a different database from production. That is drift, and the hand edit caused all of it.

The plan register at the municipal office

A four-storey building in Pune has a file at the municipal office: approval #1, the original plan; #2, a balcony; #3, a lift shaft — each approval refers to the one before it.

When a buyer's lawyer asks "is this building legal?", nobody walks around measuring walls. They read the file and check that the building matches approval #3.

Now a contractor quietly knocks out a wall on the second floor. The file still says #3. The building no longer matches it. The next architect who draws approval #4 from the file designs a beam resting on a wall that isn't there.

Migrations are the file. alembic_version is the "currently at #3" stamp. A hand-run ALTER TABLE is the knocked-out wall — the stamp stays the same while reality changes underneath it.

The workflow for every schema change

The accented step is the one people skip. Autogenerate is a typist, not a reviewer.

The commands, with what they actually print

$ alembic init migrations
Creating directory .../migrations ...  done
Generating .../alembic.ini ...  done

Point sqlalchemy.url in alembic.ini at the database, and in migrations/env.py set target_metadata = Base.metadata (import your models). Then:

$ alembic revision --autogenerate -m "create rides"
INFO  [alembic.autogenerate.compare.tables] Detected added table 'rides'
Generating .../versions/9ac08a5ffa79_create_rides.py ...  done

$ alembic upgrade head
INFO  [alembic.runtime.migration] Running upgrade  -> 9ac08a5ffa79, create rides

$ alembic current
9ac08a5ffa79 (head)

$ alembic history
9ac08a5ffa79 -> fde39261969c (head), add city
<base> -> 9ac08a5ffa79, create rides

$ alembic downgrade -1          # step back one revision
$ alembic upgrade head --sql    # print the SQL instead of running it

--sql is how you show a DBA exactly what will run: for the city migration it printed ALTER TABLE rides ADD COLUMN city VARCHAR(40); and the UPDATE alembic_version line.

What a migration file looks like

After adding city: Mapped[str | None] = mapped_column(String(40)) to the model, autogenerate wrote:

revision: str = 'fde39261969c'
down_revision: Union[str, Sequence[str], None] = '9ac08a5ffa79'

def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('rides', sa.Column('city', sa.String(length=40), nullable=True))
    # ### end Alembic commands ###

def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('rides', 'city')
    # ### end Alembic commands ###

Three things to see:

• down_revision is the chain link. Alembic walks it from whatever alembic_version says up to head.
• str | None became nullable=True, which is why this is safe on a table with rows already in it.
• The comment literally says please adjust! — this is a draft.

Autogenerate meets a rename

The rides table has 2 rows. You rename the model field fare: Mapped[int] to fare_paise: Mapped[int] and run alembic revision --autogenerate -m "rename fare".

Rename a column with zero downtime: expand, backfill, contract

The table states shown are what sqlite3 printed after alembic upgrade 0002, 0003 and 0004. Three migrations and two deploys instead of one — that's the price of never having a moment where running code and schema disagree.

What autogenerate misses, and other traps

• Renames — column or table renames come out as drop + add (a table rename drafted add_table('trips') + remove_table('rides')). Always hand-fix.
• Server defaults — compare_server_default is False by default; changing server_default in the model made alembic check say No new upgrade operations detected. compare_type is on by default in current Alembic.
• Unnamed constraints — give constraints names (a naming_convention on MetaData) or Alembic can't target them later.
• Data changes — autogenerate only sees schema. Backfills are op.execute(...) you write.
• Editing a migration that already ran — production won't run it again. Write a new revision.
• Two heads — two branches each add a revision on the same parent; upgrade head fails with Multiple head revisions are present. alembic merge creates a revision with both parents.
• Locks — on a large Postgres table, a plain CREATE INDEX blocks writes; use postgresql_concurrently=True inside op.get_context().autocommit_block().

Words to know

In two minutes

• Every schema change is a versioned migration file with upgrade() and downgrade(), chained by down_revision.
• Each database records its position in the alembic_version table; alembic upgrade head runs whatever is missing, in order.
• Autogenerate diffs models against the live database — it drafts, you review: renames become drop+add, server defaults are ignored by default.
• Data changes (backfills, unit conversions) are op.execute you write yourself.
• Breaking changes go expand → dual-write → backfill → switch reads → contract, so old and new code both work mid-deploy.
• Hand-run ALTERs cause drift: alembic_version doesn't change, staging stops matching prod, and the next autogenerate tries to revert them.
• Run alembic check in CI, migrate once before new code serves traffic, and merge parallel heads with alembic merge.

Test yourself

There are 9 questions, 2 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.