mgcheck

v0.2.8

Published

18 days ago

Migration Guardian — Catch unsafe database migrations before they hit production

0High
0Medium
0Low

aditya31032006

migration database postgresql postgres safety linter prisma drizzle sql ci mcp shadow-database migration-check

Migration Guardian

`mgcheck` — catch unsafe database migrations before they lock production

Compatible with PostgreSQL, Supabase, Neon, AWS RDS/Aurora, Cloud SQL, TimescaleDB & CockroachDB. Whether the migration was written by you, a teammate, or an AI coding agent.

npx mgcheck

Quickstart · How it works · Safety rules · Showcase · AI agents · CI · Configuration · API · Security · Roadmap · Contributing

Free and open source (MIT). Migration Guardian is an early-stage, community-driven project. If you've ever been paged because a migration locked a table, you already have the domain knowledge to help — new rules, framework detectors and fixes are very welcome.

🧭 Why Migration Guardian?

You can roll back application code in seconds. A bad schema migration can take your database down in milliseconds — and it usually looks completely harmless in review.

-- Looks fine. Passes local tests on 10 rows. Gets approved in review.
CREATE INDEX idx_users_email ON users(email);

-- In production:
--   • Takes a SHARE lock on "users" → every INSERT/UPDATE/DELETE blocks
--   • Requests queue behind the lock, the connection pool saturates
--   • Your API starts timing out while the index builds

This class of bug is getting more common, not less. AI coding agents (Claude Code, Cursor, Copilot, Windsurf, …) are great at writing the DDL that produces the schema you asked for, but they have no idea how big your tables are or how PostgreSQL locks behave under load.

Migration Guardian sits between whoever writes the migration and the database that runs it. It parses every statement with the real PostgreSQL grammar, flags dangerous lock and rewrite patterns, dry-runs the migration on a throwaway shadow database, and makes a human sign off on anything risky before it reaches a real database.

✨ Features

| | | | :--- | :--- | | 🔬 Real PostgreSQL parser | Uses pgsql-parser — PostgreSQL's own grammar compiled to WebAssembly — so rules inspect real AST nodes rather than guessing from text. Statements the parser can't read are flagged (MG014) and checked by text matching. | | 🧪 Shadow database dry-run | Executes the migration on an in-process PGlite instance (no Docker) or a disposable postgres:16-alpine container, then throws it away. | | 🚦 Risk-based approval gate | Every migration is classified 🟢 LOW / 🟡 MEDIUM / 🔴 HIGH. HIGH risk is never auto-approved, under any configuration. | | 🤖 MCP server for AI agents | One command wires check_migration, analyze_migration and apply_migration tools into 13 AI clients. | | 👁️ Live terminal watcher | Approve, reject or override agent migrations in real time. Switch auto ⇄ manual mode with Shift+Tab. | | 🔁 Agent retry loop | When a fixable violation is found, the agent gets the suggested safe SQL and is asked to rewrite and retry (up to 3 attempts). | | 🛠️ Auto-fix | Rewrites common unsafe patterns into zero-downtime equivalents, shows a diff, and re-verifies the fix on a shadow DB. | | 🪝 Pre-commit hook & CI | Block unsafe migrations at git commit and in pull requests (bundled GitHub Action included). | | 📜 Audit log | Every intercepted migration, its risk level and the human decision are appended to .mgcheck/audit.jsonl. | | 🧩 Framework-aware | Auto-detects Prisma, Drizzle, Supabase, Flyway, Goose, dbmate, golang-migrate and plain .sql. |

⚡ Quickstart

Requirements: Node.js 18+. Docker is optional — use --provider pglite to run everything in-process.

Choose your setup

1. Connect your AI agents and start the watcher

npx mgcheck

The setup wizard detects installed AI clients, registers the MCP server, and opens the live approval watcher.

2. Lint migrations statically (no database needed)

npx mgcheck analyze ./migrations

3. Lint + dry-run on a shadow database

npx mgcheck run ./migrations --provider pglite --seed ./schema.sql

4. Auto-fix an unsafe migration

npx mgcheck fix ./migrations/20260101_add_email_index.sql

5. Guard every commit

npx mgcheck hook install

🔍 How it works

                 Developer  /  AI coding agent
                              │
                              ▼
 ┌──────────────────────────────────────────────────────────────┐
 │ ① Detect      Prisma · Drizzle · Supabase · Flyway · Goose · │
 │               dbmate · golang-migrate · raw .sql             │
 └──────────────────────────────┬───────────────────────────────┘
                                ▼
 ┌──────────────────────────────────────────────────────────────┐
 │ ② Analyze     PostgreSQL AST (WASM) checked against 14 rules │
 │               → locks, table rewrites, data loss, rollbacks  │
 └──────────────────────────────┬───────────────────────────────┘
                                ▼
 ┌──────────────────────────────────────────────────────────────┐
 │ ③ Shadow run  Execute on throwaway PGlite or Docker Postgres │
 │               (optionally seeded with your existing schema)  │
 └──────────────────────────────┬───────────────────────────────┘
                                ▼
 ┌──────────────────────────────────────────────────────────────┐
 │ ④ Gate        🟢 LOW     → approved                           │
 │               🟡 MEDIUM  → y/N prompt  (or agent retry, auto) │
 │               🔴 HIGH    → blocked unless human types         │
 │                            "override"                        │
 └──────────────────────────────┬───────────────────────────────┘
                                ▼
 ┌──────────────────────────────────────────────────────────────┐
 │ ⑤ Apply       Optional: run verified SQL on the target DB    │
 │               Every decision → .mgcheck/audit.jsonl          │
 └──────────────────────────────────────────────────────────────┘

🛡️ The 14 Safety Rules

error rules block the migration (🔴 HIGH risk). warning rules are reported and require approval (🟡 MEDIUM risk).

Any rule can be re-leveled or disabled per project — see Configuration.

Input:

CREATE INDEX idx_users_email ON users(email);
ALTER TABLE users ADD COLUMN is_active boolean NOT NULL;

Rewritten (and re-verified on a shadow DB before you're asked to accept it):

SET lock_timeout = '5s';

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);
ALTER TABLE users ADD COLUMN is_active boolean DEFAULT false NOT NULL;

Destructive statements (DROP TABLE, DROP COLUMN, TRUNCATE, DROP SCHEMA, DROP DATABASE) are never rewritten automatically.

🤖 AI Agent Integration (MCP)

Migration Guardian ships an MCP server so AI agents check their own migrations before running them — and so you stay in control of what reaches the database.

Supported clients (auto-configured by `npx mgcheck`)

Target a single client, or also write workspace instruction files (CLAUDE.md, AGENTS.md, .cursorrules, .windsurfrules, .cursor/rules/, .github/copilot-instructions.md):

npx mgcheck setup --client cursor
npx mgcheck setup --client all --with-rules --yes

Manual configuration

For any other MCP-compatible client:

{
  "mcpServers": {
    "migration-guardian": {
      "command": "npx",
      "args": ["-y", "mgcheck", "mcp"]
    }
  }
}

Tools exposed to agents

| Tool | What it does | | :--- | :--- | | check_migration | AST analysis and shadow-DB execution (pglite by default). Verifies only; it applies to the target database only when called with auto_apply: true. | | analyze_migration | Fast static analysis only — no database required. | | apply_migration | Applies verified SQL to the real target database. The agent must pass confirm_user_approved: true; 🟢 LOW (and 🟡 MEDIUM in auto mode) then run without a watcher prompt, anything else needs approval in the watcher. Target URL falls back to DATABASE_URL / TARGET_DATABASE_URL. |

The live watcher

npx mgcheck (or mgcheck setup) leaves a watcher running in your terminal. Requests from every project and editor stream into it:

  👁️  Guardian Active — Watching for AI migration activity...
  ─────────────────────────────────────────────────────────
  ● manual mode (shift+tab to cycle) · type /auto or /manual

  🔔 Approval Request [HIGH RISK]
  Client: Cursor (my-web-app)
  Query:  DROP TABLE "legacy_users";
  Violations:
    • [MG006] DROP TABLE is destructive and causes permanent data loss

  Type "override" to approve this query or press Enter to reject: _

Switch approval mode on the fly with Shift+Tab / Shift+Enter, or type /auto or /manual.

🚦 Approval Modes

[!IMPORTANT] high can never be added to autoApprove. Config that tries to do so is rejected with an error.

npx mgcheck mode                       # show active mode and where it came from
npx mgcheck mode auto                  # set globally   (~/.mgcheck/settings.json)
npx mgcheck mode manual --project      # set per project (.mgcheckrc.json)
npx mgcheck mode auto --levels low     # auto mode, but only auto-approve LOW

Resolution order: MGCHECK_MODE env var → project config → global settings → manual.

⌨️ CLI Reference

💡 Tip: Every command below can be run without install via npx mgcheck <command>. If installed globally (npm install -g mgcheck), you can use mgcheck <command> directly.

analyze supports --format, --ci, --confirm-destructive, --verbose and --config. fix supports --seed, --provider (default pglite) and --config.

Exit codes

| Code | Meaning | | :---: | :--- | | 0 | Passed, no violations | | 1 | Failed — blocking violation or shadow execution error | | 2 | Passed with warnings | | 3 | Tool error (bad path, invalid config, …) |

🧩 Supported Databases & Frameworks

Databases & Cloud Platforms

Compatible with standard PostgreSQL and all major Postgres-wire and compatible cloud engines:

Migration Frameworks (Auto-Detected)

Point npx mgcheck at any migration directory or file — the format is detected automatically:

| Framework | Detected by | | :--- | :--- | | Prisma | migration.sql files in prisma/migrations/*/ | | Drizzle | meta/_journal.json next to .sql files | | Supabase | supabase/migrations/ layout | | Flyway | V*__*.sql / U*__*.sql | | golang-migrate | *.up.sql / *.down.sql pairs | | Goose | -- +goose directives | | dbmate | -- migrate: directives | | Raw SQL | Any .sql file or directory of .sql files |

🔄 CI / CD

GitHub Action (bundled)

name: Migration safety

on:
  pull_request:
    paths: ['migrations/**', 'prisma/migrations/**', 'drizzle/**', 'supabase/migrations/**']

jobs:
  mgcheck:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: Aditya31032006/agent-safe-migrate/action@main
        with:
          migration-path: ./migrations
          seed-path: ./schema.sql      # optional
          provider: pglite             # or docker

Outputs: result (JSON report) and passed (true / false). Warnings-only runs (exit code 2) count as passed.

Any other CI

npx mgcheck run ./migrations --ci --provider pglite

Pre-commit hook

npx mgcheck hook install

Staged .sql files are checked with fast static analysis. Errors block the commit; warnings are printed but don't. Using Husky or Lefthook? Call the check directly:

# .husky/pre-commit
npx --yes mgcheck hook run

⚙️ Configuration

mgcheck looks for .mgcheckrc.json, .mgcheckrc or mgcheck.config.json in the current directory (JSON). Use --config <path> to point elsewhere.

{
  "approvalMode": "manual",
  "autoApprove": ["low"],
  "shadowDb": {
    "provider": "pglite",
    "dockerImage": "postgres:16-alpine"
  },
  "rules": {
    "MG008": "error",
    "MG010": "off"
  },
  "output": {
    "format": "terminal",
    "verbose": false
  },
  "confirmDestructive": false
}

Each entry in rules can be "error", "warning" or "off".

Priority: CLI flags → environment variables → config file → defaults.

Environment variables

Where things are stored

💻 Programmatic API

Install via npm:

npm install mgcheck

mgcheck is also an ESM library with full TypeScript types.

import { loadConfig, runCheck, renderReport, getExitCode, fixMigrationSql } from 'mgcheck';

const config = loadConfig({
  shadowDb: { provider: 'pglite', dockerImage: 'postgres:16-alpine' },
});

// 'analyze' = static rules only, 'run' = rules + shadow DB execution
const report = await runCheck('./migrations', config, 'run');

console.log(report.passed);                          // boolean
console.log(report.violations.map((v) => v.ruleId)); // e.g. ['MG001', 'MG009']
console.log(renderReport(report, 'markdown'));       // 'terminal' | 'json' | 'markdown'
process.exitCode = getExitCode(report);

// Rewrite unsafe SQL into zero-downtime patterns
const sql = 'CREATE INDEX idx_users_email ON users(email);';
const fix = fixMigrationSql(sql, report.violations);
if (fix.fixed) console.log(fix.diff);

Also exported: detectMigration, analyze, ALL_RULES, createShadowDatabase, getApprovalMode / setApprovalMode, readAuditLog, and the installer helpers. See src/index.ts for the full list.

🗑️ Uninstall

npx mgcheck uninstall           # add --purge to also delete audit logs

This removes the migration-guardian entry from every editor's MCP config, strips the injected blocks from agent rule files, restores your original pre-commit hook, and leaves everything else alone.

🗺️ Roadmap & Help Wanted

These are real gaps in the current codebase — each one is a self-contained, well-scoped contribution. Comment on (or open) an issue before starting so work isn't duplicated.

🟢 Good first contributions

New rule: ALTER COLUMN ... SET NOT NULL — scans the whole table under an ACCESS EXCLUSIVE lock; the safe path is a CHECK (col IS NOT NULL) NOT VALID constraint, validate, then SET NOT NULL.
New rule: VACUUM FULL / CLUSTER — full table rewrites that block reads and writes.
YAML config — .mgcheckrc.yml is in the config search list but is currently parsed as JSON.
More test fixtures — real-world unsafe migrations from Prisma, Drizzle and Supabase projects make the rules better for everyone.

🟡 Bigger pieces

Auto-fixes for MG007, MG008 and MG010 — rename and rollback rules have no automatic rewrite yet.
AST-based fixer — the fixer in src/fixer/rules-fixer.ts rewrites SQL with regexes; generating fixes from the parsed AST would make them far more robust.
Table-size awareness — use --db-url stats (row counts, table size) to downgrade warnings on tiny tables and escalate them on huge ones.
More frameworks — e.g. Knex, TypeORM, Sequelize, Rails, Alembic, Liquibase.
More AI clients — any MCP-capable editor or agent that isn't auto-configured yet.

Have an idea that isn't here? Open an issue — "this migration took down our prod and mgcheck didn't catch it" is the best kind of bug report.

🤝 Contributing

Contributions of all sizes are welcome: a new rule, a detector for your favorite migration tool, a better error message, a docs fix, or a test fixture from a migration that once burned you.

Get set up in 2 minutes

No Docker or running Postgres needed — the test suite uses in-process PGlite.

git clone https://github.com/Aditya31032006/agent-safe-migrate.git
cd agent-safe-migrate
npm install

npm test          # unit + integration tests
npm run lint      # type check
npm run build     # bundle to dist/

# Try the CLI from source
npm run dev -- analyze ./test/fixtures/migrations/unsafe-create-index.sql

CI runs the same lint → test → build steps on Node 18, 20 and 22 across Ubuntu and Windows.

Your first rule, step by step

Rules are small, deterministic and isolated — the easiest way into the codebase.

Create src/analyzer/rules/<rule-name>.ts implementing the Rule interface:

import type { Rule } from './types.js';

export const vacuumFull: Rule = {
  id: 'MG015',
  name: 'vacuum-full',
  severity: 'error',
  description: 'VACUUM FULL rewrites the table under an exclusive lock',
  check(statements, context) {
    // Walk statement.ast and return RuleViolation[] with a plain-English
    // message explaining the danger and a `suggestion` with the safe pattern.
    return [];
  },
};

Register it in src/analyzer/rules/index.ts.
Test it in test/unit/rules.test.ts — cover both the unsafe and the safe form. Add a fixture under test/fixtures/migrations/ if useful.
Document it in the rules table above.

Look at create-index-not-concurrent.ts for a complete example.

Pull request checklist

[ ] npm run lint and npm test pass
[ ] New behavior has tests
[ ] Commit messages follow Conventional Commits (feat:, fix:, docs: …)
[ ] README / rules table updated if user-facing behavior changed

See CONTRIBUTING.md for more detail.

Project layout

src/
├── analyzer/     # PostgreSQL AST parsing + the safety rules
├── detector/     # Migration framework detection
├── shadow/       # PGlite and Docker shadow databases
├── fixer/        # Zero-downtime auto-fix + verification
├── mcp/          # MCP server and tools
├── installer/    # AI client setup, watcher, Git hook
├── reporter/     # terminal / json / markdown output
└── core/         # engine, config, approval modes, audit log, applier

💜 Contributors

Thanks to everyone who has helped make migrations safer:

If Migration Guardian saved you from a bad deploy, give it a ⭐ — it helps other developers find the project.