pg-safe-migrate — Stop Shipping Unsafe Postgres Migrations

A safety-first PostgreSQL migration engine for Node.js that catches lock-heavy DDL, deploy races, and schema drift before they hit production.

I’ve seen the same three Postgres migration failures repeat across teams, stacks, and companies. Not because people are careless—because migrations are deceptively easy to write and painfully easy to ship in a way that hurts production.

First: the lock incident. Someone creates an index the “normal” way, Postgres takes a heavy lock, traffic queues, and suddenly everything feels like it’s on fire. Second: the deploy race. Two pipelines trigger, both attempt migrations, and you end up with a failed deploy and a questionable migration state. Third: the drift bug. A migration file gets edited after it already ran, the database no longer matches what the repo “says” it is, and you discover the mismatch weeks later in the worst possible way.

Here’s the project: link

What it does (in plain terms)

• Lints migrations with safety rules so risky SQL is flagged early
• Uses Postgres advisory locks so only one migrator runs at a time
• Stores SHA-256 checksums so edited migrations are detected as drift
• Handles transaction policy realistically (some statements can’t run in a transaction)
• Allows overrides, but makes them explicit and auditable

Safety linting (10 rules)

Before migrations run, pg-safe-migrate checks your SQL and fails fast on patterns that commonly cause outages.

$ npx pg-safe-migrate lint

PGSM003 [ERROR] Index creation should be CONCURRENTLY to avoid blocking writes
  → migrations/20260302_120000_add_users_email_index.sql:3
  Fix: CREATE INDEX CONCURRENTLY IF NOT EXISTS ... 

The point isn’t to be “strict for fun”—it’s to make the safe choice the default.

Deploy races: solved with advisory locks
Migrations acquire a Postgres advisory lock first. If another runner tries to migrate at the same time, it waits or fails (depending on config). The outcome is simple: no more two CI jobs stepping on each other.

Schema drift: solved with checksums
Every applied migration is stored with a SHA-256 checksum in the migration history table. If the file changes after it ran, pg-safe-migrate will catch it:

$ npx pg-safe-migrate check

✗ Drift detected in 20260301_094512_add_posts.sql
  Applied: a1b2c3...
  Current: d4e5f6...

This turns a “mystery bug weeks later” into a CI failure you can address immediately.

Transaction policy that matches Postgres reality
Some operations (like CREATE INDEX CONCURRENTLY) can’t run inside a transaction. pg-safe-migrate supports:

transaction=auto (default): detects and runs non-transactional migrations safely

transaction=always: strict mode (fails if a migration includes non-transactional statements)

transaction=never: runs everything without wrapping

Overrides are allowed, but must be auditable
Sometimes you really do need to do a risky operation. Overrides work, but they require a reason:

-- pgsm:allow PGSM001 reason="Removing deprecated table after 90 days" ticket="CHG-1042"
DROP TABLE IF EXISTS feature_flags;

That way, the “risky thing” is intentional, reviewable, and documented.

Quick start

npm install -D pg-safe-migrate
npx pg-safe-migrate init
npx pg-safe-migrate create add-users-table
npx pg-safe-migrate lint
npx pg-safe-migrate up
npx pg-safe-migrate status
npx pg-safe-migrate check

CI gate (GitHub Action)

• uses: defnotwig/pg-safe-migrate@v1

with:
command: check
database_url: ${{ secrets.DATABASE_URL }}
dir: migrations

If you’re interested, the docs and examples are in the repo, and PRs/issues are welcome:

npm: https://www.npmjs.com/package/pg-safe-migrate

contributing: https://github.com/defnotwig/pg-safe-migrate/blob/main/CONTRIBUTING.md

What’s the most painful migration incident you’ve seen—and what guardrail would have prevented it?

That’s what motivated me to build pg-safe-migrate: a safety-first migration engine for Node.js that tries to catch these problems before they reach production.