pistachio

README

Declarative schema management tool for PostgreSQL with a Terraform-like plan/apply workflow, built on pg_query_go. Define the desired schema in SQL; pistachio generates the DDL diff.

[!WARNING] Changed in 1.52.0: Options such as -c and -n now go after the subcommand, not before it.

pista -n myschema -c postgres://user@host/db dump   # before 1.52.0
pista dump -n myschema -c postgres://user@host/db   # 1.52.0 and later

-C / --config, --pager and --version are unchanged, and so are the environment variables and the config file.

Documentation | Getting Started | Commands | Supported objects | Design and scope

Workflow

Installation

Homebrew

brew install winebarrel/pistachio/pistachio

mise

mise use github:winebarrel/pistachio            # latest
mise use github:winebarrel/pistachio@<version>  # a specific version

Download binary

Download the latest binary from Releases.

OS	Arch
macOS	amd64, arm64
Linux	amd64, arm64
Windows	amd64

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

See Try it with Docker.

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

See what each statement costs before it runs:

$ pista plan --explain schema.sql
-- rewrite, blocks reads and writes: public.orders (~2,000,000 rows, 210 MB, as of 2026-09-09, 3 indexes rebuilt)
ALTER TABLE public.orders ALTER COLUMN amount SET DATA TYPE numeric(12,2);
-- scan, blocks writes: public.orders (~2,000,000 rows, 210 MB, as of 2026-09-09), public.customers (~50,000 rows, 6280 kB, as of 2026-07-21)
ALTER TABLE ONLY public.orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES public.customers (id);
-- scan, blocks nothing: public.orders (~2,000,000 rows, 210 MB, as of 2026-09-09)
CREATE INDEX CONCURRENTLY orders_created_at_idx ON public.orders USING btree (created_at);

The rows and bytes are the estimates the last VACUUM or ANALYZE wrote, with the date it wrote them, so the comment costs no read of the table. See Explaining a plan.

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

Lay the files out:

pista fmt ./schema/*.sql           # rewrite them in place
pista fmt --check ./schema/*.sql   # exit 2 when a file is not formatted

Or diff two schema files without a database:

pista diff old.sql new.sql         # print the DDL that takes old.sql to new.sql
pista diff --check old.sql new.sql # exit 2 when the two differ

Or two revisions of one schema file, read out of git:

pista diff --git HEAD^..HEAD schema.sql          # what the last commit changed
pista diff --git origin/main...HEAD schema.sql   # what this branch changes

The output carries the same schema DDL as plan, so diffing two revisions of a schema file previews a migration in CI. See Diffing schema files.

Or read the schema as JSON:

pista parse ./schema/*.sql         # print the objects the files declare as JSON

It uses the same parser plan and apply use and reads no database, so another tool can consume the schema. pista dump --json writes the same shape, read from the database. See Parsing schema files.

Development

docker compose up -d
make test

See Contributing for the test suites and the PostgreSQL version matrix.

Related projects

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

Documentation

Index

• Constants
• func YAMLConfig(r io.Reader) (kong.Resolver, error)
• type ApplyFromOptions
• 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) ApplyFrom(ctx context.Context, options *ApplyFromOptions, w io.Writer) (*ApplyResult, error)
  • func (client *Client) ConnInfoComment() (string, error)
  • func (client *Client) Diff(options *DiffOptions) (*PlanResult, error)
  • func (client *Client) Dump(ctx context.Context, options *DumpOptions) (*DumpResult, error)
  • func (client *Client) ParseSchema(files []string) (*parser.ParseResult, error)
  • func (client *Client) Plan(ctx context.Context, options *PlanOptions) (*PlanResult, error)
• type ConnOptions
• type DiffOptions
• type DropPolicy
  • func (p *DropPolicy) IsDropAllowed(objectType string) bool
• type DumpOptions
• type DumpResult
  • func (r *DumpResult) Document() *parser.ParseResult
  • func (r *DumpResult) Files() map[string]string
  • func (r *DumpResult) String() string
• type ExecOptions
• 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
• type PlanOptions
• type PlanResult
• type ScopeOptions
  • func (o *ScopeOptions) AfterApply() error
  • func (o *ScopeOptions) RemapSchema(schema string) string
  • func (o *ScopeOptions) ReverseRemapSchema(schema string) string
  • func (o *ScopeOptions) ValidateSchemaMap() error
• type UnsignedDuration
  • func (d *UnsignedDuration) Decode(ctx *kong.DecodeContext) error
  • func (d *UnsignedDuration) UnmarshalText(text []byte) error

Constants

const DefaultSearchPath = "public"

DefaultSearchPath is the search_path every connection is opened with unless --search-path says otherwise. The catalog reads the schema through pg_get_viewdef, pg_get_constraintdef, pg_get_expr and format_type, each of which drops the schema from an object the session can reach unqualified, so this value decides what dump writes and what the diff compares. Left to a server-side "ALTER ROLE ... SET search_path", one database would read differently depending on who connected.

PostgreSQL's own default, `"$user", public`, would leave the same gap: a schema named after the connecting role sits on the path, so a dump taken as that role writes its objects without a schema and another role cannot reload it. The role that runs migrations is often not the role the application connects as, so this drops the "$user" entry and keeps public alone. Pre-SQL runs under this setting.

kong defaults the Options field to this, and connect falls back to it, so a library caller that builds Options directly connects the same way the CLI does.

Functions

func YAMLConfig

func YAMLConfig(r io.Reader) (kong.Resolver, error)

YAMLConfig is a kong.ConfigurationLoader that reads options from a YAML file. Keys must exactly match CLI flag names (e.g. "conn-string"); snake_case or camelCase variants are not accepted. Keys that do not match any flag are rejected. Values only apply to flags not set on the command line; a config file passed with --config takes precedence over environment variables.

Types

type ApplyFromOptions

type ApplyFromOptions struct {
	ExecOptions
	PlanFile string `arg:"" type:"path" help:"Path to the plan file written by plan --out."`
	// No env var: --force stands down the one check the plan file is for, and
	// a variable exported once in a CI environment would stand it down for
	// every run there. It is typed where it is meant.
	Force bool `` /* 140-byte string literal not displayed */
}

type ApplyOptions

type ApplyOptions struct {
	DropPolicy
	Files                    []string `arg:"" help:"Path to the desired schema SQL file(s)."`
	PreSQL                   string   `xor:"pre-sql" env:"PISTA_PRE_SQL" help:"SQL to execute before applying changes."`
	PreSQLFile               string   `type:"path" xor:"pre-sql" env:"PISTA_PRE_SQL_FILE" help:"Path to a SQL file to execute before applying changes."`
	ConcurrentlyPreSQL       string   `` /* 218-byte string literal not displayed */
	ConcurrentlyPreSQLFile   string   `` /* 144-byte string literal not displayed */
	DisableIndexConcurrently bool     `` /* 155-byte string literal not displayed */
	ForceIndexConcurrently   bool     `` /* 145-byte string literal not displayed */
	BulkAlter                bool     `` /* 199-byte string literal not displayed */
	AssumeValidated          bool     `` /* 167-byte string literal not displayed */
	ExecOptions
}

type ApplyResult

type ApplyResult struct {
	Count           ObjectCount
	DisallowedDrops string
	Ignored         string
	// Applied reports whether any schema change was actually applied: schema
	// DDL or an executed -- pista:execute statement. Pre-SQL,
	// concurrently-pre-SQL, transaction control, search_path setup, and
	// -- pista:execute directives skipped by their check SQL do not count.
	Applied bool
	// Duration is the elapsed time of the apply phase: every statement sent to
	// the database (transaction BEGIN/COMMIT, pre-SQL, schema DDL, search_path
	// setup, -- pista:execute check SQL, and execute statements) plus the time
	// writing them to the output writer. It excludes connection setup and diff
	// computation, and is zero unless Applied is true. With a fast writer it is
	// dominated by database execution time.
	Duration time.Duration
}

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

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

ApplyFrom executes a plan file. The statements are the plan's, decided when it was written, so nothing is diffed here: the database is read only to check that it is still the one the plan was computed against.

The scope comes from the file rather than from the command line. The state hash means nothing unless it is recomputed over the same read, and a flag that quietly narrowed the read here would report drift that is not there.

func (*Client) ConnInfoComment

func (client *Client) ConnInfoComment() (string, error)

ConnInfoComment returns a SQL comment describing the connection target (host/port/dbname/user) for inclusion at the top of plan/apply/dump output. The password is intentionally never included.

TCP connections render as a libpq URI (postgres://user@host:port/dbname). IPv6 hosts are bracketed via net.JoinHostPort; user and dbname are URL-escaped via net/url so identifiers with URI-meaningful characters (including '/' in the dbname) round-trip safely; Path holds the decoded form and RawPath the encoded form, so url.URL.String() uses RawPath when the default encoding would differ. libpq unix-socket connections (host starts with "/") render as a keyword/value string ("host=/path dbname=db user=u") instead; percent-encoding the socket path into the URI host component would be unreadable in a comment.

func (*Client) Diff

func (client *Client) Diff(options *DiffOptions) (*PlanResult, error)

Diff diffs two schemas without a database: the first stands in for the current state the catalog gives Plan, the second is the desired state. Without --git the two are the files named on the command line; with it both come out of a git repository. The output is the DDL that takes the first schema to the second, and nothing else: -- pista:execute statements are left out.

func (*Client) Dump

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

func (*Client) ParseSchema

func (client *Client) ParseSchema(files []string) (*parser.ParseResult, error)

ParseSchema parses the desired schema files without connecting to a database. Unqualified names are qualified with the first schema in Options.Schemas, the same way Plan and Apply read their input.

func (*Client) Plan

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

type ConnOptions

type ConnOptions struct {
	ConnString string `` /* 196-byte string literal not displayed */
	DBName     string `name:"dbname" short:"d" env:"PISTA_DBNAME" help:"PostgreSQL database name. Overrides the dbname in --conn-string."`
	Password   string `env:"PISTA_PASSWORD" help:"PostgreSQL password."`
}

ConnOptions is how a command reaches the database. It decides nothing about what is read once connected, so a command that takes its scope from somewhere else than the command line (apply-from, which reads it from the plan file) embeds this alone.

type DiffOptions

type DiffOptions struct {
	FilterOptions
	DropPolicy
	Files                    []string `arg:"" help:"Paths to the schema SQL files. Without --git, exactly two: the current schema and the desired schema."`
	Git                      string   `` /* 207-byte string literal not displayed */
	DisableIndexConcurrently bool     `` /* 155-byte string literal not displayed */
	ForceIndexConcurrently   bool     `` /* 137-byte string literal not displayed */
	BulkAlter                bool     `` /* 199-byte string literal not displayed */
	AssumeValidated          bool     `` /* 167-byte string literal not displayed */
}

type DropPolicy

type DropPolicy struct {
	AllowDrop []string `` /* 219-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 {
	Split      string `` /* 129-byte string literal not displayed */
	OmitSchema bool   `help:"Omit schema name from the dump output."`
	NoReadOnly bool   `env:"PISTA_NO_READ_ONLY" help:"Open the database connection read-write. By default dump uses a read-only connection."`
	NoFormat   bool   `` /* 127-byte string literal not displayed */
	// JSON writes the dump as JSON rather than SQL, in the shape `pista parse`
	// writes, so the same schema describes both. The flags that lay SQL out have nothing to
	// change in it, so kong refuses them alongside it.
	JSON bool `xor:"json-split,json-no-format,json-explain" env:"PISTA_DUMP_JSON" help:"Write the dump as JSON instead of SQL."`
	// Explain writes the size estimate of each table, materialized view and
	// index into the comment above it. The JSON carries no comment to hold it.
	Explain bool `` /* 136-byte string literal not displayed */
}

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]
	CompositeTypes *orderedmap.Map[string, *model.CompositeType]
	Sequences      *orderedmap.Map[string, *model.Sequence]
	Routines       *orderedmap.Map[string, *model.Routine]
	OmitSchema     bool
	NoFormat       bool
	Count          ObjectCount
}

func (*DumpResult) Document

func (r *DumpResult) Document() *parser.ParseResult

Document returns what the dump holds in the shape `pista parse` writes, so one JSON Schema describes both. The accessors carry --omit-schema, and a database holds no `-- pista:execute` statements, so that half is empty.

func (*DumpResult) Files

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

func (*DumpResult) String

func (r *DumpResult) String() string

type ExecOptions

type ExecOptions struct {
	WithTx bool `xor:"tx-mode,tx-choice" env:"PISTA_WITH_TX" help:"Execute pre-SQL and schema changes in a transaction."`
	TryTx  bool `` /* 196-byte string literal not displayed */
	Timing bool `` /* 155-byte string literal not displayed */
	// Exclusive and ExclusiveWait guard the database rather than one apply, so
	// apply-from takes them too: a plan file executed beside another apply
	// would be checked against a state that apply is still changing.
	Exclusive bool `` /* 159-byte string literal not displayed */
	// ExclusiveWait enables the same mutual exclusion as Exclusive and waits
	// for the other apply instead of failing. A pointer because 0 is a valid
	// value (wait without limit) and must be distinguishable from "not set".
	// The type rejects a negative value at parse time.
	ExclusiveWait *UnsignedDuration `` /* 179-byte string literal not displayed */
	// WaitWriter receives the line that says apply is waiting for another
	// exclusive apply. The CLI buffers the output writer until the apply is
	// done, which would hold that line back until the wait it announces is
	// over, so it passes the terminal here. nil writes the line to the
	// output writer.
	WaitWriter io.Writer `kong:"-"`
}

ExecOptions decides how the statements are run rather than what they are, so apply and apply-from, which takes its statements from a plan file, share it.

type FilterOptions

type FilterOptions struct {
	Include []string `` /* 196-byte string literal not displayed */
	Exclude []string `` /* 191-byte string literal not displayed */
	Enable  []string `` /* 155-byte string literal not displayed */
	Disable []string `` /* 153-byte string literal not displayed */
	// ManageRoutine opts into functions and procedures. They are unmanaged by
	// default: a schema that has been maintained with -- pista:execute holds
	// routines the desired schema does not declare, and reading pg_proc
	// unasked would report every one of them as a drop.
	ManageRoutine bool `` /* 152-byte string literal not displayed */
	// ManageStorageParam opts into the storage parameters of a table and a
	// materialized view, the WITH clause. They are unmanaged by default: the
	// autovacuum settings a relation is tuned with are usually set on the
	// database, not written in the schema file, and reading them unasked would
	// RESET every parameter the desired schema does not name. A plain view's
	// two parameters are managed either way; they decide what the view means
	// rather than how it is stored.
	ManageStorageParam bool `` /* 324-byte string literal not displayed */
	// SkipPartitionChild leaves the partitions of a partitioned table
	// unmanaged. Where another tool creates them, their names follow no
	// pattern --exclude can state, and a schema file that declares the parent
	// alone would plan a DROP for each one.
	SkipPartitionChild bool `` /* 203-byte string literal not displayed */
}

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 `json:"schemas"`
	Tables         int      `json:"tables"`
	Views          int      `json:"views"`
	Enums          int      `json:"enums"`
	Domains        int      `json:"domains"`
	CompositeTypes int      `json:"composite_types"`
	Sequences      int      `json:"sequences"`
	// Routines is nil unless --manage-routine is set. A nil value leaves the
	// slot out of Summary entirely, so the line reads exactly as it did
	// before routines were managed.
	Routines *int `json:"routines"`
}

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 {
	ConnOptions
	ScopeOptions
}

type PlanOptions

type PlanOptions struct {
	DropPolicy
	Files                    []string `arg:"" help:"Path to the desired schema SQL file(s)."`
	PreSQL                   string   `xor:"pre-sql" env:"PISTA_PRE_SQL" help:"SQL to prepend to the plan output."`
	PreSQLFile               string   `type:"path" xor:"pre-sql" env:"PISTA_PRE_SQL_FILE" help:"Path to a SQL file to prepend to the plan output."`
	ConcurrentlyPreSQL       string   `` /* 192-byte string literal not displayed */
	ConcurrentlyPreSQLFile   string   `` /* 140-byte string literal not displayed */
	DisableIndexConcurrently bool     `` /* 155-byte string literal not displayed */
	ForceIndexConcurrently   bool     `` /* 137-byte string literal not displayed */
	BulkAlter                bool     `` /* 199-byte string literal not displayed */
	AssumeValidated          bool     `` /* 167-byte string literal not displayed */
	NoReadOnly               bool     `env:"PISTA_NO_READ_ONLY" help:"Open the database connection read-write. By default plan uses a read-only connection."`
	Explain                  bool     `` /* 174-byte string literal not displayed */
	Out                      string   `` /* 258-byte string literal not displayed */
}

type PlanResult

type PlanResult struct {
	SQL             string
	DisallowedDrops string
	Ignored         string
	Count           ObjectCount
	// HasChanges is true when the plan contains executable statements
	// (DDL or execute directives). Suppressed drops do not count.
	HasChanges bool
}

PlanResult holds the result of a Plan operation.

type ScopeOptions

type ScopeOptions struct {
	Schemas   []string          `short:"n" env:"PISTA_SCHEMAS" default:"public" json:"schemas" help:"Schemas to inspect and modify."`
	SchemaMap map[string]string `short:"m" json:"schema_map" help:"Schema name mapping (e.g. -m old=new)."`
	// SearchPath is a pointer so that an empty value is a path of its own,
	// under which the catalog qualifies everything, rather than a request for
	// the default. nil means the default.
	SearchPath *string `` /* 266-byte string literal not displayed */
	FilterOptions
}

ScopeOptions decides the shape of the current-side schema a run reads: which schemas the catalog is read from, how its output is qualified, and which of the objects it returns are managed. Two runs that agree on every field here read the same model out of an unchanged database, which is what the plan file's state hash compares, so a plan file records this struct whole and apply-from restores it rather than taking it from the command line.

A new option that changes what the diff reads belongs here. One that only shapes the statements the diff writes (--bulk-alter, the pre-SQL, the drop policy) does not: the plan file holds those statements already.

No connection field belongs here either. The struct is written to the plan file as it stands, and a password cannot be written to a file it never reaches.

func (*ScopeOptions) AfterApply

func (o *ScopeOptions) AfterApply() error

AfterApply trims each schema name, since -n 'public, billing' reaches kong as ["public", " billing"] and -m 'old=new; other=third' with " other" as the second source, then validates the schema map. Two sources that differ only in whitespace are an error rather than one entry with either destination.

func (*ScopeOptions) RemapSchema

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

func (*ScopeOptions) ReverseRemapSchema

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

func (*ScopeOptions) ValidateSchemaMap

func (o *ScopeOptions) ValidateSchemaMap() error

ValidateSchemaMap rejects an empty source or destination, which model.Ident would drop and leave the object unqualified, and two sources mapping to one destination, which would leave ReverseRemapSchema no way to pick between them. The sources are walked in name order so the pair the message names is the same on every run; a Go map iterates in a random one, and three sources on one destination then produced a different message each time the same command was run.

type UnsignedDuration

type UnsignedDuration time.Duration

UnsignedDuration is a time.Duration that rejects a negative value at parse time, so a flag, env var or config key using it cannot be set below zero.

func (*UnsignedDuration) Decode

func (d *UnsignedDuration) Decode(ctx *kong.DecodeContext) error

Decode reads the value kong hands over. The command line and an environment variable hand over text. The config file hands over what YAML read, so a bare 0, the value that waits without limit, arrives as a number and is read as its text.

func (*UnsignedDuration) UnmarshalText

func (d *UnsignedDuration) UnmarshalText(text []byte) error