Project Awesome project awesome

MigrationPilot

PostgreSQL migration safety CLI that catches dangerous DDL before production — 80 rules, lock classification, auto-fix, GitHub Action.

Package 3 stars GitHub

MigrationPilot

npm version CI License: MIT

Know exactly what your PostgreSQL migration will do to production — before you merge.

MigrationPilot is a static analysis tool for PostgreSQL schema migrations. It parses your SQL with the actual PostgreSQL parser (libpg-query), classifies every lock acquired, flags dangerous patterns with 83 safety rules, scores overall risk, and suggests safe alternatives — all without touching your database. Works as a CLI, a GitHub Action, and a Node.js library.


Quick Start

Analyze any migration file — no install required:

npx migrationpilot analyze migration.sql

That's it. One command, instant results. MigrationPilot parses your SQL with the real PostgreSQL parser, checks 83 safety rules, and tells you exactly what's dangerous.

Example

Given this migration:

-- 002_add_index_and_constraint.sql
CREATE INDEX idx_users_email ON users (email);
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

MigrationPilot catches:

  MigrationPilot — 002_add_index_and_constraint.sql

  Risk:  RED   Score: 80/100

  Violations:

  ✗ [MP001] CRITICAL
    CREATE INDEX blocks writes on "users". Use CREATE INDEX CONCURRENTLY.
    Safe alternative:
    CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

  ✗ [MP027] CRITICAL
    UNIQUE constraint without USING INDEX scans full table under ACCESS EXCLUSIVE.

  ⚠ [MP004] WARNING
    No SET lock_timeout before DDL on "users".
    Auto-fixable: run with --fix

  83 rules checked in 23ms

More CLI usage

# Check all migrations in a directory
npx migrationpilot check migrations/ --pattern "*.sql"

# Auto-fix what's fixable
npx migrationpilot analyze migration.sql --fix

# Pipe from Django, Prisma, Knex, or any framework
python manage.py sqlmigrate myapp 0042 | npx migrationpilot analyze --stdin

GitHub Action

Add migration safety checks to every PR in 30 seconds:

# .github/workflows/migration-check.yml
name: Migration Safety Check
on: [pull_request]

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 safety report as a PR comment, fails the check on critical violations, and generates SARIF for GitHub Code Scanning.


CLI Commands

Command Description
analyze <file> Analyze a single SQL migration file
check <dir> Check all migration files in a directory
plan <file> Show execution plan with timeline visualization
init Generate a .migrationpilotrc.yml config file
detect Auto-detect migration framework (14 supported)
watch <dir> Watch migration files and re-analyze on change
hook Install/uninstall git pre-commit hook
list-rules List all 83 safety rules with metadata
doctor Run diagnostic checks on your environment
completion <shell> Generate shell completion scripts (bash/zsh/fish)
drift Compare two database schemas for differences
trends View historical analysis trends
explain <rule> Show detailed information about a specific rule
rollback <file> Generate reverse DDL for migration rollback

CLI Options

Option Commands Description Default
--pg-version <n> analyze, check Target PostgreSQL version (9-20) 17
--format <fmt> analyze, check Output: text, json, sarif, markdown text
--fail-on <level> analyze, check Exit code threshold: critical, warning, never critical
--fix analyze Auto-fix violations in-place (12 rules)
--dry-run analyze Preview auto-fix changes without writing
--quiet analyze, check One-line-per-violation (gcc-style) output
--verbose analyze Per-statement PASS/FAIL for all rules
--stdin analyze Read SQL from stdin instead of file
--exclude <rules> analyze, check Comma-separated rule IDs to skip
--database-url <url> analyze, check PostgreSQL connection for production context (Pro)
--license-key <key> analyze, check License key for Pro features
--pattern <glob> check File pattern for directory scanning *.sql
--output <file> analyze, check Write report to file instead of stdout
--offline analyze, check Air-gapped mode: skip network access
--no-config analyze, check Ignore config file
--no-color all Disable colored output
--json list-rules Output rules as JSON array

Environment variables: MIGRATIONPILOT_LICENSE_KEY, NO_COLOR, TERM=dumb.


All 80 Rules

Lock Safety (Critical)

Rule Name Auto-fix What it catches
MP001 require-concurrent-index Yes CREATE INDEX without CONCURRENTLY blocks writes
MP002 require-check-not-null SET NOT NULL without CHECK pattern scans full table
MP003 volatile-default-rewrite ADD COLUMN DEFAULT now() rewrites table on PG < 11
MP004 require-lock-timeout Yes DDL without SET lock_timeout blocks queue
MP005 require-not-valid-fk ADD CONSTRAINT FK without NOT VALID scans full table
MP006 no-vacuum-full VACUUM FULL rewrites table under ACCESS EXCLUSIVE
MP007 no-column-type-change ALTER COLUMN TYPE rewrites table
MP008 no-multi-ddl-transaction Multiple DDL in one transaction compound lock time
MP025 ban-concurrent-in-transaction CONCURRENTLY ops inside a transaction = runtime ERROR
MP026 ban-drop-table DROP TABLE permanently removes table and data
MP027 disallowed-unique-constraint UNIQUE constraint without USING INDEX scans full table
MP030 require-not-valid-check Yes CHECK constraint without NOT VALID scans full table
MP031 ban-exclusion-constraint EXCLUSION constraint builds GiST index under ACCESS EXCLUSIVE
MP032 ban-cluster CLUSTER rewrites table under ACCESS EXCLUSIVE
MP034 ban-drop-database DROP DATABASE in a migration file
MP035 ban-drop-schema DROP SCHEMA permanently removes schema + objects
MP036 ban-truncate-cascade TRUNCATE CASCADE silently truncates FK-referencing tables
MP046 require-concurrent-detach-partition Yes DETACH PARTITION without CONCURRENTLY (PG 14+)
MP047 ban-set-logged-unlogged SET LOGGED/UNLOGGED rewrites entire table
MP049 require-partition-key-in-pk Partitioned table PK doesn't include partition key columns
MP055 drop-pk-replica-identity-break Dropping PK breaks logical replication
MP057 rls-enabled-without-policy ENABLE RLS without CREATE POLICY = silent deny-all
MP060 alter-type-rename-value RENAME VALUE breaks logical replication subscribers
MP064 ban-disable-trigger DISABLE TRIGGER ALL/USER breaks replication + FK enforcement
MP065 ban-lock-table Explicit LOCK TABLE blocks queries and can cause deadlocks
MP062 ban-add-generated-stored-column Stored generated column causes full table rewrite
MP069 warn-fk-lock-both-tables FK constraint locks both source and referenced table
MP072 warn-partition-default-scan ATTACH PARTITION scans DEFAULT partition under lock
MP073 ban-superuser-role ALTER SYSTEM / CREATE ROLE SUPERUSER in migrations

Warnings

Rule Name Auto-fix What it catches
MP009 require-drop-index-concurrently Yes DROP INDEX without CONCURRENTLY
MP010 no-rename-column RENAME COLUMN breaks app queries
MP011 unbatched-backfill UPDATE without WHERE (full table scan)
MP012 no-enum-add-in-transaction ALTER TYPE ADD VALUE inside transaction
MP015 no-add-column-serial SERIAL creates implicit sequence (use IDENTITY)
MP016 require-fk-index FK columns without index = slow cascading deletes
MP017 no-drop-column DROP COLUMN under ACCESS EXCLUSIVE
MP018 no-force-set-not-null SET NOT NULL without CHECK pre-validation
MP020 require-statement-timeout Yes Long-running DDL without statement_timeout
MP021 require-concurrent-reindex Yes REINDEX without CONCURRENTLY (PG 12+)
MP022 no-drop-cascade DROP CASCADE silently drops dependents
MP023 require-if-not-exists Yes CREATE TABLE/INDEX without IF NOT EXISTS
MP024 no-enum-value-removal DROP TYPE destroys enum + dependent columns
MP028 no-rename-table RENAME TABLE breaks queries, views, FKs
MP029 ban-drop-not-null DROP NOT NULL may break app assumptions
MP033 require-concurrent-refresh-matview Yes REFRESH MATERIALIZED VIEW without CONCURRENTLY
MP037 prefer-text-over-varchar Yes VARCHAR(n) has no benefit over TEXT in PostgreSQL
MP038 prefer-bigint-over-int INT PK/FK columns can overflow (use BIGINT)
MP039 prefer-identity-over-serial SERIAL quirks — use GENERATED ALWAYS AS IDENTITY
MP040 prefer-timestamptz Yes TIMESTAMP without timezone causes timezone bugs
MP041 ban-char-field Yes CHAR(n) wastes space, causes comparison bugs
MP042 require-index-name Unnamed indexes are hard to reference
MP043 ban-domain-constraint Domain constraints validate against ALL using columns
MP044 no-data-loss-type-narrowing Narrowing column type (e.g., BIGINT → INT) can lose data
MP045 require-primary-key Tables without PK break replication
MP048 ban-alter-default-volatile Volatile SET DEFAULT on existing column is misleading
MP050 prefer-hnsw-over-ivfflat pgvector: HNSW index preferred over IVFFlat
MP051 require-spatial-index PostGIS: spatial columns need GIST/SPGIST index
MP052 warn-dependent-objects DROP/ALTER COLUMN may break views, functions, triggers
MP053 ban-uncommitted-transaction BEGIN without matching COMMIT leaves open transaction
MP054 alter-type-add-value-in-transaction New enum value not visible until COMMIT
MP056 gin-index-on-jsonb-without-expression Plain GIN on JSONB useless for ->> queries
MP058 multi-alter-table-same-table Multiple ALTER TABLE on same table = extra lock cycles
MP059 sequence-not-reset-after-data-migration INSERT with explicit IDs without setval()
MP061 suboptimal-column-order Variable-length columns before fixed-size wastes padding
MP063 warn-do-block-ddl DO block contains DDL that bypasses static analysis
MP066 warn-autovacuum-disabled autovacuum_enabled = false causes bloat + wraparound risk
MP067 warn-backfill-no-batching DELETE without WHERE clause = full table lock + WAL bloat
MP068 warn-integer-pk-capacity CREATE SEQUENCE AS integer risks overflow — use bigint
MP070 warn-concurrent-index-invalid CONCURRENTLY can leave invalid index on failure
MP071 ban-rename-in-use-column RENAME COLUMN without updating dependent views/functions
MP074 require-deferrable-fk FK constraints should be DEFERRABLE for bulk loading
MP075 warn-toast-bloat-risk UPDATE on TOAST columns causes bloat from full-row copies
MP076 warn-xid-consuming-retry SAVEPOINT creates subtransactions consuming XIDs
MP077 prefer-lz4-toast-compression Use lz4 over pglz for TOAST compression (PG 14+)
MP078 warn-extension-version-pin CREATE EXTENSION without VERSION = non-deterministic
MP079 warn-rls-policy-completeness RLS policies don't cover all operations (SELECT/INSERT/UPDATE/DELETE)
MP080 ban-data-in-migration DML (INSERT/UPDATE/DELETE) mixed with DDL in same migration

Production Context (Pro)

Rule Name What it catches
MP013 high-traffic-table-ddl DDL on tables with 10K+ queries
MP014 large-table-ddl Long-held locks on tables with 1M+ rows
MP019 no-exclusive-lock-high-connections ACCESS EXCLUSIVE with many active connections

Auto-fix: 12 rules can be automatically fixed with --fix: MP001, MP004, MP009, MP020, MP021, MP023, MP030, MP033, MP037, MP040, MP041, MP046.


Features

Auto-fix

migrationpilot analyze migration.sql --fix         # Fix in-place
migrationpilot analyze migration.sql --fix --dry-run  # Preview changes

Automatically fixes: missing CONCURRENTLY, lock_timeout, statement_timeout, NOT VALID on CHECK constraints, CONCURRENTLY on materialized view refresh.

Framework Detection

migrationpilot detect
# → Detected: Prisma (prisma/migrations/)

Detects 14 frameworks: Flyway, Liquibase, Alembic, Django, Knex, Prisma, TypeORM, Drizzle, Sequelize, goose, dbmate, Sqitch, Rails, Ecto.

Watch Mode

migrationpilot watch migrations/ --pattern "*.sql"

Re-analyzes on file changes with intelligent debouncing.

Pre-commit Hook

migrationpilot hook install   # Installs git hook (supports Husky)
migrationpilot hook uninstall

Execution Plan

migrationpilot plan migration.sql

Visual timeline showing lock type, duration estimate, blocking impact, and transaction boundaries for each statement.

Schema Drift Detection

migrationpilot drift \
  --source postgresql://localhost/production \
  --target postgresql://localhost/staging

Compares tables, columns, indexes, constraints, and sequences between two databases.

MCP Server

MigrationPilot includes a Model Context Protocol server for AI assistant integration:

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

Exposes 4 tools: analyze_migration, suggest_fix, explain_lock, list_rules.

Historical Trends

migrationpilot trends

Stores analysis results in ~/.migrationpilot/history/ and tracks risk scores, violation counts, and improvement trends over time.

Air-Gapped Mode

migrationpilot analyze migration.sql --offline

Skips update checks and network access. Ed25519 license keys validate entirely client-side — no phone-home, no telemetry.

Config File

# .migrationpilotrc.yml
extends: "migrationpilot:strict"   # Built-in preset
pgVersion: 16
failOn: warning
rules:
  MP037: false                     # Disable a rule
  MP004:
    severity: warning              # Downgrade severity
  MP013:
    threshold: 5000                # Custom threshold
ignore:
  - "migrations/seed_*.sql"

Five built-in presets: migrationpilot:recommended (default), migrationpilot:strict (all rules at critical, fail on warning), migrationpilot:ci, migrationpilot:startup (relaxed for early-stage), migrationpilot:enterprise (maximum safety with audit logging).

Inline Disable

-- migrationpilot-disable MP001
CREATE INDEX idx_users_email ON users (email);

-- migrationpilot-disable-next-line MP004
ALTER TABLE users ADD COLUMN bio TEXT;

Severity Overrides

Override any rule's severity via config:

rules:
  MP009:
    severity: critical   # Upgrade from warning to critical
  MP007:
    severity: warning    # Downgrade from critical to warning

PG Version Awareness

Rules adapt their advice based on --pg-version:

  • PG 10+: Recommends GENERATED ALWAYS AS IDENTITY over SERIAL
  • PG 11+: Non-volatile ADD COLUMN DEFAULT is safe (no rewrite)
  • PG 12+: REINDEX CONCURRENTLY available
  • PG 14+: DETACH PARTITION CONCURRENTLY available
  • PG 18+: SET NOT NULL NOT VALID + VALIDATE NOT NULL pattern

Output Formats

Format Flag Use case
Text --format text (default) Terminal with colors, tables, risk bars
JSON --format json Scripting, CI pipelines (versioned $schema)
SARIF --format sarif GitHub Code Scanning, VS Code, IntelliJ
Markdown --format markdown Docs, wikis, Notion
Quiet --quiet One-line-per-violation (gcc-style)
Verbose --verbose Per-statement PASS/FAIL for all 83 rules

JSON Schema

JSON output includes a $schema URL and version field for reliable parsing:

{
  "$schema": "https://migrationpilot.dev/schemas/report-v1.json",
  "version": "1.5.0",
  "file": "migrations/001.sql",
  "riskLevel": "RED",
  "riskScore": 80,
  "violations": [...]
}

SARIF

# CLI
migrationpilot analyze migration.sql --format sarif > results.sarif

# GitHub Action (auto-generated)
- uses: github/codeql-action/upload-sarif@v3
  with:
    sarif_file: ${{ steps.migration-check.outputs.sarif-file }}

Production Context (Pro)

With a Pro license + --database-url, MigrationPilot connects read-only to query system catalogs:

Data Source What it provides
pg_class Table row counts, total size, index count
pg_stat_statements Affected queries by call frequency
pg_stat_activity Active connections on target tables

This data feeds into risk scoring (table size 0-30 pts, query frequency 0-30 pts) and unlocks rules MP013, MP014, MP019.

Safety: Only reads pg_stat_* and pg_class. Never reads user data. Never runs DDL. Single read-only connection with timeouts.


Risk Scoring

Factor Weight Free Pro
Lock Severity 0-40 Yes Yes
Table Size 0-30 Yes
Query Frequency 0-30 Yes
Level Score Meaning
GREEN 0-24 Safe to deploy
YELLOW 25-49 Review recommended
RED 50-100 Dangerous — use safe alternative

GitHub Action

Inputs

Input Description Required Default
migration-path Glob pattern for SQL files Yes
github-token GitHub token for PR comments No ${{ github.token }}
license-key Pro license key No
database-url PostgreSQL connection (Pro) No
pg-version Target PostgreSQL version No 17
fail-on Fail threshold: critical, warning, never No critical

Outputs

Output Description
risk-level Overall: RED, YELLOW, or GREEN
violations Total violation count
sarif-file Path to SARIF file for Code Scanning upload

With Production Context + Code Scanning (Pro)

- uses: mickelsamuel/migrationpilot@v1
  id: migration-check
  with:
    migration-path: "migrations/*.sql"
    license-key: ${{ secrets.MIGRATIONPILOT_LICENSE_KEY }}
    database-url: ${{ secrets.DATABASE_URL }}
    fail-on: critical

- uses: github/codeql-action/upload-sarif@v3
  if: always()
  with:
    sarif_file: ${{ steps.migration-check.outputs.sarif-file }}

PostgreSQL Lock Reference

DDL Operation Lock Type Blocks Reads Blocks Writes Long-held
CREATE INDEX SHARE No Yes Yes
CREATE INDEX CONCURRENTLY SHARE UPDATE EXCLUSIVE No No No
DROP INDEX ACCESS EXCLUSIVE Yes Yes No
DROP INDEX CONCURRENTLY SHARE UPDATE EXCLUSIVE No No No
ADD COLUMN (no default) ACCESS EXCLUSIVE Yes Yes No
ADD COLUMN DEFAULT now() ACCESS EXCLUSIVE Yes Yes Yes*
ALTER COLUMN TYPE ACCESS EXCLUSIVE Yes Yes Yes
SET NOT NULL ACCESS EXCLUSIVE Yes Yes Yes
ADD CONSTRAINT FK ACCESS EXCLUSIVE Yes Yes Yes
ADD CONSTRAINT FK NOT VALID ACCESS EXCLUSIVE Yes Yes No
VALIDATE CONSTRAINT SHARE UPDATE EXCLUSIVE No No No
RENAME COLUMN/TABLE ACCESS EXCLUSIVE Yes Yes No
DROP TABLE ACCESS EXCLUSIVE Yes Yes No
TRUNCATE ACCESS EXCLUSIVE Yes Yes No
CLUSTER ACCESS EXCLUSIVE Yes Yes Yes
VACUUM FULL ACCESS EXCLUSIVE Yes Yes Yes
VACUUM SHARE UPDATE EXCLUSIVE No No No
REINDEX ACCESS EXCLUSIVE Yes Yes Yes
REINDEX CONCURRENTLY SHARE UPDATE EXCLUSIVE No No No
REFRESH MATERIALIZED VIEW ACCESS EXCLUSIVE Yes Yes Yes
REFRESH MATERIALIZED VIEW CONCURRENTLY SHARE UPDATE EXCLUSIVE No No No
SET LOGGED/UNLOGGED ACCESS EXCLUSIVE Yes Yes Yes
DETACH PARTITION ACCESS EXCLUSIVE Yes Yes No
DETACH PARTITION CONCURRENTLY SHARE UPDATE EXCLUSIVE No No No

* On PG < 11, volatile defaults cause full table rewrite. On PG 11+, evaluated per-row at read time.


Comparison

MigrationPilot Squawk Atlas
Total rules 80 31 ~15
Free rules 77 31 0 (paywalled since v0.38)
Auto-fix 12 rules 0 0
Output formats 6 (text, JSON, SARIF, markdown, quiet, verbose) 3 2
Framework detection 14 frameworks 0 0
Watch mode Yes No No
Pre-commit hooks Yes No No
Execution plan Yes No No
Config file + presets 5 presets 0 0
PG-version-aware Full (9-20) Partial Partial
Programmatic API Node.js No Go
GitHub Action Yes Yes Yes
SARIF for Code Scanning Yes No No
Inline disable comments Yes Yes No
Open source MIT Apache 2.0 Proprietary

Pricing

Feature Free Pro ($19/mo) Team ($49/mo) Enterprise
80 free safety rules (83 total) Yes Yes Yes Yes
All output formats + GitHub Action Yes Yes Yes Yes
Auto-fix (12 rules) Yes Yes Yes Yes
Config file + 5 presets Yes Yes Yes Yes
Production context Yes Yes Yes
Production rules (MP013, MP014, MP019) Yes Yes Yes
Custom rules plugin API Yes Yes
Team seats 1 Up to 10 Unlimited
Team seat management Yes Yes
Audit logging Yes Yes
Policy enforcement Yes
SSO / SAML + air-gapped mode Yes

Get a license key at migrationpilot.dev.


Architecture

src/
├── parser/        # DDL parsing with libpg-query WASM (actual PG parser)
├── locks/         # Lock type classification (pure lookup table)
├── rules/         # 83 safety rules (MP001-MP083), engine, registry, helpers
├── production/    # Production context queries (Pro: pg_stat_*, pg_class)
├── scoring/       # Risk scoring (RED/YELLOW/GREEN, 0-100)
├── generator/     # Safe migration SQL generation
├── output/        # CLI, JSON, SARIF, markdown, PR comment, execution plan
├── analysis/      # Shared pipeline, transaction boundaries, migration ordering
├── fixer/         # Auto-fix engine (12 rules)
├── frameworks/    # Migration framework detection (14 frameworks)
├── watch/         # File watcher with debounce
├── hooks/         # Git pre-commit hook installer
├── config/        # Config loader (.migrationpilotrc.yml, presets)
├── license/       # Ed25519 license key validation
├── billing/       # Stripe checkout + webhook + email delivery
├── action/        # GitHub Action entry point
├── doctor/        # Diagnostic checks (node version, config, latest version)
├── completion/    # Shell completion generators (bash/zsh/fish)
├── update/        # npm update checker with cache
├── prompts/       # One-time prompts (GitHub star)
├── drift/         # Schema drift detection (compare two DBs)
├── history/       # Historical analysis storage and trends
├── audit/         # Enterprise audit logging (JSONL)
├── mcp/           # MCP server (Model Context Protocol)
├── usage/         # Free usage tracking (3 production analyses/month)
├── team/          # Team management (seats, members, activity)
├── policy/        # Policy enforcement (required rules, severity floors)
├── auth/          # SSO authentication (device code flow, API keys)
├── index.ts       # Programmatic API (45+ exports)
└── cli.ts         # CLI entry point (20 commands)

Programmatic API

import { analyzeSQL, allRules, parseMigration, classifyLock } from 'migrationpilot';

const result = await analyzeSQL(sql, 'migration.sql', 16, allRules);
console.log(result.violations);
console.log(result.overallRisk);

Full TypeScript types included.


Development

pnpm install
pnpm test          # 970+ tests across 54 files
pnpm dev analyze path/to/migration.sql
pnpm build         # CLI 1.0MB, Action 1.6MB, API 390KB, MCP 1.2MB
pnpm lint
pnpm typecheck

Contributing

See CONTRIBUTING.md for development setup and guidelines.

Security

See SECURITY.md for our security policy.

License

MIT

Back to PostgreSQL