Migrationpilot

MigrationPilot

Block unsafe Postgres migrations before merge.

Local, deterministic analysis for PostgreSQL migrations. Uses PostgreSQL's parser, checks 112 rules, and exits non-zero in CI. No account required. MIT.

npx migrationpilot analyze migration.sql

Try it in your browser · GitHub Action · Documentation

Benchmark

56 labelled files. Author-built corpus. Tools pinned.

Methodology · Corpus · What MigrationPilot missed · Reproduce: pnpm build && node bench/run.mjs

A finding

-- migration.sql

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

$ migrationpilot analyze migration.sql

✗ MigrationPilot — RED Score: 80/100 migration.sql ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ 1 statement · 2 critical · rollback GREEN

┌─────┬─────────────────────────────────────────────┬─────────────────────────┬────────┬────────────┐ │ # │ Statement │ Lock Type │ Risk │ Long lock? │ ├─────┼─────────────────────────────────────────────┼─────────────────────────┼────────┼────────────┤ │ 1 │ ALTER TABLE users ADD CONSTRAINT users_e... │ ACCESS EXCLUSIVE │ RED │ YES │ └─────┴─────────────────────────────────────────────┴─────────────────────────┴────────┴────────────┘

Violations:

✗ [MP004] CRITICAL (line 1) DDL statement acquires ACCESS EXCLUSIVE lock without a preceding SET lock_timeout. Without a timeout, this statement could block the lock queue indefinitely if it can't acquire the lock, causing cascading query failures.

Safe alternative: -- Set a timeout so DDL fails fast instead of blocking the queue SET lock_timeout = '5s'; ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email) RESET lock_timeout;

Why: Without lock_timeout, if the table is locked by another query, your DDL waits indefinitely. All subsequent queries pile up behind it in the lock queue, causing cascading timeouts across your application. GoCardless enforces a 750ms lock_timeout for this reason. Docs: https://migrationpilot.dev/rules/mp004

✗ [MP027] CRITICAL (line 1) Adding UNIQUE constraint "users_email_unique" on "users" scans the entire table under ACCESS EXCLUSIVE lock. Create the index concurrently first, then use USING INDEX.

Safe alternative: -- Step 1: Create the unique index concurrently (non-blocking) CREATE UNIQUE INDEX CONCURRENTLY users_email_unique_idx ON users (...);

-- Step 2: Add the constraint using the pre-built index (instant) ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_unique_idx;

Why: ALTER TABLE ADD CONSTRAINT UNIQUE builds a unique index while holding ACCESS EXCLUSIVE lock, blocking all reads and writes for the entire scan. Instead, create the unique index concurrently (non-blocking), then attach it as a constraint with USING INDEX. Docs: https://migrationpilot.dev/rules/mp027

Risk Factors: Lock Severity ██████████ 40/40 — ACCESS EXCLUSIVE (long-held) Rule Violations ████████░░ 80/100 — 2 critical

112 rules checked in 11ms

Exit code is 2. The Risk column combines what a statement's lock does with what the rules found in it, so a statement carrying a critical violation reads RED whatever its lock costs. The lock half of that is capped without a database connection — table size and query frequency need one. See Production context.

Contents

Install · AI coding agents · CI · What it checks · Beyond one file · Configuration · Output · Production context · Comparison · Pricing · Architecture · API

Install

npx migrationpilot analyze migration.sql   # no install

npm install -g migrationpilot # global

Node 22 or newer. The PostgreSQL parser ships compiled in, so there is nothing else to set up. Exit codes are the same everywhere: 0 clean, 1 warnings under --fail-on warning, 2 critical.

Packaged builds land with each release, including single-file executables for Linux, macOS and Windows on the release page for machines without Node. The Windows .exe is not code-signed, so SmartScreen and most browsers will warn about it on download — SHA256SUMS on the same release is how you check you got the file we published, not a signature.

brew install mickelsamuel/migrationpilot/migrationpilot

docker run --rm -v "$PWD:/work" ghcr.io/mickelsamuel/migrationpilot:1 analyze migration.sql

On Windows in Git Bash, MSYS rewrites paths inside the mount flag, so use the Windows-form working directory instead:

docker run --rm -v "$(pwd -W):/work" ghcr.io/mickelsamuel/migrationpilot:1 analyze migration.sql

AI coding agents

Agents write migrations now. They are good at SQL and bad at knowing which statement takes an ACCESS EXCLUSIVE lock on a table with 40 million rows, and by then the outage has already happened.

MCP server. Seven tools, the important one being check_before_apply: a pass/fail gate the agent calls before it writes or runs DDL. It resolves your .migrationpilotrc.yml exactly like the CLI does, so its verdict is the verdict CI will give.

{

"mcpServers": { "migrationpilot": { "command": "npx", "args": ["migrationpilot-mcp"] } } }

Claude Code plugin. integrations/claude-code/ pairs a skill that tells Claude to check migrations with a PreToolUse hook that blocks the tool call when it doesn't. It fails open on purpose: a missing install, unparseable SQL, or a timeout lets the call through with a note on stderr, because a guardrail that breaks your workflow when it can't run gets uninstalled.

claude plugin install ./integrations/claude-code

Cursor and Copilot. Copy integrations/cursor/migrationpilot.mdc into .cursor/rules/, or paste integrations/copilot/copilot-instructions-snippet.md into .github/copilot-instructions.md. Both tell the agent when to run MigrationPilot and that suppressing a rule to get past a violation is the user's call, not the agent's.

CI

GitHub Action

# .github/workflows/migration-check.yml

name: Migration Safety Check on: [pull_request]

New repositories default the workflow token to read-only; the report comment

needs pull-request write.

permissions: contents: read pull-requests: write

jobs: check: runs-on: ubuntu-latest steps:

• uses: actions/checkout@v4
• uses: mickelsamuel/migrationpilot@v1 with: migration-path: "migrations/*.sql" fail-on: critical

Posts a report as a PR comment, fails the check on critical violations, and writes a SARIF file. To feed it into Code Scanning, add an upload step (needs Advanced Security on private repos):

      - uses: github/codeql-action/upload-sarif@v3

if: always() with: sarif_file: migrationpilot-results.sarif

Without the permissions block the Action still runs. It warns, analyzes every file matching the glob instead of only the ones the PR changed, and skips the comment. The check verdict, the SARIF file and the inline annotations come from the analysis either way.

A migration the Action can't read or parse fails the check, since nothing analyzed it, and the failure names the file and the parser's error. Files that aren't plain SQL, like ones with psql meta-commands or template placeholders, go under ignore in the config. With fail-on: never they're reported and the check passes.

The Action reads the same config file as the CLI, so ignore, rules, failOn, pgVersion and extends all apply there. An input you set in the workflow wins over the config. With an Org license key, so do the config's policy block and audit log, and the policy wins over the inputs: exclude can't drop a required rule, and a blocked statement fails the check even under fail-on: never.

Outputs: risk-level, violations, sarif-file.

Pre-commit

migrationpilot hook install writes a plain git hook and is Husky-aware. With the pre-commit framework instead:

repos:

• repo: https://github.com/mickelsamuel/migrationpilot rev: v1.6.0 hooks:
• id: migrationpilot args: [--fail-on, warning]

Clean files print nothing. Only migrations with violations are reported.

If pre-commit install answers Cowardly refusing to install hooks with 'core.hooksPath' set, something else already owns your hooks directory — Husky sets it. Check with git config core.hooksPath, then either git config --unset-all core.hooksPath and let pre-commit manage the hooks, or keep Husky and run migrationpilot hook install, which appends to .husky/pre-commit instead of fighting it.

GitLab CI

include:

• remote: 'https://raw.githubusercontent.com/mickelsamuel/migrationpilot/v1.6.0/integrations/gitlab/.gitlab-ci-migrationpilot.yml'

migrationpilot: variables: MIGRATIONPILOT_PATH: db/migrate

Runs on merge requests that touch migrations, keeps the JSON report as an artifact, and anno