Database Migration Checklist

Chapter 2 of 11 · 5 checks · 3 min

Backward compatibility: expand and contract

Checks that both the old and new versions of the application keep working at every step, because during a rolling deploy both are running.

Show
  1. The currently deployed application works against the migrated schemaMust

    Why it matters and how to check

    Migrations usually run before or during the deploy, so for minutes or hours the old code talks to the new schema. If the migration drops a column the old code reads, adds a NOT NULL column it does not write, or renames a table, requests fail until the deploy finishes, and the rollback path is broken too.

    To verify: in staging, apply the migration and keep the previous release running against it. Run its smoke tests or end-to-end tests. Any failure means the change needs to be split into expand and contract steps.

  2. Renames and type changes use expand, migrate, contract instead of one stepMust

    Why it matters and how to check

    Parallel change, also called expand and contract, makes a backward-incompatible change in three phases: add the new form alongside the old one, move every reader and writer to the new form, then remove the old one once nothing uses it. For a column rename that means: add the new column, write to both, backfill, read from the new one, stop writing the old one, then drop it.

    To verify: for any rename, type change or table split, the plan should show each phase as its own release or deploy step, with the code change that goes with it. A plan with RENAME COLUMN in the same deploy as the code that uses the new name fails this check.

    Example

    SQL
    -- release 1 (expand): new column, app writes both
ALTER TABLE users ADD COLUMN email_address text;

-- between releases: backfill in batches (see the backfill chapter)

-- release 2: app reads email_address, still writes both
-- release 3: app stops writing email
-- release 4 (contract): nothing references the old column
ALTER TABLE users DROP COLUMN email;
  3. Columns and tables are dropped only after no deployed code references themMust

    Why it matters and how to check

    Many ORMs list every column they know about in SELECT and INSERT statements, so removing a column the model still declares breaks queries even when the code never uses it. Drop in a later release than the one that removes the field from the code and the model.

    To verify: search the code for the column or table name, including raw SQL, reports and background jobs. Confirm the release that removed it from the model is fully deployed, and check the database's statistics or logs for recent queries that still reference it before running the drop.

  4. New required columns get a default or are filled before NOT NULL is enforcedMust

    Why it matters and how to check

    Adding a column as NOT NULL without a default fails on a table with rows, and adding it with a default the old code does not know about is fine only if old inserts still succeed. The safe order is: add the column as nullable or with a constant default, deploy code that writes it, backfill old rows, then enforce NOT NULL.

    To verify: for each new non-nullable column, check that old application code can still insert rows (the default covers it, or the column stays nullable until the new code is everywhere). Confirm the NOT NULL step comes after the backfill, using one of the low-lock methods in the PostgreSQL chapter.

  5. New enum values, lookup rows and constraints are added before code depends on themShould

    Why it matters and how to check

    If the new code writes a status the database enum does not have yet, or inserts a reference to a lookup row that is not there, inserts fail during the deploy. Equally, a new constraint that old code violates breaks the old code.

    To verify: check the order. Allowed values and lookup rows should ship in a migration before the code that writes them. New constraints should ship after every writer complies, and old data should be checked against them first.