Writing Custom Migration Lint Rules

Off-the-shelf linters know the universal hazards. They do not know your conventions: that every migration touching a hot table must set lock_timeout; that CASCADE is banned because a view owned by the data team once vanished; that CREATE INDEX CONCURRENTLY must be alone in its file because your tool runs files in transactions; that index names follow <table>_<columns>_idx; that the payments schema requires a second approver. These rules live in a wiki page and in the heads of two senior engineers, and they are enforced only when one of those engineers reviews the pull request. This guide turns them into automated checks: a small parser-based rule runner in CI, a handful of rules that cover most house conventions, and tests that keep the rules honest. It extends Migration Linting & Static Analysis.

A Small Custom Rule Runner Changed migration files are parsed into statements by a real PostgreSQL parser library. Each rule receives the file's statements and context such as whether the file runs in a transaction. Rules emit findings with a rule id, message and severity. Suppression comments are checked, and remaining findings fail CI or post warnings. A Small Custom Rule Runner Changed migrations git diff vs main Parser pglast / libpg_query Rules lock_timeout, no CASCADE, … Suppressions comment with reason CI result fail on errors, post findings
Parse once, run many small rules — each rule is a few lines of code with its own tests.

Symptom / Error Signatures

House conventions need automation when:

  • The same review comment (“please add SET lock_timeout”, “no CASCADE here”) appears on migration after migration.
  • Incidents are caused by violating a convention everyone agreed on.
  • A convention depends on context a generic linter lacks — which tables are hot, which schemas need extra approval, which tool runs files in transactions.
  • Regex-based checks exist but misfire on comments, string literals or multi-line statements.

Root Cause Analysis

Conventions are knowledge about your system — table sizes, traffic, tooling, organisational rules — that no generic tool has. Encoding them requires two things: a reliable view of the statements in a migration, and a place to express the rule. A real parser provides the first: for PostgreSQL, libpg_query (and bindings such as pglast for Python or pgsql-parser for JavaScript) turns SQL into the same parse tree the server builds, so rules can match on statement types and options rather than on text. The rule runner provides the second: each rule is a function from a file’s statements (plus context) to findings.

Convention Why a generic linter misses it Rule logic
lock_timeout before DDL on hot tables does not know which tables are hot if file alters a table in HOT_TABLES and no SET lock_timeout precedes it → error
no CASCADE CASCADE is sometimes fine elsewhere any DropStmt with behavior = CASCADE → error
CONCURRENTLY alone in its file depends on your tool’s transaction handling concurrent index stmt in a file with other stmts → error
index naming pure convention index name not matching <table>_..._idx → warning
extra approval for sensitive schemas organisational statement touches payments.* → require label

Parsing matters most for correctness. A regex for CASCADE matches a comment that says “never use CASCADE”; a regex for lock_timeout accepts -- TODO: set lock_timeout. A parse tree contains only real statements.

Regex Check vs Parser Rule Two panels. A regex check for CASCADE fires on a comment line and misses a DROP split across lines with odd spacing. A parser rule inspects DropStmt nodes for behavior CASCADE, ignoring comments and handling any formatting. Regex Check vs Parser Rule grep CASCADE fires on '-- never use CASCADE' misses 'DROP TABLE t\n cascade' no idea of statement type noisy and leaky Parser rule walk DropStmt nodes check behavior == CASCADE comments and formatting irrelevant precise
Parser-based rules match what the database will execute, not what the file happens to contain as text.

Immediate Mitigation

1. Start with the conventions that caused incidents. Write them down as precise, testable statements (“any ALTER TABLE on orders, payments or sessions must be preceded by SET lock_timeout in the same file”).

2. Implement a minimal parser-based runner. Python with pglast is enough for most teams.

# Python 3.11 · scripts/migration_rules.py · CI step; pip install pglast
# WARNING: rules see only the SQL in the file; keep context (hot tables) in configuration.
import sys, pglast
from pglast import ast

HOT_TABLES = {"orders", "payments", "sessions"}

def statements(path):
    return [s.stmt for s in pglast.parse_sql(open(path).read())]

def rule_no_cascade(stmts):
    for s in stmts:
        if isinstance(s, ast.DropStmt) and s.behavior == pglast.enums.DropBehavior.DROP_CASCADE:
            yield "error", "no-cascade: name dependent objects explicitly instead of CASCADE"

def rule_lock_timeout_on_hot_tables(stmts):
    seen_timeout = False
    for s in stmts:
        if isinstance(s, ast.VariableSetStmt) and s.name == "lock_timeout":
            seen_timeout = True
        if isinstance(s, ast.AlterTableStmt) and s.relation.relname in HOT_TABLES and not seen_timeout:
            yield "error", f"hot-table-lock-timeout: SET lock_timeout before altering {s.relation.relname}"

def rule_concurrently_alone(stmts):
    if len(stmts) > 1 and any(isinstance(s, ast.IndexStmt) and s.concurrent for s in stmts):
        yield "error", "concurrent-alone: CREATE INDEX CONCURRENTLY must be the only statement in its file"

RULES = [rule_no_cascade, rule_lock_timeout_on_hot_tables, rule_concurrently_alone]

failed = False
for path in sys.argv[1:]:
    stmts = statements(path)
    for rule in RULES:
        for severity, msg in rule(stmts):
            print(f"{path}: {severity}: {msg}")
            failed |= severity == "error"
sys.exit(1 if failed else 0)

3. Run it next to the off-the-shelf linter on changed migration files, with the same pull-request reporting.

Permanent Fix / Long-Term Pattern

Treat custom rules as a small product. Every rule gets a stable id, a message that explains the safe alternative, and tests: a set of SQL snippets that must pass and must fail. Keep configuration (hot tables, sensitive schemas) in a file reviewed by the database owners. Support suppressions with an inline comment naming the rule and giving a reason, handled as described in suppressing lint false positives with reviewed exceptions.

# Python · tests/test_migration_rules.py · pytest; each rule has pass and fail fixtures
import pglast
from scripts.migration_rules import rule_no_cascade

def stmts(sql):
    return [s.stmt for s in pglast.parse_sql(sql)]

def test_cascade_flagged():
    assert list(rule_no_cascade(stmts("DROP TABLE legacy_orders CASCADE;")))

def test_comment_not_flagged():
    assert not list(rule_no_cascade(stmts("-- never use CASCADE\nDROP TABLE legacy_orders;")))

Retire rules that stop earning their keep and add rules after incidents: every migration-related post-incident review should ask “could a lint rule have caught this?” For MySQL, the same runner shape works with a MySQL-capable parser or with focused checks on ALTER TABLE clauses, as covered in linting MySQL migrations for online DDL compatibility. Rules that need table sizes or data belong in dynamic checks instead, such as detecting table rewrites before they ship.

Lifecycle of a Custom Rule Five steps. An incident or repeated review comment identifies a convention; write it as a precise statement; implement the rule with pass and fail tests; run in warning mode; promote to blocking once false positives are understood. Lifecycle of a Custom Rule STEP 1 Identify convention incident / review comment STEP 2 Precise statement testable wording STEP 3 Rule + tests pass/fail fixtures STEP 4 Warn mode measure noise STEP 5 Block promote
Rules come from real failures and graduate from warning to blocking with evidence.

Keep rule messages actionable. A finding that says “violates hot-table-lock-timeout” makes the author look up a wiki page; one that says “SET lock_timeout before altering orders — add SET LOCAL lock_timeout = '3s'; as the first statement” fixes itself. Include the safe alternative, a link to the internal runbook, and the rule id for suppressions in every message, and treat unclear messages as bugs in the rule.

Verification Checklist

Frequently Asked Questions

Why not use regular expressions? They match text, not statements. Comments, string literals, casing and line breaks produce false positives and misses. A parser gives you the statement types and options the database will actually execute.

Which parser should I use for PostgreSQL? Any binding of libpg_query, which embeds PostgreSQL’s own parser: pglast for Python, pgsql-parser for JavaScript, pg_query for Ruby and Go. The parse tree matches the server’s, so version-specific syntax is handled correctly.

Can custom rules replace Squawk? They complement it. Squawk covers the universal hazards with maintained rules; custom rules cover conventions specific to your system. Run both.

How many custom rules should a team maintain? Usually a handful — five to ten rules covering the conventions that have actually caused incidents or repeated review comments. Every rule has a maintenance cost, so retire rules that no longer fire or no longer matter.

How do I lint ORM migrations with custom rules? Generate the SQL the ORM will execute in CI — sqlmigrate, migrations script, Prisma’s migration files — and run the rules on that SQL.

Related Articles