Automated migrations for Postgres

Install

$ npm install --save migratio

Usage

Create directory migrations with migrations and use migratio:

const migratio = require('migratio');

await migratio.current({
	connection: 'postgres://localhost/db',
	verbose: true
});
//   000005-images.sql  (batch:3)
//   000004-files.sql   (batch:3)
//   000003-stats.sql   (batch:3)

await migratio.up({
	connection: 'postgres://localhost/db',
	verbose: true
});
// ↑ 000006-posts.sql   (batch:4)

await migratio.down({
	connection: 'postgres://localhost/db',
	verbose: true
});
// ↓ 000005-images.sql  (batch:3)
// ↓ 000004-files.sql   (batch:3)
// ↓ 000003-stats.sql   (batch:3)

Migrations format

All migrations files should start with revision (digits) followed by - and name of migration. For example 000001-init.js and 000002-users.sql is valid file names.

Migrations will be applied in order of numbers in front of filename.

Migration process is running in transaction with lock on migrations table. If one of migrations failed – all batch will be reverted.

JavaScript format

Migration file with extension .js is treated as module, that exports two functions:

• up – contains code to apply migration
• down – contains code to revert migration

These functions must return Promise. If these functions are generators, they will be wrapped in co.

exports.up = async function (db) {
	await db.query(`
		CREATE TABLE test (
			id serial PRIMARY KEY
		)
	`);
};

exports.down = async function (db) {
	await db.query(`
		DROP TABLE IF EXISTS test;
	`);
};

SQL format

Migration file with extension .sql is treated as file with SQL instructions. Instructions to apply migration should be placed after -- +migrate Up and instructions to revert migration should be placed after -- +migrate Down.

-- +migrate Up

CREATE TABLE test (
	id serial PRIMARY KEY
);

-- +migrate Down

DROP TABLE IF EXISTS test;

Configuring defaults

Migratio supports overriding default values with migraio section in package.json:

{
	"migratio": {
		"directory": "migrations",
		"tableName": "migrations"
	}
}

API

up(options)

Applies all migrations from current to latest available.

options

connection

Type: string
Default: process.env.DATABASE_URL

Connection string to Postgres database.

db

Type: Database

Database object. Will be used instead of connection.

directory

Type: string
Default: ./migrations

Directory with migrations.

revision

Type: Number
Default: Infinity

Latest revision to up to.

unsafe

Type: Boolean
Default: false

Disables meta-table locking.

verbose

Type: boolean
Default: false

Enables output.

tableName

Type: string
Default: migratio

Table in which migratio will store metadata.

down(options)

Rollbacks migrations in current batch.

options

connection

Type: string
Default: process.env.DATABASE_URL

Connection string to Postgres database.

directory

Type: string
Default: ./migrations

Directory with migrations.

unsafe

Type: Boolean
Default: false

Disables meta-table locking.

verbose

Type: boolean
Default: false

Enables output.

tableName

Type: string
Default: migratio

Table in which migratio will store metadata.

current(options)

Shows current batch.

connection

Type: string
Default: process.env.DATABASE_URL

Connection string to Postgres database.

unsafe

Type: Boolean
Default: false

Disables meta-table locking.

verbose

Type: boolean
Default: false

Enables output.

revision

Type: Number
Default: Infinity

First revision to show info about.

tableName

Type: string
Default: migratio

Table in which migratio will store metadata.

CLI

$ npm install --global migratio

$ migratio --help

  Usage
    migratio [command] [options]

  Options
    -d, --directory    Directory with migrations files [Default: ./migrations]
    -c, --connection   Connection string to Postgres [Default: $DATABASE_URL]
    -r, --revision     Specify revision to up/down to
    -t, --table        Table name for metadata [Default: migratio]
    -s, --schema       Schema name for table with metadata [Default: public]
    --unsafe           Skip transaction and table locking

  Commands

    up             Applies all migrations from current to latest
    down           Rollbacks all migrations in current batch
    current        Shows migrations in current batch

  Examples
    $ migratio

      Current batch:
        000005-images.sql  (batch:3)
        000004-files.sql   (batch:3)
        000003-stats.sql   (batch:3)

    $ migratio down

      ↓ 000005-images.sql  (batch:3)
      ↓ 000004-files.sql   (batch:3)
      ↓ 000003-stats.sql   (batch:3)

    $ migratio up

      ↑ 000003-stats.sql   (batch:3)
      ↑ 000004-files.sql   (batch:3)
      ↑ 000005-images.sql  (batch:3)
      ↑ 000006-posts.sql   (batch:3)

License

MIT © Vsevolod Strukchinsky