How pgplex/pgschema works
1. What this repository builds
- pgschema is a single Go CLI for PostgreSQL declarative schema management.
- The core workflow is: dump current DB schema → edit declarative SQL files → plan diff → apply migration.
2. Top-level runtime boundary
- The executable entrypoint is main.go, which delegates into cmd/root.go.
- The CLI is split into three primary user-facing commands:
- dump for exporting schema from a live database
- plan for generating a migration plan from desired-state SQL
- apply for executing a reviewed plan against the database
3. Desired-state input layer
- The repository treats schema as a set of SQL source files, not migration scripts.
- The internal/include package processes modular schema composition, so a schema can be split across files and assembled into a full desired state.
- The ir package is the normalized in-memory schema model used after parsing; it handles identifiers, quoting, ignore rules, and schema normalization.
4. SQL parsing and schema understanding
- internal/gram.y and internal/scan.l indicate a custom SQL grammar/lexer for understanding PostgreSQL DDL.
- This is what lets the tool recognize PostgreSQL-specific objects such as tables, views, functions, procedures, triggers, policies, privileges, domains, and aggregates rather than treating SQL as opaque text.
5. Current-state introspection from Postgres
- internal/postgres is the database boundary.
- It has two modes:
- external.go for connecting to the real target database
- embedded.go for the built-in isolated Postgres instance used during validation / planning
- ir/queries and sqlc-generated code are used to query catalog metadata and build the current database state.
6. Diff engine
- internal/diff is the central comparison subsystem.
- It contains object-specific diff logic for tables, columns, constraints, indexes, views, materialized views, triggers, types, functions, procedures, sequences, policies, privileges, and default privileges.
- This layer converts “desired state vs current state” into an ordered set of schema changes, not just line-based SQL differences.
7. Dependency and ordering logic
- Schema changes are not emitted in arbitrary order.
- internal/diff/topological.go and related dependency tests show that the system computes object dependency order before generating DDL.
- This matters for cases like views depending on other views, functions referenced by policies, foreign keys, triggers, and other cross-object links.
8. Plan generation
- internal/plan turns the diff into an execution plan.
- It produces both human-readable and JSON representations, which are reflected in cmd/plan and the testdata/diff/**/plan.* fixtures.
- The plan layer also rewrites DDL where needed so the output is executable and respects Postgres constraints like transactional safety, online DDL patterns, and object replacement rules.
9. Apply path
- cmd/apply consumes the generated plan and executes it against the target database.
- The README calls out concurrent change detection, transaction-adaptive execution, and lock timeout control, so apply is not a blind SQL runner; it validates that the database has not drifted and then applies changes carefully.
10. Dump path
- cmd/dump and internal/dump export a live Postgres schema into SQL form.
- This is the reverse data flow of planning: catalog state is read from the database and formatted into schema files that can later become the desired-state input.
11. Validation and drift protection
- internal/fingerprint provides schema fingerprinting / comparison used for detecting concurrent changes or drift.
- This is an important architectural boundary: apply is protected by comparing the plan’s expected state against the live database state before execution.
12. Output, UX, and observability
- internal/color and internal/logger are supporting layers for CLI presentation and logging.
- cmd/util centralizes connection handling, environment resolution, ignore loading, and SQL logging so the command implementations stay focused on command behavior.
13. Test and fixture architecture
- The repo is heavily fixture-driven.
- testdata/dump and testdata/diff contain golden schemas, raw inputs, and expected outputs for many Postgres edge cases and bug regressions.
- Integration tests under cmd/*_integration_test.go validate end-to-end behavior against real Postgres, while unit tests cover parser, diff, normalization, and utility layers.
14. Build, release, and deployment tooling
- CI is handled through GitHub Actions, with workflows for tests, Docker image publishing, and releases.
- The repository also includes Nix packaging (flake.nix, default.nix, nix/pgschema.nix) and a Dockerfile, so the CLI can be built and distributed reproducibly across environments.
15. Documentation as part of the product surface
- The docs/ tree is extensive and documents syntax, workflows, CLI commands, and supported PostgreSQL objects.
- For architecture purposes, this is not runtime logic, but it does define the supported feature boundaries and expected user workflow for the tool.
Main entry points: plan.go plan · root.go root command · external.go external DB · dump.go dump
Components 23
CLI
Executable entrypoint and user-facing commands
- main.go · CLI binary entrypoint
main.go - root command · Wires subcommands and shared flags
cmd/root.go - dump · Exports live schema to SQL
cmd/dump/dump.go - plan · Builds a migration plan from desired state
cmd/plan/plan.go - apply · Executes a reviewed plan against Postgres
cmd/apply/apply.go
Core model
Schema parsing, composition, and normalization
- include · Assembles modular SQL files into one desired state
internal/include/processor.go - IR model · In-memory schema representation with quoting and ignore rules
ir/ir.go - SQL parser · Custom PostgreSQL DDL grammar and lexer
internal/gram.y
Postgres boundary
Live database access and catalog introspection
- external DB · Connects to the target live database
internal/postgres/external.go - embedded DB · Isolated Postgres instance for validation and planning
internal/postgres/embedded.go - catalog queries · Generated catalog queries for introspection
ir/queries/queries.sql.go
Change engine
Diffing, ordering, planning, and execution safety
- diff engine · Compares desired and current schema objects
internal/diff/diff.go - ordering · Computes dependency order for schema changes
internal/diff/topological.go - planner · Rewrites diff into executable plan output
internal/plan/plan.go - rewrite · Adjusts DDL for transactional and Postgres constraints
internal/plan/rewrite.go - fingerprint · Compares expected and live schema state
internal/fingerprint/fingerprint.go
Support
Logging, formatting, and CLI utilities
- cmd util · Connection, env, ignore, and SQL logging helpers
cmd/util - dump formatter · Formats catalog state back into schema SQL
internal/dump/formatter.go - logger · Structured logging support
internal/logger/logger.go - color · Terminal output coloring
internal/color/color.go
Fixtures
Golden inputs and outputs used by tests
- dump fixtures · Expected dump outputs and catalog samples
testdata/dump - diff fixtures · Expected plans and diffs for schema changes
testdata/diff
Packaging
Documentation and distribution tooling
- docs · Workflow and command documentation
docs/README.md
Connections 31
- main.go → root command: starts · Binary hands control to the root command
- root command → dump: exposes · Registers the dump subcommand
- root command → plan: exposes · Registers the plan subcommand
- root command → apply: exposes · Registers the apply subcommand
- root command → cmd util: shares · Centralizes common CLI plumbing
- dump → external DB: reads · Connects to live Postgres for schema export
- dump → dump formatter: formats · Renders catalog state as SQL files
- plan → include: loads · Composes modular desired-state SQL
- plan → external DB: reads · Inspects the current target database
- plan → diff engine: computes · Builds schema changes from desired vs current state
- plan → planner: produces · Turns diffs into executable plan output
- apply → planner: consumes · Executes the reviewed plan representation
- apply → fingerprint: verifies · Checks for drift or concurrent changes before applying
- apply → external DB: writes · Applies changes to the live database
- include → IR model: builds · Converts multi-file SQL into normalized schema input
- SQL parser → IR model: parses into · Creates the in-memory schema model from DDL
- external DB → catalog queries: uses · Reads catalog metadata via generated queries
- embedded DB → catalog queries: uses · Uses the same catalog inspection layer in sandbox mode
- catalog queries → IR model: maps to · Shapes catalog rows into the schema model
- IR model → diff engine: feeds · Provides normalized desired-state objects to the diff engine
- external DB → diff engine: feeds · Provides current-state objects to compare against desired state
- diff engine → ordering: orders · Sorts changes by dependency before emission
- ordering → planner: feeds · Supplies ordered operations to the planner
- planner → rewrite: rewrites · Makes DDL executable and constraint-safe
- planner → embedded DB: validates · Uses sandbox Postgres for planning/validation paths
- fingerprint → external DB: checks · Compares live state against expected state
- dump → logger: logs · Emits command progress and diagnostics
- plan → color: renders · Formats human-readable CLI output
- dump fixtures → dump: tests · Golden dump behavior coverage
- diff fixtures → diff engine: tests · Golden diff and plan coverage
- docs → root command: documents · Describes the supported CLI workflow and commands