DEV Community: Miłosz Kordziński

I tested a PostgreSQL migration checker on 760 real migration files

Miłosz Kordziński — Wed, 07 Oct 2026 11:06:49 +0000

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.

Running the same SQL checks in a browser, CLI and pull request

Miłosz Kordziński — Thu, 13 Aug 2026 09:32:30 +0000

I wanted one set of SQL checks to work in three places: while exploring a query, from a terminal and during code review.

That became SQL Atlas. It is a local, deterministic SQL analyzer with a browser interface, a CLI and a GitHub Action. This article covers the interfaces, the CI contract and the limits of static SQL analysis.

One analyzer, three interfaces

The analyzer returns structured data instead of printing messages directly. Each interface decides how to present the same result:

The browser explains findings and links them to learning material.
The CLI returns text, JSON or Markdown and uses stable exit codes.
The GitHub Action converts findings into file annotations and a job summary.

Keeping presentation outside the analyzer prevents the CLI and Action from becoming separate implementations with different behavior.

A CLI needs a contract

The CLI accepts one or more files, or SQL through standard input:

npx --yes sql-atlas@0.5.1 analyze query.sql

echo "SELECT * FROM customers;" | npx --yes sql-atlas@0.5.1 analyze -

It supports PostgreSQL, MySQL, Oracle, SQLite, SQL Server and a generic mode. Output can be text for a person, JSON for another program or Markdown for an issue or report.

Exit codes are part of the interface:

0 means analysis completed and the configured policy passed.
1 means analysis completed but a severity or score threshold failed.
2 means the command or input was invalid.

This distinction matters in CI. A policy failure is not the same as a broken invocation.

Turning findings into pull request feedback

The Action runs as a bundled Node 24 program and does not download dependencies at runtime. A minimal workflow looks like this:

name: SQL review

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

permissions:
  contents: read

jobs:
  sql-atlas:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v7
      - uses: milekv/sql-atlas@v0.5.1
        with:
          paths: |
            migrations/**/*.sql
            schema/**/*.sql
          dialect: postgresql
          fail-on: critical
          min-score: 60

Findings become GitHub file annotations. The full result is written to the job summary, and the Action exposes file count, finding count and lowest score as outputs.

The default policy only fails on critical findings. Teams can start in report-only mode with fail-on: none, inspect false positives and add stricter thresholds later.

What static analysis cannot know

SQL Atlas does not connect to a database. It cannot know table sizes, data distribution, available indexes, planner settings or the real execution plan.

For that reason, a warning such as a function applied to a filtered column means "check whether this blocks the index strategy you expect", not "this query is slow". Runtime performance still needs EXPLAIN, representative data and production-like measurements.

The browser includes a local PostgreSQL EXPLAIN JSON viewer for that next step, but the analyzer deliberately keeps its claims narrow.

Testing the distribution surfaces

The project tests the analyzer and both automation interfaces. CI builds the web app, CLI and Action bundle. A smoke workflow runs the repository's own Action against a known SQL file and verifies its outputs. CI also rebuilds the committed Action bundle and checks that it has no uncommitted difference.

The CLI package has no runtime dependencies. I verified the public npm package from an empty directory with a clean cache, including the executable version and a real stdin analysis.

Try it

Demo: https://milekv.github.io/sql-atlas/
Source: https://github.com/milekv/sql-atlas
npm: https://www.npmjs.com/package/sql-atlas
v0.5.1: https://github.com/milekv/sql-atlas/releases/tag/v0.5.1

I am particularly interested in examples where a rule is too broad, misses a dialect detail or produces an unhelpful CI annotation.