pgdesign v0.26.0 /Quickstart
Updated
On this page

Get started with pgdesign: install it, write a TOML schema, generate PostgreSQL DDL, validate rules, audit normal forms, and run the revise workflow.

#Quickstart

pgdesign is a PostgreSQL schema compiler. You define your database schema in TOML, and pgdesign compiles it to SQL DDL with strict enforcement of database design principles.

#Installation

#Go

go install github.com/smm-h/pgdesign/cmd/pgdesign@v0

@v0, not @latest. The Go module proxy has a cached v1.0.0 for this module that was never a real release, and @latest resolves to it. See the phantom v1.0.0 note below.

#npm

npm install pgdesign

#pip

pip install pgdesign

#The phantom v1.0.0

A v1.0.0 tag was pushed to this repository once and immediately removed, but the Go module proxy had already cached it. Proxy entries are permanent and immutable: github.com/smm-h/[email protected] will resolve forever, to code that was never released under that version, and go install ...@latest picks it up in preference to every real release.

It cannot be retracted. A retract directive only takes effect in a version the proxy also serves, so retracting v1.0.0 would mean tagging v1.0.1 -- and this project does not issue 1.x tags. Pin @v0 (or an exact @v0.x.y) and the proxy serves the real releases.

#Creating your first schema

Create a file called schema.toml in your project directory. This TOML file defines your database schema using pgdesign's declarative format, including table definitions with typed columns, foreign key relationships, indexes, and constraints. pgdesign compiles this into PostgreSQL DDL with strict enforcement of naming conventions, NOT NULL defaults, and required ON DELETE clauses on foreign keys.

TM toml
format_version = 1

[meta]
version = 16
schema = "public"

[tables.users]
comment = "User accounts"

[tables.users.columns.id]
type = "id"

[tables.users.columns.email]
type = "email"

[tables.users.columns.display_name]
type = "short_text"

[tables.users.columns.created_at]
type = "timestamp"

[tables.posts]
comment = "User-authored posts"

[tables.posts.columns.id]
type = "id"

[tables.posts.columns.author_id]
type = "ref"

[tables.posts.columns.title]
type = "short_text"

[tables.posts.columns.body]
type = "short_text"

[tables.posts.columns.published]
type = "flag"

[tables.posts.columns.created_at]
type = "timestamp"

[tables.posts.fks.fk_posts_author]
columns = ["author_id"]
ref_table = "users"
ref_columns = ["id"]
on_delete = "CASCADE"

[tables.posts.indexes.idx_posts_author_id]
columns = ["author_id"]

Key design decisions enforced by pgdesign:

  • Every schema document must declare format_version = 1 at the top level (see Format Reference).
  • Every table requires a comment.
  • Columns use semantic types (id, email, timestamp) instead of raw PG types.
  • Every FK must declare on_delete.
  • FK columns should have an index.

#Generating SQL

pgdesign generate schema.toml

This produces the full DDL: CREATE TABLE statements, constraints, indexes, and COMMENT ON statements, all in dependency order.

Add --idempotent for IF NOT EXISTS guards:

pgdesign generate --idempotent schema.toml

Other output formats:

pgdesign generate --format json schema.toml
pgdesign generate --format d2 schema.toml
pgdesign generate --format svg schema.toml

#Validating a schema

pgdesign check --tag validation

The validator checks for errors (missing types, FK targets that don't exist, naming violations) and warnings (god tables, orphan tables, missing timestamps). Exit code is 1 if any errors are found.

Disable specific rules in pgdesign.toml:

TM toml
[validate]
disable = ["W002", "W005"]

#Formatting a schema

pgdesign fmt schema.toml

Formats the TOML file with consistent ordering: tables in dependency order, columns ordered by PK then FK then alphabetical.

Check mode (exit 1 if not formatted, useful in CI):

pgdesign fmt --check schema.toml

Options for ordering:

pgdesign fmt --table-order=alphabetical --column-order=fk_last schema.toml

#Auditing for normal form violations

pgdesign check --tag nf

The NF check examines 1NF, 2NF, and 3NF violations using declared functional dependencies. For tables without dependencies declared, the check is skipped. When a database URL is configured (in pgdesign.toml or PGDESIGN_DB), pgdesign discovers functional dependencies automatically.

Use --strict-nf on the generate command to block DDL output when NF violations exist:

pgdesign generate --strict-nf schema.toml

#The one-command workflow

Once you have a pgdesign.toml with [output] targets configured, the day-to-day development loop is a single command that regenerates all outputs (SQL, D2, codegen, JSON), chains the migration edge for your schema change, runs blocking normal-form and structural checks, and commits:

pgdesign revise

revise regenerates every configured output, generates the migration edge for your schema change, and commits — the pure tier (outputs + migration + blocking normal-form and structural checks) first, then a non-retroactive database tier (live FD discovery and workload analysis) when a database is reachable. The result is one schema revision stamped consistently across every artifact.

Under the hood, migrations form a content-addressed chain: each change is an edge between two schema revisions, applied with preconditions, a journal, and a post-apply reconcile. See The Migration Chain for the concepts and the Migration Guide for the commands.

#Next steps

Search