Most migration outages are not caused by the SQL. They are caused by a window in which the running code and the live schema disagree — old instances still serving traffic against a changed table, or new code deployed before the column it needs exists.

Expand and contract

Any change that removes or renames something should be split into stages, each safe on its own: add the new structure; deploy code that writes to both old and new; backfill existing rows; switch reads to the new structure; and only then, once nothing references it, remove the old one. Each stage is independently reversible.

Specific traps

  • Adding a NOT NULL column with no default fails on a table with rows. Add it nullable, backfill, then apply the constraint.
  • Renaming a column breaks running code instantly. Add the new name, dual-write, migrate, remove.
  • Long-running updates can hold locks. Backfill in batches with pauses rather than one large statement.
  • On SQLite and D1 there is no ADD COLUMN IF NOT EXISTS, and a failing statement inside a batch rolls the whole batch back. Run each statement separately and handle the already-applied case.

Backfilling

Batch it, make it resumable, and make it idempotent — it will be interrupted at some point. Bound each batch by primary key rather than offset, so a restart does not reprocess or skip rows, and log progress so you know where it stopped.

Rollback

Write the rollback before running the migration, and test it on a copy of production data. A rollback that has never been executed is a hypothesis. Take a backup immediately before the migration and confirm you can restore from it — a backup you have never restored is also a hypothesis.

Deploy order

Additive schema changes go first, then code. Removals go last, well after the code that used them has stopped running everywhere. Between those two points, both schemas must work — which is exactly what makes the approach safe.