Alembic

Interview answer (say this first). Alembic is the migration tool for SQLAlchemy. A migration is a versioned script with an upgrade() and a downgrade() that moves the database schema — and optionally the data — from one revision to the next. Alembic records the applied revision in a small alembic_version table, so every environment can be brought to the same known state, in order, reproducibly.

Why this exists

A database schema changes constantly: a new column, a new index, a renamed table, a backfill. Without a tool, each change is a hand-written SQL statement run by hand.

Dev:   ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;
Staging: (nobody remembers)
Prod:  the deploy fails because the column is missing

This manual approach fails in a predictable way:

• No record. Nobody can say which statements ran in which environment.
• No order. Two engineers apply changes in different sequences and the schemas diverge.
• No rollback. When a change is wrong at 2 a.m., there is no tested way back.
• No review. Schema changes skip the code-review process that all other code goes through.

Migrations turn schema changes into version-controlled code. Each change is a small script with a stable id and a pointer to the change before it. Alembic applies them in order and remembers where each database is.

Start from zero

Two small distinctions prevent most confusion:

• upgrade vs downgrade. One moves forward, one moves back. You write both.
• Schema vs data. upgrade() can do either. A clean codebase keeps them in separate revisions and labels them clearly.

The core idea

Think of Git for the database schema. Each migration is a commit. The down_revision field is the parent pointer. alembic_version is the HEAD pointer for one particular database. To move a database, you replay commits from its current pointer to the target.

flowchart LR
    B["base<br/>(empty)"] --> R1["rev 1<br/>create users"]
    R1 --> R2["rev 2<br/>add is_active"]
    R2 --> R3["rev 3<br/>backfill emails"]
    R3 --> H["head"]
    V["alembic_version<br/>= rev 2"] -.-> R2

Every database carries its own pointer. Two databases with the same pointer have the same schema. That is the whole guarantee.

Two comparisons pin the tool down:

How it works

1. alembic init alembic creates the scaffold. It makes alembic.ini, an alembic/ directory with env.py, and an empty alembic/versions/ folder.
2. You configure the database URL in alembic.ini, or read it from an environment variable in env.py.
3. You wire env.py to your models. Import Base and set target_metadata = Base.metadata. Autogenerate compares this metadata to the live database.
4. You create a revision. alembic revision --autogenerate -m "..." diffs metadata against the database and writes a new file in versions/. alembic revision -m "..." writes an empty one for you to fill in.
5. You review the generated file. Autogenerate is a draft, not a source of truth. This step is mandatory.
6. alembic upgrade head applies migrations in order. For each revision between the current version and the target, Alembic calls upgrade(), then updates alembic_version.
7. alembic downgrade -1 (or a revision id or base) reverses them, calling downgrade() in reverse order.
8. Alembic reads the current state from alembic_version. Normally that is a single row; a branched history has one row per head until a merge revision rejoins them.
9. Offline mode renders SQL without connecting. alembic upgrade base:head --sql prints the full sequence for review or for a DBA to run.
10. Branches happen when two revisions share a parent. Alembic then reports two heads. alembic merge creates a merge revision with a tuple down_revision to rejoin them.

Warning:

Autogenerate does not know everything. It detects added and dropped tables and columns. It can miss server defaults, some type changes, and it cannot tell a rename from a drop-plus-add. A rename generated as drop-plus-add destroys the data.

The syntax you will use

Initialise the project. One directory holds the scripts; alembic.ini points at it.

alembic init alembic

Configure the URL. In alembic.ini:

sqlalchemy.url = postgresql+psycopg://user:pass@localhost/app

Wire env.py to your metadata. This is the step people forget.

# alembic/env.py
from models import Base          # import the models so metadata is populated
target_metadata = Base.metadata

Autogenerate a revision from a model change.

alembic revision --autogenerate -m "add is_active"

A hand-written revision, for data or careful changes.

alembic revision -m "backfill missing emails"

The generated file has a stable shape.

revision = "8dd7387349a6"
down_revision = "aa9e780cfb43"

def upgrade() -> None:
    op.add_column("users", sa.Column("is_active", sa.Boolean(),
                                     server_default=sa.text("1"), nullable=False))

def downgrade() -> None:
    op.drop_column("users", "is_active")

Common schema operations.

op.create_table("tags", sa.Column("id", sa.Integer, primary_key=True))
op.add_column("users", sa.Column("email", sa.String(200)))
op.alter_column("users", "email", existing_type=sa.String(200), nullable=False)
op.create_index("ix_users_email", "users", ["email"])
op.drop_column("users", "email")

A data migration. Use op.execute for DML, and be explicit that it may be irreversible.

def upgrade() -> None:
    op.execute("UPDATE users SET email = name || '@example.com' WHERE email IS NULL")

def downgrade() -> None:
    raise NotImplementedError("email backfill cannot be undone")

Batch mode for SQLite. SQLite cannot ALTER COLUMN, so Alembic rebuilds the table.

with op.batch_alter_table("users") as batch_op:
    batch_op.alter_column("email", existing_type=sa.String(200), nullable=False)

The commands you run day to day.

alembic upgrade head          # apply everything
alembic upgrade +1            # apply the next revision only
alembic downgrade -1          # go back one revision
alembic downgrade base        # remove all application tables
alembic current               # what is applied here?
alembic history               # the full chain
alembic heads                 # the newest revision(s)
alembic check                 # fail if models differ from the DB (CI)

Offline SQL for review.

alembic upgrade base:head --sql

Examples: simple to real

Example 1 — first migration, end to end.

alembic init alembic
# edit alembic.ini -> sqlalchemy.url
# edit alembic/env.py -> target_metadata = Base.metadata
alembic revision --autogenerate -m "create users and posts"
alembic upgrade head
alembic current

After upgrade, your tables exist and alembic_version holds one row: the revision id. alembic current prints that id.

Example 2 — add a column and reverse it.

# autogenerated
def upgrade() -> None:
    op.add_column("users", sa.Column("is_active", sa.Boolean(),
                                     server_default=sa.text("1"), nullable=False))

def downgrade() -> None:
    op.drop_column("users", "is_active")

alembic upgrade head adds the column; alembic downgrade -1 removes it. Test the downgrade in a staging database, not for the first time in an incident.

Example 3 — backfill existing rows.

Adding a column gives old rows NULL. A data migration fills them in.

def upgrade() -> None:
    op.execute("UPDATE users SET email = name || '@example.com' WHERE email IS NULL")

Keep this in its own revision, after the schema revision that added the column. One revision, one purpose.

Example 4 — the safe way to add a required column.

Adding NOT NULL to an existing table with rows fails without a default. Alembic on SQLite reports:

sqlite3.OperationalError: Cannot add a NOT NULL column with default value NULL

The production-safe sequence is three steps, spread across revisions for a large table:

# Step 1: add the column as nullable (no lock-heavy rewrite)
op.add_column("users", sa.Column("nickname", sa.String(50)))

# Step 2: backfill the existing rows
op.execute("UPDATE users SET nickname = 'unknown' WHERE nickname IS NULL")

# Step 3: tighten the constraint, using batch mode on SQLite
with op.batch_alter_table("users") as batch_op:
    batch_op.alter_column("nickname", existing_type=sa.String(50), nullable=False)

Example 5 — branches and merge.

Two people add revisions from the same head. Now there are two heads.

alembic revision -m "branch a" --head=8dd7387349a6
alembic revision -m "branch b" --head=8dd7387349a6 --splice   # second head
alembic heads                    # shows two heads
alembic merge -m "merge a and b" d1ad4c192389 6706828716fa
alembic heads                    # back to one

The merge revision has a tuple down_revision = ("d1ad4c192389", "6706828716fa"). Until you merge, alembic upgrade head is ambiguous, so use alembic upgrade heads in the meantime.

Example 6 — review SQL and detect drift in CI.

alembic upgrade base:head --sql     # prints CREATE TABLE / ALTER TABLE / UPDATE
alembic check                       # fails if a model change has no migration

alembic check is the guard that keeps the models and migrations honest. It prints the pending operations and exits non-zero, which is exactly what a CI job needs.

In production

• Always review an autogenerated migration. It is a diff, not a decision. Check every operation, and make sure nothing that should be an ALTER was generated as a drop-plus-create.
• Never edit a migration that has already run. Other databases recorded its id. Add a new revision instead. Editing history makes environments diverge silently.
• Rename with intent. Autogenerate cannot detect a rename; it emits a drop and an add, which loses data. Use op.rename_table or op.alter_column("users", "old", new_column_name="new") by hand.
• Adding a NOT NULL column to a populated table needs a server_default or a backfill. Verified failure on SQLite: Cannot add a NOT NULL column with default value NULL. The safe order is add nullable, backfill, then tighten.
• Backfill in batches, not one giant UPDATE. A single update on a large table takes a long lock, bloats the transaction log, and can time out. Chunk it by id range inside the migration, and make each chunk idempotent.
• Mind lock duration on large ALTER TABLEs. Some operations rewrite the table and hold a strong lock. On PostgreSQL, set a short lock_timeout, add indexes with CONCURRENTLY, and schedule the migration in a low-traffic window.
• Keep schema changes backward-compatible for one deploy. Deploy order is usually migrate, then app. Add columns and tables first; drop them in a later release after the code stops using them.
• Separate schema and data migrations. One revision, one purpose. It makes review, rollback, and reasoning much easier.
• Write and test downgrade(). A migration with no working downgrade is a one-way door. For genuinely irreversible data changes, raise NotImplementedError so the intent is explicit rather than silent.
• Use batch mode on SQLite. SQLite cannot alter a column in place, so use op.batch_alter_table, which rebuilds the table. This is for local development and tests; production schema changes are not really a SQLite concern.
• One head in main. Two heads mean two independent schema histories. Merge promptly, and put alembic check in CI so a model change without a migration fails the build.
• Stamp only when adopting an existing database. alembic stamp head marks a database as current without running anything. It is the right tool for bringing a pre-existing schema under Alembic, and the wrong tool for skipping a migration.

Interview questions

1. What is a database migration, and why not just run SQL by hand?

Answer. A migration is a versioned, ordered script with an upgrade() and a downgrade(). Hand-run SQL has no record, no ordering, and no rollback, so environments drift and deploys fail unpredictably. Migrations make schema changes reviewable, reproducible, and reversible, and Alembic records the applied revision in alembic_version.

Follow-up: “What does a migration contain exactly?” A revision id, a down_revision pointer, and the two functions. The id and pointer form a chain; the functions contain DDL and optionally DML.

Trap. Treating migrations as a deployment script instead of source code. They belong in the repository and in code review.

2. How does autogenerate work, and what does it miss?

Answer. Autogenerate compares your SQLAlchemy metadata to the live database and writes the difference as a new revision. It detects added and dropped tables, columns, and indexes. It cannot reliably detect renames (it produces drop-plus-add, which loses data), and it can miss server defaults, some type changes, and custom constraints. Every generated migration must be reviewed.

Follow-up: “Why would autogenerate produce an empty migration?” Usually because env.py did not import the model modules, so target_metadata is empty, or because the database is already at the model state.

Trap. Trusting the diff blindly. A silent drop-plus-add is a data-loss incident, not a style issue.

3. Why does Alembic need target_metadata, and what breaks if it is wrong?

Answer. target_metadata is the in-memory description of what the schema should be. Autogenerate diffs that against the live database. If it is None or imports only Base without the model modules, the metadata is empty and Alembic thinks every table should be dropped.

Follow-up: “What is the fix?” Import the modules that define the models, directly or through a central models/__init__.py, so importing Base also registers every table.

Trap. Importing a model file that does not define the tables you think, or having several Base objects from different modules. Use one metadata object for the whole application.

4. How does Alembic track which migrations have run?

Answer. It reads the row (or rows, when the history is branched) in the alembic_version table. Each revision’s upgrade() runs, then the stored revision id is updated to the new one. alembic current prints it; alembic history shows the full chain. Running the same upgrade twice is a no-op because the recorded revision never moves backward.

Follow-up: “What if that table is missing?” Alembic assumes the database is at base and plans to run everything. On a database that already has tables, that is dangerous, which is what alembic stamp is for.

Trap. Deleting the alembic_version row to “reset” a database. It makes Alembic try to re-create existing objects.

5. What are heads, branches, and merges?

Answer. A head is a revision with no children. Normally there is one. Two revisions that share a down_revision create a branch and therefore two heads, which makes upgrade head ambiguous. alembic merge creates a merge revision whose down_revision is the tuple of branch heads, rejoining the history into one head.

Follow-up: “How do branches usually appear?” Two developers generate migrations from the same starting revision and both merge. The fix is to merge promptly, or to rebase and regenerate one of the migrations.

Trap. Running alembic upgrade heads forever instead of merging. Two histories accumulate, and the schema becomes hard to reason about.

6. How do you add a required column to a large, populated table safely?

Answer. In three steps, because a NOT NULL column with no default cannot be added to a populated table. First add it as nullable. Then backfill existing rows in batches. Then set nullable=False in a separate revision. Splitting the steps keeps each lock short and lets the app deploy between them.

Follow-up: “Why not just add server_default and be done?” That works for a constant default and is often fine. But a computed backfill still needs a data migration, and setting a volatile default can force an expensive rewrite.

Trap. Adding the column as NOT NULL directly and discovering it fails in production because the table is not empty. It passes on an empty test database.

7. What is offline mode, and why would you use it?

Answer. Offline mode (--sql) renders the SQL that the migrations would run, without connecting to a database. It is used to review a schema change in a pull request, to hand the SQL to a DBA, or to run it through a controlled pipeline. Online mode is the normal path: Alembic connects and executes.

Follow-up: “What do you lose offline?” Alembic cannot read the current revision, so you must tell it a start and end range, such as base:head. It still emits the alembic_version INSERT/UPDATE statements, but nothing executes them — you get the SQL, not live bookkeeping.

Trap. Assuming offline output is always runnable as-is. It still needs review, and some operations behave differently by dialect.

8. What does a production-safe migration process look like?

Answer. Migrations are committed and reviewed with the code. CI runs alembic check so a model change without a migration fails. Deploys run alembic upgrade head before the new application code starts, and each release keeps the schema backward-compatible for one version. Expensive backfills are batched and scheduled, downgrade() is tested, and irreversible changes are flagged.

Follow-up: “What if a migration fails halfway?” Each migration runs in a transaction where the database supports transactional DDL. If it fails, the transaction rolls back and alembic_version does not move. Fix the migration as a new revision and redeploy.

Trap. Running migrations from every application instance at startup. Several replicas race to apply the same revision. Run migrations once, as a separate step, before rolling out the app.

Remember this

• A migration is versioned code with an upgrade() and a downgrade(); alembic_version records what has run.
• Autogenerate is a draft. Review it; it cannot see renames and will drop-and-add instead.
• NOT NULL on a populated table needs add-nullable → backfill → tighten. Batch mode does it on SQLite.
• One head in main. Merge branches, and run alembic check in CI to catch missing migrations.
• Deploy migrations once, before the app. Keep changes backward-compatible for one release.