node-db-migration

Source control for your database. This is the dummies and simplest package that allows you to automatically install new migrations on your database. Just write your migration scripts in bare sql and this package will do the rest of magic for you!

What this package does is:

• creates a database table migrations (you can configure it with migrationTable) that keeps a track on all migration scripts
• scans directory for .sql files
• orders sql files by date pattern and executes it sequentially if they weren't executed before
• marks sql files as executed in database
• if sql execution fails it saves the exception to database and prevents further migration until you resolve it manually

To get started:

• npm install node-db-migration
• create the directory with sql migrations somewhere.
• put all .sql migration files there and name them as date-name.sql, e.g. 201705231245-add-pets-table.sql.
• integrate the code bellow into your project:

mysql:

var mysql = require('mysql'); // or mysql2
let {CommandsRunner, MysqlDriver} = require('node-db-migration');
var connection = mysql.createConnection({
    "host" : "localhost",
    "user" : "root",
    "database" : "test8",
    "multipleStatements" : true, // if you have multiple sql in your scripts
});
connection.connect(function(err) {
    let migrations = new CommandsRunner({
        driver: new MysqlDriver(connection),
        directoryWithScripts: __dirname + '/diff',
    });
    migrations.run(process.argv[2])
});

psql:

let {CommandsRunner, PsqlDriver} = require('node-db-migration');
const { Client } = require('pg')
const client = new Client({
    connectionString: 'postgresql://postgres:@localhost:5432/test5',
});
client.connect(function() {
    let migrations = new CommandsRunner({
        driver: new PsqlDriver(client),
        directoryWithScripts: __dirname + '/diff',
    });
    migrations.run(process.argv[2])
});

sqlite:

var sqlite3 = require('sqlite3').verbose();
let {CommandsRunner, SQLite3Driver} = require('node-db-migration');
var db = new sqlite3.Database('./test');
let migrations = new CommandsRunner({
    driver: new SQLite3Driver(db),
    directoryWithScripts: __dirname + '/diff',
});
migrations.run(process.argv[2])

Then run this file via node:

node yourFile.js command

Man

Commands:

migration.run accepts the following commands:

• init: Initialized database for migrations
• fake: Fakes the migrations, marks that files in ./diff are executed successfully
• list: Show all unapplied migrations from ./diff
• migrate: Installs all new updates from ./diff
• forceMigrate: Installs all new updates from ./diff. If one migration fails it goes to another one.
• resolve: Marks all failed migrations as resolved
• getFailed: Show all failed migrations
• help: Prints help

Different sql directory:

You can configure path to sqlDirectory passing different path directoryWithScripts to CommandsRunner. directoryWithScripts: __dirname + '/migrations/sqls'

Migration table name :

Pass 2nd parameter to new driver constructor e.g. MysqlDriver(connection, 'migration_table'). Note that table should be in lowercase especially in postgres.

Time format:

The default time format is YYYYMMDDHHmm. You can configure date format with dateFormat. e.g. new CommandsRunner({ dateFormat: 'YYYYMMDDHHmm'}). This format uses to orders sql files and set theirs creation date in database. For available formats see moment documentation

Tips:

• You can also add npm script and run it with npm run migrate or something
• You can also integrate this script into initing script of your server. You can use await migration.run('migrate'). This will automagically migrate database to the latest version
• Currently node-db-migration was tested only with mysql, pg and sqlite3 But it doesn't depend on any specific implementation of db driver. You can create your own driver:

let {CommonDriver} = require('node-db-migration');

class MyDriver extends CommonDriver {
   isInitedSql() {
        return `SHOW TABLES LIKE '${this.migrationTable}'`;
    }

    createTableSql() {
        return `CREATE TABLE ${this.migrationTable}` +
            `(` +
            `    id INT PRIMARY KEY AUTO_INCREMENT,` +
            `    name VARCHAR(128) NOT NULL,` +
            `    run_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,` +
            `    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,` +
            `    error_if_happened LONGTEXT` +
            `)`;
    }

    query(sql, params, cb) {
        this.dbRunner.query(sql, params, function(error, result) {
            return cb(error /* should be simple string */, result /* should be array of rows */);
        })
    }
}

Using async code:

import * as sqlite3 from 'sqlite3';
import {CommandsRunner, SQLite3Driver} from 'node-db-migration';

async function run() {
    const db = new sqlite3.Database(':memory:');
    const migrations = new CommandsRunner({
        driver: new SQLite3Driver(db),
        directoryWithScripts: __dirname + '/diff',
    });
    await migrations.run('migrate')
    console.log("this will print after migrations are finished");
}
run();