migration-review

PostgreSQL migration safety review for CI and local development.

migration-review statically reviews SQL migrations and flags patterns that can cause data loss, blocking locks, long-running transactions, rollout incompatibilities, or migration-runner failures.

v0.1 is intentionally PostgreSQL-first and offline. It does not connect to production databases and does not claim to know table size, live locks, or the current application version.

Install

npm install --save-dev migration-review

Run without installing locally:

npx migration-review db/migrations

CLI

migration-review db/migrations
migration-review db/migrations/20260904_add_users.sql
migration-review db/migrations --format json
migration-review db/migrations --format github
migration-review db/migrations --fail-on critical
migration-review db/migrations --min-severity high

Exit codes:

• 0 review completed without reaching the failure threshold.
• 1 usage/input error.
• 2 review reached --fail-on threshold.

Default CI threshold is high.

Example

Input:

ALTER TABLE users ADD COLUMN tenant_id uuid NOT NULL;
CREATE INDEX users_email_idx ON users(email);
UPDATE users SET tenant_id = '00000000-0000-0000-0000-000000000000';
ALTER TABLE users DROP COLUMN legacy_email;

Output includes findings such as:

CRITICAL Destructive DROP operation on legacy_email
HIGH ADD COLUMN ... NOT NULL without a default
HIGH Index build is not CONCURRENTLY
HIGH Unbounded UPDATE detected

JavaScript / TypeScript API

import { reviewFile, reviewMigration, renderTerminal } from 'migration-review';

const result = reviewMigration(sql);
console.log(result.score, result.risk, result.findings);

const fromFile = await reviewFile('./db/migrations/001.sql');
console.log(renderTerminal(fromFile));

What it checks

Destructive operations

• DROP TABLE
• DROP COLUMN
• TRUNCATE
• DELETE without WHERE

Locking / availability

• non-concurrent CREATE INDEX
• non-concurrent REINDEX
• ALTER TABLE review trigger

Expand / contract compatibility

• ADD COLUMN ... NOT NULL without a default
• ALTER COLUMN ... TYPE
• constraint removal during rolling deploys

Data movement

• unbounded UPDATE
• join-style backfills that may require batching

Migration-runner compatibility

• CREATE/DROP INDEX CONCURRENTLY and other CONCURRENTLY operations that cannot safely be wrapped in a transaction block

GitHub Actions

A simple CI step:

name: Migration Review

on:
  pull_request:

jobs:
  migration-review:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: 20
      - run: npm ci
      - run: npx migration-review db/migrations --format github --fail-on high

For a PR comment workflow, save --format github output to a file and publish it using your preferred PR-comment action.

Risk scoring

The score is a heuristic from 0 to 100, based on detected findings. It is deliberately conservative:

• 0: safe (no findings)
• 1–24: low
• 25–49: medium
• 50–79: high
• 80–100: critical

The score is not a guarantee of runtime behavior. Table size, indexes, PostgreSQL version, active traffic, replication topology, and deployment strategy can materially change actual risk.

Why PostgreSQL-first?

PostgreSQL has operation-specific locking and transaction constraints. For example, regular CREATE INDEX can block writers, whereas CREATE INDEX CONCURRENTLY is designed to avoid blocking concurrent inserts/updates/deletes during the index build; concurrent index creation also has trade-offs and cannot run inside a transaction block. See the PostgreSQL docs linked in the finding output.

Development

npm install
npm run build
npm test

Before publishing, update the repository URLs in package.json and version the package appropriately.

Publishing

npm login
npm publish

For a scoped public package:

npm publish --access public

Roadmap

The architecture is ready for future analyzers without changing the public API:

1. PostgreSQL schema snapshot comparison (--schema schema.sql).
2. Migration history ordering / duplicate detection.
3. pg-backed online checks against a disposable or CI database.
4. SQL parser integration for fewer regex false positives.
5. Framework-aware rules for Prisma, Drizzle, Knex, TypeORM, Flyway, and raw SQL.
6. GitHub PR annotations and SARIF output.
7. Custom rule configuration (migration-review.config.ts).

License

MIT