johngibb / migrate

Exceedingly simple Postgres migration tool

migrate

Migrate is an exceedingly simple Postgres database migration tool.

The simplicity is manifested in the following principles:

• There are no "down" migrations. Because migrations necessarily mutate state on a live server, you can't have a safe rollback button.
• Migrations are not automatically executed within a transaction. Transactions are expensive, often unnecessary, and prevent certain operations (e.g. create index concurrently). If the migration warrants transactional semantics, simply include begin; ... ; commit; within the source of your migration.
• Migrations are not skipped if they are added out of order with regard to their version number, unlike some alternative tools. This handles the case where a migration is added in a feature branch, and no longer has the highest version number when merged into master.

Install

$ go get -u github.com/johngibb/migrate/cmd/migrate

This will install migrate to your $GOPATH/bin directory.

Usage

Create a migration:

$ migrate create -src <folder> <migration name>:
    Creates a new migration file.
  -src string
      directory containing migration files (default ".")

View pending and applied migrations:

$ migrate status:
    Display a list of pending and applied migrations.
  -conn string
      postgres connection string
  -src string
      directory containing migration files (default ".")

Apply pending migrations:

$ migrate up -src <migrations folder> -conn <connection string> [-quiet]:
    Apply all pending migrations.
  -conn string
      postgres connection string
  -quiet
      only print errors
  -src string
      directory containing migration files (default ".")

Migrations

Migrations are written as plain SQL scripts. All statements should be terminated with a semicolon, as migrate will execute the script one statement at a time.

A simple migration to add a users table might look like:

create table users (id int, name text);

A more complicated migration that uses a transaction to create multiple tables and build an index might look like:

begin;
create table users (id int, name text);
create table groups (id int, name text);
create table users_groups (user_id int, group_id int);
commit;
create index concurrently on users (id);

Development

To run the full integration tests, you'll need to have Docker for Mac installed.

make install  // compiles the entire project
make test     // runs the unit tests on your host machine
make test-all // compiles and runs all tests (including integration
              // tests against a Postgres instance) using Docker

migrate's Issues

Reconsider Down Migrations

While I still agree they shouldn't generally be run in production, they're very handy when developing locally.

Add quite flag (`-q`) to `migrate up`

It would be great to be able to have migrate up only print output on error.

`migrate create` shouldn't require a connection string

It should be possible to run migrate create without a connection string since no interaction with the database will be performed.

Handle SIGINT (ctrl-c) Gracefully

migrate should explicitly handle receiving a SIGINT (ctrl-c) command.

Proposed behavior: finish running the current migration, then abort before running any more. If ctrl-c is received twice, abort immediately with an exit code.

Prevent Concurrent Execution of Migrations

We probably ought to obtain an advisory lock before running the migrations, to prevent someone from accidentally trying to migrate in parallel.

Prior art:
Rails (although might be worth incorporating the schema name, per rails/rails#31627).

Store Date at which Migration Was Applied

Add a date column to the migrations table to record the time at which each migration was applied.

@jeffreylo: do you think this would be a worthwhile feature?