pgmi
Programmable PostgreSQL deployments, controlled entirely by SQL.
pgmi loads your project files into one PostgreSQL session as queryable data, then
runs the deploy.sql you write. Your deployment selects migrations, loads
reference data, tests the changed database — and commits only if everything
passes. A failing test aborts the deployment transaction.
Migration frameworks provide their own ordering, history, and transaction model. pgmi gives those decisions to your deploy.sql. (Architecturally it's an execution fabric, not a migration framework — Why pgmi? explains the distinction.)
See it work
Install pgmi first using one of the commands in Install. Then, if
nothing is running, start a disposable PostgreSQL in Docker (already have one?
skip this block and point PGMI_CONNECTION_STRING at it):
docker run -d --name pgmi-demo -e POSTGRES_PASSWORD=postgres -p 5434:5432 postgres:17-alpine
docker exec pgmi-demo timeout 60 sh -c 'until pg_isready -h 127.0.0.1 -q; do sleep 1; done'
If either command prints an error — say, port 5434 is already in use — stop
here: run docker rm pgmi-demo and pick another port, in both -p and the
connection string below. Otherwise the deploy reaches whatever server holds it.
export PGMI_CONNECTION_STRING="postgresql://postgres:[email protected]:5434/postgres"
# PowerShell: $env:PGMI_CONNECTION_STRING = "postgresql://postgres:[email protected]:5434/postgres"
pgmi init demo --template basic
pgmi deploy demo -d demo_db
Database "demo_db" does not exist; creating
Preparing session: scanning files, loading parameters
Loaded 7 files
Loaded 1 parameter(s)
Executing deploy.sql
[development] Deploying demo v1.0.0 (2 migration file(s))
Dev seed: admin user ready ([email protected] id=1)
[pgmi] Test suite started
[pgmi] Fixture: ./__test__/_setup.sql
[pgmi] Test: ./__test__/test_user_crud.sql
[pgmi] Test suite passed
___ ___ _ _ ___
| \ / _ \| \| | __|
| |) | (_) | .` | _|
|___/ \___/|_|\_|___|
✓ demo_db: 7 files loaded in 0.91s
Now the failure case: add a migration creating an audit_log table, and a test asserting it contains a deploy event (it won't — nothing inserts one):
-- migrations/003_audit_log.sql
CREATE TABLE audit_log (
event text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
-- __test__/test_audit_log.sql
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM audit_log WHERE event = 'deploy') THEN
RAISE EXCEPTION 'audit_log must contain a deploy event';
END IF;
END $$;
[pgmi] Test suite started
[pgmi] Test: ./__test__/test_audit_log.sql
✗ demo_db: failed after 0.72s
pgmi: error: execution failed: ERROR: Failed in ./__test__/test_audit_log.sql: audit_log must contain a deploy event (SQLSTATE P0001)
pgmi exits with code 13, the transaction aborts, and the audit_log table from
the new migration does not exist. Tests run inside the deployment transaction
(each isolated in its own savepoint), and only a deployment whose tests pass
commits. PostgreSQL does not roll back sequence advances or effects outside the
transaction.
A complete, CI-verified version of this pattern lives in examples/test-gated-deploy/ — both paths run on every push.
Requirements: PostgreSQL 11+ (advanced template 15+ — compatibility matrix) over a direct connection or session-mode pooler. Transaction-mode poolers (PgBouncer txn mode, RDS Proxy) reassign connections between statements and destroy the session temp tables pgmi depends on — details.
The entire model in one file
deploy.sql is plain PostgreSQL. Your files are rows in a session view; you query them and decide what to execute:
-- deploy.sql
BEGIN;
DO $$
DECLARE v_file RECORD;
BEGIN
FOR v_file IN (
SELECT path, content FROM pg_temp.pgmi_source_view
WHERE is_sql_file
ORDER BY path
) LOOP
RAISE NOTICE 'Executing: %', v_file.path;
EXECUTE v_file.content;
END LOOP;
END $$;
CALL pgmi_test();
COMMIT;
Filter by directory, branch on a --param, skip files whose checksum already ran, load JSON/XML/CSV reference data with PostgreSQL's built-in functions — it's your SQL. See the deploy.sql Guide for patterns and the Session API for the views (pgmi_source_view for raw path-ordered access, pgmi_plan_view for metadata-driven ordering).
Why it feels different:
- Your SQL owns the deploy — transactions, execution order, idempotency, and retries live in
deploy.sql, not in the tool. - The CLI is infrastructure-only — connections, parameters, auth. No
--dry-run, no--rollback, no orchestration flags to learn. - Atomic head, psql tail — everything before your first top-level
COMMITis one transaction; everything after it autocommits statement-by-statement, soCREATE INDEX CONCURRENTLYworks inside a deploy. See the execution contract. - Built for coding agents — the binary embeds machine-readable guidance (
pgmi ai, llms.txt style) and can expose pgmi commands to agents over MCP (pgmi serve). The docs site also publishes anllms.txtmap for agents browsing the web.
Install
macOS / Linux:
curl -sSL https://raw.githubusercontent.com/vvka-141/pgmi/main/scripts/install.sh | bash
It installs to /usr/local/bin. Without root or sudo, choose a directory you own:
curl -sSL https://raw.githubusercontent.com/vvka-141/pgmi/main/scripts/install.sh | INSTALL_DIR="$HOME/.local/bin" bash
Windows (PowerShell):
irm https://raw.githubusercontent.com/vvka-141/pgmi/main/scripts/install.ps1 | iex
Prefer a package manager or a checksum-verified binary (recommended for CI and production):
Homebrew (macOS):
brew install --cask vvka-141/pgmi/pgmi
Debian/Ubuntu (APT, GPG-verified):
curl -1sLf 'https://dl.cloudsmith.io/public/vvka-141/pgmi/setup.deb.sh' | sudo bash
sudo apt update && sudo apt install pgmi
Direct download: grab an archive from GitHub Releases and verify it against the published checksums.txt.
From source (requires the Go toolchain):
go install github.com/vvka-141/pgmi/cmd/pgmi@latest
Then follow the Getting Started Guide for a complete walkthrough, or the CI/CD Guide to deploy from a pipeline.
Choose your path
- Just deploying SQL? → the basic template: a small, explicit migration scaffold.
pgmi init myapp --template basic - Building a PostgreSQL-backed application? → the advanced template: ~19k lines of tested SQL scaffolded into your project as code you own. One handler registry drives REST routing, JSON-RPC dispatch, MCP tools, and a live OpenAPI 3.1 document; per-route transaction policy resolves isolation and read-only before
BEGIN; multi-tenant membership with RLS; API key lifecycle; audit trails on both the deployment and request planes. It is more infrastructure, not a higher safety tier — full capability tour. Privilege requirements: Production Guide. - Evaluating the approach first? → read Why pgmi? and the honest Tradeoffs.
Either template can be adapted for production; advanced provides more infrastructure, not a higher safety tier. The Choosing a template section has a side-by-side decision table; pgmi templates list shows what's available.
When pgmi makes sense — and when it doesn't
A good fit when you need:
- Conditional deployment logic — different behavior per environment, feature flags, custom phases
- Test-gated deployments — database tests inside the deployment transaction, rollback on failure (Testing Guide)
- Explicit transaction control — you decide where
BEGINandCOMMITgo - Data files alongside schema — JSON config, XML reference data, CSV seeds in the same transaction as migrations
- Multi-cloud PostgreSQL targets — the same
deploy.sqlworks on Azure, AWS, GCP with native auth
A poor fit when:
- Your team avoids SQL/PL/pgSQL — pgmi's power is writing deployment logic in PostgreSQL's language; without that fluency the advantage disappears
- You want tool-managed migration history — pgmi ships no version table; tracking state is a pattern you implement (or take from the advanced template), not a built-in
- Your connection path is a transaction-mode pooler — session temp tables can't survive it
Tradeoffs is the full honest list.
Documentation
📖 Browse the full docs as a searchable site at https://vvka-141.github.io/pgmi/ (built from this docs/ directory). The links below open the same pages on GitHub.
Start here
Why pgmi exists (deep dives)
Reference
AI assistant support
pgmi embeds machine-readable documentation directly in the binary, so coding agents can learn pgmi's conventions on demand:
pgmi ai # Overview for AI assistants (llms.txt style)
pgmi ai skills # List embedded skills
pgmi ai skill pgmi-sql # Print one skill's full content
pgmi ai contract # Print the session API contract (views/functions)
pgmi ai setup # Materialize a discoverable skill into the project
pgmi ai check # Report whether that skill exists and is current
pgmi serve additionally exposes pgmi commands as MCP tools over stdio — see the CLI reference. (The advanced template's MCP gateway is a separate surface: it exposes your deployed application to AI assistants.)
Configuration and authentication
Store connection defaults and parameters in pgmi.yaml next to deploy.sql for zero-flag deployments (pgmi deploy .) — see Configuration.
Beyond standard PostgreSQL auth (connection strings, PGPASSWORD, .pgpass), pgmi authenticates natively to Azure Database for PostgreSQL (Entra ID), Amazon RDS (IAM), and Google Cloud SQL (IAM) — passwordless, using each cloud's credential chain. Commands and setup: Connections.
Contributing
Contributions welcome. See CONTRIBUTING.md for development guidelines, SUPPORT.md for questions and issue routing, and the Code of Conduct for community standards.
License
Mozilla Public License 2.0. Template code in internal/scaffold/templates/ is MIT licensed—code you generate is yours.
Copyright 2024-2026 Alexey Evlampiev