Database XP / Change

Migrations that can run twice

Because one day one will be run twice.

Schema changes are applied by scripts, and scripts are re-run: a deployment retried, an environment rebuilt, an operator uncertain whether the first attempt completed. A migration that fails destructively on a second run turns an ordinary uncertainty into an incident.

Writing them to be safe on repetition costs almost nothing at authoring time. Checking whether the column exists before adding it, whether the value is already set before updating, and whether the constraint is present before creating it, are a few extra lines each.

The harder discipline is data migrations, which frequently cannot be made repeatable in the same way because they transform existing rows. Those need a marker recording that they ran, and the marker needs to be in the database rather than in a deployment log.

The other habit worth having is that every migration should be tested against a copy of production data rather than against an empty schema. Migrations that work on a clean database and fail on real data are the norm rather than the exception, because real data contains everything the schema permits.

There is a further migration discipline worth adopting, which is that every change should be accompanied by its reversal, written at the same time. Writing the rollback while the change is fresh takes minutes; reconstructing it during an incident, under pressure, is where the second failure usually comes from.