I tested a PostgreSQL migration checker on 760 real migration files

A migration can be valid SQL and still be a bad production change.

An index build can block writes. A type change can rewrite a large table. A foreign key can validate every existing row at deployment time. These problems are easy to miss in review because the migration often looks harmless.

I wanted a check that runs before PostgreSQL sees the migration, so I tested the first version of pg-migration-guard against two established open source codebases.

The test set

I used the migration histories from Cal.com and Documenso:

• 760 SQL migration files
• 2,638 SQL statements
• 1,340 findings from the initial full-history scan

Those 1,340 findings are not 1,340 confirmed bugs. They are rule matches that need context. That distinction matters.

The first implementation could detect useful patterns, but running it over an entire migration history on every pull request was the wrong workflow. Old migrations cannot be changed safely in many projects, and repeating old warnings makes new warnings easier to ignore.

The useful unit of review is usually the migration introduced by the current change.

Checking only the current change

The CLI now accepts a Git reference:

npx pg-migration-guard "migrations/**/*.sql" --changed-since origin/main

It intersects the supplied paths with SQL files that were added, copied, modified, or renamed in origin/main...HEAD. Existing migration history stays out of the result.

In GitHub Actions the check can be kept small:

name: Migration safety

on:
  pull_request:
    paths:
      - "migrations/**/*.sql"

jobs:
  check:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0
      - uses: actions/setup-node@v4
        with:
          node-version: 20
      - run: npx --yes pg-migration-guard@0.2.0 "migrations/**/*.sql" --changed-since origin/${{ github.base_ref }} --format github

The GitHub output format creates annotations on the relevant lines. The command also has separate exit codes for a failed safety threshold and for invalid input or configuration.

What it checks

The current rules cover a deliberately limited set of PostgreSQL operations:

• indexes created without CONCURRENTLY
• CREATE INDEX CONCURRENTLY inside a transaction
• SET NOT NULL on existing tables
• column type changes that may rewrite a table
• volatile defaults
• foreign keys that validate existing rows immediately
• table and column removal
• ALTER TABLE without a migration-level lock_timeout

The default output includes the lock level when it is relevant and suggests a safer deployment sequence.

For example, a direct SET NOT NULL can be replaced with a CHECK (column IS NOT NULL) NOT VALID, validation in a separate step, and then the final constraint change.

Database context is optional

A static check cannot know whether a table has ten rows or ten million. The CLI can connect to PostgreSQL using a restricted role and read catalog metadata:

npx pg-migration-guard "migrations/**/*.sql" \
  --database-url "$DATABASE_URL"

The connection is used only for catalog inspection inside a read-only transaction. Migration SQL is never sent to the database. Table size and estimated row count can then raise the severity of a finding.

Boundaries

This is not a PostgreSQL parser and it does not prove that a migration is safe. The checks are deterministic and intentionally narrow. Dynamic SQL, application deployment order, and production rehearsal are outside the current scope.

That limitation is useful. A small check that explains exactly why it matched is easier to trust than a broad score with unclear reasoning.

The package is available on npm, and the source, rules, examples, and database queries are in the GitHub repository.

The next work is driven by reproducible false positives and migration layouts that the current file selection does not handle well.

Comments

[Vlad Tkachenko]

A rule I'd use on Supabase projects: CREATE TABLEin public with no ENABLE ROW LEVEL SECURITY in the same migration. Supabase serves public through its API, a table created from SQL starts with RLS off, and while the automatic grants are still on, that migration is what leaves a table readable with the anon key.
From 30 October there's a second one, CREATE TABLE with no GRANT, which is where the app starts getting 42501 on a new table. Both are narrow and deterministic, which seems to fit how you've scoped it.
Does the parser keep enough state across the statements in a file to check "created, then enabled"?