gem-hunter

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:

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.

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.

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.

• 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

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.

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.

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

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.

• Docs
• GitHub issues

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

# 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
 );

$ 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;

# 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!

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.

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

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

# Run unit tests only
go test -short -v ./...

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

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