A database migration that fails halfway through and can’t be safely re-run is a genuine production incident waiting to happen — writing migrations idempotently (safe to run multiple times) is cheap insurance against exactly that scenario.
-- NOT idempotent: fails on a second run if the column already exists ALTER TABLE users ADD COLUMN last_login TIMESTAMP; -- Idempotent: safe to run multiple times ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login TIMESTAMP; -- Same principle for indexes CREATE INDEX IF NOT EXISTS idx_users_email ON users(email); -- And for data migrations - use a condition that's naturally re-runnable UPDATE users SET status = 'active' WHERE status IS NULL; -- (running this twice is harmless - the second run simply matches zero rows)
Why This Matters Beyond Just Errors
Idempotent migrations also make deployment automation genuinely safer — if a deployment pipeline retries a failed step (common in CI/CD systems), a non-idempotent migration can fail on the retry even though the original attempt actually partially succeeded, turning a transient infrastructure hiccup into a confusing, hard-to-diagnose migration failure that has nothing to do with the migration’s actual logic.