pgy

Forward-only PostgreSQL migration tool. It reads YAML schema files, diffs against a live DB, writes SQL to a buffer, and commits/applies migrations. No rollbacks.

Install

Prebuilt binaries

Download the archive for your platform from the latest release (Linux/macOS amd64+arm64, Windows amd64), verify against checksums.txt if desired, and put pgy on your PATH:

# example: v0.1.0 on Linux amd64
curl -LO https://github.com/suprbdev/pgy/releases/download/v0.1.0/pgy_0.1.0_linux_amd64.tar.gz
tar xzf pgy_0.1.0_linux_amd64.tar.gz
sudo install -m 0755 pgy /usr/local/bin/pgy
pgy version

go install

go install github.com/suprbdev/pgy/cmd/pgy@latest

(Note: go install builds report version 0.1.0 without the release stamping.)

Build from source

make build     # builds bin/pgy with version ldflags
make test      # runs unit tests
make clean     # cleans bin/ and buffer file
make install   # installs to $HOME/go/bin if present, else PREFIX/bin (/usr/local/bin)

The binary is placed at bin/pgy.

Configuration

Configuration precedence: flags > env > config file > defaults.

• Common flags and mirrored env vars:

  • --config / PGY_CONFIG (config file path; default lookup: pgy.yaml, pgy.yml, then legacy .pgy.yaml, .pgy.yml)
  • --dsn / PGY_DSN (PostgreSQL DSN)
  • --schema-root / PGY_SCHEMA_ROOT (root for YAML files)
  • --schemas / PGY_SCHEMAS (comma-separated YAML files, relative to schema-root unless absolute; a missing file is an error)
  • --migrations-dir / PGY_MIGRATIONS_DIR (default: ./migrations)
  • --buffer / PGY_BUFFER (default: ./.pgy.buffer.sql)
  • --quiet / PGY_QUIET=1
  • --verbose / PGY_VERBOSE=1
  • --json / PGY_JSON=1

• Optional pgy.yaml (in project root; pgy.yml and legacy .pgy.yaml/.pgy.yml also work):

dsn: postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable
schema_root: ./schemas
schemas: ["base.yml", "ext.yml"]
migrations_dir: ./migrations
buffer: ./.pgy.buffer.sql
quiet: false
verbose: false
json: false

If --schemas/PGY_SCHEMAS/config are not set, pgy auto-discovers all .yml/.yaml files under schema-root.

Commands (basics)

• Init migrations table (idempotent):

pgy init --dsn "$PG_DSN"            # defaults: schema pgy, table migrations

• Generate diff from YAML to DB (writes SQL to buffer):

pgy diff --dsn "$PG_DSN" --schema-root ./schemas --schemas "base.yml,ext.yml"
# exit code 2 when changes are detected

• Inspect or clear buffer:

pgy buffer           # prints buffer SQL
pgy buffer --stat    # size + statement count
pgy buffer --clear   # delete buffer file

• Commit buffer to numbered migration with checksum header:

pgy commit users_and_auth   # creates ./migrations/0001_users_and_auth.sql

• Split a diff into grouped migrations (one per schema file or directory):

pgy diff --from-empty --split          # one group per YAML file (same as --split=file)
pgy diff --from-empty --split=dir      # one group per directory
pgy buffer --stat                      # per-group statement counts
pgy commit init                        # 0001_init_core_user.sql, 0002_init_core_org.sql, ...

See Grouped migrations below.

• Apply pending migrations (each in its own transaction):

pgy migrate --dsn "$PG_DSN"                  # apply all pending (exit 0 on success)
pgy migrate --dsn "$PG_DSN" --dry-run        # show what would run (exit 2 if pending)
pgy migrate --dsn "$PG_DSN" --until 0003     # apply up to migration 0003*
pgy migrate --dsn "$PG_DSN" --limit 1        # apply only one
pgy migrate --dsn "$PG_DSN" --lock-timeout 5s --statement-timeout 30s

• Manually mark applied (requires confirmation):

pgy mark-applied --dsn "$PG_DSN" --force 0003_products
# accepts bare name or full path; inserts missing up to target and removes later ones

• Status summary:

pgy status --dsn "$PG_DSN"   # shows current, last, pending (exit 2 if pending)

Grouped migrations

When prototyping you often wipe migrations/ and regenerate from scratch, which normally produces one huge initial migration. pgy diff --split instead writes the buffer as a series of groups, one per schema file (--split=file, the default) or per directory (--split=dir), and pgy commit turns each group into its own numbered migration. Group names come from the file paths relative to their common directory, so schemas/modules/core/user.yml becomes 0001_modules_core_user.sql (or 0001_init_modules_core_user.sql with pgy commit init).

The buffer marks each group with a -- pgy:group <name> line; pgy commit splits on those markers. A buffer without markers commits to a single file as before.

How groups are ordered and filled:

• Each group is diffed against the state the previous groups leave behind, so a file that only adds columns to another file's table (like the link files in examples/links/) becomes alter table ... add column plus its foreign keys and indexes, in its own migration.
• Groups keep the order of --schemas (or discovery order) unless a dependency forces otherwise. A group always comes after the groups it depends on via dependsOn, partitionOf, sequence ownedBy, column/attribute/domain and function argument/return types, objects named in view queries, SQL-language function bodies and domain checks, and after the file that owns a table it extends (the declaration with the primary key). Groups whose dependencies form a cycle are merged into one migration named a+b.
• Foreign keys (including the unique key they reference), triggers, policies (with the table's rowLevelSecurity switch), indexes, constraints and column defaults that reference something a later group creates are moved into that later group, so circular references between files still work.
• Extensions and roles always go into the first migration.
• With --unsafe, drops are only emitted in the last migration, so a column declared by a later file is never dropped and re-added.
• Against an existing database, only groups with changes produce migrations.

Object references inside SQL text (view queries, function bodies, defaults, checks, policies) are found by name. A name that only looks like a reference can, at worst, reorder or merge groups. Use dependsOn for any cross-file dependency the scan cannot see.

YAML schema (minimal model)

The minimal YAML supported by the initial diff engine:

tables:
  public.users:           # or just "users" (defaults to public)
    columns:
      id:
        type: int
        nullable: false
      email:
        type: text
        nullable: false
    constraints:
      email_unique:
        type: unique
        columns: [email]
      age_check:
        type: check
        expression: "id > 0"  # Example check constraint

Note: In addition to primaryKey, foreignKeys, and indexes, table constraints (such as check, unique, and exclude) are fully supported and will be emitted when creating new tables.

See docs/schema.md for the full YAML reference, and examples/ for a library of composable starter modules (users, orgs, e-commerce, billing, CRM, messaging, and more) plus link files that wire them together.

Diff behavior highlights:

• Creates missing schemas, extensions, tables, types, functions, views, triggers, and more; skips objects already live.
• Adds missing columns to existing tables; alters changed columns and constraints (type/default/nullability changes gated behind --unsafe).
• Drops columns only with --unsafe.

See docs/feature_support.md for the full capability matrix.

Notes

• Forward-only: no down/rollback.
• Advisory locks during init/migrate.
• Checksums added to committed files; verified before applying.
• SQL splitting respects single/double quotes, dollar quotes, and line/block comments. E'...' backslash-escape strings are not handled.