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. When you deploy, the database runs the upgrade path; if a release goes bad, downgrade rolls it back. Because migrations are code, they're reviewable in pull requests, they produce a complete audit trail, and they're reversible — no manual SQL scripts that someone has to remember how to unwind.
Multi-environment advantages
The same migration runs identically on your laptop, in staging, and in production. You don't get schema drift — a dev database that doesn't match staging, or a production schema nobody can fully describe. When a new environment spins up, it runs the entire migration chain from the start and lands on the exact same schema as production. There's no "run these files in this order, except skip this one" — everything is explicit, tracked, and reproducible.
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. You know exactly who changed what and when.
- Reversible — Each migration has an
upgrade()anddowngrade()function. If a release goes bad, you can roll back the schema to the previous state. - No schema drift — Dev, staging, and production all run the same migrations in the same order. No "this works on my machine" database schema problems.
- Reproducible across environments — New environments spin up by running the entire migration chain from the start and land on the exact same schema as production.
- 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 — Migrations live in the same pull request as the application code that depends on them, keeping them synchronized.
- Auditable — You have a complete record of every schema change, why it happened, and who approved it.
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 not perfect (complex constraints sometimes need manual tweaking), but it handles the common cases: new tables, columns, indexes, and basic type changes. 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 records itself in the alembic_version table.
downgrade — Rolls back migrations in reverse. downgrade -1 undoes the most recent migration by running its downgrade() function. This is where the reversibility promise lives — if a migration broke the schema, you can undo it. downgrade is rarely used in production (you're usually trying to go forward, not backward), but it's invaluable for development and testing rollback procedures.
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)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.
The outcome
Schema changes become auditable, reproducible across environments, and reversible. Your migrations live in the same pull request as the application code that depends on them, so the schema and the code that queries it evolve together. Deployments include schema changes as code, not as manual SQL scripts or runbooks that someone has to remember.