Prisma Strong Migrations

Lint the SQL in your Prisma migrations for patterns that are dangerous to apply to a live production database — before they merge.

Prisma generates a migration.sql file for every migration. Most are harmless, but a few patterns can take your database down, lose data, or break the app mid-deploy. This tool reads those SQL files, understands them via a real SQL parser (not string matching), and flags the dangerous ones with an explanation and a safer alternative. It runs as a GitHub Action on your pull requests, or as a CLI anywhere.

It's the strong_migrations / Squawk idea, tuned to the SQL Prisma emits and to how Prisma applies migrations.

Scope: PostgreSQL today. The architecture is dialect-aware so more engines can be added; until then non-Postgres projects aren't supported.

What it catches

Every rule maps to a real way a migration hurts you in production:

| Failure mode | Example | Why it's dangerous | | --- | --- | --- | | Destructive | DROP TABLE, DROP COLUMN | Data is gone, and old code still reading it errors. | | Backwards-incompatible | renames, column type changes | The previously-deployed app breaks during the rolling deploy. | | Locking | CREATE INDEX, SET NOT NULL, validating constraints | Heavy locks or full-table scans stall reads/writes. | | Correctness | ADD COLUMN ... NOT NULL with no default | The migration fails outright on a non-empty table. |

See the full rule list below.

Quick start (GitHub Action)

Add a workflow that runs on pull requests. Findings show up as inline annotations on the diff.

# .github/workflows/migration-safety.yml
name: Migration Safety
on: pull_request

permissions:
  contents: read

jobs:
  lint-migrations:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v6
        with:
          fetch-depth: 0 # needed for changed-only detection
      - uses: mileszim/prisma-strong-migrations@v1
        with:
          changed-only: true # only lint migrations added in this PR

Action inputs

| Input | Default | Description | | --- | --- | --- | | migrations-dir | ./prisma/migrations | Where your migrations live. | | changed-only | false | Only lint migrations this branch changed versus its base — the whole branch diff, like a PR, not just the latest commit. On a PR it compares against the base branch; on a push, the repo's default branch. The action deepens shallow checkouts automatically. | | base | auto | Branch to compare against for changed-only. Defaults to the PR base branch (PRs) or the default branch (pushes). | | fail-on | error | Severity that fails the check: error or warning. | | reporter | github | github (inline annotations), stylish, or json. | | config | auto | Path to a config file. | | working-directory | . | Directory containing the Prisma project. | | node-version | 20 | Node.js version used to run the linter. |

Quick start (CLI)

npm install --save-dev prisma-strong-migrations

# Lint every migration
npx prisma-strong-migrations lint

# Lint only what changed versus a base branch (great locally and in CI)
npx prisma-strong-migrations lint --changed --base origin/main

# Lint specific files
npx prisma-strong-migrations lint prisma/migrations/20240101_init/migration.sql

# Other reporters
npx prisma-strong-migrations lint --reporter json

# See every rule and its default
npx prisma-strong-migrations list-rules

# Write a starter config
npx prisma-strong-migrations init

The CLI exits non-zero when it finds an issue at or above --fail-on (default error), so it fails CI on its own.

Example output:

prisma/migrations/20240201_risky/migration.sql
  2:20  error    Dropping column "bio" from "User" is destructive.  no-drop-column
                 why: The column data is lost, and any deployed code that still selects it will error until redeployed.
                 fix: Remove the field from your Prisma schema and deploy that first, then drop the column in a follow-up migration.

✖ 1 problem (1 error)

Configuration

Configuration is optional — the defaults are sensible. To customize, run prisma-strong-migrations init or create a prisma-strong-migrations.config.js (also supports .cjs, .json, an .prisma-strong-migrationsrc, or a "prisma-strong-migrations" key in package.json):

/** @type {import('prisma-strong-migrations').UserConfig} */
module.exports = {
  migrationsDir: './prisma/migrations',
  dialect: 'postgresql',
  failOn: 'error', // 'error' | 'warning'
  rules: {
    // Set a severity...
    'no-data-manipulation': 'error',
    // ...turn a rule off...
    'require-concurrent-index': 'off',
    // ...or enable an opt-in rule.
    'require-pii-comment': 'warning',
  },
};

Each rule can be set to 'error', 'warning', 'off', true (its default severity), false (off), or { severity, enabled }.

Suppressing a finding

Sometimes a flagged statement really is safe — you've shipped the code that stops using a column before dropping it, for example. Silence a finding with an inline SQL comment in the migration.sql file, right where the statement is:

-- psm-disable-next-line no-drop-table -- removed all readers of this table in #482
DROP TABLE "legacy_sessions";

The directive applies to the statement on the next line (it covers the whole statement, even if it spans multiple lines). Forms:

| Directive | Scope | | --- | --- | | -- psm-disable-next-line [rules] [-- reason] | the statement below the comment | | -- psm-disable-line [rules] [-- reason] | the statement on the same line (trailing comment) | | -- psm-disable-file [rules] [-- reason] | every statement in the file | | -- psm-disable [rules] … -- psm-enable [rules] | every statement between the two comments |

• rules is an optional space/comma-separated list of rule names (e.g. no-drop-table, no-set-not-null). Omit it to suppress all rules for that scope — prefer naming the rule so a different problem on the same statement still gets caught.
• reason is optional free text after -- or :. It's strongly encouraged: it documents why the statement is safe for the next reader.
• psm-ignore and psm-ignore-file are accepted as aliases for the next-line and file forms. Block comments (/* psm-disable-next-line */) work too.

Suppressions are never hidden: every reporter notes how many findings were suppressed, and the json reporter lists them (with reasons) under a suppressed key — so they stay auditable in review and can't quietly rot.

Rules

Run prisma-strong-migrations list-rules for the live list. Rules marked opt-in are off by default; enable them in config.

Destructive

• no-drop-table (error) — Dropping a table deletes its data and breaks code still reading it.
• no-drop-column (error) — Dropping a column deletes its data; Prisma keeps selecting every scalar field, so old code errors.

Backwards-incompatible

• no-rename-column (error) — A rename is a drop + add to the running app; old code queries a column that's gone. Use expand-and-contract.
• no-rename-table (error) — The deployed app keeps querying the old name. Use @@map or expand-and-contract.
• no-change-column-type (error) — Usually rewrites the table under an exclusive lock and may break the running app. A USING clause doesn't make it safe.

Correctness

• no-add-not-null-column-without-default (error) — ADD COLUMN ... NOT NULL with no default aborts on a table that already has rows.

Locking

• no-set-not-null (warning) — SET NOT NULL scans the whole table under an exclusive lock and fails if any value is null. Use a validated CHECK (... IS NOT NULL) first.
• require-concurrent-index (warning) — CREATE INDEX without CONCURRENTLY blocks writes while it builds.
• constraint-missing-not-valid (warning) — Adding a CHECK or FOREIGN KEY validates every existing row under a lock. Add it NOT VALID, then VALIDATE separately.
• no-data-manipulation (warning) — INSERT/UPDATE/DELETE inside a schema migration runs under the migration lock and can stall the deploy.

Opinionated (opt-in)

• require-explicit-not-null (off) — Require new columns to declare NULL or NOT NULL explicitly.
• require-pii-comment (off) — Flag columns whose names look like personal data, for a compliance review.
• no-unindexed-foreign-key (off) — Flag foreign keys whose referencing column has no covering index in the same migration.

A note on CREATE INDEX CONCURRENTLY and Prisma

Prisma wraps each migration in a transaction, and CREATE INDEX CONCURRENTLY cannot run inside one. To follow require-concurrent-index, put the concurrent index in its own migration and apply it outside the transactional batch (for example with prisma db execute). The rule's suggestion text spells this out.

Programmatic API

import { lintProject, shouldFail, stylish } from 'prisma-strong-migrations';

const { result, config } = lintProject({
  overrides: { migrationsDir: './prisma/migrations' },
  changedSince: 'origin/main', // optional
});

console.log(stylish(result));
process.exit(shouldFail(result, config.failOn) ? 1 : 0);

Lower-level building blocks are exported too: parseSql, loadMigrations, lint, loadConfig, ALL_RULES, and the reporters.

How it works

1. Find every migration.sql under migrationsDir (or just the changed ones).
2. Parse each file with sql-parser-cst into a concrete syntax tree. Statements that don't parse are reported, never silently skipped.
3. Run each enabled rule against the tree. Rules read the structure of the SQL (statement kind, table, columns, clauses) — not substrings — so they don't trip over identifiers that happen to contain keywords.
4. Report findings with the configured severity, sorted by location.

Limitations

• PostgreSQL only for now.
• Static analysis can't know table size or row contents, so locking/correctness rules flag the pattern; whether it actually causes pain depends on your data. Tune severities per rule.

Development

npm install
npm run build      # compile TypeScript to dist/
npm test           # run the vitest suite
npm run lint       # eslint
npm run typecheck  # tsc --noEmit

Each rule is a small module in src/rules/ that reads facts from the parsed tree via helpers in src/rules/ast.ts and calls ctx.report(...). Add a rule by creating the module, registering it in src/rules/index.ts, and adding a test in test/rules.test.ts.

License

MIT — see LICENSE.