Note
pgplex: The Postgres Toolchain for Humans and Agents - pgconsole · pgschema · pgtui · pgparser
Brought to you by Bytebase, open-source database governance platform.
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. Config data, the rows of lookup tables, settings, and feature flags, can be managed the same way, so a release ships its rows together with its schema.
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 config data (lookup tables, settings, feature flags) versioned as CSV and applied with the schema
- 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
| pgschema | Flyway / Liquibase | Atlas | |
|---|---|---|---|
| Pricing | Free and open source (Apache 2.0) | Free tier; advanced features paid | Free tier; advanced features paid |
| Workflow | State-based (desired state) | Migration-file-based | State-based |
| Database support | PostgreSQL only | Multi-database | Multi-database |
| Postgres-specific objects | Auto-detected and diffed (RLS, partitioning, triggers, …) | You write the migration SQL manually | Auto-detected and diffed |
| Shadow database | Not required | Not required | Required by default |
| Migration history table | Not required | Required | Not required |
| Config data (lookup tables, settings) | Rows diffed by primary key, CSV files as source of truth | Manual DML in migration files | Supported |
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:
| Object | Key Features |
|---|---|
| Tables | Columns, identity/generated columns, partitioning (RANGE/LIST/HASH), LIKE clauses, inline and table-level constraints |
| Constraints | Primary keys, foreign keys (with ON DELETE/ON UPDATE), unique, check, NOT VALID, DEFERRABLE |
| Indexes | Regular, UNIQUE, partial, functional/expression; all methods (btree, hash, gist, spgist, gin, brin); CONCURRENTLY |
| Views | CREATE OR REPLACE, dependency-ordered migrations |
| Materialized Views | WITH [NO] DATA, indexes on materialized views |
| Functions | IN/OUT/INOUT parameters with defaults, SETOF/TABLE return types, SECURITY DEFINER, IMMUTABLE/STABLE/VOLATILE, STRICT |
| Procedures | IN/OUT/INOUT parameters with defaults, all procedural languages |
| Aggregates | SFUNC/STYPE/FINALFUNC/INITCOND, COMBINEFUNC and SERIALFUNC/DESERIALFUNC, moving-aggregate support, SORTOP, PARALLEL, ordered-set/hypothetical-set |
| Triggers | BEFORE/AFTER/INSTEAD OF, INSERT/UPDATE/DELETE/TRUNCATE, ROW/STATEMENT level, WHEN conditions, constraint triggers, REFERENCING OLD/NEW TABLE |
| Sequences | START WITH, INCREMENT BY, MINVALUE/MAXVALUE, CYCLE, CACHE, OWNED BY |
| Types | ENUM (add values in-place), composite types |
| Domains | Base type, DEFAULT, NOT NULL, named and anonymous CHECK constraints |
| Policies | Row-level security (RLS), PERMISSIVE/RESTRICTIVE, ALL/SELECT/INSERT/UPDATE/DELETE commands, USING/WITH CHECK expressions, ENABLE/DISABLE/FORCE ROW LEVEL SECURITY |
| Privileges | GRANT/REVOKE for tables (including column-level), sequences, functions, procedures, types/domains; WITH GRANT OPTION |
| Default Privileges | ALTER DEFAULT PRIVILEGES for tables, sequences, functions, types |
| Comments | COMMENT ON for tables, columns, views, materialized views, functions, procedures, aggregates, indexes |
| Config Data | Rows of config tables (lookups, settings, feature flags) listed in pgschema.toml, kept in CSV files loaded by \copy, diffed by primary key into INSERT/UPDATE/DELETE |
See Unsupported for objects that are explicitly out of scope.
Visit https://www.pgschema.com/installation
Note
Windows is not supported. Please use WSL (Windows Subsystem for Linux) or a Linux VM.
nix build
nix runTo pass arguments, use --:
nix run . -- planIf the build fails with a vendorHash mismatch, update nix/pgschema.nix with the hash printed by Nix.
# 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.
