# pgplex/pgschema architecture

> Architecture diagram of the GitHub repository pgplex/pgschema, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-06-17). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/pgplex/pgschema
- Repository: https://github.com/pgplex/pgschema

## Overview

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: [`cmd/plan/plan.go`](https://github.com/pgplex/pgschema/blob/main/cmd/plan/plan.go) (plan), [`cmd/root.go`](https://github.com/pgplex/pgschema/blob/main/cmd/root.go) (root command), [`internal/postgres/external.go`](https://github.com/pgplex/pgschema/blob/main/internal/postgres/external.go) (external DB), [`cmd/dump/dump.go`](https://github.com/pgplex/pgschema/blob/main/cmd/dump/dump.go) (dump)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_cli["CLI"]
  node_main["main.go<br/>entrypoint<br/>[main.go]"]
  node_cmd_root["root command<br/>cli root<br/>[root.go]"]
  node_cmd_dump["dump<br/>command<br/>[dump.go]"]
  node_cmd_plan["plan<br/>command<br/>[plan.go]"]
  node_cmd_apply["apply<br/>command<br/>[apply.go]"]
end

subgraph group_core["Core model"]
  node_include["include<br/>schema composer<br/>[processor.go]"]
  node_ir["IR model<br/>normalized schema<br/>[ir.go]"]
  node_parser["SQL parser<br/>grammar+lexer<br/>[gram.y]"]
end

subgraph group_db["Postgres boundary"]
  node_postgres_ext[("external DB<br/>postgres conn<br/>[external.go]")]
  node_postgres_emb[("embedded DB<br/>postgres sandbox<br/>[embedded.go]")]
  node_queries["catalog queries<br/>sqlc catalog<br/>[queries.sql.go]"]
end

subgraph group_change["Change engine"]
  node_diff{{"diff engine<br/>object diff<br/>[diff.go]"}}
  node_topo["ordering<br/>dependency sort<br/>[topological.go]"]
  node_plan["planner<br/>execution plan<br/>[plan.go]"]
  node_rewrite["rewrite<br/>[rewrite.go]"]
  node_fingerprint(("fingerprint<br/>drift check<br/>[fingerprint.go]"))
end

subgraph group_support["Support"]
  node_cmd_util["cmd util<br/>cli helpers"]
  node_dumpfmt["dump formatter<br/>sql formatter<br/>[formatter.go]"]
  node_logger["logger<br/>logging<br/>[logger.go]"]
  node_color["color<br/>cli styling<br/>[color.go]"]
end

subgraph group_fixtures["Fixtures"]
  node_dump_fixtures["dump fixtures<br/>golden data"]
  node_diff_fixtures["diff fixtures<br/>golden data"]
end

subgraph group_docs["Packaging"]
  node_docs["docs<br/>[README.md]"]
end

node_main -->|"starts"| node_cmd_root
node_cmd_root -->|"exposes"| node_cmd_dump
node_cmd_root -->|"exposes"| node_cmd_plan
node_cmd_root -->|"exposes"| node_cmd_apply
node_cmd_root -->|"shares"| node_cmd_util
node_cmd_dump -->|"reads"| node_postgres_ext
node_cmd_dump -->|"formats"| node_dumpfmt
node_cmd_plan -->|"loads"| node_include
node_cmd_plan -->|"reads"| node_postgres_ext
node_cmd_plan -->|"computes"| node_diff
node_cmd_plan -->|"produces"| node_plan
node_cmd_apply -->|"consumes"| node_plan
node_cmd_apply -->|"verifies"| node_fingerprint
node_cmd_apply -->|"writes"| node_postgres_ext
node_include -->|"builds"| node_ir
node_parser -->|"parses into"| node_ir
node_postgres_ext -->|"uses"| node_queries
node_postgres_emb -->|"uses"| node_queries
node_queries -->|"maps to"| node_ir
node_ir -->|"feeds"| node_diff
node_postgres_ext -->|"feeds"| node_diff
node_diff -->|"orders"| node_topo
node_topo -->|"feeds"| node_plan
node_plan -->|"rewrites"| node_rewrite
node_plan -.->|"validates"| node_postgres_emb
node_fingerprint -->|"checks"| node_postgres_ext
node_cmd_dump -->|"logs"| node_logger
node_cmd_plan -->|"renders"| node_color
node_dump_fixtures -.->|"tests"| node_cmd_dump
node_diff_fixtures -.->|"tests"| node_diff
node_docs -.->|"documents"| node_cmd_root

click node_main "https://github.com/pgplex/pgschema/blob/main/main.go"
click node_cmd_root "https://github.com/pgplex/pgschema/blob/main/cmd/root.go"
click node_cmd_dump "https://github.com/pgplex/pgschema/blob/main/cmd/dump/dump.go"
click node_cmd_plan "https://github.com/pgplex/pgschema/blob/main/cmd/plan/plan.go"
click node_cmd_apply "https://github.com/pgplex/pgschema/blob/main/cmd/apply/apply.go"
click node_cmd_util "https://github.com/pgplex/pgschema/tree/main/cmd/util"
click node_include "https://github.com/pgplex/pgschema/blob/main/internal/include/processor.go"
click node_ir "https://github.com/pgplex/pgschema/blob/main/ir/ir.go"
click node_parser "https://github.com/pgplex/pgschema/blob/main/internal/gram.y"
click node_postgres_ext "https://github.com/pgplex/pgschema/blob/main/internal/postgres/external.go"
click node_postgres_emb "https://github.com/pgplex/pgschema/blob/main/internal/postgres/embedded.go"
click node_queries "https://github.com/pgplex/pgschema/blob/main/ir/queries/queries.sql.go"
click node_dumpfmt "https://github.com/pgplex/pgschema/blob/main/internal/dump/formatter.go"
click node_diff "https://github.com/pgplex/pgschema/blob/main/internal/diff/diff.go"
click node_topo "https://github.com/pgplex/pgschema/blob/main/internal/diff/topological.go"
click node_plan "https://github.com/pgplex/pgschema/blob/main/internal/plan/plan.go"
click node_rewrite "https://github.com/pgplex/pgschema/blob/main/internal/plan/rewrite.go"
click node_fingerprint "https://github.com/pgplex/pgschema/blob/main/internal/fingerprint/fingerprint.go"
click node_logger "https://github.com/pgplex/pgschema/blob/main/internal/logger/logger.go"
click node_color "https://github.com/pgplex/pgschema/blob/main/internal/color/color.go"
click node_dump_fixtures "https://github.com/pgplex/pgschema/tree/main/testdata/dump"
click node_diff_fixtures "https://github.com/pgplex/pgschema/tree/main/testdata/diff"
click node_docs "https://github.com/pgplex/pgschema/blob/main/docs/README.md"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_main,node_cmd_root,node_cmd_dump,node_cmd_plan,node_cmd_apply,node_docs toneBlue
class node_include,node_ir,node_parser toneAmber
class node_postgres_ext,node_postgres_emb,node_queries toneMint
class node_diff,node_topo,node_plan,node_rewrite,node_fingerprint toneRose
class node_cmd_util,node_dumpfmt,node_logger,node_color toneIndigo
class node_dump_fixtures,node_diff_fixtures toneTeal
```

## Components

### CLI

Executable entrypoint and user-facing commands

- [main.go](https://github.com/pgplex/pgschema/blob/main/main.go): entrypoint · CLI binary entrypoint · `main.go`
- [root command](https://github.com/pgplex/pgschema/blob/main/cmd/root.go): cli root · Wires subcommands and shared flags · `cmd/root.go`
- [dump](https://github.com/pgplex/pgschema/blob/main/cmd/dump/dump.go): command · Exports live schema to SQL · `cmd/dump/dump.go`
- [plan](https://github.com/pgplex/pgschema/blob/main/cmd/plan/plan.go): command · Builds a migration plan from desired state · `cmd/plan/plan.go`
- [apply](https://github.com/pgplex/pgschema/blob/main/cmd/apply/apply.go): command · Executes a reviewed plan against Postgres · `cmd/apply/apply.go`

### Core model

Schema parsing, composition, and normalization

- [include](https://github.com/pgplex/pgschema/blob/main/internal/include/processor.go): schema composer · Assembles modular SQL files into one desired state · `internal/include/processor.go`
- [IR model](https://github.com/pgplex/pgschema/blob/main/ir/ir.go): normalized schema · In-memory schema representation with quoting and ignore rules · `ir/ir.go`
- [SQL parser](https://github.com/pgplex/pgschema/blob/main/internal/gram.y): grammar+lexer · Custom PostgreSQL DDL grammar and lexer · `internal/gram.y`

### Postgres boundary

Live database access and catalog introspection

- [external DB](https://github.com/pgplex/pgschema/blob/main/internal/postgres/external.go): postgres conn · Connects to the target live database · `internal/postgres/external.go`
- [embedded DB](https://github.com/pgplex/pgschema/blob/main/internal/postgres/embedded.go): postgres sandbox · Isolated Postgres instance for validation and planning · `internal/postgres/embedded.go`
- [catalog queries](https://github.com/pgplex/pgschema/blob/main/ir/queries/queries.sql.go): sqlc catalog · Generated catalog queries for introspection · `ir/queries/queries.sql.go`

### Change engine

Diffing, ordering, planning, and execution safety

- [diff engine](https://github.com/pgplex/pgschema/blob/main/internal/diff/diff.go): object diff · Compares desired and current schema objects · `internal/diff/diff.go`
- [ordering](https://github.com/pgplex/pgschema/blob/main/internal/diff/topological.go): dependency sort · Computes dependency order for schema changes · `internal/diff/topological.go`
- [planner](https://github.com/pgplex/pgschema/blob/main/internal/plan/plan.go): execution plan · Rewrites diff into executable plan output · `internal/plan/plan.go`
- [rewrite](https://github.com/pgplex/pgschema/blob/main/internal/plan/rewrite.go): ddl rewrite · Adjusts DDL for transactional and Postgres constraints · `internal/plan/rewrite.go`
- [fingerprint](https://github.com/pgplex/pgschema/blob/main/internal/fingerprint/fingerprint.go): drift check · Compares expected and live schema state · `internal/fingerprint/fingerprint.go`

### Support

Logging, formatting, and CLI utilities

- [cmd util](https://github.com/pgplex/pgschema/tree/main/cmd/util): cli helpers · Connection, env, ignore, and SQL logging helpers · `cmd/util`
- [dump formatter](https://github.com/pgplex/pgschema/blob/main/internal/dump/formatter.go): sql formatter · Formats catalog state back into schema SQL · `internal/dump/formatter.go`
- [logger](https://github.com/pgplex/pgschema/blob/main/internal/logger/logger.go): logging · Structured logging support · `internal/logger/logger.go`
- [color](https://github.com/pgplex/pgschema/blob/main/internal/color/color.go): cli styling · Terminal output coloring · `internal/color/color.go`

### Fixtures

Golden inputs and outputs used by tests

- [dump fixtures](https://github.com/pgplex/pgschema/tree/main/testdata/dump): golden data · Expected dump outputs and catalog samples · `testdata/dump`
- [diff fixtures](https://github.com/pgplex/pgschema/tree/main/testdata/diff): golden data · Expected plans and diffs for schema changes · `testdata/diff`

### Packaging

Documentation and distribution tooling

- [docs](https://github.com/pgplex/pgschema/blob/main/docs/README.md): docs · Workflow and command documentation · `docs/README.md`

## Connections

- 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