How to Use the Alembic Autogenerate Migration Reviewer Prompt to Ship Schema Changes Without Data Loss

Alembic autogenerate saves a lot of typing, but it does not understand intent. It sees a renamed column as one column dropped and another added. It creates a Postgres enum type in upgrade and forgets to drop it in downgrade. It writes a plain index creation that locks a busy table, and it has no idea that SQLite in your test suite cannot alter most columns. None of that shows up until the migration runs somewhere important. The Alembic Autogenerate Migration Reviewer: SQLAlchemy 2.0 Revision Fixes, Batch Mode for SQLite, Data Migrations, Downgrades, and Zero Downtime Postgres Steps prompt reviews the generated revision against the model change you actually made and gives you corrected revision files, a real downgrade, and a rollout plan.

What the prompt produces

1. A findings list that compares the revision with the model change and calls out renames, missed type changes, nullable problems, unnamed constraints, and enum handling.
2. A corrected upgrade function using alter_column for renames, explicit constraint names, and batch_alter_table where SQLite is involved.
3. A downgrade function that reverses upgrade in the right order, or an explicit error when reversal would lose data.
4. A separate data migration that uses a lightweight table definition rather than your ORM models, batched for large tables.
5. A Postgres locking review with safer patterns such as adding a NOT VALID check constraint and validating it later, and creating indexes concurrently inside an autocommit block.
6. An expand and contract sequence for rolling deploys, split across revisions and app releases.
7. Verification commands including alembic check and offline SQL output for review.

How to fill the inputs

RevisionFile is the full generated file, including the revision and down_revision identifiers. Paste it as is, before you edit anything.

ModelChange is what you meant to do. Old and new model classes are best, but a clear sentence works, such as renamed full_name to display_name.

DatabaseTargets lists the engine and version for each environment. This is where many surprises come from, especially when tests run on SQLite and production runs on Postgres.

TableSize gives rough row counts and write traffic. A change that is instant on a small table can block writes for a long time on a large one.

EnvConfig covers the env.py settings that change autogenerate behavior: compare_type, compare_server_default, render_as_batch, and whether your MetaData has a naming convention.

DeployStyle says whether old and new versions of your app run at the same time. Rolling deploys need extra care with renames and dropped columns.

Reading the example output

The example reviews a revision that renames a user column, adds an order status enum, and adds an index on a table with millions of rows:

• The rename is caught. Autogenerate wrote a drop and an add, which would erase every display name. The fix uses alter_column with new_column_name.
• The enum is handled both ways. The type is created with checkfirst in upgrade and dropped in downgrade, so running the migration twice does not fail.
• The index does not lock writes. It is created concurrently inside an autocommit block.
• The status column is added in stages. Nullable first, backfilled in batches by a separate revision, then made required through a check constraint that is validated without a long lock.
• The rolling deploy is respected. The prompt warns that old pods still read the old column name and suggests a compatibility step.

Tips for better results

• Turn on compare_type and add a naming convention to MetaData before your next autogenerate run. Many findings disappear at the source.
• Run upgrade, downgrade, and upgrade again against a restored copy of production data, not an empty database.
• Generate offline SQL with the sql flag and attach it to the pull request so a reviewer can read exactly what will execute.
• Keep data migrations in their own revision. It makes reruns and rollbacks much easier to reason about.

Mistakes to avoid

• Do not import ORM models inside a migration. Models change over time and old revisions will break.
• Do not add a required column with no default to a populated table in one step.
• Do not leave downgrade as pass. Either make it work or make it fail loudly with a reason.
• Do not assume SQLite in CI proves the migration is safe for Postgres.

Who it is for

Python backend developers using SQLAlchemy and Alembic, tech leads who review migrations, and small teams without a dedicated database administrator who still need safe deploys.

Related PromptDig links

Start with the Alembic Autogenerate Migration Reviewer: SQLAlchemy 2.0 Revision Fixes, Batch Mode for SQLite, Data Migrations, Downgrades, and Zero Downtime Postgres Steps prompt and paste your generated revision along with the model change. To find more prompts for developers, Browse more prompts. If you have a review checklist that has saved your team from a bad migration, Share a prompt.