Skip to main content

Schema migrations — version control for the database

Summary: you can't redeploy a database the way you redeploy code — it holds data you have to keep. A migration tool records each schema change as an ordered, committed script and tracks in the database which ones have run, so any environment can be brought to the current version by replaying what it's missing.

How it works​

  • One file per change, each with an ID and a pointer to its parent — so the files form a chain with a defined order, not a folder anyone can reorder.
  • The database records where it is. The tool keeps a version row (Alembic: alembic_version) and compares it to the chain to work out what's outstanding. That's why running a migration twice is safe.
  • Each script has a forward and a back — upgrade() and downgrade().
  • Generate, then apply — two steps. Autogenerate diffs your models against the live schema and writes a draft script; nothing changes until alembic upgrade head. The gap is the point: it's where review happens.

What to do​

  • Treat migrations as code — reviewed, and committed in the same change as the model edit that caused them.
  • Read every autogenerated script. The tool diffs shapes, not intent: a renamed column reads as a drop plus an add, which silently destroys the data.
  • Apply as an explicit deploy step, not on app startup — N containers booting together will race to run the same migration.
  • Sequence risky changes so old and new code can both run during a rollout: add the column nullable → backfill → add the constraint. One deploy each.
  • Never edit a migration that has already run anywhere. Its ID is recorded as done, so the edit will never be applied. Add a new migration instead.

Where this came up​

The data-patch-agent's schema lives in Alembic scripts, with alembic upgrade head as a step in the compose startup rather than something run by hand.

Further reading: Alembic's tutorial covers the revision chain and the autogenerate workflow.