Alembic Cheatsheet 03 — Autogenerate

Cheatsheet for Alembic autogenerate.

Generate

alembic upgrade head                                # ensure DB at current head
alembic revision --autogenerate -m "add status"    # compares Base.metadata to DB

Always upgrade first; otherwise migration includes changes already applied.

What autogenerate detects

New / dropped tables.
New / dropped columns.
Type changes (with compare_type=True in env.py).
Server default changes (with compare_server_default=True).
Constraints: PK, FK, UNIQUE, CHECK (mostly).
Indexes (most types).

What it misses

Renames — sees as drop + add.
Reorderings — column order rarely matters.
Data migrations.
Some CHECK constraint changes.
Postgres-specific: native ENUM value additions, exclusion constraints, generated columns sometimes.
Triggers / functions / views.
Materialized views.

Rename detection (manual)

Autogenerate produces:

op.add_column("users", sa.Column("full_name", sa.String, ...))
op.drop_column("users", "name")

Edit to:

op.alter_column("users", "name", new_column_name="full_name")

Preserves data.

Required env.py settings

context.configure(
    connection=conn,
    target_metadata=target_metadata,
    compare_type=True,                  # detect type changes
    compare_server_default=True,
    include_schemas=False,              # True for multi-schema
    render_as_batch=False,              # True for SQLite
)

Without compare_type=True: type changes silently missed.

include_object filter

def include_object(object, name, type_, reflected, compare_to):
    if type_ == "table" and name in IGNORE_TABLES:
        return False
    return True

context.configure(..., include_object=include_object)

Ignore tables you don’t manage (third-party migrations).

Drift test (CI)

def test_no_drift(engine):
    from alembic.autogenerate import compare_metadata
    from alembic.migration import MigrationContext
    
    with engine.sync_engine.connect() as conn:
        ctx = MigrationContext.configure(conn)
        diff = compare_metadata(ctx, Base.metadata)
        assert diff == [], f"Drift: {diff}"

Forces a migration whenever models drift from DB.

Review checklist for every autogenerated migration

Drops + adds that should be renames?
NOT NULL adds with no default — need backfill?
Big-table changes — should be CONCURRENTLY?
FK changes — check ON DELETE / UPDATE.
Type changes — data conversion needed?
Index changes — drop + add for free? Compare to existing.
Constraint names — match conventions?
Downgrade still correct?

NOT NULL pitfall

Autogenerate produces:

op.add_column("users", sa.Column("status", sa.String, nullable=False))

If existing rows exist: fails. Fix:

# Add nullable + backfill + set NOT NULL
op.add_column("users", sa.Column("status", sa.String(20), nullable=True))
op.execute("UPDATE users SET status = 'active'")
op.alter_column("users", "status", nullable=False)

# OR add with server default (PG 11+ doesn't rewrite)
op.add_column("users", sa.Column("status", sa.String(20), nullable=False, server_default="active"))

Postgres-specific autogenerate

Some Postgres features autogenerate may handle awkwardly:

ENUM additions: manual ALTER TYPE ... ADD VALUE.
Generated columns: sometimes recreated unnecessarily.
Exclusion constraints: manual.
Extensions: manual CREATE EXTENSION.

Helping autogenerate

Comments survive in DDL:

class User(Base):
    __table_args__ = {"comment": "Application users"}

Type stability:

type_annotation_map = {
    datetime: DateTime(timezone=True),
    dict: JSONB,                       # consistent JSONB
}

Common mistakes

Generating without running alembic upgrade head first.
compare_type=False (default).
Trusting drop+add for renames.
Not reviewing — autogenerate output IS NOT final.
Importing models lazily — Base.metadata empty.

Generate#

What autogenerate detects#

What it misses#

Rename detection (manual)#

Required env.py settings#

include_object filter#

Drift test (CI)#

Review checklist for every autogenerated migration#

NOT NULL pitfall#

Postgres-specific autogenerate#

Helping autogenerate#

Common mistakes#

Read this next#