🐽 pgmigrate

pgmigrate is a modern Postgres migrations CLI and golang library. It is designed for use by high-velocity teams who practice continuous deployment. The goal is to make migrations as simple and reliable as possible.

Major features

• Applies any previously-unapplied migrations, in ascending filename order — that's it.
• Each migration is applied within a transaction.
• Only "up" migrations, no "down" migrations.
• Uses Postgres advisory locks so it's safe to run in parallel.
• All functionality is available as a golang library, a docker container, and as a static cli binary
• Can dump your database schema and data from arbitrary tables to a single migration file
  • This lets you squash migrations
  • This lets you prevent schema conflicts in CI
  • The dumped sql is human readable
  • The dumping process is roundtrip-stable (dumping > applying > dumping gives you the same result)
• Supports a shared configuration file that you can commit to your git repo
• CLI contains "ops" commands for manually modifying migration state in your database, for those rare occasions when something goes wrong in prod.
• Compatible with pgtestdb so database-backed tests are very fast.

Documentation

• The primary documentation is this Github README, https://github.com/peterldowns/pgmigrate.
• The code itself is supposed to be well-organized, and each function has a meaningful docstring, so you should be able to explore it quite easily using an LSP plugin or by reading the code in Github or in your local editor.
• You may also refer to the go.dev docs, pkg.go.dev/github.com/peterldowns/pgmigrate.

Quickstart Example

Please visit the ./example directory for a working example of how to use pgmigrate. This example demonstrates:

• Using the CLI
• Creating and applying new migrations
• Dumping your schema to a file
• Using pgmigrate as an embedded library to run migrations on startup
• Writing extremely fast database-backed tests

CLI

Install

Homebrew:

# install it
brew install peterldowns/tap/pgmigrate

Download a binary:

Visit the latest Github release and pick the appropriate binary. Or, click one of the shortcuts here:

• darwin-amd64
• darwin-arm64
• linux-amd64
• linux-arm64

Nix (flakes):

# run it
nix run github:peterldowns/pgmigrate -- --help
# install it
nix profile install --refresh github:peterldowns/pgmigrate

Docker:

The prebuilt docker container is ghcr.io/peterldowns/pgmigrate and each version is properly tagged. You may reference this in a kubernetes config as an init container.

To run the pgmigrate cli:

# The default CMD is "pgmigrate" which just shows the help screen.
docker run -it --rm ghcr.io/peterldowns/pgmigrate:latest
# To actually run migrations, you'll want to make sure the container can access
# your database and migrations directory and specify a command. To access a
# database running on the host, use `host.docker.internal` instead of
# `localhost` in the connection string:
docker run -it --rm \
  --volume $(pwd)//migrations:/migrations \
  --env PGM_MIGRATIONS=/migrations \
  --env PGM_DATABASE='postgresql://postgres:password@host.docker.internal:5433/postgres' \
  ghcr.io/peterldowns/pgmigrate:latest \
  pgmigrate plan

Golang:

I recommend installing a different way, since the installed binary will not contain version information.

# run it
go run github.com/peterldowns/pgmigrate/cmd/pgmigrate@latest --help
# install it
go install github.com/peterldowns/pgmigrate/cmd/pgmigrate@latest

Configuration

pgmigrate reads its configuration from cli flags, environment variables, and a configuration file, in that order.

pgmigrate will look in the following locations for a configuration file:

• If you passed --configfile <aaa>, then it reads <aaa>
• If you defined PGM_CONFIGFILE=<bbb>, then it reads <bbb>
• If your current directory has a .pgmigrate.yaml file, it reads $(pwd)/.pgmigrate.yaml
• If the root of your current git repo has a .pgmigrate.yaml file, it reads $(git_repo_root)/.pgmigrate.yaml

Here's an example configuration file. All keys are optional, an empty file is also a valid configuration.

# connection string to a database to manage
database: "postgres://postgres:password@localhost:5433/postgres"
# path to the folder of migration files. if this is relative,
# it is treated as relative to wherever the "pgmigrate" command
# is invoked, NOT as relative to this config file.
migrations: "./tmp/migrations"
# the name of the table to use for storing migration records.  you can give
# this in the form "table" to use your database's default schema, or you can
# give this in the form "schema.table" to explicitly set the schema.
table_name: "custom_schema.custom_table"
# this key configures the "dump" command.
schema:
  # the name of the schema to dump, defaults to "public"
  name: "public"
  # the file to which to write the dump, defaults to "-" (stdout)
  # if this is relative, it is treated as relative to wherever the
  # "pgmigrate" command is invoked, NOT as relative to this config file.
  file: "./schema.sql"
  # any explicit dependencies between database objects that are
  # necessary for the dumped schema to apply successfully.
  dependencies:
    some_view: # depends on
      - some_function
      - some_table
    some_table: # depends on
      - another_table
  # any tables for which the dump should contain INSERT statements to create
  # actual data/rows. this is useful for enums or other tables full of
  # ~constants.
  data:
    - name: "%_enum" # accepts wildcards using SQL query syntax
    - name: "my_example_table" # can also be a literal
      # if not specified, defaults to "*"
      columns:
        - "value"
        - "comment"
      # a valid SQL order clause to use to order the rows in the INSERT
      # statement.
      order_by: "value asc"

Usage

The CLI ships with documentation and examples built in, please see pgmigrate help and pgmigrate help <command> for more details.

# pgmigrate --help
Docs: https://github.com/peterldowns/pgmigrate

Usage:
  pgmigrate [flags]
  pgmigrate [command]

Examples:
  # Preview and then apply migrations
  pgmigrate plan     # Preview which migrations would be applied
  pgmigrate migrate  # Apply any previously-unapplied migrations
  pgmigrate verify   # Verify that migrations have been applied correctly
  pgmigrate applied  # Show all previously-applied migrations
  
  # Dump the current schema to a file
  pgmigrate dump --out schema.sql

Migrating:
  applied     Show all previously-applied migrations
  migrate     Apply any previously-unapplied migrations
  plan        Preview which migrations would be applied
  verify      Verify that migrations have been applied correctly

Operations:
  ops         Perform manual operations on migration records
  version     Print the version of this binary

Development:
  config      Print the current configuration / settings
  dump        Dump the database schema as a single migration file
  help        Help about any command
  new         generate the name of the next migration file based on the current sequence prefix

Flags:
      --configfile string   [PGM_CONFIGFILE] a path to a configuration file
  -d, --database string     [PGM_DATABASE] a 'postgres://...' connection string
  -h, --help                help for pgmigrate
      --log-format string   [PGM_LOGFORMAT] 'text' or 'json', the log line format (default 'text')
  -m, --migrations string   [PGM_MIGRATIONS] a path to a directory containing *.sql migrations
      --table-name string   [PGM_TABLENAME] the table name to use to store migration records (default 'public.pgmigrate_migrations')
  -v, --version             version for pgmigrate

Use "pgmigrate [command] --help" for more information about a command.

Library

Install

• requires golang 1.18+ because it uses generics.
• only depends on stdlib; all dependencies in the go.mod are for tests.

# library
go get github.com/peterldowns/pgmigrate@latest

Usage

All of the methods available in the CLI are equivalently named and available in the library. Please read the cli help with pgmigrate help <command> or read the the go.dev docs at pkg.go.dev/github.com/peterldowns/pgmigrate.

FAQ

How does it work?

pgmigrate has the following invariants, rules, and behavior:

• A migration is a file whose name ends in .sql. The part before the extension is its unique ID.
• All migrations are "up" migrations, there is no such thing as a "down" migration.
• The migrations table is a table that pgmigrate uses to track which migrations have been applied. It has the following schema:
  • id (text not null): the ID of the migration
  • checksum (text not null): the MD5() hash of the contents of the migration when it was applied.
  • execution_time_in_millis (integer not null): how long it took to apply the migration, in milliseconds.
  • applied_at (timestamp with time zone not null): the time at which the migration was finished appl