skip to content

Revision Chains & Deploys

Revisions link through down_revision into a chain that upgrade and downgrade walk, and parallel branches leave two heads to merge. Interviewers ask how a migration runs safely on deploy.

on this pageshow

explore

questions

6

In Alembic, how do `revision`, `down_revision` and the `alembic_version` table decide which scripts `alembic upgrade head` runs?

level: juniorimportance: must knowfreq 50%

answer

  1. a pointer to the parent script
  2. a graph, not file name order
  3. one stored row per current head
  4. path from stored revision to target

basics

~20 s

Each Alembic script names itself in revision and its parent in down_revision, forming a chain. alembic upgrade head reads the stored revision from alembic_version, runs every upgrade() between it and the newest script, and records each step.

solid answer

~40 s

Every revision file declares `revision` (its own id) and `down_revision` (the id it builds on, `None` for the first script). Alembic builds a graph from those pointers; file names and creation dates play no part in the order. A database's position lives in its `alembic_version` table: one `version_num` column holding the current head revision, one row per head, not a log of every script ever applied. `alembic upgrade head` creates that table if it is missing, reads it, computes the path to the newest revision, runs each script's `upgrade()` and updates the row after each step; at head there is nothing to run. Other targets walk the same graph: `upgrade +1`, a revision id or a unique prefix of one, `downgrade -1`, and `downgrade base`, which runs every `downgrade()` and leaves the table empty.

code

python · 20 lines
python
"""add due date

Revision ID: 2b3c4d5e6f7a
Revises: 1a2b3c4d5e6f
"""
from alembic import op
import sqlalchemy as sa

revision = "2b3c4d5e6f7a"
down_revision = "1a2b3c4d5e6f"  # the parent; None on the first script
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column("invoices", sa.Column("due_date", sa.Date(), nullable=True))


def downgrade() -> None:
    op.drop_column("invoices", "due_date")

go deeper

for a junior

Recall that down_revision names the parent, that alembic_version stores where the database is, and that upgrade head runs only what is missing.

for a middle

Explain how Alembic builds a graph from the pointers, why the version table holds heads rather than a history, and how relative targets such as +1 and -1 resolve.

for a senior

Show you can read alembic current and history to diagnose a database in an unexpected state, including an unknown identifier in the version table.

for a principal

Weigh what it means that the version table stores only a position: no checksums, so the team's review and release discipline must guard scripts that already ran.

## What a revision script declares Alembic keeps each schema change in its own Python file under the `versions/` directory. Near the top of every file sit four module-level names: - **`revision`**: the script's own identifier. `alembic revision` generates a random 12-character hex string such as `2b3c4d5e6f7a`. - **`down_revision`**: the identifier of the script this one builds on, or `None` for the first script in the chain, the **base**. - **`branch_labels`** and **`depends_on`**: usually `None`; they name branches and declare dependencies across them. Below them sit two functions: `upgrade()`, which applies the change through `alembic.op` (for example `op.add_column()`), and `downgrade()`, which reverses it. ```python revision = "2b3c4d5e6f7a" down_revision = "1a2b3c4d5e6f" # the parent, not the child ``` The order lives entirely in the `down_revision` pointers. Alembic reads every file in `versions/`, builds a graph from child to parent and walks it. File names, the `Create Date` line in the docstring and the digits of the identifiers play no part, which is why identifiers can be random: they only have to be unique. A script that no other script names as its parent is a **head**. ## How Alembic records where a database is A database's position is stored in a table Alembic creates inside that database, `alembic_version` by default. It has one column, `version_num` (a `VARCHAR(32)` primary key), and holds **the current head revision, one row per head**. In a linear chain that is exactly one row. The name is a default, not a fixed rule: `context.configure()` in `env.py` accepts `version_table` and `version_table_schema`, which lets two independent chains share one database, each with its own version table. Whatever it is called, Alembic creates it on the first online command that needs it, so a brand-new empty database needs no preparation before `alembic upgrade head`. That table is not a log. Alembic keeps no row per applied script, no timestamp and no checksum; it stores where the database is now and derives the rest by walking `down_revision` pointers back from there. Two consequences follow: - Editing a script that a database has already passed changes nothing on that database, because Alembic never revisits it there. - If the table holds an identifier that no script in `versions/` declares, commands that read it, such as `current`, `upgrade` and `stamp`, fail with `Can't locate revision identified by '...'`. ## What `alembic upgrade head` does 1. Connects with the URL configured in `alembic.ini` or `env.py`, and creates `alembic_version` if it does not exist yet. 2. Reads the current revision from it; an empty database has none. 3. Resolves `head` to the newest revision and computes the path from the current revision to it. 4. For each step, runs that script's `upgrade()`, then updates `version_num` to the script's `revision`. 5. Stops at the target. A database already at head has no path, so nothing runs and nothing is re-executed. Each step is logged, for example `Running upgrade 1a2b3c4d5e6f -> 2b3c4d5e6f7a, add due date`. ## Targets other than head | Command | What it runs | |---|---| | `alembic upgrade head` | every script between the current revision and the newest | | `alembic upgrade +1` | exactly one step forward | | `alembic upgrade 2b3c` | up to that revision; a unique prefix of an identifier is accepted | | `alembic upgrade 2b3c+2` | that revision plus two further steps | | `alembic downgrade -1` | the current script's `downgrade()`, one step back | | `alembic downgrade base` | every `downgrade()` back to the start | After `alembic downgrade base` the objects the scripts created are gone, and `alembic_version` still exists with no rows: Alembic deletes its version row but does not drop its own table. ## Reading the state - `alembic current` prints the revision stored in the database, marked `(head)` when it matches the newest script. - `alembic history` prints the chain from newest to base, ending in a line such as `<base> -> 1a2b3c4d5e6f, create invoices`. - `alembic heads` prints the newest revision; two lines there mean the chain has forked and needs a merge. - `alembic upgrade head --sql` prints the SQL instead of running it, for review before a release. ## Mistakes that reveal a weak model - Assuming scripts run in file name order, and renaming files to "fix" the order. - Treating `alembic_version` as a history table and expecting a row per applied script. - Expecting `upgrade head` to re-run the latest script after someone edited it. - Pointing `down_revision` at the newer script instead of the older one it builds on.

  • Why does Alembic use random hex identifiers rather than sequential numbers?
    Because order comes from `down_revision`, an identifier only has to be unique. Random 12-character ids make it practically impossible for two developers to mint the same one; parallel work instead shows up as two scripts naming the same parent, which Alembic reports as two heads to merge rather than a silent numbering clash. Any unique prefix can be typed on the command line.
  • What does `alembic upgrade head` do on a database that is already at head?
    Nothing. Alembic finds no path between the stored revision and the target, runs no `upgrade()` and leaves `alembic_version` unchanged. An edited script is not re-applied; to re-run one you would step back with `alembic downgrade -1` and forward again, or ship the correction as a new revision.
  • Can `alembic_version` ever hold more than one row?
    Yes. When the chain has forked and both branches are applied, for example with `alembic upgrade heads`, the table holds one row per head. A merge revision, whose `down_revision` lists both heads, collapses them back into a single row when it runs.

A bookmark in a serial whose every chapter names the chapter before it: the bookmark records only where you stopped, not which pages you read, and you find your way forward by following the chapter links, never by page titles.

  • Alembic orders revision scripts by file name or creation date.
  • alembic_version keeps one row for every revision ever applied.
  • Running upgrade head again re-executes the latest upgrade() function.
  • downgrade base drops the alembic_version table along with the schema.
  • down_revision points at the newer script that follows this one.
open as a page

Why does an Alembic revision that changes a column's type fail on SQLite, and what does `op.batch_alter_table()` do instead?

level: middleimportance: should knowfreq 30%

basics

~20 s

SQLite's ALTER TABLE cannot change a column's type, and Alembic refuses constraint changes on SQLite, so plain operations fail there. op.batch_alter_table() collects the changes and rebuilds the table: create a new one, copy the rows, drop the old, rename.

open as a page

In Alembic, when should you run `alembic stamp head` rather than `alembic upgrade head`, and what does stamp change in the database?

level: middleimportance: should knowfreq 35%

basics

~20 s

alembic stamp writes a revision id into alembic_version without running any upgrade() or downgrade(). Use it when the schema already matches that revision, such as a database built with MetaData.create_all() or one just adopting Alembic.

open as a page

When `alembic upgrade head` fails partway through several new revisions, what state is the database left in on PostgreSQL versus MySQL, and how do you recover?

level: seniorimportance: should knowfreq 28%

basics

~20 s

On PostgreSQL, Alembic by default runs the whole upgrade in one transaction, so a failure rolls everything back. On MySQL, DDL commits as it runs: earlier revisions stay applied and recorded, and the failing one can leave half its changes unrecorded.

open as a page

After two feature branches each add an Alembic revision on the same parent, why does `alembic upgrade head` fail, and how do you fix it?

level: seniorimportance: should knowfreq 42%

basics

~20 s

Both revisions name the same down_revision, so the chain forks into two heads and the target head no longer means one revision. alembic merge heads writes an empty revision whose down_revision lists both, giving the chain one head again.

open as a page

In Alembic, what does `alembic upgrade --sql` produce for a DBA to review, and how must a data step be written to appear in it?

level: middleimportance: nice to knowfreq 22%

basics

~20 s

With --sql, Alembic connects to nothing and prints the DDL, data statements and alembic_version updates it would run. Data steps must be statements Alembic can render, op.execute() and op.bulk_insert() with inline values, never code that reads rows.

open as a page