pistachio

README

pistachio

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

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 ($PGSCHEMAS).
  -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.

  fmt <files> ... [flags]
    Format SQL file(s) into canonical form.

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-file pre.sql

apply

Apply the diff to the database.

pist apply schema.sql

# Multiple files
pist apply tables.sql views.sql

Use --pre-sql-file to run SQL before applying changes. Use --with-tx to wrap everything in a transaction.

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

dump

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

pist dump

fmt

Format SQL file(s) into the canonical form used by dump. Useful for normalizing hand-written schema files.

# Print formatted SQL to stdout
pist fmt schema.sql

# Format multiple files to stdout (combined)
pist fmt tables.sql views.sql

# Overwrite file(s) in place
pist fmt -w schema.sql

# Check if files are formatted (exit 1 if not, useful for CI)
pist fmt --check schema.sql

[!NOTE] dump output uses PostgreSQL's own formatting (e.g. pg_get_viewdef), while fmt normalizes through the pg_query parser. This means dump output may not pass fmt --check directly. Run fmt -w once after the initial dump to normalize, then use --check in CI going forward.

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 tables/views/enums

Use -I / --include to include only matching tables/views/enums, or -E / --exclude to exclude them. These flags are available on the dump, plan, and apply subcommands. Patterns support * and ? wildcards. Patterns match against object names only (not schema-qualified names).

# 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

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:rename-from <old_name> directives to rename objects instead of dropping and recreating them.

Tables, views, enums:

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

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

-- pist:rename-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:rename-from name
    display_name text NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    -- pist:rename-from users_name_key
    CONSTRAINT users_display_name_key UNIQUE (display_name)
);

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

-- pist:rename-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.

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
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 not tracked by pistachio because PostgreSQL auto-generates their names at creation time, making them unpredictable from the SQL file alone. Use explicit CONSTRAINT <name> clauses to ensure constraints are managed correctly.

Supported Objects

• Enum types (CREATE TYPE ... AS ENUM, ALTER TYPE ... ADD VALUE)
• Tables (including unlogged and partitioned tables)
• 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)
• Renaming (tables, views, enums, columns, constraints, foreign keys, indexes via -- pist:rename-from directive)
• Array, JSON, UUID, and other built-in types
• Quoted identifiers

Development

docker compose up -d
make test

Documentation

Index

• type ApplyOptions
• type Client
  • func NewClient(options *Options) *Client
  • func (client *Client) Apply(ctx context.Context, options *ApplyOptions, w io.Writer) error
  • func (client *Client) Dump(ctx context.Context, options *DumpOptions) (*DumpResult, error)
  • func (client *Client) Format(options *FmtOptions) (map[string]string, error)
  • func (client *Client) Plan(ctx context.Context, options *PlanOptions) (string, error)
• 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) MatchName(name string) bool
  • func (f *FilterOptions) ValidatePatterns() error
• type FmtOptions
• 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

Types

type ApplyOptions

type ApplyOptions struct {
	FilterOptions
	Files      []string `arg:"" help:"Path to the desired schema SQL file(s)."`
	PreSQLFile string   `type:"path" help:"Path to a SQL file to execute before applying changes."`
	WithTx     bool     `help:"Execute the pre-SQL and schema changes in a transaction."`
}

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

func (*Client) Dump

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

func (*Client) Format

func (client *Client) Format(options *FmtOptions) (map[string]string, error)

Format formats SQL files and returns the results. Each file is formatted independently and returned as a map from file path to formatted SQL.

func (*Client) Plan

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

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]
	OmitSchema bool
}

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" help:"Include only tables/views/enums matching the pattern (wildcard: *, ?)."`
	Exclude []string `short:"E" help:"Exclude tables/views/enums matching the pattern (wildcard: *, ?)."`
}

func (*FilterOptions) AfterApply

func (f *FilterOptions) AfterApply() error

func (*FilterOptions) MatchName

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

func (*FilterOptions) ValidatePatterns

func (f *FilterOptions) ValidatePatterns() error

type FmtOptions

type FmtOptions struct {
	Files []string `arg:"" help:"Path to the SQL file(s) to format."`
	Write bool     `short:"w" xor:"mode" help:"Write result to source file(s) instead of stdout."`
	Check bool     `xor:"mode" help:"Check if files are formatted. Exit with non-zero status if any file needs formatting."`
}

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:"PGSCHEMAS" 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
	Files      []string `arg:"" help:"Path to the desired schema SQL file(s)."`
	PreSQLFile string   `type:"path" help:"Path to a SQL file to execute before applying changes."`
}