# Lint and gate unsafe SQL

Analyze migration files for production-unsafe patterns, set the rule policy, declare rules of your own, and gate destructive statements at apply time.

Source: https://docs.ptah.run/v0.8.0/versioned/lint/

Some SQL is legal and still wrong to run against a database people depend on.
Ptah reports those statements before they run. You define which findings block
a release and carry that policy into the apply-time destructive gate.

Prerequisites: a migration directory (see
[Generate migrations](../generate/)). Whether that directory is the one you
reviewed is a separate question, answered by
[Integrity and safety](../integrity-and-safety/).

`ptah migrations lint` analyzes migration files for patterns that are legal
SQL but dangerous in production — dropped tables and columns, lock-heavy
DDL, and dialect-specific hazards:

```bash
ptah migrations lint --dir ./migrations --dialect sqlite
```

A finding names the file, line, rule, and remediation (exit `1`):

```text
migrations/0000000003_drop_users.up.sql:1 [error] DS101: DROP TABLE permanently deletes table users and every row in it; take a verified backup first and consider a rename-and-retire window instead (table dropped)

1 finding(s).
```

A clean run prints `No lint findings.` and exits `0`.

Every rule identifier, with its one-line meaning, the dialects it applies to,
the surface that reports it, and whether the name is Atlas's or Ptah's, is
enumerated in [Lint rules](../../reference/lint-rules/).

## Which server the run plans against

Whether a DDL statement is safe against a live database is decided by the
server version at least as often as by the statement. `ADD COLUMN` with a
non-volatile default rewrites the table on PostgreSQL 10 and edits the catalog
on 11. `DROP COLUMN` copies the table on MySQL 8.0.28 and is `INSTANT` on
8.0.29. A rule that fires on every version is noise on the new ones; one that
stays quiet is wrong on the old ones.

`--server-version` names that server:

```bash
ptah migrations lint --dir ./migrations --dialect postgres --server-version 17
```

The value takes the same spellings every other Ptah command accepts — `17`,
`8.4.6`, `10.11.6-MariaDB`, or a full server banner. It needs a target dialect,
and the first source that names one wins:

1. `--server-version` and `--dialect`.
2. `server-version` and `dialect` in `.ptah-lint.yaml`.
3. The dev database, when `--dev-url` is given. Its banner is read off the same
   connection the replay uses, so no extra round trip and no second answer.

A run that names no server plans against the dialect's default capability set.
That is a starting point rather than a measurement, so the report carries no
version and a machine can tell the two apart. A run that names no dialect
either is the ordinary offline invocation: every dialect-independent rule
runs, and there is no version to refine. Naming a version without a dialect
stops at exit `2`, because a run over every engine has no single target the
version could describe, and so does a version naming no server or a different
product than the target.

The dialect and a declared version both come from the product the connection
reports, not from the URL's scheme, when nothing else named the dialect. A
scheme is not a product: MariaDB speaks the MySQL protocol, so its address is a
`mysql://` URL, and `postgres://` reaches CockroachDB, YugabyteDB and Spanner.
The dialect picks the rules, so that server is linted as MariaDB; a dialect the
run named itself still wins.

`migrations up` and `ptah-compat migrate lint` read the same file. The apply
gate resolves `server-version` against the dialect its connection reports, and
still runs its dialect-independent rules on an engine the linter has none for;
the compatibility command prints the same warning on `stderr`, after its
report.

A version Ptah recognizes but has not measured resolves to the nearest release
line it has, and the run says so. `--format json` carries it as
`server_version_note`; every other format renders findings and nothing else, so
there the sentence is printed on whichever stream the report did not take:

```text
warning: postgres 99 is newer than the newest measured release line 18.x; capabilities were planned as 18.x
```

## Which direction each rule reads

Most rules describe a forward schema change, so they read the `.up.sql` half
only: a down file dropping what its up created is the expected shape, not a
hazard.

Rules whose subject is the **cost or the executability** of a statement read
both halves, because PostgreSQL charges the same lock and enforces the same
transaction restriction whichever direction asked for it:

- `PG106` — `DROP INDEX` without `CONCURRENTLY` blocks writes. This is the
  statement a rollback file is normally made of, including the one Ptah
  generates whenever the forward statement was not a concurrent build.
- `PG103` — a `CONCURRENTLY` statement in a file with no `no_transaction`
  marker cannot execute at all. A rollback that cannot run is only discovered
  when someone needs it.
- `TX101` — a file mixing autocommit-only statements with transactional DDL.
  The classification is semantic, not keyword-based: a file that adds a value to
  an enum type it creates itself is not a mix, because PostgreSQL allows the new
  value immediately when the type is new in the same transaction, while a file
  adding a value to a pre-existing type and then using it is one. The migrator
  refuses that second shape before its first statement runs, using the same
  classification, so lint and apply cannot disagree about one file.

Useful controls, all designed for CI:

- `--latest N` lints only the newest N migration revision keys — the changeset
  of a pull request rather than all of history. Atlas-format repeatables keep
  their string keys (`R` or `<number>R`), and bare `R` sorts after numeric
  files. `--git-base <branch>` selects the changeset from Git instead.
- `--fail-on error` (default) fails only on error-severity findings;
  `--fail-on any` fails on warnings too; `--fail-on none` always exits `0`.
- `--format json`, `--format sarif`, `--format github-actions`, and
  `--format gitlab` feed code scanners and merge-request annotations. The
  SARIF output is a SARIF 2.1.0 document that GitHub code scanning ingests,
  and `gitlab` is a GitLab Code Quality report that GitLab CI reads as a
  `codequality` artifact — [CI](../../testing/ci/) shows both upload steps.
- `--dialect` gates dialect-specific rules; accepted values are `postgres`,
  `mysql`, `mariadb`, `sqlite`, `sqlserver`, `clickhouse`, `cockroachdb`,
  `yugabytedb`, and `spanner`. Every documented alias resolves to the canonical
  name — see [Dialects and capabilities](../../concepts/dialects-and-capabilities/)
  for the spelling table. `--dev-url` infers the dialect and replays the
  directory on the dev database. Before each analyzed version
  the run reads the schema state that version starts from and hands it to
  the rules that ask for it: the columns with their type, nullability,
  default, character set and collation, the indexes with their key parts,
  and what reads each column. A rule that compares a statement with that
  state stays quiet without it, a rule the state only refines reports from
  the text alone, and the run names both kinds as unmet so the thinner
  report is never mistaken for a clean one. Two info-severity rules, `MY130P`
  and `PG301P`, say where the state shows a column change applied in place,
  and by which algorithm, so quiet cost rules read as a judgment rather than
  as a rule that did not look.
- `--disable DS101` (or a family such as `MY`) skips rules ad hoc; a
  committed `.ptah-lint.yaml` does it persistently and adds per-rule
  severity and path scoping — see below.

For OCI-distributed directories, `lint --dir oci://...` lints the published
artifact, and `--attach` stores the canonical report next to it — see
[OCI registry artifacts](../../operate/oci-registry/).

## Configure rules with `.ptah-lint.yaml`

Commit a `.ptah-lint.yaml` next to the migration files to make lint policy
part of the reviewed migration directory. `ptah migrations lint --config`
selects an explicit lint policy; without that flag, lint loads
`<dir>/.ptah-lint.yaml` when present:

```yaml
dialect: postgres
server-version: "17"
disabled-rules:
  - MF103
  - MY
rules:
  DS103:
    severity: warning
  DS104:
    severity: info
  DS102:
    severity: error
    exclude:
      - legacy/**
```

- `dialect` sets the default lint dialect; `--dialect` overrides it. It takes
  the same spellings as `--dialect`, aliases included, and is stored
  canonicalized — `dialect: pgx` and `dialect: postgres` select the same rules.
- `server-version` names the server the migrations will run against, in the
  same spelling `--server-version` takes: `"17"`, `"8.4.6"`,
  `"10.11.6-MariaDB"`. It decides the capability set a rule reads, because
  whether a statement is safe against a live database is decided by the server
  version at least as often as by the statement. `--server-version` overrides
  it, and it overrides what a dev database reports about itself. A value that
  names no server, or one naming a different product than `dialect`, fails
  config parsing (exit `2`). See [Which server the run plans
  against](#which-server-the-run-plans-against).
- `disabled-rules` lists rule codes (`DS101`) or family prefixes (`MY`) to
  skip entirely; entries merge with `--disable` flags. Selectors and custom
  rule codes use uppercase ASCII letters and digits and start with a letter.
  A selector that is a rule's own code selects that rule alone, and any other
  selector is a prefix: `MF101` disables the unique-index check and leaves
  the missing-down rule `MF101P` running, while `MF` disables both and `PG3`
  disables every `PG3xx` rule. The same rule decides a `ptah:nolint`
  selector.
- `rules` keys name an exact code or a family prefix; the most specific key
  wins, so a `DS102` entry beats a `DS` entry, and a key that is a code
  governs that code alone.
- `severity` accepts `info`, `warning` or `error` and replaces the rule's
  default severity on its findings — the level that `--fail-on` and the
  apply-time destructive gate below evaluate. Only `error` gates: a rule set to
  `info` or `warning` is reported and exits `0`. `info` exists so a rule can be
  introduced to a repository that still violates it, and so a team can say
  "surface this, never block on it" without the alternatives being loud enough
  to fail or absent from the report entirely. In SARIF the three levels are
  `note`, `warning` and `error`. Any other value fails config parsing (exit
  `2`), so the vocabulary gained a level rather than becoming permissive.
- `exclude` lists slash-separated path globs (`**` crosses directory
  levels) where the rule is skipped. Prefer paths relative to the migration
  directory, such as `legacy/**`; these match regardless of how `--dir` was
  spelled. A directory-prefixed pattern such as `migrations/legacy/**`
  matches only when the command path has that prefix, for example
  `--dir migrations`, and need not match an absolute `--dir` path. Patterns
  must already be normalized: repeated separators, `.` or `..` segments, and
  trailing separators are configuration errors rather than being rewritten
  into a broader match. Empty patterns and malformed glob syntax are also
  errors; Ptah reports the rule and pattern instead of silently weakening the
  policy.

- `gate` names the rule families whose error-severity findings refuse
  `ptah migrations up`, beyond the `DS` family the apply gate always blocks
  on. `gate: { families: [MY, PG] }` with `rules: { MY130: { severity: error } }`
  makes a MySQL table copy stop a deploy; without the section the same finding
  is reported by `ptah migrations lint` and applies. A family no rule belongs
  to, and an empty list, fail config parsing.

- `online: require` selects the online mode, which reports every statement it
  cannot prove takes no blocking lock and refuses the apply. See
  [The online mode](../online-mode/).

Configuration decoding is strict. Unknown keys, misspelled keys such as
`severty`, lowercase or whitespace-padded selectors, selectors that match no
registered rule, unsupported dialects or severities, empty or malformed
or non-normalized exclusion globs, and multiple YAML documents fail before
linting or migration execution instead of silently weakening policy.

Precedence: a rule listed in `disabled-rules` never runs, regardless of its
`rules` entry; then `exclude` skips the matching files; then `severity`
relabels the findings that remain. Per-rule entries apply to custom
analyzer codes the same way as to built-in ones — see
[Reusable components](../../extend/components/) for registering custom
rules from Go.

## Enforce a naming convention

A `naming` section makes the six `NM` rules check every name a migration
introduces against the patterns the project chooses: a schema or table it
creates or renames to, a column it declares, adds or renames to, an index or
unique key it names, and a foreign key or check constraint it names. Without
the section the rules stay silent, because the convention is the project's to
state.

```yaml
naming:
  match: '^[a-z][a-z0-9_]*$'
  message: use lower snake case
  severity: error
  index:
    match: '^idx_[a-z0-9_]+$'
    message: prefix an index with idx_
  foreign-key:
    match: '^fk_[a-z0-9_]+$'
```

- `match` is a Go regular expression every kind of name must satisfy. Anchor
  it: `^[a-z_]+$` is a convention, `[a-z]` accepts any name with a letter in
  it. Quoting is stripped before the match, so `"Users"` is judged as `Users`.
- `schema`, `table`, `column`, `index`, `foreign-key` and `check` each take a
  `match` and `message` of their own, which replace the shared ones for that
  kind. A kind with no pattern from either place is not checked. A unique or
  primary key constraint counts as an index, as it does for Atlas.
- `message` is appended to each finding. `severity` is the level the six
  findings report at, `warning` by default; a `rules` entry for one of the
  codes still wins, so `rules: { NM103: { severity: info } }` softens columns
  alone.
- A pattern that does not compile, a kind block without a `match`, and a
  section with no pattern at all fail config parsing, so a naming block never
  reads as a convention enforced while checking nothing.

The same convention in `atlas.hcl`, where it is the Atlas naming analyzer's
block and reaches the same six rules; `error = true` is the block's severity
switch:

```hcl
env "local" {
  lint {
    naming {
      error   = true
      match   = "^[a-z][a-z0-9_]*$"
      message = "use lower snake case"
      index {
        match   = "^idx_[a-z0-9_]+$"
        message = "prefix an index with idx_"
      }
      foreign_key {
        match = "^fk_[a-z0-9_]+$"
      }
    }
  }
}
```

A `.ptah-lint.yaml` `naming` section takes precedence over the project file's
block, the way `rules` entries do.

## Declare a rule of your own

A `rules` entry that carries `match` **defines** a rule instead of configuring
one. The rule runs on `ptah migrations lint` and on the compat `migrate lint`
alike, with no Go build anywhere:

```yaml
dialect: postgres
rules:
  NOVARCHAR:
    title: varchar(n) instead of text
    severity: warning
    match: 'strcontains(lower(statement.sql), "varchar(")'
    message: use text, not varchar(n) — postgres stores them identically
```

The same rule in `atlas.hcl`, where `match` is written as a bare expression:

```hcl
env "local" {
  lint {
    rule "NOVARCHAR" {
      title    = "varchar(n) instead of text"
      severity = "warning"
      match    = strcontains(lower(statement.sql), "varchar(")
      message  = "use text, not varchar(n)"
    }
  }
}
```

`match` is an expression evaluated once per statement; the rule fires where it
is true. `message` is required — a finding whose text is its own rule code says
what fired and not why it matters. `severity` defaults to `warning`, `title`
defaults to the code, `dialects` restricts the rule to named dialects, and
`applies-to-down` (`applies_to_down` in HCL) extends it to the down half of a
migration.

The code follows the same form as every other rule — uppercase ASCII letters
and digits — because it is what findings print and what `--disable`,
`disabled-rules` and a `ptah:nolint` directive select. Put the readable name in
`title`. A code that already belongs to a built-in rule is refused rather than
overridden: replacing a data-safety check with an expression that never fires
would leave the report naming a rule that is not running.

### What an expression can read

| Name | Meaning |
| --- | --- |
| `statement.sql` | the statement as written, comments included |
| `statement.canonical` | comment-stripped, whitespace-collapsed, uppercased |
| `statement.words` | token words; string literals and quoted identifiers stay whole |
| `statement.line` | 1-based line of the statement's first token |
| `file.path` | the path findings report |
| `file.is_up`, `file.is_down` | which direction the statement belongs to |
| `dialect` | the dialect being linted |

Prefer `statement.words` for a rule about SQL keywords: a column named `drop`
or a string literal containing `DROP COLUMN` cannot impersonate a keyword
there, and a substring match on `statement.sql` has no way to tell them apart.

The functions are the same set `atlas.hcl` evaluates — `lower`, `upper`,
`join`, `regexall`, `length` and the rest — plus `strcontains(haystack,
needle)` for substring matching. Note that `contains` tests **list** membership,
so it belongs on `statement.words`; using it on a string reports the mistake and
names `strcontains` as the fix.

There is deliberately no `file`, `fileset`, `getenv` or `print`. A rule that
could read a file or the environment would report findings that depend on the
machine it ran on, so the same migration would lint clean on one checkout and
fail in CI with nothing in the migration to explain it. Evaluation is a pure
function of the statement, which is what makes a finding reproducible.

An expression must evaluate to a boolean; anything else is refused rather than
coerced, since a coerced value fires on every statement or on none and both
look like a working rule. A malformed declaration — an empty or unparseable
expression, an unknown name, a missing message, an unsupported dialect — fails
the run before any findings are reported.

## Suppress a single statement inline

When one reviewed statement is acceptable but the rule should stay active
everywhere else, put a `ptah:nolint` comment directly above it:

```sql
-- ptah:nolint DS102
ALTER TABLE users DROP COLUMN archived_note;
```

The directive suppresses only the named rules, and only for the statement
directly below it — a blank line between the comment and the statement
detaches the two. List several codes separated by spaces or commas, name a
family (`DS`), or write a bare `-- ptah:nolint` to silence every rule for
that one statement.

`-- atlas:nolint DS102` is accepted as an alias with the same
statement-scoped behavior, so a directory shared with Atlas tooling keeps
one set of directives. Atlas analyzer-name selectors (`destructive`,
`data_depend`, `concurrent_index`, `incompatible`, `nestedtx`) name rule
families and work here too. A code selector always names the code the
command you ran printed, so on this surface it is the native code:
`ptah migrations lint` reports `DS102` for a dropped column and
`-- atlas:nolint DS102` silences it, while `ptah-compat migrate lint`
prints the same finding as `DS103`.

The `atlas:` spelling follows Atlas's matching rule rather than Ptah's, so a
code selector there matches one code exactly: `-- ptah:nolint DS` silences
the data-safety family and `-- atlas:nolint DS` silences nothing. An
unrecognized `atlas:nolint` selector is accepted and silences nothing,
without a warning; `.ptah-lint.yaml` `disabled-rules` stays strict and
rejects a selector matching no registered rule. Whole-file `atlas:nolint`
headers take effect only on the Atlas-compatible surface — see
[Atlas migrate commands](../../atlas/migrate-commands/).

## Where each rule runs, and what a finding does there

A rule being implemented, a rule running on a command, and a rule refusing to
proceed are three different facts, and the page you are reading keeps them
apart. The [lint rules reference](../../reference/lint-rules/) lists what is
implemented. This table says where each of those rules runs and what a finding
does on that surface:

| Surface | Rules that run | What a finding does |
| --- | --- | --- |
| `ptah migrations lint` | the whole registry, gated by `--dialect` | reported; exit `1` at `--fail-on` |
| `ptah migrations up` | every family but `MF`, `BC`, `PG` and `MY` | an error-severity `DS` or gated finding refuses the apply, exit `2` |
| `ptah-compat migrate lint` | the same registry, under the `atlas.hcl` policy | reported in Atlas's format; an error exits `1` |
| `ptah-compat schema apply` | the rules the `atlas.hcl` `lint` block names | an error-severity finding refuses the apply |
| `ptah-compat schema plan lint` | the whole registry, over the plan's SQL | reported; exit `0` unless told to fail |
| the `ptah` GitHub action | `ptah migrations lint` | the job fails at `lint-fail-on` |

What each row leaves out:

- `ptah migrations lint` runs the rules that need a dev database when
  `--dev-url` is given and names them as unmet otherwise. `--fail-on` takes
  `error`, the default, `any`, or `none`. Nothing is applied.
- `ptah migrations up` treats `MF`, `BC`, `PG` and `MY` as advisory and drops
  their findings without printing them. A family named under `gate` in
  `.ptah-lint.yaml` refuses on its error-severity findings the way `DS` does,
  and `--allow-destructive` bypasses the refusal.
- `ptah-compat migrate lint` exits `0` for a version whose findings are
  warnings alone.
- `ptah-compat schema apply` runs no lint pass without a `lint` block, and
  `--skip-lint` applies anyway.
- `ptah-compat schema plan lint` fails the command on an error-severity
  finding only under `PTAH_ATLAS_PLAN_LINT_FAIL_ON_ERROR=1`.
- The GitHub action runs the lint step with `lint: "true"`; `lint-fail-on`
  is `error` by default.

Lint and apply are separate on purpose: a locking hazard, a naming convention,
or a rewrite the operator has planned for is something to review, not
something the migrator should refuse to run on its own. A project that wants a
finding to stop a deploy has two explicit places to say so: the lint run in
CI with `--fail-on`, and the `gate` section, which widens the apply-time gate
by family for the rules the project has rated `error`.

## The destructive-change gate

Destructive statements require explicit policy at two points:

- **At generation**, `plan`/`generate --check-destructive` refuse to write
  destructive SQL without `--allow-destructive` — see
  [Generate migrations](../generate/).
- **At apply**, `ptah migrations up` refuses pending migrations that contain
  destructive statements (exit `2`):

```text
error: error running migrations: pending migrations contain destructive statements; rerun with --allow-destructive after review:
- 0000000003_drop_users.up.sql:1 DS101 error: DROP TABLE permanently deletes table users and every row in it; take a verified backup first and consider a rename-and-retire window instead
```

Use `--allow-destructive` only after the plan has been reviewed and the
rollback path is understood.

`ptah migrations up` always loads and validates the conventional
`<migrations-dir>/.ptah-lint.yaml`; when the apply-time gate is active, it
blocks on error-severity `DS` data-safety findings, and on error-severity
findings from any family the policy's `gate` section names. What the gate
lints is always the dialect the connection reports, never the policy's — a
policy `dialect` is a statement about the directory, not a scanner selector.

A nonempty policy `dialect` must name the **same engine family** as the
connected database, and a cross-family mismatch fails before migration analysis
or execution. The families are the ones on
[Dialects and capabilities](../../concepts/dialects-and-capabilities/):

| Policy `dialect` | Connected database | Verdict |
| --- | --- | --- |
| `postgres`, or any alias such as `pgx` | PostgreSQL | matches |
| `postgres` | CockroachDB, YugabyteDB, Spanner | matches — they ride the PostgreSQL family |
| `mysql` | MariaDB | matches — one family |
| `mariadb` | MySQL | matches — one family |
| `mysql` or `mariadb` | PostgreSQL | **does not match** |
| `postgres` | MySQL or MariaDB | **does not match** |
| `sqlite`, `sqlserver`, `clickhouse` | anything else | **does not match** — each stands alone |

Naming a family member rather than the exact engine is accepted because it
does not change the analysis: every built-in MySQL-family rule applies to both
`mysql` and `mariadb`, and the scanner treats them identically. Note the one
asymmetry inside the PostgreSQL family: the `PG` and `TX` rules apply to
PostgreSQL only, except where a rule was measured on the other engines, which
today is `PG303` on CockroachDB, YugabyteDB and Spanner. So those databases
run the dialect-independent families plus that measured set — whether or not
a policy file exists, and regardless of what it declares.

On the standalone lint command, an explicit `--dialect` still overrides the
policy, and `--dev-url` is checked against the policy by exactly the same
family rule, so `ptah migrations lint` and `ptah migrations up` accept the same
policy files. The `--config` flag on `ptah migrations up` selects `ptah.yaml`;
it does not select a lint policy. This distinction prevents a project
configuration path from silently replacing the policy shipped with the
migration directory.

The gate otherwise uses the same dialect selection, rule configuration, and
path matching as `ptah migrations lint`. That makes the escape hatch
proportional: a rule downgraded with
`severity: warning`, listed under `disabled-rules`, or excluded for a path
stops blocking exactly that reviewed pattern — a widening
`ALTER COLUMN ... TYPE` under `rules: {DS103: {severity: warning}}` applies
without `--allow-destructive` — while a `DROP TABLE` in another pending
file of the same batch still aborts the apply. `--allow-destructive`
remains the all-or-nothing per-run override rather than the only way past
a single warning-grade finding. `--allow-destructive` bypasses the findings
gate, but it does not bypass loading or validating the lint policy.

Lint-policy severities are `warning` and `error`. Generated safety reports use
the operational vocabulary `safe`, `warning`, and `destructive`; an `error`
lint assessment and a `destructive` safety assessment are both blocking.

Know the limits of this gate: the policy file is not tamper-evident.

`ptah.sum` hashes only the migration `*.sql` files, so `ptah migrations
validate` and `up --verify-sum` still pass when `.ptah-lint.yaml` is added or
edited out-of-band, and the apply prints no notice when the config suppresses
a destructive finding — a one-line `disabled-rules: [DS]` dropped next to the
migrations at deploy time disables this gate silently.

Loosening the gate is a visible committed change only if your process makes it
one: commit `.ptah-lint.yaml`, treat any edit to it as an edit to the gate in
review, and restrict writes to the deployed migration directory, because the
integrity file does not protect the policy the way it protects the SQL.

## Next steps

- Every rule identifier, its meaning, and which surface reports it:
  [Lint rules](../../reference/lint-rules/).
- Running these gates on every pull request? [CI](../../testing/ci/).
- Proving the directory is the one you reviewed?
  [Integrity and safety](../integrity-and-safety/).
- Registering a rule from Go instead of YAML?
  [Reusable components](../../extend/components/).