Alembic Autogenerate: What It Detects, What It Misses, How to Fix It

Alembic's --autogenerate is a diff tool, not a code generator you can trust blindly. It compares the SQLAlchemy metadata your application declares with the schema of a live database and writes a migration script for the differences it can recognise. Some differences it cannot recognise at all, and some it recognises wrongly in ways that destroy data. This article lists both kinds, shows the configuration that closes most gaps, and gives a review checklist for the scripts it writes.

How autogenerate works#

alembic revision --autogenerate -m "create visits" connects to the database configured in env.py, reflects its tables, compares them with target_metadata (your models' MetaData), and renders the differences as operations in a new revision file:

"""create visits"""
from alembic import op
import sqlalchemy as sa

revision = '0003'
down_revision = '0002'
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'visits',
        sa.Column('id', sa.Integer, primary_key=True, autoincrement=False),
        sa.Column('pet_id', sa.Integer, sa.ForeignKey('pets.id'), nullable=False),
        sa.Column('visit_date', sa.Date, nullable=True),
        sa.Column('description', sa.String(255), nullable=True),
    )


def downgrade():
    op.drop_table('visits')

Two consequences follow from the mechanism. First, the comparison is against a real database, so the result depends on which database you point it at: run it against a stale development database and it will happily generate changes that production already has. Second, anything that is not represented in MetaData is invisible to it, which is the root of most of the gaps below.

What it detects reliably#

• Tables added or removed.
• Columns added or removed.
• Changes in nullability.
• Indexes and unique constraints added or removed, provided they are named.
• Foreign key constraints added or removed.
• Column type changes, when type comparison is enabled. Recent Alembic versions compare types by default; older projects needed compare_type=True in the context.configure() call of env.py, so check yours.

What it misses or gets wrong#

Renames. A renamed column or table is a removed one and an added one. The generated script drops telephone and adds phone, and the data goes with the drop. Replace the pair with op.alter_column('owners', 'telephone', new_column_name='phone') or op.rename_table by hand, every time.

Server defaults. Not compared unless compare_server_default=True, and even then engines report defaults in their own spelling ('0'::integer, CURRENT_TIMESTAMP), which produces spurious changes on every run until you supply a callable that normalises them.

CHECK constraints. Autogenerate does not produce add or drop operations for them; the same applies to constraints that SQLAlchemy models express through Boolean or Enum types on backends that implement them as checks.

Enum values on PostgreSQL. Adding a value to a native enum type is an ALTER TYPE that autogenerate never writes; you add it with op.execute, outside a transaction on older PostgreSQL versions.

Sequences, views, functions, triggers, policies. None of these are in MetaData, so none are compared. Keep them in hand-written revisions, or in a replaceable-object helper as the Alembic cookbook suggests.

Tables outside your metadata. Every table in the database that is not in target_metadata looks like a table to drop. Tables owned by another application, by an extension, or by the migration tooling itself need an include_object filter in env.py, or an include_schemas restriction.

Unnamed constraints. A constraint the database named for you cannot be dropped by a script that does not know the name. This bites hardest on SQLite and MySQL and is the reason for the next section.

Naming conventions save you later#

Give MetaData a naming convention on day one and every index and constraint gets a deterministic name that autogenerate can compare and later drop:

from sqlalchemy import MetaData

convention = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}

metadata = MetaData(naming_convention=convention)

Adopting a convention on an existing project produces one large migration that renames everything; do it once, review it, and enjoy quiet autogenerate runs afterwards. The same argument for predictable names applies beyond Alembic and is made in database naming conventions.

Batch mode for SQLite#

SQLite implements very little of ALTER TABLE: no dropping of constraints, no changing of types, and until recent versions no dropping of columns. Alembic's batch mode works around it by creating a new table, copying the rows, dropping the old table and renaming, all from a block that reads like ordinary operations:

def upgrade():
    with op.batch_alter_table('pets') as batch_op:
        batch_op.alter_column('name', type_=sa.String(100), existing_type=sa.String(50))
        batch_op.create_foreign_key('fk_pets_owners', 'owners', ['owner_id'], ['id'])

Set render_as_batch=True in env.py and autogenerate emits batch blocks itself; on PostgreSQL and MySQL they degrade to plain ALTER TABLE, so one script serves both the SQLite test database and production. Batch mode still needs names for the constraints it recreates, which is one more reason for the naming convention. The other places where SQLite diverges from a server database are collected in using SQLite in development when production is PostgreSQL.

Reviewing a generated script#

Treat the output as a draft written by an assistant with no context. Read it top to bottom and ask:

1. Is every drop intended? A drop paired with an add of a similar column is a rename.
2. Does the ordering respect foreign keys? Autogenerate usually gets it right; hand edits break it.
3. Does downgrade() really reverse upgrade()? Run both on a copy of the database.
4. Are there operations that need to run outside a transaction, such as concurrent index creation on PostgreSQL? Mark the revision accordingly.
5. Does the script contain anything environment-specific, a schema name or a role, that was reflected from the development database?

Then run alembic check, which compares metadata and database without writing a file and exits with a non-zero status when they differ. In CI, after applying all revisions to a fresh database, a failing alembic check means somebody changed a model and forgot the migration.

Branches and multiple heads#

Two developers who both branch from revision 0003 produce two heads, and alembic upgrade head refuses to guess. alembic heads shows them; alembic merge -m "merge branches" head1 head2 creates an empty revision with two down_revision values that joins them. branch_labels and depends_on exist for deliberately parallel histories, for instance one revision chain per bounded context, but most projects are better off with a single line and a merge revision on the rare occasion two heads appear.

Where SchemaDrawer fits#

The best check of a set of Alembic revisions is what they build from an empty database. SchemaDrawer takes the revision files, supplies its own env.py pointed at a throw-away PostgreSQL, MySQL, MariaDB, SQL Server, Oracle or SQLite of the version you choose, runs alembic upgrade head and returns an ER diagram of the tables, columns, primary keys and foreign keys that exist afterwards. A rename that autogenerate turned into a drop and an add is visible at a glance as a column with the wrong name. The Alembic page describes the upload layout, and the SQLite page notes what to expect from that engine.

Key takeaways#

• Autogenerate diffs MetaData against a live database; it only sees what both sides model.
• Renames come out as drop and add; fix them by hand every time.
• Enable type and, carefully, server default comparison; filter foreign tables with include_object.
• Adopt a naming convention early and use batch mode if SQLite is anywhere in your pipeline.
• Review every script, run downgrade on a copy, and gate CI with alembic check.