pistachio

README

pistachio

Declarative schema management tool for PostgreSQL. Define your desired schema in SQL and let pistachio figure out the diff.

See also: Getting Started Guide

Installation

Homebrew

brew install winebarrel/pistachio/pistachio

Download binary

Download the latest binary from Releases.

Usage

Usage: pist <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
                              ($PIST_CONN_STR)
      --password=STRING       PostgreSQL password ($PIST_PASSWORD).
  -n, --schemas=public,...    Schemas to inspect and modify ($PIST_SCHEMAS).
  -m, --schema-map=KEY=VALUE;...
                              Schema name mapping (e.g. -m old=new).
      --version

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 "pist <command> --help" for more information on a command.

plan

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

pist plan schema.sql

# Multiple files
pist plan tables.sql views.sql

# Include pre-SQL in the output
pist plan schema.sql --pre-sql "SET search_path TO myschema;"
pist plan schema.sql --pre-sql-file pre.sql

--pre-sql / --pre-sql-file are also available as $PIST_PRE_SQL / $PIST_PRE_SQL_FILE.

apply

Apply the diff to the database.

pist apply schema.sql

# Multiple files
pist apply tables.sql views.sql

Use --pre-sql or --pre-sql-file to run SQL before applying changes (mutually exclusive). Also available as $PIST_PRE_SQL / $PIST_PRE_SQL_FILE. Use --with-tx to wrap everything in a transaction.

# Inline SQL
pist apply schema.sql --pre-sql "SET search_path TO myschema;" --with-tx

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

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

-- pist: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 only emitted/executed when the plan actually contains CREATE/DROP INDEX CONCURRENTLY, so it's safe to set unconditionally. 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 $PIST_CONCURRENTLY_PRE_SQL / $PIST_CONCURRENTLY_PRE_SQL_FILE.

pist 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. Useful when you want to keep the directives / inline CONCURRENTLY in your schema files but run a one-off plan/apply inside a transaction. Also available as $PIST_DISABLE_INDEX_CONCURRENTLY.

pist plan --disable-index-concurrently schema.sql
pist apply --disable-index-concurrently --with-tx 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.

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

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

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

Suppressed drops are emitted as commented-out DDL prefixed with -- skipped: so you can see what would be dropped without executing it. 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)
-- 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 -- pist:execute to include non-managed SQL (functions, triggers, grants) in your schema files. The check SQL after the directive is evaluated during apply — when it returns true the statement is executed, otherwise it is skipped. The simplest form skips when an object already exists:

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

For idempotent management of 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:

-- pist: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 you change the body, bump the tag in both places (e.g. 'v1' → 'v2'); the next apply will re-run.

See Getting Started for details.

dump

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

pist dump

Schema name mapping

Use -m / --schema-map to remap schema names. This is useful when you want to manage a database whose schema name differs from the one used in your SQL files.

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

pist -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
pist -n staging -m staging=public plan schema.sql
pist -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 $PIST_INCLUDE / $PIST_EXCLUDE environment variables.

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

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

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

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

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

# Dump only enums
pist dump --enable enum

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

# Dump everything except views
pist dump --disable view

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

# Using environment variables
PIST_ENABLE=enum pist dump
PIST_DISABLE=view pist dump
PIST_INCLUDE='user*' pist dump
PIST_EXCLUDE='tmp_*' pist 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), so use them primarily for inspection (dump, plan) rather than 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 PIST_EXCLUDE='tmp_*' pist plan -E 'foo_*' schema.sql excludes only foo_*; tmp_* is ignored.

Omit schema

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

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

pist dump --omit-schema --split ./schema/
# => ./schema/users.sql, ./schema/orders.sql, ...

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

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

Renaming objects

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

Tables, views, enums:

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

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

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

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

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

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

-- pist: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, so you can safely leave them in your schema files 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,
    -- pist: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, pist 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 comes out 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

Split dump

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

pist dump --split ./schema/
# => ./schema/public.status.sql, ./schema/public.users.sql, ./schema/public.orders.sql, ...

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:

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

Or split schema into multiple files and use them together:

pist dump --split ./schema/       # dump per table/view/enum
pist plan ./schema/*.sql          # review the diff
pist 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 naming convention ({table}_pkey, {table}_{col}_key, {table}_{col}_check, {table}_{col}_fkey, {table}_{col}_excl). However, pistachio's auto-naming has the following 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.

It is strongly recommended to 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 CONSTRAINT)
• Enum types (CREATE TYPE ... AS ENUM, ALTER TYPE ... ADD VALUE)
• 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)
• Renaming (tables, views, enums, domains, columns, constraints, foreign keys, indexes via -- pist:renamed-from directive)
• Array, JSON, UUID, and other built-in types
• Quoted identifiers

Development

docker compose up -d
make test

Documentation

Index

• type ApplyOptions
• type ApplyResult
• type Client
  • func NewClient(options *Options) *Client
  • func (client *Client) Apply(ctx context.Context, options *ApplyOptions, w io.Writer) (*ApplyResult, error)
  • func (client *Client) Dump(ctx context.Context, options *DumpOptions) (*DumpResult, error)
  • func (client *Client) Plan(ctx context.Context, options *PlanOptions) (*PlanResult, error)
• type DropPolicy
  • func (p *DropPolicy) IsDropAllowed(objectType string) bool
• type DumpOptions
• type DumpResult
  • func (r *DumpResult) Files() map[string]string
  • func (r *DumpResult) String() string
• type FilterOptions
  • func (f *FilterOptions) AfterApply() error
  • func (f *FilterOptions) IsTypeEnabled(typeName string) bool
  • func (f *FilterOptions) MatchName(name string) bool
  • func (f *FilterOptions) ValidatePatterns() error
• type ObjectCount
  • func (c ObjectCount) SchemaLabel() string
  • func (c ObjectCount) Summary() string
• type Options
  • func (o *Options) AfterApply() error
  • func (o *Options) RemapSchema(schema string) string
  • func (o *Options) ReverseRemapSchema(schema string) string
  • func (o *Options) ValidateSchemaMap() error
• type PlanOptions
• type PlanResult

Types

type ApplyOptions

type ApplyOptions struct {
	FilterOptions
	DropPolicy
	Files                    []string `arg:"" help:"Path to the desired schema SQL file(s)."`
	PreSQL                   string   `xor:"pre-sql" env:"PIST_PRE_SQL" help:"SQL to execute before applying changes."`
	PreSQLFile               string   `type:"path" xor:"pre-sql" env:"PIST_PRE_SQL_FILE" help:"Path to a SQL file to execute before applying changes."`
	ConcurrentlyPreSQL       string   `` /* 228-byte string literal not displayed */
	ConcurrentlyPreSQLFile   string   `` /* 150-byte string literal not displayed */
	WithTx                   bool     `help:"Execute the pre-SQL and schema changes in a transaction."`
	DisableIndexConcurrently bool     `` /* 191-byte string literal not displayed */
}

type ApplyResult

type ApplyResult struct {
	Count           ObjectCount
	DisallowedDrops string
}

ApplyResult holds the result of an Apply operation.

type Client

type Client struct {
	*Options
}

func NewClient

func NewClient(options *Options) *Client

func (*Client) Apply

func (client *Client) Apply(ctx context.Context, options *ApplyOptions, w io.Writer) (*ApplyResult, error)

func (*Client) Dump

func (client *Client) Dump(ctx context.Context, options *DumpOptions) (*DumpResult, error)

func (*Client) Plan

func (client *Client) Plan(ctx context.Context, options *PlanOptions) (*PlanResult, error)

type DropPolicy

type DropPolicy struct {
	AllowDrop []string `` /* 185-byte string literal not displayed */
}

DropPolicy controls which object types are allowed to be dropped. If AllowDrop is empty, no drops are allowed (safe default). If AllowDrop contains "all", all drops are allowed. Otherwise, only the listed object types are allowed to be dropped.

func (*DropPolicy) IsDropAllowed

func (p *DropPolicy) IsDropAllowed(objectType string) bool

type DumpOptions

type DumpOptions struct {
	FilterOptions
	Split      string `help:"Output each table/view/enum as a separate file in the specified directory."`
	OmitSchema bool   `help:"Omit schema name from the dump output."`
}

type DumpResult

type DumpResult struct {
	Tables     *orderedmap.Map[string, *model.Table]
	Views      *orderedmap.Map[string, *model.View]
	Enums      *orderedmap.Map[string, *model.Enum]
	Domains    *orderedmap.Map[string, *model.Domain]
	OmitSchema bool
	Count      ObjectCount
}

func (*DumpResult) Files

func (r *DumpResult) Files() map[string]string

func (*DumpResult) String

func (r *DumpResult) String() string

type FilterOptions

type FilterOptions struct {
	Include []string `short:"I" env:"PIST_INCLUDE" help:"Include only tables/views/enums/domains matching the pattern (wildcard: *, ?)."`
	Exclude []string `short:"E" env:"PIST_EXCLUDE" help:"Exclude tables/views/enums/domains matching the pattern (wildcard: *, ?)."`
	Enable  []string `enum:"table,view,enum,domain" env:"PIST_ENABLE" help:"Enable only specified object types (can be repeated)."`
	Disable []string `enum:"table,view,enum,domain" env:"PIST_DISABLE" help:"Disable specified object types (can be repeated)."`
}

func (*FilterOptions) AfterApply

func (f *FilterOptions) AfterApply() error

func (*FilterOptions) IsTypeEnabled

func (f *FilterOptions) IsTypeEnabled(typeName string) bool

IsTypeEnabled returns true if the given object type should be included. Enable takes precedence: if set, only listed types are enabled. Disable excludes listed types (ignored when Enable is set). If neither is set, all types are enabled.

func (*FilterOptions) MatchName

func (f *FilterOptions) MatchName(name string) bool

func (*FilterOptions) ValidatePatterns

func (f *FilterOptions) ValidatePatterns() error

type ObjectCount

type ObjectCount struct {
	Schemas []string
	Tables  int
	Views   int
	Enums   int
	Domains int
}

ObjectCount holds the number of objects inspected by type.

func (ObjectCount) SchemaLabel

func (c ObjectCount) SchemaLabel() string

func (ObjectCount) Summary

func (c ObjectCount) Summary() string

type Options

type Options struct {
	ConnString string            `` /* 195-byte string literal not displayed */
	Password   string            `env:"PIST_PASSWORD" help:"PostgreSQL password."`
	Schemas    []string          `short:"n" env:"PIST_SCHEMAS" default:"public" help:"Schemas to inspect and modify."`
	SchemaMap  map[string]string `short:"m" help:"Schema name mapping (e.g. -m old=new)."`
}

func (*Options) AfterApply

func (o *Options) AfterApply() error

func (*Options) RemapSchema

func (o *Options) RemapSchema(schema string) string

func (*Options) ReverseRemapSchema

func (o *Options) ReverseRemapSchema(schema string) string

func (*Options) ValidateSchemaMap

func (o *Options) ValidateSchemaMap() error

type PlanOptions

type PlanOptions struct {
	FilterOptions
	DropPolicy
	Files                    []string `arg:"" help:"Path to the desired schema SQL file(s)."`
	PreSQL                   string   `xor:"pre-sql" env:"PIST_PRE_SQL" help:"SQL to prepend to the plan output."`
	PreSQLFile               string   `type:"path" xor:"pre-sql" env:"PIST_PRE_SQL_FILE" help:"Path to a SQL file to prepend to the plan output."`
	ConcurrentlyPreSQL       string   `` /* 198-byte string literal not displayed */
	ConcurrentlyPreSQLFile   string   `` /* 146-byte string literal not displayed */
	DisableIndexConcurrently bool     `` /* 191-byte string literal not displayed */
}

type PlanResult

type PlanResult struct {
	SQL             string
	DisallowedDrops string
	Count           ObjectCount
}

PlanResult holds the result of a Plan operation.