Idempotent Script Design for Safe, Re-runnable Migrations

A migration that cannot survive being run twice cannot be automated, because every pipeline retries. When a deploy step times out, a runner restarts, or a network blip orphans a connection mid-DDL, the next attempt re-executes the same script — and a script that throws duplicate column name on its second run turns a transient blip into a blocked deploy and a 2 a.m. page. Idempotent script design makes the second, third, and tenth run of a migration a no-op that converges to the one correct state. This guide serves the engineers writing the DDL and the platform teams whose pipelines retry it, and it is the precondition for almost everything else in the database migration fundamentals — you cannot gate, test, or auto-roll-back a migration that corrupts state on its second run.

Concept & Mechanism

Idempotency is a property of the effect, not the syntax: running the script once and running it N times must leave the database in the same final state. Two mechanisms achieve it. The first is the conditional guard — DDL that checks for the object before creating it, so a second run sees the column already present and does nothing. The second is the version ledger — a tracking table that records which migrations have applied, so a runner can skip a completed step entirely and so a retry cannot double-record.

The engine decides how much the guards have to do. PostgreSQL wraps most DDL in a transaction, so a failed statement rolls back atomically and the ledger insert either commits with the DDL or not at all. MySQL 8.0 commits each DDL statement implicitly, which means a multi-step migration can be left half-applied with no automatic rollback — the guards and ledger must therefore be re-entrant on their own, because the engine offers no safety net. That difference is the entire reason to map your architecture against transactional vs non-transactional databases before writing the script. Idempotency also depends on the environments agreeing on what “already exists” means, which is why configuration parity from environment parity strategies is a prerequisite — a guard that checks information_schema is only reliable when collation and version match across tiers.

Prerequisites & Decision Criteria

Apply idempotent design to every migration that a pipeline can retry — which, in practice, is all of them. Confirm the floor below before relying on a script being safe to re-run.

• Every CREATE/ALTER has a guard (IF NOT EXISTS, or an information_schema check on MySQL where the inline guard is missing).
• A version ledger table exists, and every migration writes to it with ON CONFLICT DO NOTHING (PostgreSQL) or INSERT IGNORE (MySQL).
• Data backfills use upserts rather than blind INSERTs, per making data backfills idempotent with upserts.
• The compensating reverse step uses DROP ... IF EXISTS so a partial rollback is also re-runnable.
• On MySQL, each statement is independently recoverable because there is no transactional rollback for DDL.

Step-by-Step Procedure

1. Dry-run the guard on a replica. Confirm the conditional DDL parses and is a no-op against current state before merging.

# Shell · run against a staging READ replica · ROLLBACK persists nothing
# Proves the guarded ALTER applies cleanly and leaves no state behind.
PGPASSWORD="$DB_PASS" psql -h staging-replica.internal -U deploy_user -d app_db \
  -c "BEGIN; \
      ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login_ts TIMESTAMPTZ; \
      ROLLBACK;"

Verify before proceeding: the command exits 0 and the linter reports no non-idempotent statements.

2. Apply the guarded migration with a ledger write (PostgreSQL). Wrap the guard and the ledger insert in one transaction so they commit together.

-- PostgreSQL · run as the migration role · must run at a low-write window
-- The guard makes the ALTER a no-op on retry; ON CONFLICT keeps the ledger single-row.
BEGIN;
ALTER TABLE orders ADD COLUMN IF NOT EXISTS fulfillment_status VARCHAR(32) DEFAULT 'pending';
INSERT INTO migration_ledger (version, applied_at, checksum)
VALUES ('2026.06.21.1', NOW(), md5('2026.06.21.1'))
ON CONFLICT (version) DO NOTHING;
COMMIT;

3. Apply the equivalent on MySQL with explicit guards. MySQL has no IF NOT EXISTS for ADD COLUMN, so build the statement conditionally from information_schema.

-- MySQL 8.0 · run as the migration role · DDL commits implicitly — no rollback
-- Guard against information_schema because ADD COLUMN IF NOT EXISTS does not exist.
SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.columns
   WHERE table_schema = DATABASE()
     AND table_name = 'orders' AND column_name = 'fulfillment_status') = 0,
  'ALTER TABLE orders ADD COLUMN fulfillment_status VARCHAR(32) DEFAULT ''pending''',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
INSERT IGNORE INTO migration_ledger (version, applied_at) VALUES ('2026.06.21.1', NOW());

Verify before proceeding: run the same script a second time and confirm it makes no further change and inserts no second ledger row.

Verification & Observability

Confirm the object landed with the exact definition you intended, and confirm re-running the script is genuinely a no-op.

# Shell · read-only · run against production after applying
# Confirms the column exists with the expected type and nullability.
psql -h prod-db.internal -U deploy_user -d app_db -t -c \
  "SELECT column_name, data_type, is_nullable
   FROM information_schema.columns
   WHERE table_name = 'orders' AND column_name = 'fulfillment_status';"

To prove idempotency rather than assume it, apply the migration twice in a throwaway environment and assert the ledger holds exactly one row for the version:

-- PostgreSQL or MySQL · read-only · run after applying the same script twice
-- A count > 1 means the ledger guard (ON CONFLICT / INSERT IGNORE) is missing or wrong.
SELECT version, COUNT(*) AS rows
FROM migration_ledger
WHERE version = '2026.06.21.1'
GROUP BY version;

On MySQL, because DDL commits implicitly, also check SHOW ENGINE INNODB STATUS during a long ALTER to confirm the operation is INPLACE rather than holding a copy lock — a half-applied copy is exactly the partial state idempotent guards exist to recover from.

Rollback Path

Make the compensating step as re-runnable as the forward one. Use IF EXISTS so a partial or repeated rollback never throws, and drop constraints before columns.

-- PostgreSQL · run as the migration role · safe only after app code stops using the column
-- IF EXISTS makes the reversal itself idempotent; reverse order of creation.
BEGIN;
ALTER TABLE orders DROP CONSTRAINT IF EXISTS chk_fulfillment_status;
ALTER TABLE orders DROP COLUMN IF EXISTS fulfillment_status;
DELETE FROM migration_ledger WHERE version = '2026.06.21.1';
COMMIT;

Safe conditions: traffic routing confirms zero active queries reference the column, the application release that used it has been rolled back, and — on MySQL — you have accepted that the DROP commits immediately and cannot itself be rolled back if the next statement fails. Prefer the forward, non-destructive reversal from the database migration fundamentals (rename to a _deprecated_ prefix) whenever the change has been live long enough to hold data.

Common Errors & Fixes

ERROR: column "..." of relation "..." already exists (PostgreSQL) / ERROR 1060 (42S21): Duplicate column name — a retried ADD COLUMN ran without a guard. Root cause: the script is not idempotent. Fix: add IF NOT EXISTS on PostgreSQL, or the information_schema guard on MySQL shown above.

ERROR 1061 (42000): Duplicate key name — a re-run tried to create an index that already exists. Root cause: MySQL has no CREATE INDEX IF NOT EXISTS. Fix: guard index creation by checking information_schema.statistics first; on PostgreSQL use CREATE INDEX IF NOT EXISTS (supported since 9.5, including the CONCURRENTLY variant).

Duplicate ledger rows after a retry — the version table accumulated a second row for the same migration. Root cause: a plain INSERT without ON CONFLICT/INSERT IGNORE. Fix: add the conflict guard, and back it with a unique constraint on version so the database enforces single-row even if a script forgets.

Half-applied MySQL migration — the first DDL committed but a later step failed, leaving the schema partly changed. Root cause: implicit commits mean there is no transaction to roll back. Fix: make every step independently guarded so re-running the whole script completes the remaining steps without redoing the committed ones.

Child Page Index

Two guides go deeper. How to write idempotent SQL scripts for safe deploys is the hands-on reference for guard-clause syntax across PostgreSQL and MySQL, with the exact DO $$ blocks and prepared-statement patterns for objects that lack an inline IF NOT EXISTS. Making data backfills idempotent with upserts extends the same property from DDL to data, using INSERT ... ON CONFLICT and INSERT ... ON DUPLICATE KEY UPDATE so a re-run of a backfill corrects rather than duplicates. For the broader phase context, return to the database migration fundamentals overview.

Frequently Asked Questions

Is a version ledger enough, or do I still need IF NOT EXISTS guards? You need both, because they cover different failure points. The ledger lets the runner skip a migration it knows completed, but a step can fail after applying its DDL and before the ledger commits — on MySQL that gap is guaranteed because DDL commits implicitly. The inline guard makes the DDL itself a no-op on re-run, so the script is safe even when the ledger is out of sync with reality.

How do I make idempotency work on MySQL, which lacks ADD COLUMN IF NOT EXISTS? Build the statement conditionally from information_schema: count the matching column or index, and only PREPARE/EXECUTE the real DDL when the count is zero. Pair it with INSERT IGNORE into the ledger. Because each DDL commits immediately, design every step to be independently re-entrant so a half-applied migration completes on the next run.

Does idempotency apply to data backfills too, or only to schema DDL? It applies to both, and data is where teams most often forget it. A blind INSERT in a re-run creates duplicate rows; a non-idempotent UPDATE can double-apply an increment. Use upserts keyed on a unique constraint so a re-run corrects the target row to the intended value rather than adding to it.