pistachio is a declarative schema management tool for PostgreSQL with a Terraform-like plan/apply workflow. You write the schema you want as DDL in SQL files. pistachio compares the database with the files and prints the DDL that makes the database match.

pista plan prints that DDL. pista apply runs it. pista dump writes the files from an existing database.

Note

Since 1.81.0, pistachio supports Amazon Aurora DSQL with --engine dsql. See Aurora DSQL. This should not change how pistachio works with PostgreSQL. If you find a problem, please open an issue.

Tip

The playground runs pista diff on two schemas that you edit in the page. There is nothing to install.

How it works

Every run computes the difference between the database and the files. There is no migration history to keep. A column added to the file becomes ALTER TABLE ... ADD COLUMN. A changed CHECK becomes a drop and an add. An object removed from the file is reported. It is dropped only when --allow-drop includes its type.

pistachio reads the SQL files with pg_query_go, a Go binding for the PostgreSQL parser. The files can use any SQL that PostgreSQL accepts.

Try it with Docker

A demo image bundles PostgreSQL with a sample schema. It lets you try pista without a local install:

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

The container starts a shell in /demo with pista and psql preconfigured. Edit desired.sql, then run:

pista plan  desired.sql     # show the DDL diff
pista apply desired.sql     # apply the changes
pista plan  desired.sql     # ...should now print -- No changes
pista dump                  # dump the current schema

The image sets $PISTA_MANAGE_ROUTINE, so pistachio manages the functions and procedures in the demo schema too.

The source for the image is under demo/.

Installation

pistachio is available from Homebrew, mise and GitHub Releases. See Installation.

Example

Create a schema file:

CREATE TYPE public.status AS ENUM ('active', 'inactive');

CREATE TABLE public.users (
    id integer NOT NULL,
    name text NOT NULL,
    status status NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

CREATE TABLE public.posts (
    id integer NOT NULL,
    user_id integer NOT NULL,
    title text NOT NULL,
    CONSTRAINT posts_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_posts_user_id ON public.posts USING btree (user_id);

ALTER TABLE ONLY public.posts
    ADD CONSTRAINT posts_user_id_fkey
    FOREIGN KEY (user_id) REFERENCES users(id);

Preview and apply:

pista plan schema.sql                  # review the diff (drops skipped by default)
pista plan --allow-drop all schema.sql # review the diff (with drops)
pista apply schema.sql                 # apply it

Alternatively, split the schema across multiple files:

pista dump --split ./schema/       # dump per table/view/enum/domain/composite type/sequence
pista plan ./schema/*.sql          # review the diff
pista apply ./schema/*.sql         # apply it

Where to go next

• Installation lists the ways to install pista.
• Getting started shows the steps: dump, edit, plan and apply.
• Guides cover one task each: renaming, drops, transactions and multiple schemas.
• Commands has a page per command with every option.
• Design and scope explains what pistachio does not manage, and why.

Related projects

• ridgepole is a DB schema management tool using a Rails DSL.
• qrev is a SQL execution history management tool.