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
                              ($DATABASE_URL)
      --password=STRING       PostgreSQL password ($PGPASSWORD).
  -n, --schemas=public,...    Schemas to inspect and modify ($PGSCHEMAS).
      --version

Commands:
  apply <file> [flags]
    Apply schema changes to the database.

  plan <file> [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 a schema file against the current database and print the SQL needed to reconcile them.

pist plan schema.sql

apply

Apply the diff to the database.

pist apply schema.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

Example

Create a schema file:

CREATE TABLE public.users (
    id integer NOT NULL,
    name text 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

Supported Objects

• 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)
• 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) (string, error)
  • func (client *Client) Plan(ctx context.Context, options *PlanOptions) (string, error)
• type DumpOptions
• type Options
• type PlanOptions

Types

type ApplyOptions

type ApplyOptions struct {
	File       string `arg:"" help:"Path to the desired schema SQL file."`
	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) (string, error)

func (*Client) Plan

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

type DumpOptions

type DumpOptions struct{}

type Options

type Options struct {
	ConnString string   `` /* 194-byte string literal not displayed */
	Password   string   `env:"PGPASSWORD" help:"PostgreSQL password."`
	Schemas    []string `short:"n" env:"PGSCHEMAS" default:"public" help:"Schemas to inspect and modify."`
}

type PlanOptions

type PlanOptions struct {
	File string `arg:"" help:"Path to the desired schema SQL file."`
}