trek

A database migration tool for MSSQL.

Install

Node 6.10.0 or higher is required to use this tool. Install trek globally by issuing the following command.

npm install -g @benningfield-group/trek

This will add a global trek command. Running trek without any arguments will describe the program's usage.

Database Connection Configuration

Database configuration is stored in a connections.json file, which should contain a single object. (The connections.json file name and location can be overridden using the --connections-file option.) Each key in the object should correspond to an environment (dev, uat, prod, etc.). The value associated with each key should be an array of connection configuration objects suitable for use with node-mssql as described, here.

For example:

{
  "dev": [
    {
      "user":"sa",
      "password":"password",
      "server":"localhost",
      "database":"db1"
    },
    {
      "user":"sa",
      "password":"password",
      "server":"localhost",
      "database":"db2"
    }
  ],
  "prod": [
    {
      "user":"sa",
      "password":"password",
      "server":"prod.example.com",
      "database":"db1"
    },
    {
      "user":"sa",
      "password":"password",
      "server":"prod.example.com",
      "database":"db2"
    }
  ]
}

The above example contains two databases in the dev environment, and two prod databases.

Environment

trek uses the NODE_ENV environmental variable for determining what connection(s) to run migrations against. By default, migrations are run against the dev environment. Other environments must be specifically set in a NODE_ENV environmental variable like so: export NODE_ENV=prod under *nix; set NODE_ENV=prod under Windows. Or, alternatively, the environment can prefix the trek command: NODE_ENV=prod trek <command>.

Creating a Migration

To create a migration, run trek create <name>, where <name> is a descriptive name for the migration. Migrations are by default created in a migrations folder, but the folder can be overridden using the --migrations-dir option.

Running: trek create create_table_widgets produces the output:

Creating migration: /some/path/migrations/2017-06-07__09-23-35-450__create_table_widgets.js

Note that the migration is prefixed with a timestamp, which is important to ensure that migration order is preserved.

The newly-created file contains a migration scaffolding with two methods: up and down. The former is used to run a migration, and the latter is used to undo it. As an example, the following could be used to create a widgets table and subsequently drop it should the need arise:

'use strict';

module.exports = {
  /**
   * Run the migration.
   * @param {Object} mssql - An mssql instance from the node-mssql package
   * (https://github.com/patriksimek/node-mssql).
   * @param {ConnectionPool} conn - A connected ConnectionPool instance
   * (https://github.com/patriksimek/node-mssql#connections-1).
   */
  up(mssql, conn) {
    const sql = `
      CREATE TABLE widgets (
        widgetID INT NOT NULL PRIMARY KEY IDENTITY,
        widgetName NVARCHAR(255) NOT NULL)`;
    const req = new mssql.Request(conn);

    console.log(sql);

    return req.query(sql);
  },

  /**
   * Bring down a migration.
   * @param {Object} mssql - See up().
   * @param {ConnectionPool} conn - See up(). 
   */
  down(mssql, conn) {
    const sql = `DROP TABLE widgets`;
    const req = new mssql.Request(conn);

    console.log(sql);

    return req.query(sql);
  }
};

Running Migrations

trek up will run all migrations that have not yet been run. The migrations are run in order, one after another (sequentially), against each database defined for the environment. Should a failure occur, the operation is aborted and the error is logged to the console (to STDERR). After each successful migration, the migration is logged in the trek_migrations (this table will be created automatically if it does not exist).

Rolling Back

trek down will bring down the most recent migration. Like the up command, if an exception is raised the operation aborts and the error is printed. After a successful down operation, the most recent migration entry is deleted from the trek_migrations table.

Run a script

trek run <script-file will run a JavaScript manually. The script should export an up function that accepts mssql and conn as arguments, the same method signature as up and down.