Marv is a programmatic database migration tool with plugable drivers for mysql and postgres.

Create a directory of migrations

migrations/
  |- 001.create-table.sql
  |- 002.create-another-table.sql

Run marv

const path = require('path')
const marv = require('marv')
const driver = require('marv-pg-driver')
const options = { connection: { host: 'postgres.example.com' } }
const directory = path.join(process.cwd(), 'migrations' )

marv.scan(directory, (err, migrations) => {
    if (err) throw err
    marv.migrate(migrations, driver(options), (err) => {
        if (err) throw err
        // Done :)
    })
})

Migration files are just SQL scripts. Filenames must be in the form <level><separator><comment>.<extension> where:

• level must be numeric
• separator can be any non numeric
• comment can contain any characters except '.'
• extension is any file extension. See here for how to filter migration files.

The following drivers exist for marv.

• marv-pg-driver
• marv-mysql-driver
• marv-foxpro-driver

If you want to add a new driver please use the compliance tests and include at least one end-to-end test. See marv-pg-driver for an example.

You can configure a driver by passing it options, e.g.

const options = {
    // defaults to 'migrations'
    table: 'db_migrations',
    // The connection sub document is passed directly to the underlying database library,
    // in this case pg.Client
    connection: {
        host: 'localhost',
        port: 5432,
        database: 'postgres',
        user: 'postgres',
        password: ''
    }
}
marv.scan(directory, (err, migrations) => {
    if (err) throw err
    marv.migrate(migrations, driver(options), (err) => {
        if (err) throw err
        // Done :)
    })
})

Before writing Marv we evaluated existing tools against the following criteria:

• Cluster safe
• Works with raw SQL
• Programmatic API so we can invoke it on application startup
• Supports multiple databases including Postgres and MySQL via optional plugins
• Can be run repeatedly from integration tests
• Reports errors via events, callbacks or promise rejections rather than throwing or logging
• Follows the rule of silence
• Reasonable code hygiene
• Reasonably well tested

Candidates were:

• pg-migrator
• db-migrate
• migratio
• postgrator
• stringtree-migrate
• migrate-database
• node-pg-migrate
• east

Disappointingly they all failed. Marv does all these things in less than 150 lines, with around another 100 lines for a driver.

One of the reasons Marv is has a small and simple code base is because it doesn't come with a lot of unnecessary bells and whistles. It doesn't support

• Rollbacks (we make our db changes backwards compatible so we can deploy without downtime).
• A DSL (high maintenance and restrictive)
• Conditional migrations
• A command line interface (we may implement this in future)
• Checksum validation (we may implement this in future)

Marv is unlike some other migration libraries in that it deliberately doesn't run your scripts in a transaction. This is because some SQL statements cannot be run in a transaction, and others(e.g. locking in Postgres) will automatically commit the current transaction if one exists. Unfortunately this means that in rare situations, scripts may be only partially applied, e.g.

CREATE TABLE customer (
  id BIGSERIAL PRIMARY KEY,
  name TEXT
);
CREATE INDEX customer_name ON customer (
  name
);

If something goes wrong (e.g. a network outage) after CREATE TABLE but before CREATE INDEX, the table would be created without the index. Because scripts are audited on successful completion, the script will be included in the next migration run, but now the CREATE TABLE step will fail because the table already exists. One way to work around this is by explicitly specifying a transactions...

BEGIN TRANSACTION;
    CREATE TABLE customer (
      id BIGSERIAL PRIMARY KEY,
      name TEXT
    );
    CREATE INDEX customer_name ON customer (
      name
    );
END TRANSACTION;

However there's still a gotcha. Now the script will either be applied or not, but consider what will happen if the network outage occurs after the script has been applied, but before Marv inserts the audit record? Because the script hasn't been audited, Marv won't know that it completed successfully and will still include it in the next migration run. Once again it will fail on the CREATE TABLE step. A better workaround is to make your script idempotent, e.g.

CREATE TABLE IF NOT EXISTS customer (
  id BIGSERIAL PRIMARY KEY,
  name TEXT
);
CREATE INDEX IF NOT EXISTS customer_name ON customer (
  name
);

Unfortunately not all statements and SQL dialects have an equivalent of IF NOT EXISTS. If you're especially unlucky and something goes wrong while applying a non-atomic / non-idempotent script you will have some manual clean up to do. This may involve applying the missing steps and inserting the audit record manually. The exact syntax will vary from driver to driver but should be similar to...

$ cat migrations/002.create-customer-table.sql | md5
82b392f3594050ecefd768bfe258843b

INSERT INTO migrations (level, comment, "timestamp", checksum) VALUES (2, 'create customer table', now(), '82b392f3594050ecefd768bfe258843b');

If you would like to exclude files from your migrations directory you can specify a filter

migrations/
  |- 001.create-table.sql
  |- 002.create-another-table.sql

marv.scan(directory, { filter: /\.sql$/ }, (err, migrations) => {
    if (err) throw err
    marv.migrate(migrations, driver, (err) => {
        if (err) throw err
        // Done :)
    })
})

You can configure marv by placing a .marvrc file in your migrations folder

migrations/
  |- .marvrc
  |- 001.create-table.sql
  |- 002.create-another-table.sql

{
    "filter": "\\.sql$",
    "directives": {
        "audit": "false"
    }
}

marv.scan(directory, (err, migrations) => {
    if (err) throw err
    marv.migrate(migrations, driver, (err) => {
        if (err) throw err
        // Done :)
    })
})

Directives allow you to customise the behaviour of migrations. You can specify directives in three ways...

1. Programatically via marv.scan

   marv.scan(directory, { filter: /\.sql$/ }, { directives: { audit: false } }, (err, migrations) => {
       if (err) throw err
       marv.migrate(migrations, driver, (err) => {
           if (err) throw err
           // Done :)
       })
   })

2. Via .marvrc

   {
       "filter": "\\.sql$",
       "directives": {
           "audit": "false"
       }
   }

3. Using a specially formed comment in a migration file

   -- @MARV AUDIT = false
   INSERT INTO foo (id, name) VALUES
   (1, 'xkcd'),
   (2, 'dilbert')
   ON CONFLICT(id) DO UPDATE SET name=EXCLUDED.name RETURNING id;

The following directives are supported:

-- @MARV AUDIT = false

When set to false, marv will run the migration but not record that it has been applied. This will cause it to be re-run repeatedly. This can be useful if you want to manage ref data, but does imply that SQL is idempotent.

-- @MARV SKIP = true

When set to true, marv will skip the migration and the audit step.

-- @MARV COMMENT = A much longer comment that can contain full stops. Yay!

Override the comment parse from the migration filename.

You can run marv with debug to see exactly what it's doing

DEBUG='marv:*' npm start

marv:migrate Connecting driver +0ms
marv:pg-driver Connecting to postgres://postgres:******@localhost:5432/postgres +0ms
marv:migrate Ensuring migrations +23ms
marv:migrate Locking migrations +5ms
marv:migrate Getting existing migrations +1ms
marv:migrate Calculating deltas +7ms
marv:migrate Running 0 migrations +2ms
marv:migrate Unlocking migrations +0ms
marv:migrate Disconnecting driver +1ms
marv:pg-driver Disconnecting from postgres://postgres:******@localhost:5432/postgres +0ms