Migrations

the most irreversible thing in a project

What it is

A migration is a change to the database's structure: adding a table, a column, an index, renaming a field. This is the bottom-right corner from article 1.5: irreversible and able to break invisibly.

The difference from ordinary code is fundamental. Wrong code rolls back with a commit (article 5.1); a wrong migration has already changed the data, and git revert will not bring it back.

What it looks like in code

Migrations are numbered files applied in order and only once:

migrations/
├── 001_initial.sql
├── 002_add_notify_hour.sql
└── 003_add_index_subs_user.sql

-- 002_add_notify_hour.sql
ALTER TABLE subs ADD COLUMN notify_hour INTEGER NOT NULL DEFAULT 9;

The database records which migrations have been applied and does not run them again. Hence the rule: an applied migration is never edited. Got it wrong — write the next one that fixes it.

What is safe and what is not

The left column doesn't break working code: old queries keep working. The right one does break it — and breaks it at the moment of application, when there is nothing left to roll back.

On NOT NULL without a default in particular: if the table already has rows, the migration simply won't run. That is still the good case — the worse one is when it runs and spoils the data.

The mandatory order

1. A backup. Before anything else. For SQLite, a copy of the file; for PostgreSQL, pg_dump.
2. A rehearsal on a copy. Not on the live database. A migration that ran on an empty test database proves nothing — you need a copy with real data.
3. A rollback check. How to get back if it goes wrong. If there is no answer, the migration must not be applied (article 1.5).
4. Application.
5. A data check. Row counts before and after, a sample of records.

Point 2 is the one people skip. And it is exactly the one that catches cases like "the city is empty in the old records and NOT NULL wouldn't apply".

Filling in data is separate

A structure migration and a data migration are different things:

-- 002: structure
ALTER TABLE subs ADD COLUMN notify_hour INTEGER NOT NULL DEFAULT 9;

# a separate script: fill in meaningful values
UPDATE subs SET notify_hour = 8 WHERE city IN (SELECT ...);

They are worth separating because a fill script can be re-run, tried on part of the data and stopped. A migration cannot.

What to tell the AI

I need a migration: add a notify_hour column to subs, an
integer from 0 to 23, default 9. Existing rows must not break.

Separately, tell me: what happens to the code that currently
reads this table, and does it need changing before or after
the migration.

The second paragraph catches the main problem with migrations — the order in which code and schema are rolled out.

This migration renames a column in a single ALTER.
Lay it out as three steps so the code keeps working between
them.

Write the reverse migration for this one. If it cannot be
rolled back without losing data, say so — don't invent
something.

What to look for

No reverse migration. Not always possible, but its absence should be a decision rather than an accident.

A DROP in a migration. Dropping a column or a table is always a separate decision made by a human, and usually not until several releases after it stopped being used.

Migration and code rolled out together. Time passes between applying the migration and updating the code, and during it both have to work.

A migration edited after being applied. On your machine it will be applied again, on the server it won't. The databases drift apart.

A migration containing an UPDATE across the whole table. Filling in data belongs in a separate script.

Cheat sheet