Chapter 2: Alembic’s autogenerate is great — and lies sometimes. We cover what it sees, what it misses, and how to review every generated migration.

What it detects

  • New tables.
  • Dropped tables.
  • New columns.
  • Dropped columns.
  • Type changes (with compare_type=True).
  • Server default changes (with compare_server_default=True).
  • Constraints (PK, FK, UNIQUE, CHECK).
  • Indexes.

What it misses

  • Renamed tables / columns (sees as drop + create).
  • Reorderings (column order rarely matters).
  • Data migrations.
  • Complex CHECK constraint changes (sometimes).
  • DB-specific features (some Postgres types, exclusion constraints).
  • Trigger / function changes.
  • Materialized views.

Renames

# You changed: column 'name' to 'full_name'
# Autogenerate produces:
op.add_column("users", sa.Column("full_name", sa.String, ...))
op.drop_column("users", "name")

This drops data. Edit manually:

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

Always check for renames; autogenerate doesn’t infer them.

Common cleanup

def upgrade():
    # ### commands auto-generated by Alembic ###
    op.add_column("users", sa.Column("status", sa.String(20), nullable=False))
    # ### end ###

If status doesn’t have a server default, this fails on existing rows. Autogenerate doesn’t know your data; you need to:

def upgrade():
    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:

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

server_default doesn’t lock the table in PG 11+ for nullable→not-null; check.

Run autogenerate

alembic revision --autogenerate -m "add status to users"

Look at the generated file. If empty: nothing changed. If full: review.

Review checklist

For every autogenerated migration:

  • Drops + creates that should be renames?
  • NOT NULL adds with no default — backfill needed?
  • Big-table changes — should be CONCURRENTLY?
  • FK changes — check ON DELETE / UPDATE behavior.
  • Type changes — data conversion needed?
  • Index changes — drop one + create another for free? Compare to current.
  • Constraints — name conflicts?
  • Multi-step downgrade still correct?

Empty migration

alembic revision -m "manual"

For changes Alembic can’t autogenerate. Edit by hand.

Inspect the diff

For larger changes, see what changed:

git diff src/myapp/models.py

Compare to the autogenerated migration. They should align.

Debugging autogenerate

If autogenerate misses something:

  1. Models not imported: Base.metadata is empty. Force import.
  2. compare_type=True not set: type changes invisible.
  3. include_schemas=True missing for non-public schemas.
  4. Tables you don’t manage: filter via include_object.
  5. Subclasses / mixins: ensure they hit Base.metadata.

Comparing to live DB

Autogenerate compares Base.metadata to the target_db. So:

alembic upgrade head     # bring DB to current model state
# now: edit models
alembic revision --autogenerate -m "..."

If you skip the upgrade: autogenerate may produce a migration that’s ahead of your real DB.

Drift detection in CI

# tests/test_drift.py
from alembic.autogenerate import compare_metadata
from alembic.migration import MigrationContext

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

Catches forgotten migrations. CI fails until you alembic revision --autogenerate.

Helping autogenerate with hints

Set comments:

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

Comments survive in DDL. Useful for documentation.

SQLAlchemy types autogenerate misses

  • Postgres ENUM: name change requires manual migration.
  • TSVECTOR generated columns: alter dependencies; manual.
  • Triggers / functions: not in metadata; always manual.
  • Postgres extensions: op.execute("CREATE EXTENSION ...") manually.

Writing the migration manually

For things autogenerate misses:

def upgrade():
    op.execute("CREATE EXTENSION IF NOT EXISTS pg_trgm")
    op.create_index(
        "ix_posts_title_trgm",
        "posts",
        ["title"],
        postgresql_using="gin",
        postgresql_ops={"title": "gin_trgm_ops"},
    )

def downgrade():
    op.drop_index("ix_posts_title_trgm", "posts")
    op.execute("DROP EXTENSION IF EXISTS pg_trgm")

Standard op.* API; full SQLAlchemy DDL access.

Common mistakes

1. Trusting autogenerate blindly

Renames-as-drop-and-create. Always review.

2. NOT NULL without default on existing data

Migration fails on apply.

3. Not running alembic upgrade head before --autogenerate

Migration includes already-applied changes.

4. Models not imported

Empty migration. Force import in env.py.

5. Skipping naming conventions

ck_1 constraint names. Set conventions in Base.metadata.

What’s next

Chapter 3: writing migrations manually.

Read this next

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .