💻 Coding
Alembic Autogenerate Migration Reviewer: SQLAlchemy 2.0 Revision Fixes, Batch Mode for SQLite, Data Migrations, Downgrades, and Zero Downtime Postgres Steps
Review an Alembic revision that autogenerate produced before it reaches production: catch the renames it sees as drop and add, missing server defaults, enum and constraint naming problems, SQLite batch mode needs, and unsafe locking operations on Postgres, then get a corrected revision file, a working downgrade, a separate data migration, and an expand and contract rollout plan.
0Reviews
Prompt
Act as a Python backend engineer who owns database migrations for SQLAlchemy 2.0 applications and reviews every Alembic revision before it ships. You treat autogenerate output as a first draft that is wrong in predictable ways. Inputs: - The generated revision file, including revision, down_revision, upgrade, and downgrade: [RevisionFile] - The model diff that caused it (old and new SQLAlchemy model classes or a description): [ModelChange] - Database engine and version for each environment (Postgres, MySQL, SQLite in tests): [DatabaseTargets] - Approximate row counts and write traffic for affected tables: [TableSize] - env.py settings that matter (target_metadata, compare_type, compare_server_default, render_as_batch, naming_convention): [EnvConfig] - Deployment style (single deploy, rolling deploy with old and new app versions live together): [DeployStyle] - Output format: [Format] Generate: 1. A findings list comparing RevisionFile with ModelChange: renamed columns or tables that autogenerate wrote as drop_column plus add_column (data loss), type changes it missed because compare_type is off, server defaults, nullable changes on populated tables, unnamed constraints, and enum types it will not create or drop on Postgres. 2. A corrected upgrade() using op.alter_column with new_column_name for renames, explicit constraint names that follow EnvConfig naming_convention, and op.batch_alter_table blocks wherever SQLite is in DatabaseTargets. 3. A downgrade() that truly reverses upgrade() in reverse order, or an explicit raise with the reason when the change cannot be undone without data loss. 4. Data migration: if rows must be backfilled or transformed, a separate revision using op.get_bind() and a lightweight sa.table definition, never imported ORM models, batched when TableSize is large. 5. Locking review for Postgres: flag ADD COLUMN with a volatile default, SET NOT NULL on a large table, new foreign keys, and plain CREATE INDEX. Show the safer pattern (add nullable, backfill, add a NOT VALID check then VALIDATE, CREATE INDEX CONCURRENTLY inside op.get_context().autocommit_block()). 6. If DeployStyle is rolling, an expand and contract sequence split into numbered revisions and app releases so old code never reads a missing column. 7. Commands to verify: alembic upgrade head and downgrade -1 against a copy, alembic check, and alembic upgrade --sql for DBA review. Constraints: - Keep the original revision id and down_revision unless a split is required, and show any new ids as placeholders. - Do not claim lock durations or timings you were not given. No em dashes.
Instructions
Replace every [bracket] with your details before running. Works on ChatGPT, Claude, and Gemini.
Generated Output
This image was generated using the prompt above.
Examples
Example Input
RevisionFile: upgrade() drops users.full_name and adds users.display_name String(120) nullable=False; adds orders.status Enum('pending','paid','refunded'); creates index ix_orders_created_at
ModelChange: User.full_name renamed to display_name; new Order.status column defaulting to pending
DatabaseTargets: Postgres 16 in production, SQLite in CI tests
TableSize: users about 400k rows; orders about 9 million rows with constant inserts
EnvConfig: compare_type=True, render_as_batch not set, no naming_convention on MetaData
DeployStyle: rolling deploy on Kubernetes
Format: findings, corrected revision files, rolloutExample Output
Findings
1. full_name to display_name is a rename. As generated, upgrade() drops the column and every display name is lost. Use alter_column with new_column_name.
2. display_name is added NOT NULL with no default on 400k rows. With the rename fix this goes away.
3. orders.status uses a Postgres ENUM. Autogenerate creates it in upgrade but downgrade drops the column without dropping the type, so a second upgrade fails.
4. A NOT NULL status with a default on a 9 million row hot table should be split. Plain CREATE INDEX blocks writes on orders.
5. SQLite in CI cannot ALTER most columns. Set render_as_batch=True in env.py and use batch_alter_table.
6. No naming_convention, so the enum and index names depend on the database. Add one to MetaData before generating more revisions.
Revision a1 (expand, ships with app release N)
status_enum = sa.Enum('pending', 'paid', 'refunded', name='order_status')
def upgrade():
with op.batch_alter_table('users') as b:
b.alter_column('full_name', new_column_name='display_name', existing_type=sa.String(120))
status_enum.create(op.get_bind(), checkfirst=True)
op.add_column('orders', sa.Column('status', status_enum, nullable=True, server_default='pending'))
with op.get_context().autocommit_block():
op.create_index('ix_orders_created_at', 'orders', ['created_at'], postgresql_concurrently=True)
def downgrade():
with op.get_context().autocommit_block():
op.drop_index('ix_orders_created_at', table_name='orders', postgresql_concurrently=True)
op.drop_column('orders', 'status')
status_enum.drop(op.get_bind(), checkfirst=True)
with op.batch_alter_table('users') as b:
b.alter_column('display_name', new_column_name='full_name', existing_type=sa.String(120))
Rename warning: during a rolling deploy, old pods still query full_name. Either ship app release N with a synonym that reads both, or keep full_name and add display_name, dual write, then drop later.
Revision a2 (data migration, run after release N is fully live)
orders = sa.table('orders', sa.column('id', sa.BigInteger), sa.column('status', sa.String))
Backfill in batches of [batch size] ids: UPDATE orders SET status = 'pending' WHERE status IS NULL AND id BETWEEN :lo AND :hi.
Revision a3 (contract, ships with release N+1)
op.execute("ALTER TABLE orders ADD CONSTRAINT ck_orders_status_not_null CHECK (status IS NOT NULL) NOT VALID")
op.execute("ALTER TABLE orders VALIDATE CONSTRAINT ck_orders_status_not_null")
Then alter_column status nullable=False and drop the check.
Verify
alembic upgrade head && alembic downgrade -1 && alembic upgrade head on a restored copy; alembic check; alembic upgrade a1 --sql > a1.sql for DBA review.