Database Migration

Evolve your schema with versioned, reversible migrations that deploy without downtime.

Why

A database schema change is the riskiest operation in software — it mutates persistent state that can't be re-deployed away. A bad migration locks the table for 10 minutes, corrupts data, or leaves the app unable to start. The disciplined approach is versioned, reversible migrations applied with the expand-contract pattern: first expand (add the new column/table, dual-write), then migrate backfill, then contract (stop writing the old, drop it). Each step is independently deployable and reversible, so a problem at step 3 doesn't require restoring from backup — you just don't do step 3. This is how you change a 1TB table's schema without a maintenance window.

When

Whenever the schema must change. Never run raw ALTER TABLE by hand — always through a versioned migration tool.

How

1. Generate a migration: npx knex migrate:make add_users_display_name (or prisma migrate dev)
2. Write the UP migration (expand): ALTER TABLE users ADD COLUMN display_name TEXT
3. Write the DOWN migration (reversible): ALTER TABLE users DROP COLUMN display_name
4. Deploy the code that dual-writes (writes both old and new) — old code still works on new schema
5. Backfill: populate display_name from existing data in batches (UPDATE ... WHERE display_name IS NULL LIMIT 1000)
6. Deploy the code that reads from the new column and stops writing the old
7. Contract: drop the old column in a final migration (after a monitoring period confirms nothing reads it)
8. Each step is its own deploy + migration; never combine expand + contract in one step

Example

# Knex migration: add display_name (expand)
exports.up = function(knex) {
  return knex.schema.alterTable('users', t => {
    t.text('display_name').nullable();  // expand: add column
  });
};
exports.down = function(knex) {
  return knex.schema.alterTable('users', t => {
    t.dropColumn('display_name');        // reversible
  });
};

# Backfill script (batched, idempotent, resumable)
async function backfill() {
  let done = 0;
  while (true) {
    const rows = await db.raw(`
      UPDATE users SET display_name = name
      WHERE display_name IS NULL
      RETURNING id LIMIT 1000`);
    if (rows.length === 0) break;
    done += rows.length;
    await sleep(100);  // throttle to avoid lock contention
  }
}

# Contract migration (after monitoring confirms dual-read is gone)
exports.up = (knex) => knex.schema.alterTable('users', t => t.dropColumn('name'));

Common Problems

⚠Combining expand and contract in one migration — if the contract (drop column) fails, you can't roll back because the app already stopped writing it.
⚠A non-reversible migration (DROP COLUMN without backup) — the down migration is a no-op, so a bad deploy means data loss.
⚠Backfilling in one giant UPDATE — locks the table for minutes; batch in 1000-row chunks with sleep between.

Best Practices

✓Always use expand-contract: add the new, dual-write, backfill, switch reads, then drop the old — each step is its own deploy.
✓Make every migration reversible (write a real DOWN) and test it in staging by running UP then DOWN then UP.
✓Backfill in small idempotent batches with throttling — never a single UPDATE that locks the whole table.