Note

v1.7.0 breaking change: the CLI binary was renamed from pist to pista, environment variables from PIST_* to PISTA_*, and SQL comment directives from -- pist: to -- pista:. Existing SQL files and shell / CI configurations must be updated before upgrading. See the 1.7.0 changelog entry for the full list of renamed names.

brew install winebarrel/pistachio/pistachio

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

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

Usage: pista <command> [flags]

Flags:
  -h, --help                  Show context-sensitive help.
  -c, --conn-string="postgres://postgres@localhost/postgres"
                              PostgreSQL connection string. See
                              https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
                              ($PISTA_CONN_STR)
  -d, --dbname=STRING         PostgreSQL database name. Overrides the dbname in
                              --conn-string ($PISTA_DBNAME).
      --password=STRING       PostgreSQL password ($PISTA_PASSWORD).
  -n, --schemas=public,...    Schemas to inspect and modify ($PISTA_SCHEMAS).
  -m, --schema-map=KEY=VALUE;...
                              Schema name mapping (e.g. -m old=new).
      --version
      --[no-]pager            Force paging via $PISTA_PAGER even when stdout is
                              not a TTY. PISTA_PAGER must be set.

Commands:
  apply <files> ... [flags]
    Apply schema changes to the database.

  plan <files> ... [flags]
    Print the schema diff SQL without applying it.

  dump [flags]
    Dump the current database schema as SQL.

Run "pista <command> --help" for more information on a command.

pista plan schema.sql

# Multiple files
pista plan tables.sql views.sql

# Include pre-SQL in the output
pista plan schema.sql --pre-sql "SET statement_timeout = '5s';"
pista plan schema.sql --pre-sql-file pre.sql

pista apply schema.sql

# Multiple files
pista apply tables.sql views.sql

# Inline SQL
pista apply schema.sql --pre-sql "SET statement_timeout = '5s';" --with-tx

# From file
pista apply schema.sql --pre-sql-file pre.sql --with-tx

-- pista:concurrently
CREATE INDEX idx_users_name ON public.users USING btree (name);

-- Equivalent: inline CONCURRENTLY
CREATE INDEX CONCURRENTLY idx_users_email ON public.users USING btree (email);

-- This index will NOT use CONCURRENTLY
CREATE INDEX idx_users_id ON public.users USING btree (id);

pista apply schema.sql --concurrently-pre-sql "SET lock_timeout = '5s';"

pista plan --disable-index-concurrently schema.sql
pista apply --disable-index-concurrently --with-tx schema.sql

pista plan --force-index-concurrently schema.sql
pista apply --force-index-concurrently schema.sql

Note

When the generated diff includes CREATE INDEX CONCURRENTLY or DROP INDEX CONCURRENTLY, --with-tx cannot be used because CONCURRENTLY operations cannot run inside a transaction. If there are no index changes, --with-tx is allowed even when an index is opted into CONCURRENTLY. To run apply inside a transaction in spite of the opt-in, combine --with-tx with --disable-index-concurrently.

pista plan --bulk-alter schema.sql
pista apply --bulk-alter schema.sql

ALTER TABLE public.users
  ADD COLUMN email text,
  ALTER COLUMN name SET NOT NULL,
  DROP COLUMN legacy,
  ADD CONSTRAINT users_id_pos CHECK (id > 0);

# Allow all drops
pista plan --allow-drop all schema.sql

# Allow only column and table drops
pista apply --allow-drop column,table schema.sql

-- Plan for schema public (1 table, 0 views, 0 enums, 0 domains)
-- skipped: DROP TABLE public.legacy_users;
-- No changes

Note

Only pure removals of constraints, foreign keys, and indexes (those absent from the desired schema) are governed by --allow-drop=constraint / --allow-drop=foreign_key / --allow-drop=index. Definition changes still execute regardless of --allow-drop: constraints and foreign keys as DROP + ADD, and indexes as DROP + CREATE, because PostgreSQL has no ALTER CONSTRAINT and no general ALTER INDEX form for definition changes.

Foreign-key drops emitted because the owning table is being dropped follow the table-drop policy (not foreign_key): if the table drop is suppressed, the FK drop is suppressed too and surfaces as -- skipped: alongside the table.

-- pista:execute SELECT to_regprocedure('public.my_func()') IS NULL
CREATE OR REPLACE FUNCTION public.my_func() RETURNS void AS $$ ... $$ LANGUAGE plpgsql;

-- pista:execute SELECT obj_description(to_regprocedure('public.get_user_count()'), 'pg_proc') IS DISTINCT FROM 'v1'
DO $do$ BEGIN
  CREATE OR REPLACE FUNCTION public.get_user_count() RETURNS bigint AS $body$
    SELECT count(*) FROM public.users;
  $body$ LANGUAGE sql;
  COMMENT ON FUNCTION public.get_user_count() IS 'v1';
END $do$;

# Page with less, keeping ANSI colors
PISTA_PAGER='less -R' pista dump

# Pipe through a syntax highlighter that supports SQL
PISTA_PAGER='source-highlight -s sql -f esc | less -R' pista plan schema.sql

# One-off override
pista --no-pager plan schema.sql

# Force the pager even when stdout is not a TTY
PISTA_PAGER='source-highlight -s sql -f esc' pista --pager dump

pista -n staging -m staging=public dump

# schema.sql uses "public" as the schema name
pista -n staging -m staging=public plan schema.sql
pista -n staging -m staging=public apply schema.sql

# Dump only objects matching "user*"
pista dump -I 'user*'

# Plan changes excluding temporary tables
pista plan -E 'tmp_*' schema.sql

# Combine include and exclude
pista apply -I 'user*' -E 'user_tmp' schema.sql

# Dump only enums
pista dump --enable enum

# Dump only tables and views
pista dump --enable table,view

# Dump everything except views
pista dump --disable view

# Plan changes for enums only
pista plan --enable enum schema.sql

# Using environment variables
PISTA_ENABLE=enum pista dump
PISTA_DISABLE=view pista dump
PISTA_INCLUDE='user*' pista dump
PISTA_EXCLUDE='tmp_*' pista plan schema.sql

Note

--enable takes precedence over --disable. When --enable is set, only the specified types are included regardless of --disable. These flags may exclude dependent objects (e.g. --enable table omits enums/domains that table columns may reference); use them for inspection (dump, plan), not apply.

Note

When both a CLI flag and its corresponding environment variable are set, the CLI flag overrides the environment variable (values are not merged). For example, running PISTA_EXCLUDE='tmp_*' pista plan -E 'foo_*' schema.sql excludes only foo_*; tmp_* is ignored.

pista dump --omit-schema
# => CREATE TABLE users (...) instead of CREATE TABLE public.users (...)

pista dump --omit-schema --split ./schema/
# -- Dump of schema public (2 tables, 0 views, 0 enums, 0 domains)
# -- Wrote 2 file(s) to ./schema/
# (writes ./schema/users.sql, ./schema/orders.sql, ...)

pista -n staging plan schema.sql   # schema-less SQL is treated as "staging"
pista -n staging apply schema.sql

-- pista:renamed-from public.old_status
CREATE TYPE public.new_status AS ENUM ('active', 'inactive');

-- pista:renamed-from public.old_users
CREATE TABLE public.users (
    id integer NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

-- pista:renamed-from public.old_view
CREATE VIEW public.new_view AS SELECT 1;

CREATE TABLE public.users (
    id integer NOT NULL,
    -- pista:renamed-from name
    display_name text NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    -- pista:renamed-from users_name_key
    CONSTRAINT users_display_name_key UNIQUE (display_name)
);

-- pista:renamed-from idx_users_name
CREATE INDEX idx_users_display_name ON public.users (display_name);

-- pista:renamed-from fk_old_name
ALTER TABLE public.orders ADD CONSTRAINT fk_new_name FOREIGN KEY (user_id) REFERENCES public.users(id);

Tip

Rename directives that have already been applied are silently skipped. Leave them in place until cleanup.

CREATE TABLE public.users (
    id integer NOT NULL,
    -- pista:renamed-from name
    display_name text NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);
-- Reference the new column name here:
CREATE INDEX idx_users_name ON public.users (display_name);

pista dump --split ./schema/
# -- Dump of schema public (3 tables, 0 views, 1 enum, 0 domains)
# -- Wrote 4 file(s) to ./schema/
# (writes ./schema/public.status.sql, ./schema/public.users.sql, ./schema/public.orders.sql, ...)

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);

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

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

Note

Unnamed constraints (e.g. id integer PRIMARY KEY, name text UNIQUE, col integer REFERENCES other(id)) are auto-named by pistachio following PostgreSQL's convention ({table}_pkey, {table}_{col}_key, {table}_{col}_check, {table}_{col}_fkey, {table}_{col}_excl). The auto-naming has two limitations:

• When multiple constraints would generate the same name, PostgreSQL appends a numeric suffix (e.g. _1) that pistachio cannot predict.
• PostgreSQL truncates identifier names to 63 bytes (NAMEDATALEN - 1). pistachio does not apply this truncation, so very long table/column names may produce mismatched constraint names.

Use explicit CONSTRAINT <name> clauses to avoid these issues.

docker compose up -d
make test