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? point PGMI_CONNECTION_STRING at it and skip the first line):

docker run -d --name pgmi-demo -e POSTGRES_PASSWORD=postgres -p 5434:5432 postgres:17-alpine
export PGMI_CONNECTION_STRING="postgresql://postgres:postgres@127.0.0.1:5434/postgres"
# PowerShell: $env:PGMI_CONNECTION_STRING = "postgresql://postgres:postgres@127.0.0.1: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 parameters
Executing deploy.sql
[development] Deploying demo v1.0.0 (5 file(s) in project)
Dev seed: admin user ready (admin@example.com id=1)
[pgmi] Test suite started
[pgmi] Fixture: ./__test__/_setup.sql
[pgmi] Test: ./__test__/test_user_crud.sql
[pgmi] Test suite completed (3 steps)

  ___   ___  _  _ ___
 |   \ / _ \| \| | __|
 | |) | (_) | .` | _|
 |___/ \___/|_|\_|___|

✓ demo_db: 7 files loaded, 1 test macro(s) expanded 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):

The two files behind the failure demo

-- 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 COMMIT is one transaction; everything after it autocommits statement-by-statement, so CREATE INDEX CONCURRENTLY works 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 an llms.txt map for agents browsing the web.

Install

macOS / Linux:

curl -sSL https://raw.githubusercontent.com/vvka-141/pgmi/main/scripts/install.sh | 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 BEGIN and COMMIT go
• Data files alongside schema — JSON config, XML reference data, CSV seeds in the same transaction as migrations
• Multi-cloud PostgreSQL targets — the same deploy.sql works 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

Guide	Description
Getting Started	Your first deployment in 10 minutes (binary-first)
deploy.sql Guide	Authoring patterns: data ingestion, environment branching, multi-phase
Testing	Database tests with savepoint isolation and deploy gates

Why pgmi exists (deep dives)

Essay	Description
Why pgmi?	When pgmi's approach makes sense
Highlights	Ten distinctive pgmi capabilities, grounded in code and guides
Tradeoffs	Honest limitations and who should use pgmi
Coming from Flyway/Liquibase/Sqitch	Migration guides

Reference

Guide	Description
CLI Reference	All commands, flags, exit codes
Configuration	pgmi.yaml reference and zero-flag deployments
Session API	Temp tables and helper functions
Connections	Connection architecture: cloud auth, SSL, poolers, IaC
Metadata	Optional script tracking and ordering
Security	Secrets and CI/CD patterns
CI/CD	Deploy from GitHub Actions and other pipelines
Production Guide	Performance, rollback, monitoring, compatibility matrix
Advanced template	~19k lines you own: one handler registry → REST+RPC+MCP+OpenAPI, per-route transaction policy, RLS auth, API keys, audit trails, inbound queue
Advanced-template MCP gateway	Expose your deployed application to AI assistants

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