Metadata-Version: 2.4
Name: pg-migration-guard
Version: 1.0.0
Summary: Free static safety checker for Postgres SQL migrations (catches locking and destructive DDL before it reaches production).
Author: Technical Turtle
License: MIT
Project-URL: Homepage, https://technical-turtle.com/postgres-migration-auditor
Project-URL: Source, https://github.com/technical-turtle/pg-migration-guard
Keywords: postgres,postgresql,migration,flyway,ddl,lint,database,ci
Classifier: Development Status :: 5 - Production/Stable
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Quality Assurance
Requires-Python: >=3.8
Description-Content-Type: text/markdown
License-File: LICENSE
Dynamic: license-file

# pg-migration-guard

A free, static safety checker for PostgreSQL SQL migrations. It reads a proposed
Flyway or raw SQL migration and flags the DDL patterns that quietly take a heavy
lock, rewrite a whole table, or destroy data, before that migration reaches
production. Every finding names the hazard, explains why it blocks, and prints a
safe rewrite.

It parses SQL with libpg_query, the same C parser PostgreSQL itself uses, so it
reads real statements rather than guessing with regexes: a column named like a
keyword, a schema-qualified table name, or dollar-quoted code is read correctly,
not misread by a pattern match. The parser is bundled as a self-contained
BSD-3-Clause binary and bound through the Python standard library (ctypes), so
there are no third-party Python dependencies, and the tool never connects to a
database or the network.

## What it catches

Ten migration hazards (12 rule IDs):

| Rule | Hazard |
| --- | --- |
| IDX01 / IDX02 | `CREATE INDEX` without `CONCURRENTLY`, and a `CONCURRENTLY` index op inside a transaction (which errors) |
| COL01 | `ADD COLUMN` with a volatile `DEFAULT` (rewrites the table) |
| COL03 | `ALTER COLUMN ... TYPE` (rewrites the table) |
| NN01 | `SET NOT NULL` (scans the whole table under `ACCESS EXCLUSIVE`) |
| FK01 | `ADD FOREIGN KEY` without `NOT VALID` (validates under a write-blocking lock) |
| CHK01 | `ADD CHECK` without `NOT VALID` (scans the whole table) |
| PK01 | `ADD PRIMARY KEY` / `UNIQUE` (builds the index under `ACCESS EXCLUSIVE`) |
| ORD01 / ORD02 | a backfill and its constraint in one migration, and an unbatched `UPDATE`/`DELETE` with no `WHERE` |
| LCK01 | lock-taking DDL with no `lock_timeout` guard |
| DES01 | destructive ops: `TRUNCATE`, `DROP TABLE`, `DROP COLUMN`, `DROP SCHEMA`, `DROP DATABASE` |

## Install

```bash
pip install pg-migration-guard
```

Requires Python 3.8+. No other dependencies. A prebuilt parser binary is bundled
for Linux (x86_64, aarch64), macOS (arm64, x86_64), and Windows (x86_64).

## Use

```bash
pg-migration-guard db/migration/V42__add_index.sql
pg-migration-guard --format json db/migration/V42__add_index.sql
pg-migration-guard --fail-on warn --pg-version 15 db/migration/V42__add_index.sql
```

Options:

- `--format {human,json}` (default `human`)
- `--fail-on {blocker,warn,advisory,none}` (default `blocker`) sets the severity
  that makes the command exit nonzero.
- `--pg-version N` assumes a Postgres major version for version-gated rules.
- `--self-test` runs the internal checks. `--version` prints the version.

Exit codes: `0` clean, `1` a finding at or above `--fail-on`, `2` a usage or
parse error.

## GitHub Action

Add a workflow that fails the pull request and annotates each risky line:

```yaml
name: migration-guard
on:
  pull_request:
    paths: ["db/migration/**.sql"]
jobs:
  guard:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.x"
      - uses: technical-turtle/pg-migration-guard@v1
        with:
          path: db/migration
          fail-on: blocker
          # pg-version: "15"   # optional
```

Findings are emitted as GitHub annotations (`::error::` for blocker, `::warning::`
for warn, `::notice::` for advisory), so they appear inline on the diff.

## Before and after

A migration that looks harmless:

```sql
CREATE INDEX idx_orders_customer ON orders (customer_id);
ALTER TABLE orders DROP COLUMN legacy_note;
```

```
$ pg-migration-guard V42__orders.sql

  BLOCKER  DES01  Destructive operation (TRUNCATE / DROP TABLE / DROP COLUMN)   (high)
    stmt #1, line 2:  ALTER TABLE orders DROP COLUMN legacy_note
    Why:  DROP COLUMN is fast and metadata-only, but it is irreversible and breaks any
          app version still selecting the column during a rolling deploy.
    Safe: Confirm intent. For a live column, stop referencing it first (contract phase), then drop.

  WARN     IDX01  CREATE INDEX without CONCURRENTLY   (high)  [latent blocker on a large/hot table]
    stmt #0, line 1:  CREATE INDEX idx_orders_customer ON orders (customer_id)
    Why:  Plain CREATE INDEX takes a SHARE lock, blocking all writes to the table until the build finishes.
    Safe: CREATE INDEX CONCURRENTLY idx ON tbl (col);  -- outside a transaction

Summary: 1 blocker, 1 warn, 0 advisory
```

The safe rewrite: build the index without holding a write lock, and drop the
column in two steps (stop reading it in the app first, then drop it in a later
migration):

```sql
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);  -- outside a transaction
-- ship a release that stops reading legacy_note, then in a later migration:
ALTER TABLE orders DROP COLUMN legacy_note;
```

## Known limitations

- Liquibase changelogs (XML, YAML, JSON) are not parsed. Point this tool at SQL
  migrations (raw SQL or Flyway SQL); for Liquibase changelog formats, use the
  paid Postgres Migration Safety Auditor.
- The tool assumes a migration runs inside a transaction (the Flyway and
  Liquibase default). A Liquibase formatted-SQL file with `runInTransaction:false`
  can therefore get a spurious IDX02 finding on a `CONCURRENTLY` index operation,
  since outside a transaction that operation is actually fine.

## Full coverage

pg-migration-guard is the free version of the **Postgres Migration Safety
Auditor**. The paid tool checks 33 rules (not 12), each linked to an official
Postgres source, and adds Liquibase XML / YAML / JSON changelog support,
Postgres-version and table-size aware checks, a config file with per-rule
mute controls, and a Claude Code skill:
https://technical-turtle.com/postgres-migration-auditor

## License

MIT. Bundles the libpg_query parser (BSD 3-Clause); see `THIRD-PARTY-NOTICES.md`.
