pistachio

Declarative schema management tool for PostgreSQL, built on pg_query_go. Define the desired schema in SQL; pistachio generates the DDL diff.

See also: Getting Started Guide / Directives / Performance

Installation

Homebrew

brew install winebarrel/pistachio/pistachio

Download binary

Download the latest binary from Releases.

Demo

A demo image bundles PostgreSQL with a sample schema for trying 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 source for the image is under demo/.

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 suppressed by default)
pista plan --allow-drop all schema.sql # review the diff (with drops)
pista apply schema.sql                 # apply it

Or split the schema across multiple files:

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

Usage

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).
  -C, --config=FILE           Load options from a YAML file ($PISTA_CONFIG).
      --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.

[!note] By default, pistachio targets the public schema.

See Getting Started#working-with-specific-schemas for details.

Usage: pista plan <files> ... [flags]

Print the schema diff SQL without applying it.

Arguments:
  <files> ...    Path to the desired schema SQL file(s).

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).
  -C, --config=FILE            Load options from a YAML file ($PISTA_CONFIG).
      --version
      --[no-]pager             Force paging via $PISTA_PAGER even when stdout is
                               not a TTY. PISTA_PAGER must be set.

  -I, --include=INCLUDE,...    Include only tables/views/enums/domains/composite
                               types/sequences matching the pattern (wildcard:
                               *, ?) ($PISTA_INCLUDE).
  -E, --exclude=EXCLUDE,...    Exclude tables/views/enums/domains/composite
                               types/sequences matching the pattern (wildcard:
                               *, ?) ($PISTA_EXCLUDE).
      --enable=ENABLE,...      Enable only specified object types (can be
                               repeated) ($PISTA_ENABLE).
      --disable=DISABLE,...    Disable specified object types (can be repeated)
                               ($PISTA_DISABLE).
      --allow-drop=ALLOW-DROP,...
                               Allow dropping these object types (repeatable;
                               'all' allows everything) ($PISTA_ALLOW_DROP).
      --pre-sql=STRING         SQL to prepend to the plan output
                               ($PISTA_PRE_SQL).
      --pre-sql-file=STRING    Path to a SQL file to prepend to the plan output
                               ($PISTA_PRE_SQL_FILE).
      --concurrently-pre-sql=STRING
                               SQL to run before CONCURRENTLY index DDL (e.g.
                               SET lock_timeout). Emitted only when
                               the diff contains CONCURRENTLY index DDL
                               ($PISTA_CONCURRENTLY_PRE_SQL).
      --concurrently-pre-sql-file=STRING
                               Path to a SQL file to run before CONCURRENTLY
                               index DDL ($PISTA_CONCURRENTLY_PRE_SQL_FILE).
      --disable-index-concurrently
                               Ignore CONCURRENTLY opt-ins (directive and
                               inline) and emit plain CREATE/DROP INDEX
                               ($PISTA_DISABLE_INDEX_CONCURRENTLY).
      --force-index-concurrently
                               Force CONCURRENTLY on every
                               CREATE/DROP INDEX, including pure drops
                               ($PISTA_FORCE_INDEX_CONCURRENTLY).
      --bulk-alter             Combine consecutive ALTER TABLE actions on the
                               same table into a single statement. FK changes,
                               RENAME, VALIDATE CONSTRAINT, RLS toggles, and
                               skipped DROPs stay separate ($PISTA_BULK_ALTER).
      --assume-validated       Treat every table constraint, domain constraint,
                               and foreign key as validated: ignore NOT
                               VALID and never emit VALIDATE CONSTRAINT
                               ($PISTA_ASSUME_VALIDATED).
      --no-read-only           Open the database connection read-write.
                               By default plan uses a read-only connection
                               ($PISTA_NO_READ_ONLY).
      --check                  Exit with code 2 when the plan contains
                               executable changes ($PISTA_CHECK).

Usage: pista apply <files> ... [flags]

Apply schema changes to the database.

Arguments:
  <files> ...    Path to the desired schema SQL file(s).

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).
  -C, --config=FILE            Load options from a YAML file ($PISTA_CONFIG).
      --version
      --[no-]pager             Force paging via $PISTA_PAGER even when stdout is
                               not a TTY. PISTA_PAGER must be set.

  -I, --include=INCLUDE,...    Include only tables/views/enums/domains/composite
                               types/sequences matching the pattern (wildcard:
                               *, ?) ($PISTA_INCLUDE).
  -E, --exclude=EXCLUDE,...    Exclude tables/views/enums/domains/composite
                               types/sequences matching the pattern (wildcard:
                               *, ?) ($PISTA_EXCLUDE).
      --enable=ENABLE,...      Enable only specified object types (can be
                               repeated) ($PISTA_ENABLE).
      --disable=DISABLE,...    Disable specified object types (can be repeated)
                               ($PISTA_DISABLE).
      --allow-drop=ALLOW-DROP,...
                               Allow dropping these object types (repeatable;
                               'all' allows everything) ($PISTA_ALLOW_DROP).
      --pre-sql=STRING         SQL to execute before applying changes
                               ($PISTA_PRE_SQL).
      --pre-sql-file=STRING    Path to a SQL file to execute before applying
                               changes ($PISTA_PRE_SQL_FILE).
      --concurrently-pre-sql=STRING
                               SQL to execute before CONCURRENTLY
                               index DDL (e.g. SET lock_timeout).
                               Runs outside any transaction, only when
                               the diff contains CONCURRENTLY index DDL
                               ($PISTA_CONCURRENTLY_PRE_SQL).
      --concurrently-pre-sql-file=STRING
                               Path to a SQL file to execute before CONCURRENTLY
                               index DDL ($PISTA_CONCURRENTLY_PRE_SQL_FILE).
      --with-tx                Execute pre-SQL and schema changes in a
                               transaction ($PISTA_WITH_TX).
      --try-tx                 Execute pre-SQL and schema changes in a
                               transaction when possible. A diff containing
                               CONCURRENTLY index DDL runs without a transaction
                               instead of failing ($PISTA_TRY_TX).
      --disable-index-concurrently
                               Ignore CONCURRENTLY opt-ins (directive and
                               inline) and emit plain CREATE/DROP INDEX
                               ($PISTA_DISABLE_INDEX_CONCURRENTLY).
      --force-index-concurrently
                               Force CONCURRENTLY on every CREATE/DROP INDEX,
                               including pure drops. Cannot be combined with
                               --with-tx ($PISTA_FORCE_INDEX_CONCURRENTLY).
      --bulk-alter             Combine consecutive ALTER TABLE actions on the
                               same table into a single statement. FK changes,
                               RENAME, VALIDATE CONSTRAINT, RLS toggles, and
                               skipped DROPs stay separate ($PISTA_BULK_ALTER).
      --assume-validated       Treat every table constraint, domain constraint,
                               and foreign key as validated: ignore NOT
                               VALID and never emit VALIDATE CONSTRAINT
                               ($PISTA_ASSUME_VALIDATED).

Usage: pista dump [flags]

Dump the current database schema as SQL.

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).
  -C, --config=FILE            Load options from a YAML file ($PISTA_CONFIG).
      --version
      --[no-]pager             Force paging via $PISTA_PAGER even when stdout is
                               not a TTY. PISTA_PAGER must be set.

  -I, --include=INCLUDE,...    Include only tables/views/enums/domains/composite
                               types/sequences matching the pattern (wildcard:
                               *, ?) ($PISTA_INCLUDE).
  -E, --exclude=EXCLUDE,...    Exclude tables/views/enums/domains/composite
                               types/sequences matching the pattern (wildcard:
                               *, ?) ($PISTA_EXCLUDE).
      --enable=ENABLE,...      Enable only specified object types (can be
                               repeated) ($PISTA_ENABLE).
      --disable=DISABLE,...    Disable specified object types (can be repeated)
                               ($PISTA_DISABLE).
      --split=STRING           Output each table/view/enum/domain/composite
                               type/sequence as a separate file in the specified
                               directory.
      --omit-schema            Omit schema name from the dump output.
      --no-read-only           Open the database connection read-write.
                               By default dump uses a read-only connection
                               ($PISTA_NO_READ_ONLY).

plan

Compare schema file(s) against the current database and print the SQL needed to reconcile them.

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

--pre-sql / --pre-sql-file are also available as $PISTA_PRE_SQL / $PISTA_PRE_SQL_FILE.

Use --check to detect schema drift from the exit code. The exit code is 2 if the plan contains executable changes, 0 if not, and 1 on error. The output does not change. Suppressed drops alone exit 0 because they generate no executable DDL. Also available as $PISTA_CHECK.

pista plan --check schema.sql
echo $?  # 0: no changes, 2: changes, 1: error

plan and dump open a read-only connection, so they cannot write to the database. Pass --no-read-only (env $PISTA_NO_READ_ONLY) to use a read-write connection.

[!NOTE] apply sets search_path to the target schemas plus public so unqualified type and object references resolve. plan output does not include that SET search_path, so piping pista plan -n <schema> into psql for a non-public schema may fail on an unqualified reference. Qualify the reference or run pista apply.

apply

Apply the diff to the database.

pista apply schema.sql

# Multiple files
pista apply tables.sql views.sql

Use --pre-sql or --pre-sql-file to run SQL before applying changes (mutually exclusive). Also available as $PISTA_PRE_SQL / $PISTA_PRE_SQL_FILE. Use --with-tx to wrap the apply in a transaction (also available as $PISTA_WITH_TX).

# 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

Use --try-tx to wrap the apply in a transaction only when possible. It works like --with-tx, except that a diff containing CREATE/DROP INDEX CONCURRENTLY runs without a transaction instead of failing. The two flags are mutually exclusive. Also available as $PISTA_TRY_TX.

pista apply --try-tx schema.sql

The skipped transaction is recorded in the output:

-- Transaction skipped: plan contains CONCURRENTLY index DDL
CREATE INDEX CONCURRENTLY idx_users_name ON public.users USING btree (name);

An apply that skips the transaction is not all-or-nothing. A failure leaves the statements that already ran in place, and re-running pista apply applies the rest. A failed CREATE INDEX CONCURRENTLY also leaves an invalid index that must be dropped by hand.

To apply CONCURRENTLY to individual indexes, either write CREATE INDEX CONCURRENTLY directly or use the -- pista:concurrently directive before the CREATE INDEX statement. Both are treated equivalently:

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

Use --concurrently-pre-sql (or --concurrently-pre-sql-file) to run SQL (typically SET lock_timeout = '...') before any CONCURRENTLY index DDL. The SQL is emitted only when the plan contains CREATE/DROP INDEX CONCURRENTLY. Because SET is session-scoped and CONCURRENTLY runs outside a transaction, the value carries over to every subsequent CONCURRENTLY statement in the same apply. Also available as $PISTA_CONCURRENTLY_PRE_SQL / $PISTA_CONCURRENTLY_PRE_SQL_FILE.

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

Use --disable-index-concurrently to ignore all CONCURRENTLY opt-ins (both inline and directive) and emit plain CREATE INDEX / DROP INDEX instead. This lets you keep the directives in your schema files while running a one-off plan/apply inside a transaction. Also available as $PISTA_DISABLE_INDEX_CONCURRENTLY.

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

Use --force-index-concurrently to apply CONCURRENTLY to every CREATE INDEX and DROP INDEX the diff emits, regardless of per-index directives. This also covers pure drops (indexes removed from the desired schema), which the directive cannot reach. Conflicts with --disable-index-concurrently and --with-tx. Also available as $PISTA_FORCE_INDEX_CONCURRENTLY.

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. To keep the opt-in and run without a transaction instead, use --try-tx.

Use --bulk-alter to combine consecutive ALTER TABLE actions on the same table into a single statement with comma-separated actions. This reduces metadata-lock churn and lets PostgreSQL plan the changes together. Foreign keys, RENAME, VALIDATE CONSTRAINT, RLS toggles, and skipped DROPs are kept as separate statements. Also available as $PISTA_BULK_ALTER.

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

To merge ALTER TABLE actions for individual tables only, put the -- pista:bulk-alter directive before the CREATE TABLE statement. Other tables keep one statement per action. --bulk-alter merges every table regardless of the directive.

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

Use --assume-validated to treat every table constraint, domain constraint, and foreign key as validated. Pistachio ignores NOT VALID in the desired schema and never emits NOT VALID or VALIDATE CONSTRAINT. Use it when NOT VALID is a one-off migration step you do not want in the desired state. Also available as $PISTA_ASSUME_VALIDATED.

pista plan --assume-validated schema.sql
pista apply --assume-validated schema.sql

By default, plan and apply do not drop tables, views, enums, domains, composite types, columns, constraints, foreign keys, or indexes. Use --allow-drop to enable dropping specific object types (all, table, view, enum, domain, composite_type, column, constraint, foreign_key, index, policy). Also available as $PISTA_ALLOW_DROP. constraint covers CHECK / UNIQUE / PRIMARY KEY / EXCLUSION; foreign keys are governed by foreign_key separately. composite_type also gates DROP ATTRIBUTE on a composite type.

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

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

Suppressed drops are emitted as commented-out DDL prefixed with -- skipped:. The plan still reports -- No changes when the only diff would be a suppressed drop, since no executable DDL is generated:

-- Plan for schema public (1 table, 0 views, 0 enums, 0 domains, 0 composite types, 0 sequences)
-- 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.

Executing arbitrary SQL

Use -- pista:execute to include non-managed SQL (functions, triggers, grants) in your schema files. The check SQL after the directive is evaluated by both plan and apply. When it returns true the statement is executed, otherwise skipped, and plan leaves out the statements apply would skip. A common pattern skips when an object already exists:

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

To manage a function whose body changes over time, embed a version tag in COMMENT ON FUNCTION and execute only when the installed comment differs. Wrap the CREATE and COMMENT in a DO block so they are a single statement:

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

When the body changes, update the tag in both places (e.g. 'v1' -> 'v2'); the next apply will re-run.

-- pista:execute runs after the managed DDL. Use -- pista:execute-first when the managed DDL calls the function, as a CHECK constraint, a GENERATED expression, an index expression, or a policy can:

-- pista:execute-first SELECT to_regprocedure('public.lower_v(text)') IS NULL
CREATE OR REPLACE FUNCTION public.lower_v(t text) RETURNS text AS $$ SELECT lower(t) $$ LANGUAGE sql IMMUTABLE;

CREATE TABLE public.users (
    id integer NOT NULL,
    v text,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_v_check CHECK (lower_v(v) <> 'x')
);

The check SQL is evaluated where the statement runs, so an execute-first check sees the schema before the change and an execute check sees it after.

See Getting Started for details.

dump

Dump the current database schema as SQL. Output can be used directly as a schema file.

pista dump

Paging long output

Set $PISTA_PAGER to forward plan / apply / dump output through an external command when stdout is a TTY. The command is interpreted by sh -c (cmd /c on Windows), so quoting and arguments work as in the shell. Pipes and redirects (pista dump > file.sql, pista dump | grep ...) are unaffected; the pager runs only for interactive output. Use --no-pager to disable it for a single invocation, or --pager to force it on when stdout is not a TTY (e.g. when piping into another pager-aware tool). PISTA_PAGER must still be set for --pager to do anything.

# 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

Configuration file

Use -C / --config to load options from a YAML file.

Keys are flag names (for example conn-string, not conn_string). An unknown key is an error.

# pista.yml
conn-string: postgres://user@db.example.com/app
schemas:
  - public
  - billing
schema-map:
  staging: public
exclude:
  - tmp_*

pista --config pista.yml dump
pista --config pista.yml plan schema.sql

# Or set the path with an environment variable
export PISTA_CONFIG=pista.yml
pista dump

One file works for every command. Keys that the running command does not use are ignored.

Precedence: command-line flag > environment variable > config file > default.

Schema name mapping

Use -m / --schema-map to remap schema names when the database schema name differs from the one used in your SQL files.

For example, to dump a staging schema as if it were public:

pista -n staging -m staging=public dump

You can also use it with plan and apply. The desired SQL files use the mapped name (public), while the generated SQL targets the real database schema (staging):

# 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

Filtering objects

Use -I / --include to include only matching objects by name, or -E / --exclude to exclude them. Patterns support * and ? wildcards. Patterns match against object names only (not schema-qualified names). Also available as $PISTA_INCLUDE / $PISTA_EXCLUDE environment variables.

Use --enable to restrict operations to specific object types, or --disable to exclude specific types. Valid types: table, view, enum, domain, composite_type, sequence. Can be repeated. Also available as $PISTA_ENABLE / $PISTA_DISABLE environment variables.

These flags are available on the dump, plan, and apply subcommands.

# 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.

Omit schema

Use --omit-schema to omit schema names from the dump output.

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, 0 composite types, 0 sequences)
# -- Wrote 2 file(s) to ./schema/
# (writes ./schema/users.sql, ./schema/orders.sql, ...)

When schema is omitted in SQL files, plan and apply use the schema specified by -n:

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

Renaming objects

Use -- pista:renamed-from <old_name> directives to rename objects instead of dropping and recreating them.

Tables, views, enums:

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

Enum values (inside CREATE TYPE ... AS ENUM, before the value). The rename emits ALTER TYPE ... RENAME VALUE, which keeps stored data and the value's position:

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

Composite types and their attributes. The statement-level directive renames the type (ALTER TYPE ... RENAME TO). A directive inside CREATE TYPE ... AS (...), before an attribute, renames that attribute (ALTER TYPE ... RENAME ATTRIBUTE) and keeps stored data:

-- pista:renamed-from public.address
CREATE TYPE public.postal_address AS (
    -- pista:renamed-from street
    road text,
    city text
);

Columns, constraints, indexes (inside CREATE TABLE or before CREATE INDEX / ALTER TABLE ADD CONSTRAINT):

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.

Column rename caveats

When a column is renamed, pistachio rewrites column references in same-table indexes, constraints, and foreign keys (including EXCLUDE, partial / expression / INCLUDE indexes) on the current side, so a single ALTER TABLE ... RENAME COLUMN is emitted without redundant DROP/CREATE on the dependents.

The desired-side SQL must use the new column name in those dependent definitions:

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

If the desired side still references the old name, pista plan errors out at parse time with a message like column name referenced in index idx_users_name does not exist on table public.users (identifiers are quoted only when they aren't safe unquoted). All such unresolved references are reported in a single error.

The following references are not auto-rewritten and may produce a redundant DROP/CREATE on the first plan (the second run after applying the rename is clean):

• View / materialized view definitions that SELECT the renamed column
• Foreign keys in other tables whose REFERENCES this_table(renamed_col) points at the renamed column

Ignoring objects

Use the -- pista:ignore directive to leave an object unmanaged. pistachio does not create, alter, or drop a CREATE TABLE / CREATE TYPE ... AS ENUM / CREATE TYPE ... AS (...) / CREATE DOMAIN / CREATE VIEW marked with it. This is the in-file equivalent of --exclude for a single object, useful for a table managed by another tool or one whose definition intentionally drifts.

-- pista:ignore
CREATE TABLE public.legacy (
    id integer NOT NULL,
    CONSTRAINT legacy_pkey PRIMARY KEY (id)
);

Each ignored object is reported as an -- ignored: <name> comment in plan and apply output. The directive attaches to a statement in the schema file, so it can only ignore an object you have declared. To keep an existing object that would otherwise be dropped, write its CREATE statement with the directive.

Split dump

Use --split to output each table/view/enum/domain/composite type as a separate file in the specified directory.

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

Constraint naming

[!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.

Supported Objects

• Domain types (CREATE DOMAIN, ALTER DOMAIN SET/DROP DEFAULT, SET/DROP NOT NULL, ADD/DROP/VALIDATE CONSTRAINT)
• Enum types (CREATE TYPE ... AS ENUM, ALTER TYPE ... ADD VALUE)
• Composite types (CREATE TYPE ... AS (...), ALTER TYPE ... ADD/DROP/ALTER ATTRIBUTE, RENAME ATTRIBUTE). Attributes are matched by name, so reordering them produces no diff (PostgreSQL cannot reorder attributes). ALTER ATTRIBUTE ... TYPE fails at apply while a table column uses the type; PostgreSQL does not allow it and CASCADE does not help.
• Sequences (CREATE SEQUENCE, ALTER SEQUENCE, DROP SEQUENCE). Only standalone sequences are managed; sequences owned by a serial or identity column are handled as part of that column, not as separate objects.
• Tables (including unlogged and partitioned tables)
• Views
• Materialized views
• Columns (serial/bigserial/smallserial, identity, generated)
• Constraints (primary key, unique, check, exclusion, foreign key)
• Indexes (unique, partial, expression, hash, multi-column)
• Comments (on tables, columns, views, types, domains, composite types, composite attributes, sequences)
• Row-level security (ALTER TABLE ... ENABLE/DISABLE/FORCE/NO FORCE ROW LEVEL SECURITY, policies via CREATE POLICY / ALTER POLICY / DROP POLICY)
• Renaming (tables, views, enums, enum values, domains, composite types, composite attributes, sequences, columns, constraints, foreign keys, indexes, policies via -- pista:renamed-from directive)
• Array, JSON, UUID, and other built-in types
• Quoted identifiers

Development

docker compose up -d
make test

Related projects

• myschema: declarative schema management for MySQL.
• ridgepole: DB schema management using a Rails DSL.
• qrev: SQL execution history management tool.