Re-runnable database migrations: survive a migration that ran twice

TL;DR — If a migration might run twice, write it so the second run is a no-op: use IF EXISTS/IF NOT EXISTS where supported, guard data changes with WHERE NOT EXISTS, and record completion in a migrations table with a unique key. The most common fix is replacing "blind" ALTER TABLE and INSERT statements with existence checks plus transaction boundaries that match your database engine. Reading time: ~5 min

Goal

When you are done, your schema migration can be executed twice against the same database without failing or duplicating data, and your deployment pipeline will show a clean success exit code on both the first run and the re-run.

Prerequisites

• Access to a non-production database clone first, then production access if the test passes
• SQL client for your database:
  • PostgreSQL client psql >= 14 — check with psql --version
  • or MySQL client mysql >= 8.0 — check with mysql --version
• Your app's migration runner command, or direct DB credentials if you run SQL manually
• Permission to create/alter tables and indexes in the target schema
• The exact migration file or SQL statements that failed on re-run
• A backup or snapshot path if the migration drops columns/tables or rewrites data

Steps

Step 1: Reproduce the failure on a disposable database

Run the migration twice against a clone, not production.

⚠️ If the migration drops columns, renames columns, or updates rows in place, test on a restored backup or disposable clone first. A bad "fix" can turn a harmless duplicate-run error into data loss.

For PostgreSQL:

createdb app_migration_test
psql "$DATABASE_URL" -d app_migration_test -f ./migrations/20261001_add_status.sql
psql "$DATABASE_URL" -d app_migration_test -f ./migrations/20261001_add_status.sql

For MySQL:

mysql -e "CREATE DATABASE app_migration_test;"
mysql app_migration_test < ./migrations/20261001_add_status.sql
mysql app_migration_test < ./migrations/20261001_add_status.sql

What you should see on the second run if the migration is not re-runnable: an error shaped like one of these.

psql:./migrations/20261001_add_status.sql:3: ERROR:  column "status" of relation "orders" already exists
psql:./migrations/20261001_add_status.sql:8: ERROR:  relation "idx_orders_status" already exists
psql:./migrations/20261001_add_status.sql:12: ERROR:  duplicate key value violates unique constraint "order_events_pkey"

ERROR 1060 (42S21) at line 3: Duplicate column name 'status'
ERROR 1061 (42000) at line 8: Duplicate key name 'idx_orders_status'
ERROR 1062 (23000) at line 12: Duplicate entry '12345' for key 'PRIMARY'

Step 2: Rewrite DDL so it is safe on the second run

Replace unconditional schema changes with guarded statements.

PostgreSQL example:

BEGIN;

ALTER TABLE orders ADD COLUMN IF NOT EXISTS status text;
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';
UPDATE orders SET status = 'pending' WHERE status IS NULL;
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);

COMMIT;

MySQL 8.0 example:

ALTER TABLE orders ADD COLUMN IF NOT EXISTS status varchar(32) NULL;
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';
UPDATE orders SET status = 'pending' WHERE status IS NULL;
CREATE INDEX idx_orders_status ON orders(status);

If MySQL errors on duplicate index creation, guard it with information_schema:

SET @index_exists = (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'orders'
    AND index_name = 'idx_orders_status'
);
SET @sql = IF(@index_exists = 0,
  'CREATE INDEX idx_orders_status ON orders(status)',
  'SELECT ''idx_orders_status already exists''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

What you should see when this step succeeds: the SQL file runs once creating the objects, and a second run prints ALTER TABLE, UPDATE 0, CREATE INDEX or a harmless notice instead of exiting non-zero.

Step 3: Rewrite data backfills to be idempotent

Do not use plain INSERT for seed/backfill rows unless a unique key plus conflict handling prevents duplicates.

PostgreSQL:

INSERT INTO order_events (id, order_id, event_type)
SELECT o.id, o.id, 'created'
FROM orders o
WHERE NOT EXISTS (
  SELECT 1
  FROM order_events e
  WHERE e.id = o.id
);

Or, if a unique constraint already exists:

INSERT INTO order_events (id, order_id, event_type)
SELECT o.id, o.id, 'created'
FROM orders o
ON CONFLICT (id) DO NOTHING;

MySQL:

INSERT INTO order_events (id, order_id, event_type)
SELECT o.id, o.id, 'created'
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM order_events e WHERE e.id = o.id
);

Or with a unique key:

INSERT IGNORE INTO order_events (id, order_id, event_type)
SELECT o.id, o.id, 'created'
FROM orders o;

What you should see when this step succeeds: first run inserts rows, second run reports INSERT 0 0 in PostgreSQL or Query OK, 0 rows affected in MySQL.

Step 4: Add a migration ledger with a unique key if your runner does not already have one

If your framework already has a migrations table with a unique version/name column, use it. If not, create one and insert into it exactly once per migration.

PostgreSQL:

CREATE TABLE IF NOT EXISTS schema_migrations (
  version text PRIMARY KEY,
  applied_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO schema_migrations(version)
VALUES ('20261001_add_status')
ON CONFLICT (version) DO NOTHING;

MySQL:

CREATE TABLE IF NOT EXISTS schema_migrations (
  version varchar(255) PRIMARY KEY,
  applied_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT IGNORE INTO schema_migrations(version)
VALUES ('20261001_add_status');

If you run migrations from a shell script, fail only when the migration itself fails, not when the ledger insert is a duplicate.

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f ./migrations/20261001_add_status.sql

What you should see when this step succeeds: the ledger contains one row for the migration version, even after two runs.

Step 5: Split risky changes into expand/backfill/contract

For production-safe re-runs, do not combine incompatible changes in one file.

Use three files in this order:

20261001_01_expand.sql
20261001_02_backfill.sql
20261001_03_contract.sql

Example contents:

-- 20261001_01_expand.sql
ALTER TABLE orders ADD COLUMN IF NOT EXISTS status text;
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';

-- 20261001_02_backfill.sql
UPDATE orders SET status = 'pending' WHERE status IS NULL;

-- 20261001_03_contract.sql
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

What you should see when this step succeeds: re-running expand and backfill is harmless; contract only runs after data is ready.

Step 6: Run the migration twice and check exit codes

Use the exact command your CI/CD job uses, twice.

set -e
./bin/migrate
printf 'first exit code: %s\n' "$?"
./bin/migrate
printf 'second exit code: %s\n' "$?"

If you are running raw SQL:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f ./migrations/20261001_add_status.sql; echo $?
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f ./migrations/20261001_add_status.sql; echo $?

What you should see when this step succeeds: both runs exit with 0.

Verify it works

Run the migration twice, then inspect schema, data, and ledger.

PostgreSQL:

psql "$DATABASE_URL" -c "\d+ orders"
psql "$DATABASE_URL" -c "SELECT count(*) FROM order_events;"
psql "$DATABASE_URL" -c "SELECT version, applied_at FROM schema_migrations WHERE version = '20261001_add_status';"

Expected shape:

                                      Table "public.orders"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+-------------------
 status | text |           | not null | 'pending'::text
Indexes:
    "idx_orders_status" btree (status)

 count
-------
 12345
(1 row)

      version       |          applied_at
--------------------+-------------------------------
 20261001_add_status | 2026-10-01 12:34:56.789+00
(1 row)

The proof you want: schema exists once, backfill row count does not increase on the second run, and the migration ledger has one entry.

Common pitfalls

Using CREATE INDEX IF NOT EXISTS where your engine/version does not support it

Mistake: copying PostgreSQL syntax into MySQL or an older engine.

Symptom:

ERROR 1064 (42000): You have an error in your SQL syntax

Fix: query information_schema.statistics and conditionally execute CREATE INDEX with dynamic SQL.

Combining ADD COLUMN and SET NOT NULL before backfill

Mistake: adding a required column in one statement on a table that already has rows.

Symptom:

ERROR: column "status" of relation "orders" contains null values

Fix: split into ADD COLUMN nullable, UPDATE ... WHERE status IS NULL, then ALTER COLUMN ... SET NOT NULL.

Blind INSERT in seed or backfill logic

Mistake: inserting fixed IDs or natural keys without conflict handling.

Symptom:

ERROR: duplicate key value violates unique constraint "order_events_pkey"

Fix: use ON CONFLICT DO NOTHING, INSERT IGNORE, or WHERE NOT EXISTS tied to a real unique key.

Assuming DDL is fully transactional in every engine

Mistake: wrapping all MySQL migration steps in a transaction and expecting rollback on DDL failure.

Symptom: some statements persist even though the migration runner reports failure.

Fix: write each DDL statement to be independently safe on re-run; do not rely on rollback semantics for MySQL DDL.

Renaming or dropping objects without existence guards

Mistake: ALTER TABLE ... DROP COLUMN old_name; in a migration that may re-run after partial success.

Symptom:

ERROR: column "old_name" does not exist

Fix: use DROP COLUMN IF EXISTS old_name or check system catalogs first, then run the destructive step once after verification.

This article was written by an AI system and published pending human review. Verify anything you intend to act on.