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.

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

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

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

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.

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 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) ConnInfoComment() (string, 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
• type UnsignedDuration
  • 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 ApplyOptions

type ApplyOptions struct {
	FilterOptions
	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 */
	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 */
	DisableIndexConcurrently bool     `` /* 155-byte string literal not displayed */
	ForceIndexConcurrently   bool     `` /* 180-byte string literal not displayed */
	BulkAlter                bool     `` /* 199-byte string literal not displayed */
	AssumeValidated          bool     `` /* 167-byte string literal not displayed */
	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 */
}

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) 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) 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 `` /* 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 {
	FilterOptions
	Split      string `` /* 137-byte string literal not displayed */
	OmitSchema bool   `help:"Omit schema name from the dump output."`
	SortByDeps bool   `` /* 169-byte string literal not displayed */
	NoReadOnly bool   `env:"PISTA_NO_READ_ONLY" help:"Open the database connection read-write. By default dump uses a read-only connection."`
	NoFormat   bool   `env:"PISTA_NO_FORMAT" help:"Write the dump as the model renders it, without the layout pista fmt applies."`
}

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
	SortByDeps     bool
	NoFormat       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 `` /* 181-byte string literal not displayed */
	Exclude []string `` /* 176-byte string literal not displayed */
	Enable  []string `` /* 141-byte string literal not displayed */
	Disable []string `` /* 138-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 `` /* 130-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 `` /* 296-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 `` /* 175-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
	Tables         int
	Views          int
	Enums          int
	Domains        int
	CompositeTypes int
	Sequences      int
	// 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
}

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            `` /* 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."`
	Schemas    []string          `short:"n" env:"PISTA_SCHEMAS" default:"public" help:"Schemas to inspect and modify."`
	SchemaMap  map[string]string `short:"m" 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 `` /* 247-byte string literal not displayed */
}

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

ValidateSchemaMap rejects 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 PlanOptions

type PlanOptions struct {
	FilterOptions
	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 */
}

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 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. kong decodes it through UnmarshalText like any other duration.

func (*UnsignedDuration) UnmarshalText

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