Alembic & SQLAlchemy Migrations

Alembic is the migration engine that ships alongside SQLAlchemy, and its model is different from a code-first ORM’s: the source of truth is your SQLAlchemy metadata — the MappedColumn definitions on your declarative models — while the migration history is a directed graph of Python revision files, each naming its down_revision parent. alembic revision --autogenerate compares the metadata against the live database and writes a revision that reconciles the two, and alembic upgrade head walks the graph applying each op.* call in order. The whole discipline of running Alembic without a maintenance window comes down to three facts about that graph: autogenerate is a suggestion and not a verdict, the graph must resolve to exactly one head or upgrade refuses to run, and the individual op.add_column / op.create_index calls each acquire real database locks whose severity depends on whether you are on PostgreSQL or MySQL.

This section is for backend engineers running a Python service — FastAPI, Flask, Django-with-SQLAlchemy, or a bare worker — who own the alembic/versions/ folder and must ship schema changes while traffic keeps flowing. It sits inside the broader ORM & Framework Migration Workflows discipline and assumes the additive, forward-only contract from Expand and Contract Methodology. Where a code-first tool infers everything, Alembic hands you a Python file you are expected to read and edit — which is both its great strength and the reason most Alembic incidents trace back to an unreviewed autogenerated revision or a revision graph that quietly grew a second head.

Concept & Mechanism

Alembic stores no schema of its own. It keeps a single row in a table named alembic_version recording the revision identifier the database currently sits at, and it keeps the shape of the schema only implicitly, as the cumulative effect of every revision applied from the graph’s root to that identifier. When you run alembic upgrade head, Alembic reads alembic_version, walks the chain of down_revision pointers from the requested head backward until it reaches the current revision, and then replays the intervening upgrade() functions forward. This is why the history is a linked list — or, when branches appear, a directed acyclic graph — and not a flat folder of files: order is encoded in the pointers, not in filenames or timestamps.

Autogenerate works by building two MetaData snapshots. One is your application’s metadata, imported through the target_metadata object in env.py; the other is reflected live from the database catalog. Alembic diffs them and emits op directives for the differences it can detect. The critical caveat — the one that drives an entire child topic below — is that the comparison only sees what it is taught to compare. By default it detects added and removed tables and columns, but it does not detect a column type change, a server default change, or a constraint rename unless you explicitly enable compare_type=True and compare_server_default=True in the context.configure() call inside env.py. Anything outside a mapped table — a CHECK constraint written by hand, a partial index, an ENUM value, a trigger — is invisible to it entirely, because it is not represented in your SQLAlchemy metadata at all.

A second structural fact governs everything: Alembic’s history is intended to be linear, resolving to a single head, but nothing in Git enforces that. When two engineers each generate a revision on the same parent in separate branches and both branches merge, the graph forks — two revisions now claim the same down_revision, and Alembic reports two heads. The next upgrade head has no single target and refuses to run. The graph is still a valid DAG, but a healthy Alembic project keeps it a straight line, and recovering a forked one means explicitly merging the heads back together, covered in depth below.

The engine difference matters as much as it does everywhere else in migration work. On PostgreSQL, DDL is transactional: Alembic wraps each migration in a transaction by default, so a multi-statement revision that fails partway rolls back cleanly and alembic_version is never advanced. The sole exception is CREATE INDEX CONCURRENTLY, which PostgreSQL forbids inside a transaction — you must break Alembic’s transaction to use it. On MySQL 8.0, DDL forces an implicit commit, so a revision that runs two ALTER TABLE statements and fails on the second leaves the first permanently applied while alembic_version still points at the old revision — the database and the history now disagree. That divergence, detailed in Transactional vs Non-Transactional Databases, decides how recoverable a failed Alembic upgrade is and is the reason MySQL revisions should carry one DDL statement each.

Prerequisites & Decision Criteria

Before running any Alembic revision against a shared database, decide two things: whether autogenerate can be trusted for this change, and how the change’s locking profile maps to your table size and traffic. The table below is the decision surface for the second question — it is what separates a revision you can apply during business hours from one that must be sequenced across deploys following the zero-downtime path.

Two prerequisites gate everything above. First, env.py must point target_metadata at the real, fully imported model metadata — if a model module is not imported before autogenerate runs, its tables are invisible and autogenerate will cheerfully emit DROP TABLE for them. Second, compare_type=True and compare_server_default=True belong in context.configure() on any project that ever changes a column type or default; without them, autogenerate silently misses those changes and the revision ships incomplete. The decision rule for the whole table: if the “Safe path” column says anything other than “single revision, any time”, the change belongs in the zero-downtime revision sequence, not in a single upgrade applied at peak.

Step-by-Step Procedure

The procedure below takes one additive change from a model edit through a verified production apply. Every step names what to confirm before moving on.

1. Edit the SQLAlchemy model, then autogenerate the revision. Make the change additive first — a nullable column or a new table — so the generated SQL is backward compatible with the running code.

# Context: local workstation or CI; connects to a dev/scratch database only, applies nothing to production.
# Requires target_metadata wired in env.py and every model module imported.
alembic revision --autogenerate -m "add users.status_flag"

Verify before proceeding: a new file appears under alembic/versions/ and its down_revision names the current head — run alembic heads and confirm there is still exactly one.

2. Read and correct the generated revision. Open the file and treat it as a draft. Renames arrive as a drop_column plus an add_column, which destroys data; a type change may be missing entirely.

# Context: hand-edit in the versions/ file before it is ever applied.
# Rewrite a mis-detected rename into a non-destructive alter.
def upgrade() -> None:
    op.alter_column("users", "statusflag", new_column_name="status_flag")
    # NOT: op.drop_column(...) + op.add_column(...)  <-- data loss

Verify before proceeding: the upgrade() body contains no unexpected drop_column/drop_table, and any index creation is switched to the concurrent form shown in step 4.

3. Sanity-check the SQL Alembic will emit without touching production. The --sql offline mode prints the DDL so you can review the exact statements and their order.

# Context: read-only; prints SQL to stdout, executes nothing. Safe to run against any environment string.
alembic upgrade head --sql > /tmp/pending_migration.sql
grep -iE 'DROP (COLUMN|TABLE)|ALTER COLUMN .* TYPE' /tmp/pending_migration.sql \
  && echo "REVIEW: destructive or rewriting DDL present"

Verify before proceeding: the printed SQL matches your intent and contains no surprise rewrite.

4. Apply the revision as a discrete, forward-only deploy step. Run the upgrade before the new application image rolls out, so the additive schema is present when the new code arrives. For an index on a large table, escape Alembic’s transaction so PostgreSQL will build it concurrently.

# Context: inside the revision file. CREATE INDEX CONCURRENTLY must run OUTSIDE a transaction.
# PostgreSQL only — this tells Alembic not to wrap the migration in a transaction.
def upgrade() -> None:
    op.add_column("users", sa.Column("status_flag", sa.String(32), nullable=True))
    with op.get_context().autocommit_block():
        op.create_index(
            "idx_users_status", "users", ["status_flag"], postgresql_concurrently=True,
        )

# Context: production migration step, run as the migration role BEFORE the app fleet rolls.
# Reserve a dedicated connection so the deploy does not starve the application pool.
alembic -x lock_timeout=3000 upgrade head

Verify before proceeding: the command exits zero, alembic current reports the new revision, and the column is visible in the catalog. On MySQL, confirm each revision carried a single DDL statement so a mid-revision failure cannot strand you between states.

Verification & Observability

After applying, prove three things agree: the alembic_version pointer, the live catalog, and your model metadata. Start with Alembic’s own view.

# Context: read-only; prints the revision the database currently sits at. No writes.
alembic current --verbose        # must show exactly one revision, matching alembic heads
alembic heads                    # must print a single head, never two

Then confirm the database actually carries the change, querying the catalog directly rather than trusting the model.

-- PostgreSQL · read-only · safe any time against primary or replica
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'status_flag';

-- MySQL 8.0 · read-only · safe any time
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'users' AND COLUMN_NAME = 'status_flag';

Watch for a session stuck holding the DDL lock, which on a busy table can queue every write behind it. On PostgreSQL, a blocked ALTER TABLE waiting on an ACCESS EXCLUSIVE lock is the classic cause of a deploy that “hangs”.

-- PostgreSQL · read-only · find a migration session holding or waiting on a heavy lock
SELECT a.pid, a.state, a.wait_event_type, l.mode, a.query
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE a.query ILIKE '%alter table%' AND NOT l.granted;

The definitive drift check is Alembic’s own comparison: run autogenerate against production in a throwaway revision and confirm it produces an empty migration. A non-empty diff means the model and the database disagree.

# Context: CI gate against a clone of production; a non-empty upgrade() body means drift.
alembic revision --autogenerate -m "drift-probe" && \
  grep -q "pass" alembic/versions/*drift-probe*.py || echo "DRIFT: model and database differ"

Confirm before promotion:

• alembic heads prints exactly one head.
• alembic current matches that head on the production database.
• The new column/index is present in information_schema.
• A drift-probe autogenerate against a production clone yields an empty upgrade().
• No session is stuck waiting on an ACCESS EXCLUSIVE lock.

Rollback Path

Reversal is forward-compatible, not a downgrade. Because the deploy was additive, the previous application image already runs against the expanded schema — so the fast, safe rollback is to redeploy the old image and simply stop writing the new column, leaving the schema in place. Alembic’s own downgrade is available, but running it in production is a destructive act you reach for only deliberately.

# Context: production; step the schema back ONE revision. Destructive if downgrade() drops columns.
# Prefer redeploying the previous app image over this unless the schema itself must revert.
alembic downgrade -1

The downgrade() function is only as safe as you wrote it — an autogenerated one will drop_column the column you just added, which is exactly what you must not do while any running version might still read it. The safe conditions for a real schema reversal are narrow: the backfill has not yet run or is provably reversible, no deployed application version reads the column, and a verified point-in-time backup exists. When those hold, downgrade one step; when they do not, roll the application image back and treat the extra column as harmless until a later contract revision removes it. This is the same additive-first contract built in detail by Rollback Automation.

-- PostgreSQL · run as the migration role · non-destructive, safe any time
-- Neutralise the new column without dropping it; the app image reverts routing, not the schema.
ALTER TABLE users ALTER COLUMN status_flag DROP DEFAULT;

Common Errors & Fixes

Multiple head revisions are present for given argument 'head'; please specify a specific target revision. Root cause: two feature branches each created a revision whose down_revision is the same parent, so the graph forked and now has two heads — alembic upgrade head cannot choose between them. Fix: create a merge revision that names both heads as its parents, which rejoins the graph into one head. The full procedure, including how to avoid the fork in the first place, is in fixing Alembic multiple heads after a merge.

# Context: workstation; creates an empty revision joining the two divergent heads into one.
alembic merge -m "merge feature branches" heads

Autogenerate emits an empty migration when you know the schema changed. Root cause: the change is one autogenerate does not detect by default — a column type change, a server-default change, or a constraint on something not in your metadata — or a model module was never imported so its table is invisible. Fix: enable compare_type=True and compare_server_default=True in env.py, import every model before the diff runs, and hand-write the op call for anything outside mapped metadata. This whole failure class is dissected in why Alembic autogenerate misses changes.

Can't locate revision identified by 'a1b2c3d4e5f6'. Root cause: the database’s alembic_version points at a revision file that no longer exists in the codebase — usually a rebase or force-push that deleted a revision after it was applied somewhere. Fix: restore the missing revision file from history, or alembic stamp the database to a revision that does exist once you have confirmed the schema state manually.

CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Root cause: Alembic wraps every PostgreSQL migration in a transaction by default, and CONCURRENTLY is illegal there. Fix: wrap the index creation in op.get_context().autocommit_block() (or set transaction_per_migration off for that revision) so it runs outside the transaction, as shown in step 4.

MySQL revision half-applied: alembic_version still on the old revision but the column exists. Root cause: MySQL’s implicit commit on DDL means a multi-statement revision that failed on statement two left statement one committed while Alembic never advanced the version pointer. Fix: make MySQL revisions carry a single DDL statement each, and after a failure, inspect the catalog, manually reconcile, then alembic stamp to the revision that matches reality.

Deep-Dive Guides

Three deep-dive guides sit under this section, each covering the failure surface that most often pages a SQLAlchemy engineer at deploy time. Reach for fixing Alembic multiple heads after a merge the moment alembic upgrade refuses to run because the revision graph forked — it walks through diagnosing the two heads with alembic heads, creating the merge revision that rejoins them, and the branch-labelling and review conventions that stop the fork recurring on the next parallel feature branch. Turn to why Alembic autogenerate misses changes when a --autogenerate revision comes back empty or incomplete despite a real model change — it covers the compare_type and compare_server_default switches, the target_metadata import trap that makes tables invisible, and the categories of schema object autogenerate can never see. And read running Alembic migrations with zero downtime for the full expand-migrate-contract sequence — how to split a type change or a NOT NULL addition across deploys, where the backfill worker fits, and how to manage lock timeouts so a revision never blocks production writes on PostgreSQL or MySQL.

Related

• Fixing Alembic Multiple Heads After a Merge
• Why Alembic Autogenerate Misses Changes
• Running Alembic Migrations with Zero Downtime
• Prisma Migration Strategies
• Expand and Contract Methodology

Up one level: ORM & Framework Migration Workflows.

Frequently Asked Questions

Why does alembic upgrade head fail with “Multiple head revisions are present”? Your revision graph has forked into two heads, almost always because two branches each based a new revision on the same parent and both merged. Alembic cannot pick a single target, so it stops. Run alembic heads to see both, then create a merge revision with alembic merge heads that names both as parents and rejoins the graph into one head. Preventing the recurrence is a review-time habit: rebase a branch’s revision onto the latest head before merging.

Why did --autogenerate produce an empty migration when I changed a column? Autogenerate does not detect every change by default. Column type changes and server-default changes are only compared when you set compare_type=True and compare_server_default=True in context.configure() inside env.py. Anything not represented in your SQLAlchemy metadata — hand-written CHECK constraints, triggers, partial indexes, enum values — is invisible entirely and must be written as an explicit op call. Also confirm every model module is imported before the diff runs, or its tables will be missing.

Does Alembic roll a failed migration back automatically? It depends on the engine, not Alembic. On PostgreSQL, DDL is transactional and Alembic wraps each migration in a transaction, so a multi-statement revision that fails partway rolls back and alembic_version never advances — the exception being CREATE INDEX CONCURRENTLY, which must run outside a transaction. On MySQL 8.0, DDL forces an implicit commit, so a partially applied revision stays applied while the version pointer lags. Keep MySQL revisions to one DDL statement each so a failure can never strand the database between two states.

Should I run alembic downgrade to roll back in production? Prefer redeploying the previous application image over running downgrade. Because zero-downtime deploys are additive, the old image already runs against the expanded schema, so an extra column is harmless and there is no reason to drop it under pressure. Run alembic downgrade -1 only when the schema itself must revert, the downgrade() body is genuinely non-destructive for currently running code, and a verified backup exists — an autogenerated downgrade will drop the new column, which is unsafe while any version still reads it.