pgschema is a CLI tool that brings Terraform-style declarative schema migration to PostgreSQL. Instead of writing migration files by hand, you declare the desired schema state and pgschema generates the migration plan automatically.

pgschema dump → edit schema.sql → pgschema plan → pgschema apply

• Dump: Extract the current database schema as SQL files
• Plan: Diff your edited schema against the live database, generate migration DDL
• Apply: Execute with concurrent change detection, transaction-adaptive execution, and lock timeout control

See more details in the introduction blog post.

Watch in action:

Why pgschema

State-based, not migration-file-based

Tools like Flyway and Liquibase require you to write and number migration files manually. pgschema works like Terraform: you declare what the schema should look like, and it figures out the SQL to get there. No migration history table, no manual sequencing.

Deep Postgres support

pgschema is Postgres-only, which means it handles Postgres-specific objects that generic tools skip: row-level security policies, partitioned tables, partial indexes, constraint triggers, identity columns, domain types, default privileges, and column-level grants. See the full list below.

No shadow database required

Most state-based tools spin up a temporary "shadow" database to validate migrations. pgschema uses an embedded Postgres instance internally and cleans up after itself — no extra infrastructure needed.

When to choose pgschema

• You want a fully free and open-source tool with no feature gating
• You want to version-control your schema as plain SQL and apply changes declaratively
• You need Postgres-specific features (RLS, partitioning, complex triggers) tracked in migrations
• You want migration validation without provisioning a separate shadow database
• You want a plan/preview step before applying changes, like terraform plan
• You're migrating from a manual SQL workflow and want structure without an ORM

How it compares

Why is it free?

Fair question. We have no current plans to charge for pgschema.

pgschema is sponsored by Bytebase, a commercial database Governance platform. Bytebase covers the needs of teams that require controls beyond schema migration — data access control, data masking, audit logging, and multi-database management across an organization.

Supported Schema Objects

pgschema covers all the schema objects developers use in real-world Postgres applications, across versions 14-18:

See Unsupported for objects that are explicitly out of scope.

Installation

Visit https://www.pgschema.com/installation

Nix (flake)

nix build
nix run

To pass arguments, use --:

nix run . -- plan

If the build fails with a vendorHash mismatch, update nix/pgschema.nix with the hash printed by Nix.

Getting help

• Docs
• GitHub issues

Quick example

Step 1: Dump schema

# Dump current schema
$ PGPASSWORD=testpwd1 pgschema dump \
    --host localhost \
    --db testdb \
    --user postgres \
    --schema public > schema.sql

Step 2: Edit schema

# Edit schema file declaratively
--- a/schema.sql
+++ b/schema.sql
@@ -12,5 +12,6 @@
 CREATE TABLE IF NOT EXISTS users (
     id SERIAL PRIMARY KEY,
-    username varchar(50) NOT NULL UNIQUE
+    username varchar(50) NOT NULL UNIQUE,
+    age INT NOT NULL
 );

Step 3: Generate plan

$ PGPASSWORD=testpwd1 pgschema plan \
    --host localhost \
    --db testdb \
    --user postgres \
    --schema public \
    --file schema.sql \
    --output-human stdout \
    --output-json plan.json
Plan: 1 to modify.
Summary by type:
  tables: 1 to modify
Tables:
  ~ users
    + age (column)
Transaction: true
DDL to be executed:
--------------------------------------------------
ALTER TABLE users ADD COLUMN age integer NOT NULL;

Step 4: Apply plan with confirmation

# Or use --auto-approve to skip confirmation
$ PGPASSWORD=testpwd1 pgschema apply \
    --host localhost \
    --db testdb \
    --user postgres \
    --schema public \
    --plan plan.json
Plan: 1 to modify.
Summary by type:
  tables: 1 to modify
Tables:
  ~ users
    + age (column)
Transaction: true
DDL to be executed:
--------------------------------------------------
ALTER TABLE users ADD COLUMN age integer NOT NULL;
Do you want to apply these changes? (yes/no): yes
Applying changes...
Changes applied successfully!

LLM / AI Integration

pgschema is designed to work well in AI-assisted workflows:

• llms.txt — concise machine-readable summary of pgschema capabilities
• llms-full.txt — full documentation in a single file optimized for LLM context windows

These files follow the llms.txt standard and are suitable for including in agent tool definitions, RAG pipelines, or system prompts when building AI-assisted database tooling.

Development

[!NOTE] > For external contributors: If you require any features, please create a GitHub issue to discuss first instead of creating a PR directly.

Build

git clone https://github.com/pgplex/pgschema.git
cd pgschema
go mod tidy
go build -o pgschema .

Run tests

# Run unit tests only
go test -short -v ./...
# Run all tests including integration tests (uses Postgres testcontainers with Docker)
go test -v ./...

Sponsor

Bytebase - open source, web-based database DevSecOps platform.