#sqlalchemy

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.

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.