graphile-migrate

MIT license

Opinionated SQL-powered productive roll-forward migration tool for PostgreSQL.

Crowd-funded open-source software

To help us develop this software sustainably, we ask all individuals and businesses that use it to help support its ongoing maintenance and development via sponsorship.

Click here to find out more about sponsors and sponsorship.

And please give some love to our featured sponsors 🤩:

<table><tr> <td align="center"><a href="https://gosteelhead.com/"><img src="https://graphile.org/images/sponsors/steelhead.svg" width="90" height="90" alt="Steelhead" /><br />Steelhead</a> *</td> </tr></table>

<em>* Sponsors the entire Graphile suite</em>

Why?

fast iteration speed — save a file and database is updated in milliseconds
roll-forward only — maintaining rollbacks is a chore, and in 10 years of API development I've never ran one in production
familiar — no custom DSL to learn, just use PostgreSQL syntax
fully functional — sending SQL commands directly to PostgreSQL means you can use all of PostgreSQL's features
complements PostGraphile — works with any application, but PostGraphile's watch mode means that the GraphQL schema is instantly regenerated (without server restart) whenever the database changes

Opinions

Local iteration should be easy and fast
Migrating should be fast
Once deployed, databases should be identical (including subtleties such as column order)
Migration software should not be tied to a particular application stack
Migrations should be written in SQL
Roll-forward only (production issues should be fixed via additional migrations, development can iterate current migration)
Once a migration is signed off (deployable) it should never be edited
Use PostgreSQL ;)
Development databases are cheap; can run multiple
Resetting development database is acceptable if absolutely necessary
Production databases are critical - NEVER RESET
Migrating data (as well as DDL) is acceptable, but should be kept to fast operations (or trigger a background job)
Migrations should automatically be wrapped in transactions by default
Migrations that require execution outside of a transaction (e.g. to enable augmenting non-DDL-safe things, such as ENUMs in PostgreSQL) should be explicitly marked
Migrations should not pollute PostgreSQL global settings (e.g. use SET LOCAL rather than SET)
Roles should be managed outside of migrations (since they can be shared between databases)
Certain schemas are managed by other tools and should not be interfered with; e.g. graphile_worker

Setup

In development, graphile-migrate uses two databases: the main database and a "shadow" database. The "shadow" database is used internally by graphile-migrate to test the consistency of the migrations and perform various other tasks.

In production, most users only run graphile-migrate migrate which operates solely on the main database - there is no need for a shadow database in production.

All members of your team should run the same PostgreSQL version to ensure that the shadow dump matches for everyone (one way of achieving this is through Docker, but that isn't required).

We recommend dumping your database schema with pg_dump after migrations are completed; you can see an example of this in Graphile Starter. Tracking this file in git will allow you to easily see the changes that different migrations are making, so you can be sure you're making the changes you intend to. We recommend that you dump the shadow database as it will be unaffected by the iteration you've been applying to your development database (which may have come out of sync - see 'Drift' below).

Getting started

These instructions are for starting a new database project with Graphile Migrate; if you already have a database schema, see Using Migrate with an existing database for some tips.

Create your database role (if desired), database and shadow database:

createuser --pwprompt dbowner
createdb myapp --owner=dbowner
createdb myapp_shadow --owner=dbowner

For an in depth-discussion on the different users and roles typically involved in database and migration management, please see issue #215.

Export your database URL, shadow database URL, and a "root" database URL which should be a superuser account connection to any other database (most PostgreSQL servers have a default database called postgres which is a good choice for this).

export DATABASE_URL="postgres://dbowner:password@localhost/myapp"
export SHADOW_DATABASE_URL="postgres://dbowner:password@localhost/myapp_shadow"

export ROOT_DATABASE_URL="postgres://postgres:postgres@localhost/postgres"

Your database URL is needed for most Graphile Migrate commands. The shadow database URL is needed for the development-only commands commit, uncommit and reset. The root database URL is needed to drop and recreate databases, i.e. for the reset command and for commands that call it (commit and uncommit, which reset the shadow database).

NOTE: you should not need the shadow database URL or root database URL in production (you only need the graphile-migrate migrate command in production) unless you have actions that need them.

Then run:

graphile-migrate init

At this point you should be ready to use Graphile Migrate. You may want to store these environmental variables to a file so you can easily source them (with the . command in bash, for example) in future:

. ./.env
graphile-migrate watch

Usage

Committed and current migrations

New migrations are composed within "the current migration". You will see this term used a lot. By default this is in the migrations/current.sql file, but if you like you may delete that file and instead create a migrations/current/ folder into which you may place numbered SQL files which together comprise "the current migration".

The current migration should be idempotent (this is your responsibility, see "Idempotency" below); i.e. it should be able to be ran multiple times and have the same result. This is critical for graphile-migrate watch, which is one of the main selling points of the project.