# Alembic, From Zero

> Schema migrations for Python and SQLAlchemy with Alembic: autogenerate from your models, review the diff, and apply with upgrade/downgrade.


---

# Alembic, From Zero

You changed a model in your SQLAlchemy code, the table in the database still has the old shape, and now they disagree. Your app boots, then explodes the first time it touches that column. Alembic is the tool that keeps your models and your real database in step, version by version, with a paper trail you can move forward and backward through.

The thing that trips everyone up is autogenerate. It reads your models, looks at the database, and writes a migration for you. It feels like magic, and most of the time it is right. But it quietly misses things, and if you ship its output without reading it, it will eventually drop a column you meant to keep or skip a change you needed. This guide teaches you to drive it, not trust it blind.

## How to read this

Read the three phases in order. Phase 1 builds the mental model: what a migration is, how Alembic pairs with SQLAlchemy, and why a version table on the database is the whole trick. Phase 2 is the daily loop you will actually run: edit a model, autogenerate, review, upgrade. Phase 3 is the reality of a team: the autogenerate trap in detail, plus branching, multiple heads, and merging them back together.

If you want the vendor-neutral picture of why migrations exist at all, the companion guide [Database Migrations](/guides/database-migrations) covers the concept across tools. This guide is the SQLAlchemy-specific one.

## The phases

1. [The version table is the whole idea](01-the-version-table-is-the-whole-idea.md) - what a migration is and how Alembic tracks where your database is.
2. [The daily loop: autogenerate, review, upgrade](02-the-daily-loop.md) - the workflow you run every time a model changes.
3. [The autogenerate trap, heads, and merges](03-the-autogenerate-trap-and-heads.md) - what autogenerate misses, and how branching and merging work on a team.


---

# The version table is the whole idea

You have two pictures of your schema and they keep drifting apart. One is in your Python code: the SQLAlchemy models, the `Column` definitions, the relationships. The other is the actual database on disk, with its real tables and types. When you write code you change the first picture. The second picture does not change until something runs SQL against it. Alembic is that something, and it does the job in a way you can trust because it never loses track of where the database currently is.

## The problem before the tool

Without a migration tool, you keep the two pictures in sync by hand. You add a column to a model, then you open a SQL client and type `ALTER TABLE users ADD COLUMN ...`. This works exactly once, on your machine. Your teammate pulls your code, their database does not have the column, and their app breaks. Production is a third copy with its own history. Now you are tracking, in your head, which `ALTER` statements have run on which database. That memory is the bug.

A migration is the fix: a single, ordered, version-controlled change to the schema, written down as a file. Each migration knows how to apply itself (`upgrade`) and how to undo itself (`downgrade`). The set of migrations, run in order, builds any database from empty to current. The companion guide [Database Migrations](/guides/database-migrations) makes this case in full and tool-neutral; here we make it concrete with Alembic.

## How Alembic knows where you are

Here is the one mechanism that makes everything else work. Alembic writes a tiny table into your database called `alembic_version`. It has one column and (usually) one row, holding the id of the last migration that was applied.

```sql
SELECT * FROM alembic_version;
-- version_num
-- -------------
-- 7c2e1a9b3f04
```

*What just happened:* the database told you, in its own words, exactly which migration it is sitting at. The id `7c2e1a9b3f04` matches a filename in your migrations folder. Alembic does not guess from the schema shape; it reads this row.

This is why migrations are safe to run on any copy. Alembic reads `alembic_version`, looks at your chain of migration files, and runs only the ones that come *after* the recorded id. Run `upgrade` on a fresh database and it runs all of them. Run it on production and it runs only the new ones. The version table is the source of truth for "where is this particular database."

## Each migration points at its parent

Migration files are not only an alphabetical list. Each one records its own id and the id of the migration it follows. That forms a chain (a linked list, if you like), and the most recent migration in a chain is called the **head**.

```python
# inside a migration file: alembic/versions/7c2e1a9b3f04_add_email.py
revision = "7c2e1a9b3f04"        # this migration's id
down_revision = "5982c6f1a2bd"   # the one it comes after
```

*What just happened:* the file declared its place in the chain. `revision` is "I am this version," `down_revision` is "I come right after that version." Alembic walks `down_revision` pointers to figure out the order, never the filenames or timestamps.

Because the order lives inside the files, two people can write migrations at the same time and Git will merge the files cleanly. The catch is that you can end up with two migrations both pointing at the same parent, which means two heads. That is a normal team situation, not an error, and Phase 3 shows you how to merge them.

## The pieces, named once

A few terms you will see constantly. Learn them now and the rest reads easily.

- **Revision** - one migration. A file in `alembic/versions/` with an `upgrade()` and a `downgrade()`.
- **`upgrade()`** - the function that moves the schema forward (create a table, add a column).
- **`downgrade()`** - the function that reverses that exact change. You write both, even when you doubt you'll downgrade.
- **Head** - the newest revision in a chain; the one you upgrade *to* by default.
- **`alembic_version`** - the table in your database holding the currently-applied revision id.
- **`env.py`** - Alembic's config script. It knows your database URL and your models' metadata, so autogenerate has both pictures to compare.

```text
alembic/
  env.py              # config: DB URL + your models' MetaData
  script.py.mako      # template for new migration files
  versions/
    5982c6f1a2bd_create_users.py
    7c2e1a9b3f04_add_email.py   <- head
alembic.ini           # the .ini Alembic reads first
```

*What just happened:* you saw the standard layout `alembic init` creates. The `versions/` folder is your migration history; `env.py` is where Alembic learns about your specific database and models. Everything in Phase 2 happens in this structure.

> The pairing with SQLAlchemy matters: Alembic does not invent its own model system. It reads the same `MetaData` your app already defines. That shared metadata is what makes autogenerate possible - and, as Phase 3 explains, what bounds its blind spots. If the ORM side feels fuzzy, [How an ORM works](/guides/how-an-orm-works) is the companion for that.

## In the wild

On a real team, the migrations folder becomes a readable history of the schema. You can open any old revision and see what the database looked like at that point, and `git blame` tells you who changed it and when. New engineers run one `upgrade` command and their local database matches everyone else's. That is the payoff: the schema stops being tribal knowledge in someone's head and becomes a reviewed, ordered, reversible record in the repo.

```quiz
[
  {
    "q": "How does Alembic know which migrations still need to run on a given database?",
    "choices": [
      "It compares the schema shape to your models and infers what's missing",
      "It reads the recorded revision id from the alembic_version table and runs anything after it",
      "It runs every migration every time and ignores duplicates",
      "It checks the file modification timestamps in the versions folder"
    ],
    "answer": 1,
    "explain": "Alembic reads the current revision id from the alembic_version table and runs only the migrations that follow it in the chain."
  },
  {
    "q": "What does down_revision inside a migration file record?",
    "choices": [
      "The id of the migration this one comes right after",
      "Instructions for how to downgrade this migration",
      "The database URL to downgrade against",
      "The timestamp when the migration was created"
    ],
    "answer": 0,
    "explain": "down_revision points at the parent revision, forming the ordered chain Alembic walks; the order lives in the files, not the filenames."
  },
  {
    "q": "Why is it safe to run the same upgrade command on a fresh local DB and on production?",
    "choices": [
      "Alembic always wipes and rebuilds the database first",
      "Each runs all migrations because Alembic is stateless",
      "Each DB records its own current revision, so Alembic runs only the migrations that DB is missing",
      "Production migrations are stored separately from local ones"
    ],
    "answer": 2,
    "explain": "Because alembic_version is per-database, Alembic applies only the revisions that particular database hasn't reached yet."
  }
]
```


---

# The daily loop: autogenerate, review, upgrade

Once Alembic is wired up, almost every schema change you ever make follows the same four beats: change a model, generate a migration, read the migration, apply it. The whole loop takes a minute when nothing surprises you. The one beat people skip is "read the migration," and that is exactly the beat Phase 3 is about. For now, let's make the loop a habit.

## One-time setup

You run this once per project. `alembic init` creates the folder structure from Phase 1.

```bash
pip install alembic
alembic init alembic
```

*What just happened:* Alembic created an `alembic/` directory and an `alembic.ini` file. The directory has the `env.py`, the template, and an empty `versions/` folder. Nothing touched your database yet.

Two edits make autogenerate work. First, point Alembic at your database. The simplest place is `alembic.ini`:

```ini
# alembic.ini
sqlalchemy.url = postgresql://user:pass@localhost/myapp
```

Second, give `env.py` your models' metadata so autogenerate has something to compare the database against. Open `env.py` and set `target_metadata`:

```python
# alembic/env.py
from myapp.models import Base   # your SQLAlchemy declarative Base
target_metadata = Base.metadata
```

*What just happened:* you handed Alembic both pictures. The URL is the live database; `target_metadata` is the shape your models declare. Autogenerate is nothing more than the difference between these two. Without this line, autogenerate sees no models and generates empty migrations.

> Real projects rarely hardcode credentials in `alembic.ini`. A common pattern is to leave `sqlalchemy.url` blank and set it in `env.py` from an environment variable, so the same config works in dev, CI, and production. Keep secrets out of the repo.

## Beat 1: change a model

You decide users need a `created_at` timestamp. You change the Python, as you would for any feature.

```python
# myapp/models.py
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    email = Column(String, nullable=False)
    created_at = Column(DateTime, nullable=False)   # the new column
```

*What just happened:* your model now describes a column the database does not have. The two pictures disagree. Your job for the rest of the loop is to write that disagreement down as a migration.

## Beat 2: autogenerate the migration

This is the command you will type most. The `-m` is a human label that becomes part of the filename.

```bash
alembic revision --autogenerate -m "add created_at to users"
```

```text
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.autogenerate.compare] Detected added column 'users.created_at'
  Generating alembic/versions/9f3b2c7d1e08_add_created_at_to_users.py ... done
```

*What just happened:* Alembic connected to the database, compared its real tables to your `target_metadata`, found one difference, and wrote a new migration file. The line `Detected added column` is Alembic narrating what it noticed. It did **not** change the database - it only wrote a file.

Compare this with plain `alembic revision -m "..."` (no `--autogenerate`), which writes an empty migration with blank `upgrade()`/`downgrade()` bodies for you to fill in by hand. You'll want that for changes autogenerate can't see; Phase 3 covers which ones.

## Beat 3: read what it wrote

Open the generated file. This is the beat that separates people who trust Alembic from people who get burned by it.

```python
# alembic/versions/9f3b2c7d1e08_add_created_at_to_users.py
revision = "9f3b2c7d1e08"
down_revision = "7c2e1a9b3f04"

def upgrade():
    op.add_column("users", sa.Column("created_at", sa.DateTime(), nullable=False))

def downgrade():
    op.drop_column("users", "created_at")
```

*What just happened:* Alembic turned the detected difference into `op.add_column` (forward) and `op.drop_column` (reverse). Read both. Ask: is the forward change what I meant? Does the downgrade truly reverse it? Here, both are correct.

But notice the trap already: `nullable=False` on a table that already has rows will fail, because every existing row would have a NULL `created_at`. Autogenerate wrote what your model says, not what your data needs. You'd fix this by adding a `server_default` or doing it in two steps. The lesson is permanent - **autogenerate drafts, you decide.** Phase 3 catalogs the rest of its blind spots.

## Beat 4: apply it

When the migration reads correctly, run it.

```bash
alembic upgrade head
```

```text
INFO  [alembic.runtime.migration] Running upgrade 7c2e1a9b3f04 -> 9f3b2c7d1e08, add created_at to users
```

*What just happened:* Alembic ran your migration's `upgrade()` against the database and then updated the `alembic_version` row to `9f3b2c7d1e08`. The two pictures now match. `head` means "the newest revision in the chain"; you almost always upgrade to head.

## The commands you'll actually use

A handful covers nearly everything. Keep this list close.

```bash
alembic upgrade head          # apply everything up to the newest revision
alembic upgrade +1            # apply just the next one revision
alembic downgrade -1          # undo the most recent revision
alembic downgrade base        # undo everything, back to empty
alembic current               # which revision is this DB on right now?
alembic history               # the full ordered chain of revisions
```

*What just happened:* you saw the full daily vocabulary. `current` reads the `alembic_version` table from Phase 1; `history` prints the chain. `downgrade -1` is your undo button when you applied something wrong locally - though on production, undoing a migration that already dropped data does not bring the data back.

> Downgrade is for recovering from a mistake you catch quickly, mostly in development. In production, treat a deployed migration as one-way unless you've thought hard about it: `downgrade` of an `add_column` is a `drop_column`, and dropping a column is destroying data. The reverse path exists; it is not free.

## For builders

Wire this into your workflow so the loop is automatic. Most teams run `alembic upgrade head` as a deploy step before the new app code starts, so the schema is always ready when the code that needs it boots. In CI, a useful check is to autogenerate against a fresh database and assert that it produces *no* changes - if it does, someone changed a model without writing a migration, and the build should catch that before it reaches production.

```quiz
[
  {
    "q": "What does `alembic revision --autogenerate -m \"...\"` actually do?",
    "choices": [
      "Applies the schema change directly to the database",
      "Compares your models to the database and writes a migration file, without changing the database",
      "Deletes the alembic_version table and rebuilds it",
      "Downgrades the database by one revision"
    ],
    "answer": 1,
    "explain": "Autogenerate only writes a file describing the detected diff. Nothing hits the database until you run upgrade."
  },
  {
    "q": "Why must you read an autogenerated migration before applying it?",
    "choices": [
      "Because the file is encrypted until you open it",
      "Because Alembic writes what your models say, which may not match what your data or intent needs (e.g. NOT NULL on a populated table)",
      "Because upgrade won't run until the file has been opened in an editor",
      "Because the revision id is wrong until you fix it manually"
    ],
    "answer": 1,
    "explain": "Autogenerate drafts from the model definition; it can produce changes that fail or aren't what you meant, like a non-null column on an existing populated table."
  },
  {
    "q": "After `alembic upgrade head` succeeds, what changed in the database besides the schema?",
    "choices": [
      "Nothing else changed",
      "The alembic_version row was updated to the newly applied revision id",
      "All previous migrations were deleted",
      "The models file was rewritten to match"
    ],
    "answer": 1,
    "explain": "Upgrade runs the migration and then records the new revision id in alembic_version, so the database knows where it now sits."
  }
]
```


---

# The autogenerate trap, heads, and merges

This is the phase that saves you from the 3am page. Autogenerate is genuinely useful and you will lean on it daily, but it is a draft assistant, not an oracle. It compares two pictures and writes down the differences it can see - and there are real, common differences it cannot see. Knowing the blind spots is the difference between a tool you trust and a tool that betrays you. Then we cover the other team reality: branching, multiple heads, and how to merge them.

## What autogenerate sees, and what it misses

Autogenerate compares your models' metadata to the database and detects structural differences: added and removed tables, added and removed columns, and many changes to column type, nullability, indexes, and unique constraints. For everyday "I added a column" work, it nails it.

What it does **not** reliably detect is the trap:

- **Renames.** Rename a column in your model and autogenerate sees a *dropped* column and an *added* column. Apply that blindly and you delete the old column's data, then create an empty new one. It has no way to know `name` became `full_name`; to it, one vanished and one appeared.
- **Table or column name changes** in general - same reason. A rename looks like a delete plus a create.
- **Changes inside server defaults, CHECK constraints, and some type details** depending on backend - frequently missed or rendered imperfectly.
- **Anything not described in your `MetaData`** - data backfills, custom SQL, triggers, views, stored procedures, partial indexes. Autogenerate only knows what SQLAlchemy models declare.

```python
# You renamed `name` to `full_name` in the model.
# Autogenerate writes THIS - and it will lose data:
def upgrade():
    op.add_column("users", sa.Column("full_name", sa.String(), nullable=True))
    op.drop_column("users", "name")
```

*What just happened:* autogenerate turned a rename into a drop-and-add. Run this and every value in `name` is gone before `full_name` ever holds anything. The fix is to hand-edit it into a real rename:

```python
def upgrade():
    op.alter_column("users", "name", new_column_name="full_name")

def downgrade():
    op.alter_column("users", "full_name", new_column_name="name")
```

*What just happened:* `op.alter_column` with `new_column_name` renames in place and keeps the data. This is the canonical autogenerate trap, and the reason "read the migration" is non-negotiable.

> The rule that never expires: **autogenerate proposes, you dispose.** Every autogenerated migration is a pull request from a junior who is fast, tireless, and occasionally about to delete production data. Review it like one. For data changes (backfilling a new column), you write the SQL yourself with `op.execute(...)`, because autogenerate will never generate data movement - it only knows schema.

## Backfilling data: autogenerate's true blind spot

The `nullable=False` problem from Phase 2 is where this bites most. To add a required column to a populated table, you do it in steps, and the middle step is pure SQL that autogenerate would never write.

```python
def upgrade():
    op.add_column("users", sa.Column("created_at", sa.DateTime(), nullable=True))
    op.execute("UPDATE users SET created_at = NOW() WHERE created_at IS NULL")
    op.alter_column("users", "created_at", nullable=False)
```

*What just happened:* you added the column as nullable, filled every existing row with a value, then tightened it to NOT NULL. Each step is safe; the order is the whole point. Autogenerate gives you the first and last lines at best and never the `op.execute` in the middle - that's yours.

## Multiple heads: how the chain forks

Phase 1 said each migration points at its parent, forming a chain ending in a head. On a team, two people branch off the same parent at the same time. Each writes a migration whose `down_revision` is that shared parent. Now the chain forks: two migrations, same parent, and therefore **two heads**.

```text
            ┌── 9f3b2c7d1e08  (Ana: add created_at)   <- head
5982c6f1a2bd
            └── a1c4e8b09d22  (Ben: add is_active)     <- head
```

*What just happened:* both Ana and Ben branched off `5982c6f1a2bd`, so the history is a Y shape with two tips. This is not corruption - it is the normal result of two people working in parallel, and Git merged both files without complaint because each just added a file.

Alembic will tell you the moment it matters, because `upgrade head` becomes ambiguous: which head?

```bash
alembic upgrade head
```

```text
ERROR [alembic.util.messaging] Multiple head revisions are present;
please specify a specific target revision, '<branchname>@head' to
narrow to a specific head, or 'heads' for all heads
```

*What just happened:* Alembic refused to guess. With two heads it can't know which tip you mean, so it stops and asks you to resolve the fork. Check it yourself with `alembic heads`, which lists every current tip.

## Merging heads back together

The fix is a merge migration: a revision with *two* `down_revision` parents that rejoins the fork into a single head. Alembic generates it for you.

```bash
alembic merge -m "merge created_at and is_active" 9f3b2c7d1e08 a1c4e8b09d22
```

```text
  Generating alembic/versions/c7d9...merge_created_at_and_is_active.py ... done
```

```python
# the generated merge migration
revision = "c7d9f1a2b8e3"
down_revision = ("9f3b2c7d1e08", "a1c4e8b09d22")  # two parents

def upgrade():
    pass   # usually empty: it only rejoins the chain

def downgrade():
    pass
```

*What just happened:* the merge revision lists both heads as its parents, so the Y shape now closes back into a single tip. Its `upgrade`/`downgrade` are usually empty because it changes no schema - it exists to make the history linear again. After this, `alembic upgrade head` is unambiguous and runs cleanly.

```text
5982c6f1a2bd ─┬─ 9f3b2c7d1e08 ─┐
              └─ a1c4e8b09d22 ─┴─ c7d9f1a2b8e3   <- single head
```

*What just happened:* both branches now flow into the merge revision, which is the one true head again. The two feature migrations still run; the merge just reunites the chain.

> Two heads is a normal Tuesday, not an emergency. The mistake is hand-editing `down_revision` to force a fake linear order - that can make a migration claim to follow one it actually doesn't, and break replay on a fresh database. Use `alembic merge`; let Alembic keep the parent pointers straight.

## In the wild

The teams that never get burned have two habits. First, every migration is reviewed in the pull request like code, with a reviewer specifically checking for drop-then-add patterns that should have been renames. Second, they test the down path: a CI job that upgrades to head, downgrades to base, and upgrades again proves the migrations are genuinely reversible and replayable. The schema you can rebuild from zero on demand is the schema you actually control.

```quiz
[
  {
    "q": "You rename a model column from `name` to `full_name` and run autogenerate. What does it produce, and why is that dangerous?",
    "choices": [
      "An op.alter_column rename that preserves the data safely",
      "A drop_column plus add_column - which deletes the old column's data because it can't tell a rename from a delete-and-create",
      "Nothing, because renames aren't a schema change",
      "An op.execute backfill that copies the data over automatically"
    ],
    "answer": 1,
    "explain": "Autogenerate sees a rename as one column gone and one appeared, so it drops the old (losing its data) and adds an empty new one. You must hand-edit it into op.alter_column."
  },
  {
    "q": "Why does `alembic upgrade head` error with 'Multiple head revisions are present'?",
    "choices": [
      "The alembic_version table is corrupted",
      "Two migrations share the same down_revision parent, so the chain forked into two tips and Alembic won't guess which one you mean",
      "You forgot to run alembic init",
      "The database URL is wrong"
    ],
    "answer": 1,
    "explain": "Parallel work created two heads off the same parent. 'head' is ambiguous, so Alembic stops and asks you to specify or merge."
  },
  {
    "q": "What is the correct way to resolve two heads, and what does the resulting revision usually contain?",
    "choices": [
      "Hand-edit one migration's down_revision to point at the other; it contains the moved schema",
      "Delete one of the two migrations; it contains nothing",
      "Run `alembic merge` to create a revision with both heads as parents; its upgrade/downgrade are usually empty",
      "Run `alembic downgrade base` and start over"
    ],
    "answer": 2,
    "explain": "A merge revision lists both heads as down_revision parents, rejoining the fork into one head; it changes no schema, so its bodies are typically empty."
  }
]
```