README
¶
pg-sprite
[!WARNING] Work in progress — not ready for any use. This project is under active early-stage development. There are no releases, no stability guarantees, and no support. Interfaces, behavior, on-disk/database artifacts, and the CLI surface may all change without notice. Do not run this against any database you care about.
Working name — see the naming task in the research build tracker.
An online schema-change engine for PostgreSQL (community, RDS, and
Aurora; 14+): a decoupled planner → router → executor design where the
planner classifies each change, the router picks a strategy, and
interchangeable executors carry it out — the cheap native PostgreSQL idiom
when one exists (CONCURRENTLY, NOT VALID + VALIDATE, fast default,
USING INDEX), while a log-based, checksum-gated, resumable copy-and-swap for
genuine table rewrites lands in a later phase.
The planner is PostgreSQL's missing ALGORITHM= / LOCK= declaration: MySQL
lets authors assert a cost bracket and a concurrency impact and fails closed
when either can't be honored — PostgreSQL silently runs whichever cost
applies. pg-sprite proves both dimensions before execution, routes each
change to the safest sequence that exists, and refuses with a structured
verdict when it can't prove one (see
docs/postgres-online-ddl-reference.md).
Status: Phases 1 and 2.1–2.5. The parse boundary, declarative diff,
classifier, router seam, versioned dry-run plan report, offline linter, and
advisory suggest command are implemented. pg-sprite migrate --alter '…' classifies and
routes the statement, then executes the routed SQL — the planner's safer native sequence by
default when the submitted form blocks (reported in the verdict's executed_sql), a bounded
optimistic native attempt otherwise. A gated --force runs the submitted form as-is under the
same budgets. Changes without an available backend get a structured refusal (exit code 2).
The design docs and the phased
build plan live in docs/ — start with
docs/README.md; the vision — what pg-sprite is and is not —
is docs/vision.md.
The codebase is partitioned into a small safety-critical core and a
periphery — SAFETY.md says which packages are which and the
rules that apply inside the core. Read it before changing anything under
pkg/.
What it looks like
Everything below is captured from a real session against the compose
database (make db-up, PostgreSQL 16). The reports color their labels the
way compilers do when stdout is a terminal; --color=never or a non-empty
NO_COLOR forces plain text, and the --json / --sql machine outputs
are never colored.
Animated demos for the other routes — declarative diff, refusal with typed
help, offline lint — live in docs/demos/, rendered from committed
VHS tapes (make demos re-renders
them).
Diff: declarative desired state in, classified plan out. Point at a
reviewed CREATE TABLE file and get the statements that converge the live
table onto it, reported in the same diagnostic grammar as the dry run.
--sql prints the plan as an executable SQL script instead, and a plan
containing a statement execution would refuse exits 2 — the same CI gate
as the dry run. Watch it in
docs/demos/diff-greenfield.gif; the
machine-readable shape is in
docs/cli-output-examples.md.
Improve: a blocking form is replaced with the safer online sequence.
migrate --dry-run shows exactly what would run, as compiler-style
diagnostics with a doc anchor per finding (exit 0 — the plan is executable).
The demo above records the whole flow — dry run, real run, catalog proof;
the machine-readable shape is in
docs/cli-output-examples.md.
Refuse: no safe path exists, so nothing runs. A genuine table rewrite
needs the copy-and-swap backend (a later phase); the dry run exits 2 so CI
can gate on it without parsing JSON. The exit-code gate stops refusals only —
a destructive-but-executable change (DROP COLUMN) warns and exits 0, so a
gate that must stop drops checks .statements[].destructive in the
--json report. Watch it in
docs/demos/refuse.gif; the machine-readable shape
is in docs/cli-output-examples.md.
Lint: offline, no database needed. Flag blocking idioms in a DDL file and suggest the safer form — no connection, no Docker; error-severity findings exit non-zero, warnings alone pass. Watch it in docs/demos/lint.gif; the machine-readable shape is in docs/cli-output-examples.md.
More shapes — every disposition as JSON, destructive warnings, and exit codes — are in docs/cli-output-examples.md.
Install
Release archives for linux/darwin on amd64/arm64, with checksums.txt, are
published on the releases page
once tags exist. The binary is pure Go (the SQL parser is Wasm), so on any
other platform — or without waiting for a release — go install works with
no C toolchain:
go install github.com/block/pg-sprite/cmd/pg-sprite@latest
Commands
Half the CLI works offline on DDL text alone; the other half connects to a
live database (--url / PGSPRITE_URL, always under bounded lock_timeout
and statement_timeout). Only migrate without --dry-run ever commits a
change — every other command is read-only or fully offline.
| Command | Live database | What the connection is used for |
|---|---|---|
migrate |
required | Resolve the target table, preflight it (privileges, partitioning, size and catalog facts), classify and route the change, then execute the routed SQL under bounded budgets |
migrate --dry-run |
required | The same introspection as a real run — server version, target resolution, table facts — so the printed plan reflects the actual target; executes nothing |
diff |
required | Introspect the live table (read-only) and materialize the desired-state file on a scratch schema inside a transaction that is always rolled back; prints the plan, changes nothing |
status |
required | Read-only view over pg_stat_activity for live pg-sprite sessions on the connected database |
fmt |
none | Canonicalize a schema file — parser only |
lint |
none | Flag patterns the engine would refuse, rewrite, or gate, from the DDL text alone |
suggest |
none | Map risky DDL to the safer native form the engine would run, with typed caveats; advisory, always exits 0 |
The offline commands have no connection flags at all, so they cannot be pointed at a database by accident.
Demo
A runnable tour of the CLI against a local PostgreSQL (Docker required):
make demo
It builds the binary, starts the compose database, seeds demo tables, and walks every planner route (dry-run), the declarative diff, the offline commands, and real executions — including the safer-sequence substitutions and a structured refusal. Rerunnable; see demo/README.md.
Development
make setup # one-time: configure git hooks (.githooks)
make build # build ./... and the bin/pg-sprite binary
make test # full suite; integration tests need Docker
make test-unit # unit tests only (SKIP_INTEGRATION=1)
make lint # golangci-lint
Integration tests run against a real PostgreSQL via testcontainers. PG_VERSION
selects the major (default 16); CI runs the matrix 14 → 18. To iterate against a
long-lived local database instead of per-test containers:
make db-up PG_VERSION=14 # start PostgreSQL 14 on localhost via compose
make test-db # run the suite against it (PG_DSN)
make db-down # stop and discard it
make test-supported-postgres runs the full suite against every supported
major (14 → 18) — the local mirror of the CI matrix. See
docs/testing.md for the test-suite layout, what each build
phase owes, and the vanilla-PostgreSQL-vs-real-Aurora validation boundary.
Contributing
Not yet — see CONTRIBUTING. Safety-relevant issue reports are welcome even at this stage.
License
Directories
¶
| Path | Synopsis |
|---|---|
|
cmd
|
|
|
pg-sprite
command
Command pg-sprite is an online schema-change engine for PostgreSQL.
|
Command pg-sprite is an online schema-change engine for PostgreSQL. |
|
internal
|
|
|
cli
Package cli defines the pg-sprite command tree (Kong): migrate and status (the optimistic front door), diff and fmt (the declarative front door), and lint and suggest (the offline checker and advisor).
|
Package cli defines the pg-sprite command tree (Kong): migrate and status (the optimistic front door), diff and fmt (the declarative front door), and lint and suggest (the offline checker and advisor). |
|
testutil
Package testutil is the integration-test harness: a real PostgreSQL in a container plus per-test throwaway schemas.
|
Package testutil is the integration-test harness: a real PostgreSQL in a container plus per-test throwaway schemas. |
|
pkg
|
|
|
dbconn
Package dbconn is the engine's database connectivity layer: pgx pool construction with safe session defaults (lock_timeout, statement_timeout), RDS/Aurora TLS, bounded retries for transient errors, and a helper to terminate backends blocking a session's lock acquisition.
|
Package dbconn is the engine's database connectivity layer: pgx pool construction with safe session defaults (lock_timeout, statement_timeout), RDS/Aurora TLS, bounded retries for transient errors, and a helper to terminate backends blocking a session's lock acquisition. |
|
diffplan
Package diffplan is the declarative front door as a library: a parsed desired-state schema in, the routed convergence plan out.
|
Package diffplan is the declarative front door as a library: a parsed desired-state schema in, the routed convergence plan out. |
|
executor
Package executor runs schema changes against the database.
|
Package executor runs schema changes against the database. |
|
lint
Package lint checks DDL offline for patterns the engine would refuse, rewrite, or gate.
|
Package lint checks DDL offline for patterns the engine would refuse, rewrite, or gate. |
|
plan
Package plan defines the machine-readable dry-run plan report: the stable JSON contract an operator or orchestrator consumes to decide whether and how a change would execute.
|
Package plan defines the machine-readable dry-run plan report: the stable JSON contract an operator or orchestrator consumes to decide whether and how a change would execute. |
|
planner
Package planner classifies schema-change statements: for each operation it decides whether PostgreSQL can run it online natively (possibly via a safer idiom it suggests), whether it needs the engine's copy-and-swap path, or whether it is refused.
|
Package planner classifies schema-change statements: for each operation it decides whether PostgreSQL can run it online natively (possibly via a safer idiom it suggests), whether it needs the engine's copy-and-swap path, or whether it is refused. |
|
preflight
Package preflight verifies preconditions before the engine writes anything (invariant ST-6).
|
Package preflight verifies preconditions before the engine writes anything (invariant ST-6). |
|
progress
Package progress defines the strategy-wide, machine-readable execution progress contract.
|
Package progress defines the strategy-wide, machine-readable execution progress contract. |
|
router
Package router assigns every classified statement to an execution backend.
|
Package router assigns every classified statement to an execution backend. |
|
schemadiff
Package schemadiff builds the canonical table model both front-ends share and diffs two models into an ordered statement list.
|
Package schemadiff builds the canonical table model both front-ends share and diffs two models into an ordered statement list. |
|
statement
Package statement parses SQL through the real PostgreSQL grammar (wasilibs/go-pgquery, Wasm libpg_query) and reports the facts the engine's front door needs.
|
Package statement parses SQL through the real PostgreSQL grammar (wasilibs/go-pgquery, Wasm libpg_query) and reports the facts the engine's front door needs. |
|
suggest
Package suggest is the advisory surface: it maps DDL that is risky as written to the safer native form the engine would run instead, offline and without executing anything.
|
Package suggest is the advisory surface: it maps DDL that is risky as written to the safer native form the engine would run instead, offline and without executing anything. |
|
verdict
Package verdict is the engine's structured outcome contract: every migrate invocation ends in exactly one verdict — executed natively, refused with a typed reason and, where one exists, a safer native idiom, or failed during execution with the executor's stable outcome code and a disclosure of what committed before the failure.
|
Package verdict is the engine's structured outcome contract: every migrate invocation ends in exactly one verdict — executed natively, refused with a typed reason and, where one exists, a safer native idiom, or failed during execution with the executor's stable outcome code and a disclosure of what committed before the failure. |