graphile-migrate

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

Crowd-funded open-source software

To help us develop this software sustainably under the MIT license, 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 🤩:

Surge *

Netflix *

Qwick *

The Guild *

Chad Furman *

Fanatics *

Dovetail *

Enzuzo *

Stellate *

• Sponsors the entire Graphile suite

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 appuser
createdb myapp --owner=appuser
createdb myapp_shadow --owner=appuser

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://appuser:password@localhost/myapp"
export SHADOW_DATABASE_URL="postgres://appuser: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.

graphile-migrate

graphile-migrate <command>

Commands:
  graphile-migrate init            Initializes a graphile-migrate project by
                                   creating a `.gmrc` file and `migrations`
                                   folder.
  graphile-migrate migrate         Runs any un-executed committed migrations.
                                   Does NOT run the current migration. For use
                                   in production and development.
  graphile-migrate watch           Runs any un-executed committed migrations and
                                   then runs and watches the current migration,
                                   re-running it on any change. For development.
  graphile-migrate commit          Commits the current migration into the
                                   `committed/` folder, resetting the current
                                   migration. Resets the shadow database.
  graphile-migrate uncommit        This command is useful in development if you
                                   need to modify your latest commit before you
                                   push/merge it, or if other DB commits have
                                   been made by other developers and you need to
                                   'rebase' your migration onto theirs. Moves
                                   the latest commit out of the committed
                                   migrations folder and back to the current
                                   migration (assuming the current migration is
                                   empty-ish). Removes the migration tracking
                                   entry from ONLY the local database. Do not
                                   use after other databases have executed this
                                   committed migration otherwise they will fall
                                   out of sync. Assuming nothing else has
                                   changed, `graphile-migrate uncommit &&
                                   graphile-migrate commit` should result in the
                                   exact same hash. Development only, and liable
                                   to cause conflicts with other developers - be
                                   careful.
  graphile-migrate status          Exits with a bitmap status code indicating
                                   statuses:

                                   - 1 if there are committed migrations that
                                   have not been executed yet (requires DB
                                   connection)
                                   - 2 if the current migration is non-empty
                                   (ignoring comments)

                                   If both of the above are true then the output
                                   status will be 3 (1+2). If neither
                                   are true, exit status will be 0 (success).
                                   Additional messages may also be output.
  graphile-migrate reset           Drops and re-creates the database, re-running
                                   all committed migrations from the start.
                                   **HIGHLY DESTRUCTIVE**.
  graphile-migrate compile [file]  Compiles a SQL file, inserting all the
                                   placeholders and returning the result to
                                   STDOUT
  graphile-migrate run [file]      Compiles a SQL file, inserting all the
                                   placeholders, and then runs it against the
                                   database. Useful for seeding. If called from
                                   an action will automatically run against the
                                   same database (via GM_DBURL envvar) unless
                                   --shadow or --rootDatabase are supplied.
  graphile-migrate completion      Generate shell completion script.

Options:
  --help        Show help                                              [boolean]
  --config, -c  Optional path to gmrc file   [string]...