Alembic Tested: What Autogenerate Misses

AlembicSQLAlchemyDatabase Migrations

TL;DR: I tested Alembic 1.20.1 on my own machine, and the short version is that --autogenerate is a draft generator, not a schema diff tool. A column rename is detected as "add a column, drop a column" — after upgrade head, 100% of 1,000 rows came back NULL and the exit code was still 0. compare_server_default is off by default, so changing a column default produces a migration whose body is pass, while the version number happily moves forward. And on SQLite, batch_alter_table is a full table rebuild: one non-add_column operation inside the block turned a 0.0106 s add_column into 0.5477 s on a 500,000-row table, a 51.9x jump. Every number below was measured locally, and the scripts are reproduced as-is.

No affiliate links in this article.

The setup, reproducible end to end

I tested on Windows 11 with Python 3.13.14, Alembic 1.20.1, SQLAlchemy 2.1.6 and SQLite 3.53.1. The Alembic version comes from the official repository's Releases API (latest as of writing is rel_1_20_1, published 2026-10-22), and the SQLite version was read from sqlite3.sqlite_version rather than from a package version string.

Every experiment runs in its own throwaway directory: alembic init, write one version of models.py, revision --autogenerate, upgrade head, then open the database with sqlite3 and verify by hand. __pycache__ is deleted after every model edit so the autogenerate step cannot pick up a stale module.

1. Column renames: documented as undetectable, measured as total data loss

The official docs put this in "What does Autogenerate Detect and What Does it detect?" in plain words:

Changes of column name. Like table name changes, these are detected as a column add/drop pair, which is not at all the same as a name change.

And one sentence earlier in the same section, which deserves to be memorized:

> It is critical to note that autogenerate is not intended to be perfect. It is *always* necessary to manually review and correct the candidate migrations that autogenerate produces.

I ran that claim against real rows. The first model was Account(id, email); after creating the table I inserted 1,000 rows and SELECT COUNT(*), SUM(email IS NOT NULL) returned (1000, 1000). Then I renamed email to email_address in the model and ran revision --autogenerate again.

The log produced exactly two lines, delivered in the tone of a weather report:

INFO  [alembic.autogenerate.compare.tables] Detected added column 'account.email_address'
INFO  [alembic.autogenerate.compare.tables] Detected removed column 'account.email'

And the migration it generated:

def upgrade() -> None:
    op.add_column('account', sa.Column('email_address', sa.String(length=100), nullable=True))
    op.drop_column('account', 'email')


def downgrade() -> None:
    op.add_column('account', sa.Column('email', sa.VARCHAR(length=100), nullable=True))
    op.drop_column('account', 'email_address')

upgrade head finished with exit code 0 and no warning. Querying the database gave (1000, 0) — the rows are still there, every email_address is NULL. All 1,000 email addresses, gone. Note that downgrade is not an inverse either: it is another add/drop pair, so rolling back destroys data a second time.

If the renamed column is nullable=False, Alembic cannot even execute the migration:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) Cannot add a NOT NULL column with default value NULL
[SQL: ALTER TABLE account ADD COLUMN email_address VARCHAR(100) NOT NULL]

Exit code 1. Credit where due: the version number did not drift. After the failure alembic_version still held the old revision, and the table still had only id and email. That is more honest than a lot of migration tools.

2. `compare_server_default` is off: change a default, get an empty migration

The docs file "change of server default" under "optionally detect", not "can detect":

> Change of server default. This will occur if you set the EnvironmentContext.configure.compare_server_default parameter to True ... The feature is off by default so that it can be tested on the target schema first.

Measured: I changed server_default="anon" to "guest" in the model and ran revision --autogenerate. The generated body was:

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###

No warning, no hint, exit code 0. Pass compare_server_default=True into context.configure() and the same run immediately produces op.alter_column('t', 'name', existing_type=sa.VARCHAR(length=50), server_default='guest', existing_nullable=False).

A default-value change never errors; it just pretends to have happened. If you do not know this flag exists, you will ship an empty migration and then wonder why new rows still get the old default.

3. `compare_type` has been on since 1.12.0, but only for shared arguments

This one contradicts a lot of older tutorials. The official changelog entry reads:

> Changed in version 1.12.0: The default value of EnvironmentContext.configure.compare_type has been changed to True.

I tested String(50) to String(200) and the log printed Detected type change from VARCHAR(length=50) to String(length=200) on 't.name', with a matching op.alter_column(..., type_=sa.String(length=200), ...) in the migration.

But the docs draw the boundary immediately after: if the two sides do not share the same set of additional arguments, Alembic gives up —

> If either the metadata type or database type has additional arguments beyond that of the other type, these are not compared.

So "a type change is always detected" is the wrong expectation. The right expectation is "shared arguments (length, precision) are compared."

4. On SQLite, batch means rebuild the whole table, and one operation drags the rest down

This is the most expensive number in the article. The batch mode docs describe the mechanism without euphemism:

the existing table structure is reflected from the database, a new version of this table is created with the given changes, data is copied from the old table to the new table using "INSERT from SELECT", and finally the old table is dropped and the new one renamed to the original name.

In SQL that is CREATE TABLE _alembic_batch_temp → INSERT INTO _alembic_batch_temp ... SELECT → DROP TABLE → ALTER TABLE _alembic_batch_temp RENAME TO.

The trigger condition is the important part, quoting the docs:

> the batch context by default only does the "move and copy" process if SQLite is in use, and if there are migration directives other than Operations.add_column() present, which is the one kind of column-level ALTER statement that SQLite supports.

In other words: a single non-add_column operation flips the entire block into rebuild mode, including the add_column that would otherwise have been O(1). I measured three things inside one migration, wrapping each in time.perf_counter():

Rows	plain `add_column`	batch block, `add_column` only	batch block, `add_column` + constraint change	Slowdown
1,000	0.0128 s	0.0112 s	0.0215 s	1.9x
10,000	0.0110 s	0.0109 s	0.0309 s	2.8x
100,000	0.0195 s	0.0108 s	0.1132 s	10.4x
500,000	0.0199 s	0.0106 s	0.5477 s	51.9x

The two curves separate cleanly. Plain add_column sits at 0.011–0.020 s regardless of row count — it is a metadata operation. Batch rebuild time grows linearly with rows. At 500,000 rows the same add_column goes from 0.0106 s to 0.5477 s purely because one alter_column shares the block.

One side effect that is easy to miss: after a single batch rebuild, the 1,000-row database file grew from 36,864 bytes to 61,440 bytes (about 66.7% larger). SQLite does not hand the old table's pages back.

5. Batch rebuild silently drops unnamed CHECK constraints

This one the docs admit outright, and the version boundary is explicit:

Unnamed CHECK constraints continue to be omitted from the table recreate operation; up until version 1.20, this omission was silent. As of 1.20, it emits a warning.

I built a table with two CHECK constraints, one named and one not:

CREATE TABLE t (id INTEGER NOT NULL, n INTEGER,
  CONSTRAINT ck_named CHECK (n > 0), CHECK (n < 1000), PRIMARY KEY (id))

After one batch rebuild:

CREATE TABLE "t" (id INTEGER NOT NULL, n INTEGER NOT NULL, m INTEGER,
  PRIMARY KEY (id), CONSTRAINT ck_named CHECK (n > 0))

ck_named survived. CHECK (n < 1000) did not. Version 1.20 raises a UserWarning at alembic/operations/batch.py:298 reading "Unnamed CHECK constraint on reflected table 't' is being omitted from the table recreate; pass the constraint explicitly using batch_alter_table.table_args." Before 1.20, this was completely silent.

6. Since SQLite 3.53, NOT NULL no longer needs a rebuild

The official SQLite changelog has this entry under 3.53.0 (2026-04-09), "New SQL language features":

Enhance ALTER TABLE to permit adding and removing NOT NULL and CHECK constraints.

I tested four statement forms on SQLite 3.53.1: SET NOT NULL and DROP NOT NULL both succeed, while SET DATA TYPE and SET DEFAULT still raise syntax error. That makes Alembic's heuristic in alembic/ddl/sqlite.py — requires_recreate_in_batch(), whose docstring says "Normally, only returns True on SQLite when operations other than add_column are present" — unnecessarily conservative on 3.53 and newer.

I put both paths in the same migration and raced them:

Rows	plain `alter_column(nullable=False)`	batch rebuild of the same change	Slowdown
10,000	0.0201 s	0.0314 s	1.6x
100,000	0.0298 s	0.0879 s	2.9x
500,000	0.0708 s	0.4429 s	6.3x

The direct path emits ALTER TABLE big ALTER COLUMN b SET NOT NULL — a single O(1) statement. The batch path rebuilds the table. On 3.53+, changing NOT NULL no longer costs you a rebuild.

To be clear: Alembic still recommends batch mode for cross-version compatibility. I am reporting measured timings, not recommending that you turn batch off. On SQLite older than 3.53, that ALTER COLUMN statement is a syntax error.

Troubleshooting: five real errors and two silent failures

Error one: Cannot add a NOT NULL column with default value NULL. Autogenerate added a non-nullable column to a table that already has rows. It copies nullable=False from your model and does not invent a default for you. Fix it by hand as three steps: add the column nullable, backfill with op.execute(), then add the constraint.

Error two: NotImplementedError: No support for ALTER of constraints in SQLite dialect. Raised when you call op.create_unique_constraint() directly on SQLite. The source is alembic/ddl/sqlite.py:78, and the message continues "Please refer to the batch mode feature which allows for SQLite migrations using a copy-and-move strategy." Move the operation inside an op.batch_alter_table() block.

Error three: UserWarning: Unnamed CHECK constraint ... is being omitted. An unnamed CHECK constraint is dropped during a batch rebuild. Name your constraints (the official recommendation is naming_convention), or pass them explicitly with batch_alter_table(table_args=...).

Error four: Multiple head revisions are present for given argument 'head'. Two people branched off the same base revision and upgrade head breaks. The source is alembic/script/base.py:223, and the full text continues "please specify a specific target revision, '@head' to narrow to a specific head, or 'heads' for all heads". Use alembic merge heads -m "merge"; do not hand-edit down_revision.

Error five: Can't locate revision identified by '', exit code -1. A version file was deleted, or the database's version points at a revision that does not exist locally. The source is alembic/script/base.py:236. Use alembic stamp to align the recorded version with reality, or restore the missing file from backup.

Silent failure one: an empty migration still advances the version. A file created by alembic revision -m "xxx" with the default template has pass as the body of both upgrade() and downgrade() (the official template literally ships """Upgrade schema.""" plus pass). I measured the version moving from d22dd80cf08e to 7ec59bca7385 with nothing changed in the database; alembic downgrade -1 also exits 0, moves the version back, and changes nothing.

Silent failure two: autogenerate treats tables missing from your metadata as junk. From the docs: "If a target database has many tables that are not part of the MetaData object, the autogenerate process will normally assume these are extraneous tables in the database to be dropped". Forget to import one model into Base and you get a drop_table in the generated migration.

What this article does not cover

I ran no PostgreSQL or MySQL instance, so every conclusion here is measured on SQLite only; I did not verify the PostgreSQL batch behaviour the docs describe (emitting DROP CONSTRAINT first). Except for the six timing rows, all numbers are single measurements rather than medians of repeated runs, so they will vary on your machine. This article contains no product recommendations.

FAQ

Q: Can I ship an autogenerated migration as-is?

No. The docs say "It is always necessary to manually review and correct the candidate migrations". Section 1 measures the cost of skipping that: 1,000 rows lost, exit code 0.

Q: Why is my default-value change not detected?

compare_server_default is off by default. Pass compare_server_default=True to context.configure() in env.py; the docs also warn that the feature "cannot always produce accurate results".

Q: Do all SQLite migrations need batch_alter_table?

No. add_column is the one column-level ALTER SQLite supports, and wrapping it in batch does not trigger a rebuild (measured at 0.0106 s with 500,000 rows). The trigger is any non-add_column operation inside the block.

Q: How do I rename a column without losing data?

Do not use the autogenerated output. Write op.alter_column('account', 'email', new_column_name='email_address') by hand — one statement, data and constraints intact.

Q: How do I merge two branches?

alembic merge heads, which writes a merge revision with two down_revision entries. Hand-editing down_revision only corrupts the graph.

Q: The recorded version does not match the actual schema. What now?

Run alembic current to see where Alembic thinks it is, then alembic history to see the graph. Once you know the real state, use alembic stamp rather than deleting the alembic_version table.

One line

Autogenerate is a draft generator, not a diff tool. Every pass and every add/drop pair it emits needs your eyes on it before it goes anywhere near production.

👉 Join Xiaomi MiMo Platform: Leading AI model platform with cost-effective inference

👉 Join Aliyun AI: Top AI products with exclusive coupons for business innovation

📌 This article was AI-assisted generated and human-reviewed | TechPassive — An AI-driven content testing site focused on real tool reviews