How to Write Idempotent SQL Scripts for Safe Deploys

A deploy pod restarts, the orchestrator retries the migration, and the second run dies with ERROR: relation "users" already exists. Nothing was wrong with the schema — the script just was not safe to run twice. In a pipeline that retries on timeout, rolls forward across multiple replicas, and switches blue/green traffic mid-deploy, a migration that assumes single-run execution is a latent outage. The fix is to make every script converge to the same state no matter how many times it runs. This page shows how to write that script for PostgreSQL and MySQL 8.0, and is the hands-on companion to the broader idempotent script design section within the migration fundamentals guide.

Symptom / Error Signatures

Non-idempotent migrations fail predictably on retry or concurrent execution:

  • PostgreSQL ERROR: relation "users" already exists
  • MySQL ERROR 1050 (42S01): Table 'orders' already exists
  • ERROR: duplicate key value violates unique constraint "pk_users"
  • ERROR: column "status" of relation "orders" already exists
  • Migration orchestrator hangs on DDL lock contention while a second run waits behind the first
  • Rollback scripts fail on DROP TABLE / DROP COLUMN against an object that is already gone

Correlate the failure timestamps against deployment pod restarts, orchestrator retry loops, or blue/green routing events — the second occurrence is the tell.

Root Cause Analysis

The root cause is imperative DDL with no conditional guard. A bare CREATE TABLE or ADD COLUMN is an instruction, not a desired-state declaration: it succeeds exactly once and errors every time after. Traditional migration tooling assumes linear, single-run execution, but modern delivery introduces network timeouts, pod restarts, and parallel rollouts that re-invoke the same script. Without an existence check the RDBMS correctly rejects the duplicate DDL. The problem compounds when transactional and non-transactional statements mix: on MySQL, where DDL forces an implicit commit, a script that fails halfway leaves the schema partially applied, so the retry now faces a state that is neither the before nor the after. That non-atomic behavior is the subject of handling non-transactional DDL in MySQL migrations, and it is why idempotency is a hard prerequisite rather than a nicety.

Immediate Mitigation

When a deploy is stuck mid-pipeline, stabilize before re-running:

  1. Halt CI/CD auto-retries so the orchestrator stops piling DDL attempts onto a contended lock.
  2. Reconcile the actual state before re-running — never blindly drop a production table to “reset”.
  3. Probe what actually exists with read-only catalog queries:
-- PostgreSQL · read-only · safe against production
SELECT EXISTS (SELECT 1 FROM information_schema.tables  WHERE table_name = 'orders');
SELECT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'orders' AND column_name = 'status');

-- MySQL · read-only · safe against production
SELECT COUNT(*) FROM information_schema.tables  WHERE table_schema = DATABASE() AND table_name = 'orders';
SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'orders' AND column_name = 'status';

Only if a partially created object must be removed before re-applying:

-- PostgreSQL · run as migration role · CASCADE drops dependents — confirm FKs first
DROP TABLE IF EXISTS orders CASCADE;

-- MySQL · run as migration role · verify no dependent FKs before dropping
DROP TABLE IF EXISTS orders;

Permanent Fix / Long-Term Pattern

Shift from imperative statements to guarded, desired-state DDL so any run converges. Four techniques cover almost every case:

  • Conditional existence guards. Use native IF NOT EXISTS for tables (both engines) and for columns and indexes on PostgreSQL. MySQL has no IF NOT EXISTS for ADD COLUMN or CREATE INDEX, so guard those by querying information_schema first.
  • Expand/contract. Split a change into an additive phase and a later subtractive phase so each migration is independently safe to retry. This is the backbone of zero-downtime evolution and is detailed in making data backfills idempotent with upserts.
  • Deterministic naming. Name constraints and indexes explicitly; auto-generated names vary across environments and defeat existence checks.
  • Procedural wrappers. Wrap unsupported DDL in an anonymous block (PostgreSQL DO $$) or a prepared statement (MySQL) that checks before it acts.
-- PostgreSQL · run as migration role · DO block is safe to re-run
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'orders' AND column_name = 'status'
    ) THEN
        ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';
    END IF;
END $$;

-- PostgreSQL · run as migration role · IF NOT EXISTS supported since PG 9.5
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders (status);
-- MySQL · run as migration role · DDL implicitly commits, so this prepared guard is the idempotent path
SET @ddl := IF(
    (SELECT COUNT(*) FROM information_schema.columns
     WHERE table_schema = DATABASE()
       AND table_name = 'orders' AND column_name = 'status') = 0,
    'ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT ''pending''',
    'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

For the underlying transaction-boundary differences that decide how aggressive these guards must be, compare your tooling against the migration tool comparison.

Verification Checklist

  • 0 with zero schema deltas
  • CREATE/ADD/DROP is guarded by IF NOT EXISTS, IF EXISTS, a DO block, or an information_schema check
  • pg_stat_activity or SHOW PROCESSLIST
  • pg_dump -s before vs after) shows only the intended changes and no drift

Frequently Asked Questions

Do all SQL dialects support idempotent DDL natively? No. PostgreSQL supports IF NOT EXISTS for tables, columns, and indexes. MySQL supports it for CREATE TABLE / DROP TABLE but not for ADD COLUMN or CREATE INDEX, so those must be guarded with an information_schema lookup and a prepared statement. Constraint idempotency in both databases generally requires querying the system catalog rather than a clause.

How do I make data migrations idempotent, not just schema ones? Use upsert semantics: INSERT ... ON CONFLICT DO NOTHING or ON CONFLICT ... DO UPDATE on PostgreSQL, and INSERT ... ON DUPLICATE KEY UPDATE on MySQL. Wrap each transform in an explicit transaction and verify affected row counts before committing so a retry never double-applies.

Does idempotency actually matter for zero-downtime deploys? Yes — it is a prerequisite. Rolling updates, blue/green switches, and automatic retries all re-invoke migrations. If a script is not safe to run twice, those very mechanisms turn a routine deploy into a schema conflict and an outage during the expand/contract lifecycle.

Related Articles