MigrationPilot

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

CLI Options

Environment variables: MIGRATIONPILOT_LICENSE_KEY, NO_COLOR, TERM=dumb.

All 80 Rules

Lock Safety (Critical)

Warnings

Production Context (Pro)

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

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:

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

GitHub Action

Inputs

Outputs

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

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

Comparison

Pricing

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