Flask‑Migrate Autogenerate vs Manual SQL Scripts: Which yields safer rollbacks for complex schema changes?

Answer

Manual SQL scripts give safer rollbacks for complex schema changes; Flask‑Migrate autogenerate is reliable only for simple additive/destructive changes.

Confirmed facts

• Autogenerate creates upgrade/downgrade from model diffs; it handles add/drop columns, indexes, tables correctly.
• For column type changes, table renames, or data migrations autogenerate often omits or mis‑handles the reverse steps.
• Manual SQL lets you define both forward and reverse statements explicitly, allowing pre‑deployment testing.

Likely explanation

When a migration involves a type cast or data transformation, Alembic cannot infer the exact reverse operation, so the generated downgrade may leave columns in an intermediate type or drop data. A hand‑written script can include the precise cast back and any needed data restoration.

Steps to decide and act

1. Check for data transformation: Are there any type casts, data migrations, or irreversible operations? (If yes → manual script required.)
2. Generate a migration with flask db migrate and inspect the autogenerated downgrade() function.
3. Apply the migration to a staging copy of the production database, run flask db upgrade then flask db downgrade, and verify the schema matches the original state.
4. If the downgrade is missing, incomplete, or wraps multiple statements in a single transaction that could leave the DB inconsistent, replace the file with a hand‑written SQL script that contains both forward and reverse statements.
5. Keep the forward and reverse SQL together in the same migration file, use a clear naming convention (e.g., V001_add_column.sql / V001_drop_column.sql), and require peer review before merging.
6. Add a CI job that spins up a temporary database, runs the upgrade, runs the downgrade, and asserts schema equality (using Alembic’s compare_metadata or raw SHOW CREATE TABLE checks).