## 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.

```text
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:

```sql
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.

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

```sql
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.

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

```sql
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.

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

```sql
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".

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

```sql
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.

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

```shell
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