Metadata-Version: 2.5
Name: localtools-postgres-migration-checker
Version: 1.0.1
Summary: Check PostgreSQL migrations for the lock each statement takes, what it blocks, and a safe rewrite (localtools, offline)
Project-URL: Source, https://github.com/chayprabs/localtools
License-Expression: MIT
License-File: LICENSE
Requires-Python: >=3.10
Requires-Dist: multipart>=1.2
Requires-Dist: postgast==0.2.0
Requires-Dist: protobuf<8,>=5.29
Description-Content-Type: text/markdown

# Postgres Migration Checker

Offline static analysis of PostgreSQL migrations: estimated per-statement locks, conflicting traffic, scans/rewrites, transaction conflicts and concrete safer-step examples. SQL is never executed.

Install the portable app (no administrator or external runtime):

```powershell
irm https://raw.githubusercontent.com/chayprabs/localtools/main/postgres-migration-checker/install.ps1 | iex
```
```sh
curl -fsSL https://raw.githubusercontent.com/chayprabs/localtools/main/postgres-migration-checker/install.sh | bash
# Or Python 3.10+: pipx install localtools-postgres-migration-checker
```

Run `localtools-postgres-migration-checker` for the UI. Paste UTF-8 SQL or choose files in execution order. Each chosen file can be removed. A new paste or upload replaces the previous input. Copy/download JSON, Markdown or SARIF. Views load 50 statements at a time.

```sh
localtools-postgres-migration-checker check migration.sql --format json
localtools-postgres-migration-checker check V1__users.sql V2__index.sql --pg 17 --transaction auto
localtools-postgres-migration-checker check - --transaction always --fail-on medium --format markdown -o report.md
curl -F 'sql=CREATE INDEX i ON users(email);' http://127.0.0.1:47116/api/check
```

CLI exit 1 means risk at/above `--fail-on` (default high), or an input/runtime error; `--fail-on never` retains findings and exits 0 for a completed report. API fields: `sql` (takes precedence over files), `name`, repeatable `file`, `pg` (11–18, default 18), `transaction` (auto/always/never), `format` (json/markdown/sarif/human). Parse failures are high-risk findings with line/column, so other statements remain visible. Invalid requests return JSON errors with 400/413/422/503.

All paths share limits: 100 files, 4 MiB aggregate UTF-8 SQL, 5,000 statements, 16 MiB report, 30 seconds per check, two concurrent checks. Workers have a 512 MiB memory budget (Windows job process memory / Unix address space). The API allows 256 KiB form overhead. Inputs over limits are rejected; deep nesting can be rejected earlier by the PostgreSQL parser. Reports write atomically. No input history is stored. Parent-owned job folders are deleted after completion/cancellation; folders left by a hard crash are swept after one hour at the next start.

## Engine and scope

Pinned **postgast 0.2.0** (BSD-2-Clause) calls the real **libpg_query PostgreSQL 18.4** parser/deparser. Typed PostgreSQL AST nodes drive the rules; no regex statement splitting. PostgreSQL 11–18 selects known version-dependent rules, not version-specific grammar validation. The abandoned scaffold used GPL pglast: it is not bundled or imported. Squawk is a good general SQL linter, but its CLI does not supply this tool's per-statement lock model, so a separate linter sidecar is unnecessary.

The portable ZIP bundles CPython 3.13 and all dependencies. PyPI has postgast wheels for Windows x64, Linux x64 and macOS ARM64. **A plain Intel macOS PyPI install is unavailable**: upstream has no Intel wheel, and its published source archive lacks build headers. Use the self-contained Intel Mac ZIP/one-line installer, or Python 3.13 with `pipx install localtools-postgres-migration-checker --python python3.13 --pip-args="--find-links https://github.com/chayprabs/localtools/releases/download/postgres-migration-checker-v1.0.0/postgast-0.2.0-cp313-cp313-macosx_10_13_x86_64.whl"`. That BSD dependency wheel is built unmodified from the complete upstream v0.2.0 Git tag and submodules (the archive is not used).

Locks are **static estimates**, not live observations. Existing schema/data, relations reached through views/triggers/functions/rules, partitions/default partitions, inherited tables, dependencies, search_path resolution and actual durations are unknown. Schema-qualified and unqualified names remain distinct. Newly created tables and validated checks are inferred from the supplied ordered SQL; invalid/rolled-back statements can invalidate those assumptions. Review all examples before use: they can need app changes, synchronization triggers, backups and adaptation to partitioning or schema. No automatic SQL patching is offered.

Framework detection reads real SQL comments: goose Up/Down/NO TRANSACTION, dbmate up/down/transaction:false, Liquibase formatted SQL changesets/runInTransaction:false, and Flyway `V…__…sql`/`R__…sql` filenames. Down sections are omitted. Explicit BEGIN/COMMIT is tracked. Rails/Django source code is not SQL; export SQL first and choose the runner transaction mode. Resolve Flyway/Liquibase placeholders before checking. psql command lines/COPY data are skipped with notes. Bodies of functions/DO/dynamic SQL and unmodelled statement kinds are explicitly outside analysis. Runner configuration files, database execution, live schema inspection and lock-time predictions are cut.

Primary references: [explicit locking / conflict matrix](https://www.postgresql.org/docs/18/explicit-locking.html), [ALTER TABLE lock exceptions and rewrite conditions](https://www.postgresql.org/docs/18/sql-altertable.html), [concurrent index caveats](https://www.postgresql.org/docs/18/sql-createindex.html), [postgast engine/license](https://github.com/eddieland/postgast/tree/v0.2.0), [libpg_query](https://github.com/pganalyze/libpg_query). These rules are exercised by the built-executable tests in `tests/`.

`localtools-postgres-migration-checker update` / `uninstall` use the portable installers; package-manager installations print their appropriate commands. MIT license; bundled dependency notices in THIRD_PARTY_NOTICES.md. Bug reports: the public [localtools issue form](https://github.com/chayprabs/localtools/issues/new?template=postgres-migration-checker.yml).
