# 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."
  }
]
```