Genki Sugawara

Posted on Sep 4

I built pistachio, a declarative schema tool for PostgreSQL

#postgres #database #opensource #showdev

I built pistachio, a declarative schema management tool for PostgreSQL. You write the schema you want in SQL, and the CLI generates the DDL diff against the current database and applies it in a Terraform-like plan/apply workflow.

CREATE TABLE public.users (
    id integer NOT NULL,
    name text NOT NULL,
    email text NOT NULL, -- added this line
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

Add that one line to the schema file, and:

$ pista plan schema.sql
-- Connected to postgres://postgres@localhost/postgres
-- Plan for schema public (1 table, 0 views, 0 enums, 0 domains, 0 composite types, 0 sequences)
ALTER TABLE public.users ADD COLUMN email text NOT NULL;

$ pista apply schema.sql
-- Connected to postgres://postgres@localhost/postgres
-- Apply to schema public (1 table, 0 views, 0 enums, 0 domains, 0 composite types, 0 sequences)
ALTER TABLE public.users ADD COLUMN email text NOT NULL;
-- Apply finished in 43ms

$ pista plan schema.sql
-- Connected to postgres://postgres@localhost/postgres
-- Plan for schema public (1 table, 0 views, 0 enums, 0 domains, 0 composite types, 0 sequences)
-- No changes

plan shows the diff, apply runs it, and a second plan reports no changes. Instead of piling up migration files, you keep editing one schema file as the desired state.

Here it is in action:

Why I built it

I started with sqldef, a tool built around the same concept, but it failed to parse some of the SQL in my development schema. I also considered pgschema, but it has PostgreSQL itself interpret the desired schema, which means you need an embedded PostgreSQL or a dev server. That was heavier to operate than I wanted, so I skipped it. Instead I built pistachio, which parses the schema with pg_query_go, PostgreSQL's own parser packaged as a library. If PostgreSQL can parse it, pistachio can, and the tool stays lightweight.

That said, sqldef is better if you work with multiple databases, and I think pgschema is more accurate at interpreting SQL.

How it works

The desired schema is parsed into the same syntax tree PostgreSQL itself would build. pistachio reads the current schema straight from pg_catalog, then compares the two and generates the DDL.

What it manages

pistachio currently manages:

tables and columns
constraints and indexes
views and materialized views
enum, domain, and composite types
sequences
row-level security and policies
triggers
comments and storage parameters
functions and procedures (opt-in)

Renames go through the -- pista:renamed-from directive and cover tables, columns, constraints, indexes, enum values, and so on. The details are in Supported objects.

The dump round trip

pistachio is built so that feeding the output of pista dump back as the desired schema makes plan report no changes. If that round trip breaks, it is a bug.

CI dumps and re-plans dozens of schemas taken from real open source projects. A separate test suite loads the output of pista dump into an empty database and checks that pg_dump there matches the original. If dump and plan miss the same thing, the round-trip test alone would still pass.

To adopt it on an existing database, run pista dump > schema.sql and edit that file from then on.

What it doesn't manage

CREATE EXTENSION / CREATE ROLE / GRANT are out of scope. They sit at a different privilege layer than the schema, and the role that runs a migration is usually not the role that owns the cluster, so I think they belong with the rest of the infrastructure, in Terraform for example.

A statement pistachio doesn't manage is not dropped silently: each one gets an ignored unsupported statement: warning.

Try it with Docker

There is a demo image that bundles PostgreSQL with a sample schema.

docker run --rm -it ghcr.io/winebarrel/pistachio-demo

It starts a shell in /demo with a preloaded PostgreSQL and a preconfigured pista. Edit desired.sql, then:

pista plan  desired.sql     # show the DDL diff
pista apply desired.sql     # apply it
pista plan  desired.sql     # reports no changes
pista dump                  # dump the current schema

Install with brew install winebarrel/pistachio/pistachio or grab a binary from Releases. The documentation is here.

DEV Community