Migrations as Code with Alembic

What is a migration?

A migration is a versioned script that modifies your database schema. It's tracked in version control alongside your application code, so every change to a table structure, column, index, or constraint has a commit history and a reason. No more "nobody knows when that column was added" or "somebody dropped this table in production and we're not sure why."

Why version control migrations with code

A migration lives in a file with a unique ID, an upgrade() function, and a downgrade() function that, when practical, reverses it. Because migrations are code, they're reviewable in pull requests and they produce a complete audit trail — no manual SQL scripts that someone has to remember how to unwind. downgrade() reverses a schema change, not a release: it doesn't restore data the upgrade or a subsequent application version already destroyed or transformed, which matters more than it sounds like it should — more on that below.

Multi-environment advantages

The same migration sequence is applied to every environment, so dev, staging, and production all converge on the same intended schema history, which greatly reduces drift compared to hand-run SQL scripts that some environments get and others don't. It's a reduction, not an absolute guarantee — nothing stops someone from running ALTER TABLE by hand against production outside the migration chain, which is exactly the gap alembic check below exists to catch. When a new environment spins up, it runs the entire migration chain from the start and lands on the same schema history as production. There's no "run these files in this order, except skip this one" — everything is explicit and tracked.

Why this matters

Reviewable in pull requests — Schema changes are code, so they go through the same code review process as application logic. Teammates can catch issues before they reach production.
Complete audit trail — Every schema change has a commit message, author, and timestamp, plus a reason for it if the PR does its job — a record of who changed what, when, and why.
Reversible where practical — Each migration has an upgrade() and downgrade() function, so a purely structural change (a column, an index) can be undone. A migration that destroys data can restore the schema shape but not the rows — reversibility is a schema property, not a data-recovery guarantee.
Drift-resistant across environments — Dev, staging, and production run the same migration sequence in the same order, and a new environment reproduces the same schema history from scratch instead of depending on someone's memory of which manual scripts to run.
Explicit and tracked — No "run these files in this order, except skip this one" confusion. Everything is recorded in version control.
Schema and code evolve together, not necessarily atomically — Migrations live in the same pull request as the application code that depends on them, but the migration and the new application code still deploy as two separate events. A migration that adds a NOT NULL column before the new code is running will break the old code's inserts in the gap between the two; adding it nullable first, backfilling, then tightening it in a later migration avoids that window.

What is Alembic?

Alembic is SQLAlchemy's migration framework. It manages a table in your database called alembic_version that records which migration has most recently run. Each migration is a Python file with a revision ID and a pointer to the previous migration, creating a linked chain. You point Alembic at your database connection and your SQLAlchemy models, and it can autogenerate migration files by diffing your models against the live schema.

Core commands

autogenerate — Alembic inspects your SQLAlchemy models and the current database schema, then drafts a migration file that captures the difference. It's a candidate, not an authoritative answer: it detects a difference between metadata and database, not whether that difference is the schema change you actually intended, and it's not perfect even at detection (complex constraints sometimes need manual tweaking) — it handles the common cases well: new tables, columns, indexes, and basic type changes. Every autogenerated file gets reviewed before it's treated as correct. See the Alembic documentation for details.

upgrade — Runs all pending migrations forward to bring the database to the target state. upgrade head takes it all the way to the latest; upgrade +2 runs just the next two. Each migration executes its upgrade() function, and Alembic records the new revision in the alembic_version table.

downgrade — Rolls back migrations in reverse. downgrade -1 undoes the most recent migration by running its downgrade() function, restoring the schema shape it had before. In production, that's usually not the safest way to recover from a bad release — rolling the application back to the previous version while leaving the database ahead of it, provided the schema change was made backward-compatible in the first place, avoids ever asking the database to move backward. downgrade earns its keep in development and in testing that a migration's rollback path is actually correct.

check — Compares the database schema against your SQLAlchemy models and reports any mismatches. It's useful in CI pipelines to catch schema drift — situations where the database has drifted from what the code expects (maybe someone ran a manual SQL script, or a migration failed partway through).

A real migration

When a legacy cash-register balance table was retired in favor of proper ledger tables, the upgrade was straightforward:

def upgrade() -> None:
    op.drop_index("ix_balance_date", table_name="balance")
    op.drop_index("ix_balance_id", table_name="balance")
    op.drop_table("balance")

But the downgrade had to rebuild the entire table structure so the rollback was complete and safe:

def downgrade() -> None:
    op.create_table(
        "balance",
        sa.Column("id", sa.BIGINT(), autoincrement=True, nullable=False),
        sa.Column("employee_id", sa.BIGINT(), nullable=False),
        sa.Column("date", sa.DATE(), nullable=False),
        sa.Column("period", sa.VARCHAR(length=20), nullable=False),
        sa.Column(
            "cash_value",
            sa.NUMERIC(precision=10, scale=2),
            server_default=sa.text("0"),
            nullable=False,
        ),
        sa.CheckConstraint(
            "period::text = ANY (ARRAY['Day'::text, 'Evening'::text])",
            name="check_period",
        ),
        sa.ForeignKeyConstraint(
            ["employee_id"],
            ["employees.id"],
            name="balance_employee_id_fkey",
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint("id", name="balance_pkey"),
        sa.UniqueConstraint("date", "period", name="unique_balance_record"),
    )
    op.create_index("ix_balance_id", "balance", ["id"], unique=False)
    op.create_index("ix_balance_date", "balance", ["date"], unique=False)

Notice exactly what this downgrade restores: the table definition, its columns, constraints, indexes, and default — the schema shape. It does not restore the rows that existed in balance before upgrade() dropped the table; those are gone the moment the drop runs, and no downgrade() can bring them back. For a destructive migration like this one, "reversible" means structurally reversible, not data-reversible — the two are easy to conflate until a migration like this one forces the distinction.

A downgrade must restore the schema to the exact state it was before the upgrade ran. If it doesn't, a rollback looks like it worked while quietly corrupting your data model — and even when it does, "the schema is back" and "the data is back" are two different claims.

The outcome

Schema changes become auditable, reproducible across environments, and reversible for what reversibility actually means at the schema level — restoring shape, not resurrecting deleted data.
Migrations live in the same pull request as the application code that depends on them, with an eye on deployment ordering rather than assuming the two land atomically.
Deployments include schema changes as code, not as manual SQL scripts or runbooks that someone has to remember.
The failure modes that remain — drift from a manual query, a destructive downgrade that can't restore data — are the ones worth designing around explicitly rather than assuming migrations solved for free.

Migrations as Code: Six Advantages of Version-Controlled Database Changes