how to write idempotent SQL migrations

TL;DR

Guard everything: IF NOT EXISTS on DDL, WHERE NOT EXISTS on seed inserts, and never a bare DROP. An idempotent migration produces the same end state whether it runs once or five times, which is what lets deploys retry safely instead of failing halfway through a rerun.

how to write idempotent SQL migrations

Use this when

• Migrations run automatically on deploy and might retry
• You need seed or reference data inserted exactly once
• A migration failed halfway and you must rerun it

Not for this skill when

• You are backfilling values in existing rows (different pattern)
• Your ORM generates migrations from models automatically
• You need to undo a migration that already applied badly

Steps

1. Make DDL rerunnable with IF NOT EXISTS:

CREATE TABLE IF NOT EXISTS app_events (
  id bigint PRIMARY KEY,
  event_type text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_app_events_type ON app_events (event_type);

Expected output: CREATE TABLE / CREATE INDEX on first run, harmless notices on reruns. The schema converges instead of erroring.

1. Guard column additions the same way. ADD COLUMN IF NOT EXISTS exists on Postgres:

ALTER TABLE app_events ADD COLUMN IF NOT EXISTS user_id bigint;

Expected output: the column appears on first run, a notice on reruns. For engines without the IF NOT EXISTS variant, query information_schema first in your migration runner.

1. Make seed inserts idempotent with a NOT EXISTS guard:

INSERT INTO event_types (code, description)
SELECT 'signup', 'User signed up'
WHERE NOT EXISTS (SELECT 1 FROM event_types WHERE code = 'signup');

Expected output: one row inserted the first time, zero rows on reruns. The natural key (code) is the dedupe criterion, not the surrogate id.

1. Prefer upsert for seed data that may legitimately change between versions:

INSERT INTO event_types (code, description) VALUES ('signup', 'User signed up')
ON CONFLICT (code) DO UPDATE SET description = EXCLUDED.description;

Expected output: the row exists with the latest description after any number of runs. This handles both "never seeded" and "seeded with an older description".

1. Never write a bare DROP in a rerunnable migration. If you must remove something, guard it:

DROP INDEX IF EXISTS idx_app_events_old;
-- for tables: only drop when you are certain, and prefer renames
ALTER TABLE IF EXISTS legacy_events RENAME TO legacy_events_retired;

Expected output: safe reruns. A bare DROP fails on rerun (object gone) or, worse, destroys data a retry then cant recreate. Renaming a retired table keeps the data recoverable.

1. Prove idempotency by running the migration twice against a scratch database:

psql "$SCRATCH_DB" -f migrations/004_add_events.sql
psql "$SCRATCH_DB" -f migrations/004_add_events.sql
echo "second run exit: $?"

Expected output: exit 0 both times with no errors on the second run. If the second run fails, the migration isnt idempotent yet.

Variant phrasings

idempotent database migration example

Steps 1-4 are the complete pattern: guarded DDL, guarded seeds, upsert for evolving seeds.

migration failed halfway rerun safely

Only works if every statement is idempotent. A half-applied migration reruns cleanly when each statement converges (steps 1-5); without that, you are hand-repairing.

IF NOT EXISTS migration best practices

Use it for CREATE TABLE, CREATE INDEX, and ADD COLUMN, but remember it hides genuine drift: if the object exists with the wrong definition, IF NOT EXISTS wont fix it. Pair with a schema-diff check in CI.

Why it happens

Deploy pipelines retry: a deploy that dies after applying migration 4 of 7 reruns from migration 1, and any non-idempotent statement in the early migrations fails the retry. Idempotency turns "rerun the migration" from a scary manual operation into a boring safe one, which is the whole point of automated deploys.

Edge cases

• CREATE TABLE IF NOT EXISTS wont add missing columns to an existing table; handle columns with ADD COLUMN IF NOT EXISTS separately.
• Data migrations (UPDATEs over existing rows) need their own idempotency: a WHERE clause that selects only unmigrated rows.
• Concurrent migration runners can race; use advisory locks or a migrations ledger table so only one runner executes at a time.
• IF NOT EXISTS is not transactional DDL on every engine; on Postgres, wrap the migration in a transaction so a late failure rolls back cleanly.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_5NBk6x0PsmNrrIPlaRopbw