migration-safety

Treat every environment as production

Staging and demo environments hold real data more often than anyone admits, and a migration that destroys data in staging demonstrates that the same operation would run in production. There is no “it’s just staging” exception. Never write a migration that could silently destroy existing data.

Destructive operations — stop and surface to the human

Do not generate these autonomously. Describe what you intend and why, and wait for confirmation before writing the file:

• DROP TABLE, DROP COLUMN, TRUNCATE — destroys rows or values permanently.
• ALTER COLUMN … TYPE with a lossy cast — the database rewrites the column; values that cannot be cast are lost.
• Removing NOT NULL and then dropping the column — same as above.
• DELETE FROM inside a migration — data loss with no recovery short of a backup.

Safe patterns

Adding a column. Add it nullable first, even if the final intent is NOT NULL. Backfill in a separate migration or deploy step. Add the constraint only after the backfill is confirmed. Three small migrations beat one that rewrites data and adds a constraint at once.

Renaming a column. Never rename in a single deploy. Expand-contract: add the new column nullable; write to both; backfill new from old; switch reads; drop the old column in a later release once confirmed safe.

Changing a type. Lossless (VARCHAR → TEXT) may proceed with a note in the migration comment. Lossy (TEXT → INTEGER, shrinking a VARCHAR) stops and asks.

Removing a column. Only after confirming no deployed code reads or writes it, and a backup exists or the column is confirmed empty. Archive before dropping in the downgrade path:

CREATE SCHEMA IF NOT EXISTS archive;
CREATE TABLE IF NOT EXISTS archive.<table>_<revision>_downgrade AS
  SELECT id, <dropped_column> FROM <table>;

The archive is retained indefinitely; nobody drops it without explicit sign-off.

Additive first. Adding a column or table is always safer than modifying one. If the goal can be reached by adding, add.

Checklist before committing a migration file

• upgrade() contains no DROP TABLE, DROP COLUMN, TRUNCATE or DELETE FROM without explicit human sign-off recorded in the PR.
• Any new NOT NULL column either has a server default or is added nullable with a separate backfill.
• You read the generated file. Never rely on autogenerate output alone.
• downgrade() is implemented, or is an explicit no-op with a comment saying why rollback is impossible for this change.

Never edit an applied migration

Once a migration has been applied to any shared environment, it is immutable. Create a new migration to correct mistakes.

Collapse work-in-progress migrations before review

If iterating on one logical change produced several files, collapse them into one before opening the PR — delete and regenerate, which is safe while nothing has been applied to a shared environment. This is different from the deliberate add-nullable → backfill → constrain sequence, which stays as separate migrations by design.

After merging the main branch into a feature branch

Long-lived branches frequently produce two divergent heads even without a content conflict. Check immediately after merging (alembic heads, or the equivalent for the tool) and reconcile with a no-op merge revision before running tests or committing.