Migration Linting & Static Analysis

Most unsafe migrations are recognisable from their text. CREATE INDEX without CONCURRENTLY on an existing table, ALTER COLUMN ... TYPE that rewrites, ADD CONSTRAINT ... FOREIGN KEY without NOT VALID, DROP COLUMN while a release still reads it, SET NOT NULL without a validated check — each of these patterns has caused enough outages that it has a name, a well-known safe alternative, and a rule in at least one linter. Human reviewers catch them inconsistently: the dangerous line is one of forty in a pull request, and it looks exactly like the safe version. A linter catches them every time, in seconds, before anyone has to remember. This part of CI/CD & Migration Automation covers static analysis of migrations: which tools exist, what they can and cannot detect, how to add rules for your own conventions, and how to handle the inevitable exceptions without turning the linter into noise. It serves platform teams building migration pipelines and the engineers whose pull requests the linter reviews.

Linting is the cheapest gate in the migration pipeline and the first one a change meets. It complements, rather than replaces, dynamic checks against real data — the lock-duration estimates in gating migrations on estimated lock duration and the snapshot rehearsals in Automated Migration Testing.

Concept & Mechanism

A migration linter parses migration SQL — or, for ORM-based projects, the SQL the ORM will emit — into a syntax tree and applies rules to each statement. Parsing matters: a regex sees CREATE INDEX but cannot reliably tell whether it targets a table created earlier in the same file (safe) or an existing table (dangerous), or whether it sits inside a transaction block. Real parsers, such as the PostgreSQL parser that Squawk embeds, can.

Squawk lints PostgreSQL migration files. Its rules cover the common hazards: require-concurrent-index-creation, constraint-missing-not-valid, adding-foreign-key-constraint, adding-not-nullable-field, changing-column-type, ban-drop-column, renaming-column, renaming-table, disallowed-unique-constraint, prefer-robust-stmts (idempotency), plus type-choice rules such as prefer-bigint-over-int and prefer-timestamptz. It can be told the target PostgreSQL version (so it knows, for example, that adding a column with a constant default is safe on 11+) and whether files run inside a transaction. Atlas’s migrate lint runs analyzers over new migration files — destructive changes, backward-incompatible changes, data-dependent changes (such as a new unique index that may fail on existing duplicates), and PostgreSQL concurrency checks — and understands the migration directory’s history, so it lints only what is new. ORM-level tools add framework-aware checks: strong_migrations for Rails, django-migration-linter for Django.

What static analysis cannot see is equally important. It does not know table sizes, so it cannot tell a harmless rewrite of a 10-row lookup table from an outage on a billion-row one. It does not know which application versions still read a column. And it cannot judge data: whether a VALIDATE CONSTRAINT will find violations, or how long a backfill will take. Those need dynamic checks and human judgement.

It helps to think of lint rules in three families, because each family fails differently. Lock rules catch statements whose lock is stronger or longer than it needs to be: non-concurrent index builds and drops, validated constraints, SET NOT NULL without a proof, anything that rewrites. Their false positives usually involve new or tiny tables. Compatibility rules catch statements that break code still running: drops, renames, narrowing type changes, NOT NULL without a default on a column old code does not write. Their false positives are planned contract steps, which is why they need an exception process that references the release plan. Robustness rules catch statements that cannot be retried safely: missing IF NOT EXISTS, multiple DDL statements in a non-transactional file, no lock_timeout. They rarely produce false positives and are the easiest to adopt as blocking from day one.

The families also map to the teams that care. Lock rules protect the on-call engineer and the database; compatibility rules protect other services and the release process; robustness rules protect whoever has to repair a failed deploy. When introducing linting to a team that has never had it, starting with robustness rules as blocking and the other two families as warnings builds trust quickly: the first rules to block are the ones nobody argues with.

Prerequisites & Decision Criteria

Choose tools by stack, and decide early how exceptions will work.

Before enabling linting as a blocking gate:

• The linter runs only on new or changed migration files, not on the entire history.
• The target database version and transaction behaviour are configured, so version-dependent rules are accurate.
• An exception mechanism exists — an inline ignore comment or configuration entry — that requires a written reason.
• Output is posted to the pull request where authors will see it, not only in CI logs.
• The team agreed which rules block and which only warn.

Step-by-Step Procedure

1. Run the linter on changed migration files in CI. Verify it fails a test pull request that adds a plain CREATE INDEX on an existing table.

# Shell · CI job · lints only migration files changed in this pull request
# WARNING: linting unchanged historical files floods the output with already-applied issues.
git fetch origin main --depth=1
CHANGED=$(git diff --name-only --diff-filter=AM origin/main...HEAD -- 'db/migrations/*.sql')
[ -z "$CHANGED" ] && exit 0
squawk --config .squawk.toml $CHANGED

2. Configure version and context. Tell the linter what it needs to be accurate.

# TOML · .squawk.toml · repository root
# WARNING: pg_version must match production, or version-dependent rules misfire.
pg_version = "16.0"
assume_in_transaction = true
excluded_rules = ["prefer-text-field"]

3. Add rules for your own conventions. Common additions: every file that takes ACCESS EXCLUSIVE must set lock_timeout; MySQL ALTER TABLE must state ALGORITHM and LOCK; no CASCADE in production migrations. The mechanics are covered in writing custom migration lint rules.

4. Lint ORM-generated SQL, not just hand-written files. Generate the SQL in CI (sqlmigrate, prisma migrate diff --script, dotnet ef migrations script) and lint that output, because the ORM’s Python, TypeScript or C# does not reveal the DDL.

5. Route exceptions through review. A suppression must name the rule and include a reason; CI surfaces suppressions in the pull request for an approver who owns the database, per suppressing lint false positives with reviewed exceptions.

6. Track outcomes. Record which rules fire and how often they are suppressed; a rule suppressed constantly is either wrong for your context or a sign of a recurring design problem.

Verification & Observability

A linter is only a gate if it actually blocks. Verify with canary pull requests that contain each class of hazard, and keep them as regression tests for the lint configuration:

-- PostgreSQL · tests/lint-canaries/0001_unsafe_index.sql · must FAIL lint
CREATE INDEX orders_customer_idx ON orders (customer_id);
-- tests/lint-canaries/0002_safe_index.sql · must PASS lint
-- SET lock_timeout is omitted on purpose: CONCURRENTLY statements run outside a transaction.
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_idx ON orders (customer_id);

Over time, measure the gate’s value: the number of migrations blocked, the rules that fired, suppressions per rule, and — most importantly — incidents caused by migrations that passed lint. Each such incident is a candidate for a new rule or a dynamic check. Keep these numbers alongside the other migration SLOs described in tracking schema migration metrics and SLOs.

Lint findings are also a teaching signal. When the same rule fires repeatedly for the same team, the fix is usually not stricter enforcement but a better default — a migration template with lock_timeout already set, a generator that emits CONCURRENTLY, a documented expand-and-contract recipe for renames. Review the top rules quarterly and ask which template or tooling change would make the finding disappear at the source.

Linting also produces an audit trail that is valuable beyond the pull request. Each migration’s lint result, suppressions and approvals, stored with the build, answer the questions that come up after an incident: was this pattern flagged, who accepted the exception, and on what reasoning? Keep the linter’s machine-readable output (most tools emit JSON or SARIF) as a build artefact, and link it from the deploy record for the release that shipped the migration. Over a year, that history also shows whether the gate is improving outcomes — fewer migration incidents, fewer emergency rollbacks — or merely adding friction.

Rollback Path

The linter itself needs a rollback path: a new rule or a version upgrade can start blocking legitimate work across every team at once. Roll out new blocking rules in warning mode first, watch their findings for a few weeks, then promote them. Pin the linter version in CI so upgrades are deliberate, and keep the configuration in version control so a problematic change can be reverted like any other.

# Shell · CI job · pinned linter version; bump deliberately in its own pull request
npm install --global squawk-cli@2
squawk --version

If a rule blocks an urgent fix, the supported path is a reviewed suppression, not disabling the gate. Emergency changes that bypass linting entirely should be rare, logged, and followed by a retrospective, in the same spirit as Rollback Automation.

Common Errors & Fixes

Linter flags every historical migration. Root cause: it runs on the whole directory. Fix: lint only files added or modified relative to the target branch.

adding-field-with-default fires on PostgreSQL 16. Root cause: the linter assumes an old version where constant defaults rewrote the table. Fix: set pg_version in the configuration.

CREATE INDEX on a table created in the same file is flagged. Root cause: some rules cannot see earlier statements, or the linter version predates that awareness. Fix: suppress with a reason stating the table is new, or split table creation and indexes into one file the linter understands.

ORM migrations pass lint but cause outages. Root cause: the linter ran on the ORM’s source files, which contain no SQL. Fix: generate the SQL in CI and lint that.

Child Page Index

Five guides go deeper. Linting Postgres migrations with Squawk sets up Squawk end to end, including pull-request comments. Writing custom migration lint rules covers rules for house conventions that off-the-shelf linters do not know. Detecting table rewrites before they ship combines static patterns with a cheap dynamic check. Linting MySQL migrations for online DDL compatibility addresses MySQL, where fewer off-the-shelf rules exist. And suppressing lint false positives with reviewed exceptions keeps the gate strict without making it unworkable.

The hazards the linter looks for are explained in depth in DDL Lock Management & Timeouts and Adding Constraints Without Downtime.

Frequently Asked Questions

Does migration linting replace code review? No. It catches known unsafe patterns reliably, which frees reviewers to focus on what a linter cannot judge: whether a column is still in use, whether the release order is right, and whether the data will cooperate.

Which linter should a PostgreSQL team start with? Squawk is the most widely used dedicated PostgreSQL migration linter and works with any tool that produces SQL files. Teams using Atlas get similar analyzers built in; both can run side by side.

How do I lint migrations generated by an ORM? Generate the SQL in CI — for example with Django’s sqlmigrate, Prisma’s migration files, or EF Core’s migrations script — and run the linter on that output rather than on the ORM’s source code.

How long does migration linting add to CI? Seconds. Static linters parse SQL without a database, so even a pull request with many migration files lints almost instantly. The slower checks — dry runs, rewrite detection, snapshot rehearsals — run after linting and only when linting passes.

Can linting run before code review starts? Yes, and it should. Running the linter on every push to a pull request means authors see findings and fix them before a reviewer spends time on the change, which keeps review focused on the questions a linter cannot answer.

Should all lint rules block merges? Start with a small set of blocking rules for high-impact hazards (blocking index builds, validated constraints, rewrites, drops) and run the rest as warnings. Promote rules to blocking once their false-positive rate is known.