Writing Safe, Idempotent Database Migrations

A migration that ran successfully in staging and then fails halfway through in production leaves you in the worst possible spot: a schema that's neither the old version nor the new one, a deploy that can't roll forward, and a rollback that might not undo what already happened. Writing migrations so that re-running them is always safe — idempotent, in the strict sense — turns "the migration partially failed" from an incident into a non-event.

What actually goes wrong

A typical migration script:

ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP;
CREATE INDEX idx_users_last_login ON users (last_login_at);
UPDATE users SET last_login_at = created_at WHERE last_login_at IS NULL;

If the index creation times out on a large table (or the deploy process gets killed, or the connection drops), you're left with the column added but the index missing and the backfill not run. Re-running the whole script from the top now fails immediately on the first line — column "last_login_at" already exists — even though what you actually need is for the script to pick up where it left off.

Make each statement safe to re-run

Most schema-change statements have an IF EXISTS / IF NOT EXISTS form. Use it everywhere.

ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login_at TIMESTAMP;
CREATE INDEX IF NOT EXISTS idx_users_last_login ON users (last_login_at);
UPDATE users SET last_login_at = created_at WHERE last_login_at IS NULL;

Now the whole script can be re-run after any partial failure. The ADD COLUMN and CREATE INDEX calls become no-ops if already applied, and the UPDATE is naturally idempotent already — WHERE last_login_at IS NULL only touches rows that still need it, so running it twice does no extra work the second time.

That last point generalizes: an UPDATE guarded by a condition that describes "still needs this change" rather than "apply this change" is idempotent by construction. Compare:

-- NOT idempotent: running this twice doubles every balance
UPDATE accounts SET balance = balance * 1.05;

-- idempotent: running this any number of times has the same effect
UPDATE accounts SET balance = base_balance * 1.05 WHERE balance != base_balance * 1.05;

The first describes an action; the second describes a target state. Prefer describing the target state whenever the migration is doing anything more than a pure schema change.

Dropping things needs the same treatment

DROP TABLE IF EXISTS legacy_sessions;
ALTER TABLE orders DROP COLUMN IF EXISTS legacy_status;

Symmetric to the additive case — without IF EXISTS, re-running a migration that already succeeded in dropping something fails loudly on a statement that has, in effect, already done its job.

Backfills need to be resumable, not just idempotent

A one-shot UPDATE across a huge table is a lock held for the entire duration and a single all-or-nothing unit of work. Batch it, so a failure partway through loses minutes of progress, not the whole job:

-- repeat until this returns 0 rows affected
UPDATE users
SET last_login_at = created_at
WHERE id IN (
  SELECT id FROM users
  WHERE last_login_at IS NULL
  LIMIT 1000
);

async function backfillLastLogin(db) {
  let updated;
  do {
    const result = await db.query(`
      UPDATE users SET last_login_at = created_at
      WHERE id IN (SELECT id FROM users WHERE last_login_at IS NULL LIMIT 1000)
    `);
    updated = result.rowCount;
  } while (updated > 0);
}

Each batch is its own small transaction. If the process dies after batch 40, restarting it just continues from wherever last_login_at IS NULL still has rows — no bookkeeping table needed to track progress, because the WHERE clause is the progress tracker.

Separate the safe part from the destructive part

ADD COLUMN is safe to retry. DROP COLUMN is not reversible once data is gone. Splitting a schema change like "rename status to order_status" into separate deploys keeps every individual migration idempotent-and-safe on its own:

1. Add the new column, backfill it from the old one, deploy code that writes to both.
2. Deploy code that reads from the new column only.
3. In a later, separate migration — after confirming step 2 has been running cleanly for a while — drop the old column.

If step 3's migration fails partway, you've lost nothing irreplaceable; the old column is still there to retry against. Bundling all three into one migration means a partial failure can leave you with data written to a column your rolled-back application code no longer reads.

Migration tools already do some of this — know which part

Frameworks like Rails' ActiveRecord migrations or Django's migration system track which migrations have already run in a metadata table, so re-running the migration command won't re-execute a migration that fully completed. What they generally can't protect you from is a single migration file that fails halfway through its own body — that's still on you to write defensively, with the IF EXISTS / target-state patterns above, inside each migration.

Takeaway

Write every migration statement as "make sure this is true" rather than "do this action," and a migration that fails halfway through becomes something you re-run, not something you page someone about at 2am. The cost is a few extra IF EXISTS clauses and WHERE conditions; the payoff is that partial failure stops being a special case you have to reason about under pressure.