Alembic Migrations You Can Roll Back

The first schema change on a project is easy: edit the model, drop the database, recreate it. The second one is easy too. The change that hurts is the one applied to a database holding data you cannot regenerate, discovered to be wrong twenty minutes later, with no way back that does not involve reading someone's shell history. Migrations exist to make schema change an ordinary, reversible, reviewable operation instead of an event.

What a migration tool is actually for

Alembic is the migration tool that ships alongside SQLAlchemy, and its job is narrower than it first appears. It does not know what your schema should look like; it keeps an ordered, version-controlled list of changes and records which of them a given database has already had applied, in a single-row table called alembic_version. Everything else follows from that: alembic upgrade head means "apply every revision this database has not yet seen," and alembic downgrade -1 means "undo the most recent one." Because the list lives in your repository next to the code that depends on it, a checkout of any commit tells you exactly what schema that code expected.

That linkage is the real value. The failure mode migrations prevent is not "we cannot change the schema" — it is "the schema on staging is subtly different from production and nobody knows which change is missing."

Autogenerate is a first draft, not an answer

alembic revision --autogenerate -m "add broker_id to loads" compares your SQLAlchemy models against the connected database and writes a migration for the difference. It is a genuine time-saver and it is also routinely wrong in ways that matter, because it can only detect what it is built to compare:

It reliably detects added and removed tables and columns, and most index and unique-constraint changes.
It detects a column type change, but frequently cannot generate correct SQL for it, particularly where the conversion needs an explicit cast.
It does not detect a renamed column or table. It emits a drop and an add — which is to say, it silently generates data loss.
It ignores server defaults, CHECK constraints, and anything defined outside the models it was pointed at, unless configured to include them.

The rename case is the one to internalize, because the generated migration looks perfectly reasonable in review. Renaming loads.pickup to loads.pickup_date should be op.alter_column("loads", "pickup", new_column_name="pickup_date"). Autogenerate will instead write op.drop_column("loads", "pickup") followed by op.add_column(...), which passes tests on an empty development database and destroys a column of production data. Reading every autogenerated migration before committing it is not diligence theatre; it is the step the tool assumes you will perform.

A migration is two functions, and both matter

Every Alembic revision file has an upgrade() and a downgrade(), and the second is where discipline pays off:

"""add broker_id to loads

Revision ID: 9f2c1a4b7d3e
Revises: 4e8a2f1c9b60
"""
import sqlalchemy as sa
from alembic import op

revision = "9f2c1a4b7d3e"
down_revision = "4e8a2f1c9b60"


def upgrade() -> None:
    op.add_column("loads", sa.Column("broker_id", sa.Integer(), nullable=True))
    op.create_index("ix_loads_broker_id", "loads", ["broker_id"])
    op.create_foreign_key(
        "fk_loads_broker_id", "loads", "brokers", ["broker_id"], ["id"]
    )


def downgrade() -> None:
    op.drop_constraint("fk_loads_broker_id", "loads", type_="foreignkey")
    op.drop_index("ix_loads_broker_id", table_name="loads")
    op.drop_column("loads", "broker_id")

Two details are load-bearing. The downgrade() reverses the operations in the opposite order from upgrade(), because a foreign key cannot be dropped after the column it constrains. And constraints and indexes are named explicitly rather than left to the database to name — an unnamed constraint gets an auto-generated name that differs between backends and sometimes between versions, and downgrade() has no way to refer to it. Setting a naming convention in your MetaData makes this consistent across every future migration rather than something each author has to remember.

Leaving downgrade() as pass is a decision, not a shortcut. It may be a defensible one for a genuinely irreversible change, but it should say so in a comment, because the alternative is a future reader assuming the rollback works and finding out otherwise during an incident.

Data migrations need a frozen copy of the table

Sooner or later a migration has to move data, not just change structure — backfilling the new column from an old one, say. The instinct is to import the application's model and use it, and that is the bug: the model describes the schema as of the current checkout, while the migration runs against the schema as of this revision. Once the model gains another column, every older migration that imported it breaks, because the generated SQL references a column the database does not yet have. Define a minimal throwaway table inside the migration instead:

def upgrade() -> None:
    op.add_column("loads", sa.Column("weight_kg", sa.Numeric(10, 2), nullable=True))

    loads = sa.table(
        "loads",
        sa.column("id", sa.Integer),
        sa.column("weight_lbs", sa.Numeric(10, 2)),
        sa.column("weight_kg", sa.Numeric(10, 2)),
    )
    op.execute(
        loads.update()
        .where(loads.c.weight_lbs.isnot(None))
        .values(weight_kg=loads.c.weight_lbs * 0.45359237)
    )

The sa.table literal names only the three columns this migration touches and will keep working unchanged for as long as those columns exist, regardless of what happens to the model. On a large table the same update should be run in batches with an explicit commit between them, so a single statement does not hold a long transaction open against rows the application is trying to write.

Adding a NOT NULL column without locking everyone out

The most common way a routine migration causes an outage is adding a required column to a populated table in one step. ALTER TABLE ... ADD COLUMN ... NOT NULL with no default fails outright if any row exists; with a default, older database versions rewrite the entire table while holding a lock that blocks reads and writes for the duration. The safe pattern splits the change across three deploys, and it is worth doing in that order rather than compressing it:

Add the column as nullable. Fast, and no lock of consequence.
Deploy application code that writes the new column on every insert and update, then backfill existing rows in batches.
Once no nulls remain, add the NOT NULL constraint in a separate migration.

Each step is independently reversible, and at no point is there a version of the application running against a schema it does not understand. The same rule applies to removals in reverse: stop reading the column, deploy, then drop it — never in the same release, or a rollback of the application code leaves it querying a column that no longer exists.

Testing that the migrations themselves work

Migrations are code, and the cheapest useful test is that the full chain runs forward and back against an empty database. Running the whole ladder in a fixture catches the two most common errors — a broken downgrade(), and a revision whose down_revision points somewhere unexpected after a branch merge:

from alembic import command
from alembic.config import Config

def test_migrations_round_trip(tmp_path):
    cfg = Config("alembic.ini")
    cfg.set_main_option("sqlalchemy.url", f"sqlite:///{tmp_path}/t.db")
    command.upgrade(cfg, "head")
    command.downgrade(cfg, "base")
    command.upgrade(cfg, "head")

Wiring this into the same suite described in pytest fixtures and parametrize for a data pipeline means a migration that cannot be rolled back fails in review rather than in production. Running it against the same database engine as production, in a throwaway container of the kind covered in Docker for CS coursework, catches the backend-specific cases that SQLite will happily let through — SQLite's limited ALTER TABLE support in particular means a migration that passes there can still fail on PostgreSQL.

A short checklist

Did you read the autogenerated migration line by line, specifically checking that nothing intended as a rename became a drop plus an add?
Does downgrade() reverse upgrade() in the opposite order, or is it deliberately and visibly marked as irreversible?
Are all indexes and constraints named explicitly, ideally by a naming convention on the metadata?
Do data migrations define their own minimal table literal instead of importing an application model?
Is every required-column addition split into add-nullable, backfill, then constrain?
Does CI run the full upgrade/downgrade/upgrade cycle against the same engine as production?

None of this makes schema change interesting, which is the point. A migration should be a small reviewed diff that runs the same way everywhere and can be undone — the same reliability argument behind reproducible builds: the value is in the change being boring and predictable rather than clever.