node-baseline

Description

A simple database migration and versioning tool. Basically, it works by maintaining change logs in the database, and incrementally updating the database using user supplied change scripts. You can craft the change scripts manually or use some tools (e.g., mysql workbench, etc. ) to generate them for you. Then, you can add the database schema (tables, views, indexes, constraints, etc. ) exported by baseline and these change scripts into your version control system such as git.

Use Case

Typically, when you use a database-first development approach, your team want to keep local development databases and want to keep track of the changes collaboratively w/ each other, then baseline could help. However, baseline also supports other scenarios such as you team (probably a dedicated database team) is maintaining a shared database for development, you want to apply the changes to the database in production, etc.

Install & Usage

Baseline is a CLI tool that you could install via npm as below:

  npm install -g node-baseline

Note: this tool requires nodejs version 4.0.0 or above.

Then, type baseline --help to see how to use it as below:

Usage: baseline <command> [options]

Commands:
  init  init the baselines of configured databases
  up    migrate the configured databases up from the baselines

Options:
  --config, -c  the database configurations to use with baseline
  --force, -f   used with init command, force init if a baseline already exists
  --log-level   logging level: verbose (default), debug, info, warn, error
  --help        show help information
  --version     Show version number

Copyright 2015, MIT licensed.

You have to create a configuration file for the databases that you want to add baseline support to. Basically, you can use --config option or .baselinerc in the working directory to specify the required configurations. The config file support YAML, JSON and node module formats whichever way you choose for that. See the sample configuration files below.

Init

First, use the init command to integrate your database with baseline, e.g.,

baseline init --config /path/to/the/config/file

or without the --config option if there is a .baselinerc configuration file in the directory you're calling baseline:

baseline init

When the init command executed successfully, baseline will create the _change_log table in the database and then export all the necessary schema of the database, including tables, indexes, constraints, views, etc., and an empty changes directory where your change scripts reside. All these files will go to the corresponding sub directories under the configured rootPath scoped by the database name. For example:

temp/db/test
├── changes
├── tables
│   ├── sales.sql
│   ├── attrs.sql
│   └── costs.sql
└── views
    ├── costs_view.sql
    └── sales_view.sql

where, temp/db/ is the configured rootPath, and test is the name of the database integrated w/ baseline.

Migrate

You can add some change scripts to the changes directory (as mentioned above). The file names of the change scripts should follow the convention major(\d{2}).minor(\d{2}).patch(\d{4}).sql, and be increased sequentially, e.g, 01.00.0001.sql, 01.00.0002.sql, ... 01.01.0001.sql, etc.

Then, issue the up command below to apply the changes to the configured databases:

baseline up --config /path/to/the/config/file

or without the --config option if there is a .baselinerc configuration file in the directory you're calling baseline:

baseline up

Rebase

By rebasing, baseline will treat the current database as a fresh new one and re-integrate w/ it from a new start point. You can rebase the database at any time you want by using the init command with --force option:

baseline init --config /path/to/the/config/file --force

or without the --config option if there is a .baselinerc configuration file in the directory you're calling baseline:

baseline init --force

Collaborate

baseline is designed and implemented with team collaboration in mind. The typical workflow is:

• A integrates the database w/ baseline by init
• A commits all the necessary stuff generated by baseline to the git repository
• B pulls the stuffs committed by A
• B issues baseline init to create the database w/ baseline integrated
• A migrates up the database using change scripts by up
• A commits the change scripts to the git repository
• B pulls the change scripts committed by A
• B issues baseline up to apply the changes from A
• ...

However, there's a caveat here that when before one developer add the change scripts to the changes directory, he/she must first pull the change scripts from others first, and then add and test the changes scripts before commit.

Sample config files

YAML

rootPath: ./temp/db
host: 127.0.0.1
port: 3306
user: root
password: secret
dialect: mysql
databases:
  - name: test1
  - name: test2
    dialect: oracle
    user: user
    password: password

JSON

{
  "rootPath": "./temp/db",
  "host": "127.0.0.1",
  "port": 3306,
  "user": "root",
  "password": "secret",
  "dialect": "mysql",
  "databases": [
    { "name": "test1" },
    { "name": "test2", "dialect": "oracle", "user": "user", "password": "..." }
  ]
}

Node Module

var path = require('path');
module.exports = {
  rootPath: path.join(__dirname, 'temp/db'),
  host: '127.0.0.1',
  port: 3306,
  user: 'root',
  password: 'secret',
  dialect: 'mysql',
  databases: [
    { name: 'test1' },
    { name: 'test2', dialect: 'oracle', user: 'user', password: '...' }
  ]
};