Linting Postgres Migrations with Squawk

Three incidents in a quarter came from migrations that any experienced PostgreSQL reviewer would have rejected on sight: a plain CREATE INDEX on the busiest table, a foreign key added without NOT VALID, and an ALTER COLUMN ... TYPE bigint that rewrote 400 million rows. Each passed review because the reviewer was looking at application code in the same pull request. Squawk is a linter built for exactly this: it parses PostgreSQL migration SQL with the real PostgreSQL parser and reports statements that take dangerous locks, rewrite tables, or break running code, each with an explanation and the safe alternative. This guide installs it in CI, configures it for your PostgreSQL version and migration tool, picks which rules block, and gets its findings in front of authors in the pull request. It is the concrete starting point for Migration Linting & Static Analysis.

Symptom / Error Signatures

You need Squawk (or stronger configuration of it) when:

• Migration incidents trace back to recognisable patterns — non-concurrent index builds, validated constraints, type rewrites, renames, drops.
• Review of migrations depends on a few people who “know PostgreSQL locks”.
• Migrations are generated by an ORM and nobody reads the SQL.

Squawk’s own output looks like this, per statement:

db/migrations/V58__orders_idx.sql:1:0: warning: require-concurrent-index-creation

   1 | CREATE INDEX orders_customer_idx ON orders (customer_id);

  note: Creating an index blocks writes.
  help: Create the index CONCURRENTLY.

find detailed examples and solutions for each rule at https://squawkhq.com/docs/rules
Found 1 issue in 1 file (checked 1 source file)

Root Cause Analysis

Squawk works statement by statement over a parse tree, which lets it distinguish cases a text search cannot — for example, a CREATE INDEX on a table created earlier in the same file (safe, the table is empty) from one on an existing table. Its accuracy depends on two pieces of context you supply. The PostgreSQL version: several hazards changed with versions (constant defaults stopped rewriting tables in 11; NOT NULL via a validated check became scan-free in 12), and Squawk adjusts its rules to pg_version. The transaction context: rules such as prefer-robust-stmts and the handling of CONCURRENTLY depend on whether your migration tool wraps files in transactions, which you declare with assume_in_transaction.

Immediate Mitigation

1. Install Squawk in CI. It ships as an npm package, a standalone binary and a GitHub Action; pin the version.

# Shell · CI job · pins the major version; upgrade deliberately
npm install --global squawk-cli@2
squawk --version

2. Add the configuration file.

# TOML · .squawk.toml · repository root
# WARNING: set pg_version to production's version; assume_in_transaction to match your tool (Flyway: true).
pg_version = "16.0"
assume_in_transaction = true
excluded_rules = ["prefer-text-field", "ban-char-field"]

3. Lint only changed migration files, and fail the job on findings.

# Shell · CI job · pull requests only
git fetch origin main --depth=1
FILES=$(git diff --name-only --diff-filter=AM origin/main...HEAD -- 'db/migrations/*.sql')
if [ -n "$FILES" ]; then
  squawk --config .squawk.toml $FILES
fi

4. Post findings to the pull request. In GitHub Actions, the maintained Squawk action can comment directly; in other CI systems, capture the output and post it with the platform’s API so authors see the explanation without opening logs.

# YAML · .github/workflows/squawk.yml · runs on pull requests that touch migrations
name: lint-migrations
on:
  pull_request:
    paths: ["db/migrations/**.sql"]
jobs:
  squawk:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: sbdchd/squawk-action@v2
        with:
          pattern: "db/migrations/*.sql"
          version: "latest"

Permanent Fix / Long-Term Pattern

Treat the Squawk configuration as shared infrastructure. Keep it in the repository, review changes to it like code, and pin the version so rule changes arrive deliberately. Start with the high-impact rules blocking and the style rules warning; after a few weeks of data, promote or exclude. Add canary migrations — one unsafe and one safe example per blocking rule — to a test directory and assert that Squawk fails and passes them respectively, so a configuration change that silently weakens the gate is caught.

For ORM projects, point Squawk at the generated SQL: Prisma’s and Drizzle’s migration files are plain SQL already; for Django, EF Core, Alembic and TypeORM, generate the SQL in CI and lint that. Complement Squawk with rules for your own conventions — lock_timeout in every file, no CASCADE, one statement per non-transactional file — as described in writing custom migration lint rules, and with a reviewed exception process, per suppressing lint false positives with reviewed exceptions. The behaviours the rules protect against are explained in DDL Lock Management & Timeouts.

One practical detail makes Squawk output far more useful: file names. Migration tools use different layouts — Flyway’s V58__name.sql, Prisma’s migrations/<timestamp>_<name>/migration.sql, Sqitch’s deploy/<change>.sql — and the CI glob must match yours exactly, or new migrations are silently skipped. Add a guard to the job that fails if the pull request touches the migrations directory but the glob matched no files; a linter that quietly checks nothing is worse than none, because everyone believes it is working.

Verification Checklist

• Squawk runs on every pull request that adds or changes migration SQL, with a pinned version.
• .squawk.toml sets pg_version to production’s version and assume_in_transaction to match the migration tool.
• High-impact rules (concurrent indexes, NOT VALID, type changes, drops, renames) fail the check.
• Findings appear in the pull request, not only in CI logs.
• Canary migrations prove that the blocking rules still fire after any configuration change.

Frequently Asked Questions

Does Squawk need a database connection? No. It parses migration files offline using the PostgreSQL parser, so it runs in any CI environment in a second or two.

Why does Squawk flag adding a column with a default? On PostgreSQL before 11, a column with a default rewrote the table. Set pg_version in the configuration so Squawk knows your version and applies the rule only where it matters.

Can Squawk lint migrations generated by Prisma or Django? Yes, if you give it SQL. Prisma writes SQL migration files directly; for Django, run sqlmigrate in CI and pass the output to Squawk.

Should Squawk run on the main branch as well as pull requests? Pull requests are where it matters, because that is where authors can still change the migration. A scheduled run over recently merged files on the main branch is a useful backstop for changes that bypassed the pull-request check.

How do I ignore a finding that is actually safe? Newer Squawk versions support ignore comments for specific rules on specific statements, and the configuration file can exclude rules globally. Use per-statement ignores with a written reason, reviewed like any other exception.