npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

node-db-migrate

v1.0.1

Published

Simple and lightweight DB migration tool written in Node.js

Downloads

58

Readme

node-db-migrate

Table of Contents

Overview

Simple and lightweight database migration tool written in Node.js. MIT License. Currently supports the following database servers:

  • [MySQL] (https://www.mysql.com/)
  • [PostgreSQL] (http://www.postgresql.org/)

Quick Start

Installation

npm install -g node-db-migrate

NOTICE: -g flag is mandatory if you wish to use the command line. Use npm install node-db-migrate if you want to use the library.

Command line


$ db-migrate --help

  Usage: db-migrate [options] [command]


  Commands:

    info                show revision information
    clean               drops all objects in the managed schema
    repair              repair migration failures
    baseline <version>  baseline existing schema to initial version
    migrate [version]   migrate schema to new version

  Options:

    -h, --help     output usage information
    -V, --version  output the version number
baseline

baseline will first create the schema (if it doesn't exist already) and then will initiate the objects where the revision information will be hold.


$ db-migrate baseline 1.0
[2015-12-26 13:38:33.137] [INFO] [MySQL Client /localhost:3306] - Connection pool of 10 connections was established
[2015-12-26 13:38:33.141] [INFO] [SchemaMgr/ myproject] - Creating Schema `myproject` if not exists.
[2015-12-26 13:38:33.160] [INFO] [SchemaMgr/ myproject] - Creating `schema_version` object in `myproject` schema.
[2015-12-26 13:38:33.177] [INFO] [SchemaMgr/ myproject] - Deleting objects in `myproject`.`schema_version`
[2015-12-26 13:38:33.179] [INFO] [SchemaMgr/ myproject] - Saving version `1.0` to `myproject`.`schema_version`
[2015-12-26 13:38:33.183] [INFO] [MySQL Client /localhost:3306] - Shutting down connection pool
[2015-12-26 13:38:33.186] [INFO] console - Exit with status code 0
info

info will show the relevant information about the current revision: * Current version, defined as the highest rank that has at least one successful execution. * Latest executions, including all executions of the current version, regardless of their status, and failures of executions related to higher versions.


$ db-migrate info
[2015-12-26 13:39:53.077] [INFO] [MySQL Client /localhost:3306] - Connection pool of 10 connections was established
[2015-12-26 13:39:53.080] [INFO] [SchemaMgr/ myproject] - Reading objects from `myproject`.`schema_version`
[2015-12-26 13:39:53.112] [INFO] console - Schema: `myproject`, Version: 1.0
[2015-12-26 13:39:53.126] [INFO] console - ┌────────┬──────────────┬────────────────┬────────┐
[2015-12-26 13:39:53.126] [INFO] console - │ Script │ Description  │ Execution Time │ Status │
[2015-12-26 13:39:53.126] [INFO] console - ├────────┼──────────────┼────────────────┼────────┤
[2015-12-26 13:39:53.127] [INFO] console - │        │ Base version │ 0 ms           │ OK     │
[2015-12-26 13:39:53.127] [INFO] console - └────────┴──────────────┴────────────────┴────────┘
[2015-12-26 13:39:53.127] [INFO] [MySQL Client /localhost:3306] - Shutting down connection pool
[2015-12-26 13:39:53.128] [INFO] console - Exit with status code 0
migrate

migrate discovers new content in the data directory and executes it, moving the schema into a new state (revision), either a version bump or changes in content of the current version. migrate can work with a target version if you want to define the specific target version and will ignore any changes related to versions with higher rank. In the example below, we perform a migration to version 1.1.


$ db-migrate migrate
[2015-12-26 14:06:08.730] [INFO] [MySQL Client /localhost:3306] - Connection pool of 10 connections was established
[2015-12-26 14:06:08.734] [INFO] [SchemaMgr/ myproject] - Reading objects from `myproject`.`schema_version`
[2015-12-26 14:06:08.768] [INFO] [MySQL Client /localhost:3306] - Starting transaction
[2015-12-26 14:06:08.795] [INFO] [MySQL Client /localhost:3306] - Committing
[2015-12-26 14:06:08.798] [INFO] [SchemaMgr/ myproject] - Saving version `1.1` to `myproject`.`schema_version`
[2015-12-26 14:06:08.809] [INFO] [MySQL Client /localhost:3306] - Shutting down connection pool
[2015-12-26 14:06:08.811] [INFO] console - Exit with status code 0

And we can see the version bump by calling info again.


$ db-migrate info
[2015-12-26 14:06:57.206] [INFO] [MySQL Client /localhost:3306] - Connection pool of 10 connections was established
[2015-12-26 14:06:57.210] [INFO] [SchemaMgr/ myproject] - Reading objects from `myproject`.`schema_version`
[2015-12-26 14:06:57.238] [INFO] console - Schema: `myproject`, Version: 1.1
[2015-12-26 14:06:57.271] [INFO] console - ┌─────────────────────────────┬───────────────────┬────────────────┬────────┐
[2015-12-26 14:06:57.272] [INFO] console - │ Script                      │ Description       │ Execution Time │ Status │
[2015-12-26 14:06:57.272] [INFO] console - ├─────────────────────────────┼───────────────────┼────────────────┼────────┤
[2015-12-26 14:06:57.272] [INFO] console - │ v1_1__Create_User_Table.sql │ Create User Table │ 32 ms          │ OK     │
[2015-12-26 14:06:57.272] [INFO] console - └─────────────────────────────┴───────────────────┴────────────────┴────────┘
[2015-12-26 14:06:57.272] [INFO] [MySQL Client /localhost:3306] - Shutting down connection pool
[2015-12-26 14:06:57.273] [INFO] console - Exit with status code 0
repair

Since failures are documented by the schema manager in the same manner as successful events, it is impossible to fix a failure by running the migration again. In order to fix a failure, we must call repair, which will scan the data directory for the same script, but this time with the correct syntax, and re-base the version.

Hence, let's change the example above to have a syntax error:

CREATE TABLE IF NOT EXISTS users (
  name VARCHAR(25) NOT NULL,
  PRIMARY KEY(user_name)
);

After trying to run the migration (with no success), there will be no version bump but we will be able to see the failures of the attempted version.


$ db-migrate info
[2015-12-26 17:10:23.921] [INFO] [SchemaMgr/ myproject] - Reading objects from `myproject`.`schema_version`
[2015-12-26 17:10:24.026] [INFO] console - Schema: `myproject`, Version: 1.0
[2015-12-26 17:10:24.044] [INFO] console - ┌────────────────────────────┬───────────────────┬────────────────┬────────┬─────────────────────────────────────────────────────────────────────────────┐
[2015-12-26 17:10:24.044] [INFO] console - │ Script                     │ Description       │ Execution Time │ Status │ Reason                                                                      │
[2015-12-26 17:10:24.044] [INFO] console - ├────────────────────────────┼───────────────────┼────────────────┼────────┼─────────────────────────────────────────────────────────────────────────────┤
[2015-12-26 17:10:24.044] [INFO] console - │                            │ Base version      │ 0 ms           │ OK     │                                                                             │
[2015-12-26 17:10:24.044] [INFO] console - ├────────────────────────────┼───────────────────┼────────────────┼────────┼─────────────────────────────────────────────────────────────────────────────┤
[2015-12-26 17:10:24.044] [INFO] console - │ v1_1__Create_User_Table.js │ Create User Table │ 15 ms          │ FAILED │ ER_KEY_COLUMN_DOES_NOT_EXITS: Key column 'user_name' doesn't exist in table │
[2015-12-26 17:10:24.045] [INFO] console - └────────────────────────────┴───────────────────┴────────────────┴────────┴─────────────────────────────────────────────────────────────────────────────┘
[2015-12-26 17:10:24.048] [INFO] console - Exit with status code 0

Calling migrate again at this stage will do nothing since the migration tool will ignore any object that was already registered to the schema revision. repair will go over failures and try to run them again.


$ db-migrate repair
[2015-12-26 17:39:58.266] [INFO] [SchemaMgr/ myproject] - Reading objects from `myproject`.`schema_version`
[2015-12-26 17:39:58.350] [INFO] [SchemaMgr/ myproject] - Preparing to repair 1.1/v1_1__Create_User_Table.js
[2015-12-26 17:39:58.360] [INFO] [SchemaMgr/ myproject] - Starting transaction
[2015-12-26 17:39:58.380] [INFO] [SchemaMgr/ myproject] - Committing transaction
[2015-12-26 17:39:58.382] [INFO] [SchemaMgr/ myproject] - Saving version `1.1` to `myproject`.`schema_version`
[2015-12-26 17:39:58.391] [INFO] console - Exit with status code 0

If repair completed succesfully, we can now see the version bump.


$ db-migrate info
[2015-12-26 17:40:05.141] [INFO] [SchemaMgr/ myproject] - Reading objects from `myproject`.`schema_version`
[2015-12-26 17:40:05.224] [INFO] console - Schema: `myproject`, Version: 1.1
[2015-12-26 17:40:05.246] [INFO] console - ┌────────────────────────────┬───────────────────┬────────────────┬────────┬────────┐
[2015-12-26 17:40:05.246] [INFO] console - │ Script                     │ Description       │ Execution Time │ Status │ Reason │
[2015-12-26 17:40:05.246] [INFO] console - ├────────────────────────────┼───────────────────┼────────────────┼────────┼────────┤
[2015-12-26 17:40:05.246] [INFO] console - │ v1_1__Create_User_Table.js │ Create User Table │ 30 ms          │ OK     │        │
[2015-12-26 17:40:05.247] [INFO] console - └────────────────────────────┴───────────────────┴────────────────┴────────┴────────┘
[2015-12-26 17:40:05.247] [INFO] console - Exit with status code 0
clean

$ db-migrate clean
[2015-12-26 13:26:18.042] [INFO] [MySQL Client /localhost:3306] - Connection pool of 10 connections was established
[2015-12-26 13:26:18.045] [INFO] [SchemaMgr/ myproject] - Dropping objects in `myproject`
[2015-12-26 13:26:18.070] [INFO] [MySQL Client /localhost:3306] - Shutting down connection pool
[2015-12-26 13:26:18.072] [INFO] console - Exit with status code 0

Writing a migration script

Data Directory

Data directory is where migration scripts should be uploaded. Please make to sure to configure the data directory in your project rc file.

...

[migration]
schema      =   myproject
datadir     =   /etc/db-migraterc/data/myproject
Naming

Files in the data directory must have a name in the following format:

v${VERSION}__${DESCRIPTION}.${EXT}

${VERSION} must have the following structure:

  • One or more numeric parts.
  • Separated by a dot (.) or an underscore (_).
  • Underscores are replaced by dots at runtime.
  • Leading zeroes are ignored in each part.

${DESCRIPTION} must have the following structure:

  • Text.
  • Less then 255 characters.
  • Separated by a an underscore (_).

${EXT} can be one of the following:

  • .js / .JS
  • .sql / .SQL

Examples:

  • v1_1_Create_User_Table.sql
  • v01_1_Create_User_Table.js
SQL

Writing migration script in SQL is pretty straight-forward, as it uses the standard SQL programming (e.g. http://dev.mysql.com/doc/refman/5.7/en/sql-syntax.html). Below is a simple example in MySQL which created a new table in our project called users.

CREATE TABLE IF NOT EXISTS users (
  name VARCHAR(25) NOT NULL,
  PRIMARY KEY(name)
);

NOTICE: Currently there is no enforcement on changes that can be done to other perhaps non-managed schemas. In fact, scope of SQL scripts is not limited to the managed schema only. We highly recommend to be careful with the changes as in the future we will probably validate the syntax before calling the execution. No need for USE statement, as you can assume the execution will use the managed schema.

Node.js

For more complex statements, we support Node.js programming language. A Node.js migration module should export a function that will receive one argument, a knex [transaction] (http://knexjs.org/#Transactions). See example belows how to use the transaction in several different ways.

// example 1: using a row query
module.exports = function(trx) {
    return trx.schema.raw("CREATE TABLE users (name VARCHAR(25) NOT NULL, PRIMARY KEY(name));");
};

// example 2: using a schema builder
module.exports = function(trx) {
    return trx.schema.createTableIfNotExists("users", function (table) {
        table.string('name', 25);
    });
};

// example 3: using a query builder
module.exports = function(trx) {
    return trx.insert({title: 'Slaughterhouse Five'}).into('books');
};

NOTICE: Knex transaction is "promise aware" connection and therefore all runtime errors from the exported module will immediately trigger a rollback of the transaction. In addition, exported Node.js modules should not start or end the given transaction, as it is managed directly by the schema manager.

Using the library directly


var SchemaManager = require('node-db-migrate').SchemaManager;

var mgr = new SchemaManager("myproject", "mysql", {
    "host": "localhost",
    "port": 3367,
    "user": "root",
    "password": "nopassword"
});

mgr.migrate('/path/to/data/directory')
        .then(function() {
            // .. post-migration code ..
        })
        .catch(function(e) {
            console.error(e.message);
        })
        .finally(function() {
            mgr.close();
        })

Configuration

Migration tool uses rc file for its settings.

Configuration file should be placed in one of the following locations:

* $HOME/.db-migraterc
* $HOME/.db-migrate/config
* $HOME/.config/db-migrate
* $HOME/.config/db-migrate/config
* /etc/db-migraterc
* /etc/db-migrate/config

Alternatively, it is possible to set the path to the configuration file when using the command line.

$ db-migrate migrate --config ${CONFIG_FILE_PATH}

Configuration should be in one of the following formats:

* INI
* JSON
INI
client              =   mysql

connection          =   mysql://root@localhost

[logging]
level               =   INFO|DEBUG|WARN|ERROR

[schema]
name                =   myproject
datadir             =   /etc/mysql-migraterc/data
INI (2)
[connection]
host                =   localhost
user                =   root
password            =   pass
JSON
{
    "client": "mysql",
    "connection": "mysql://root@localhost",
    "logging": {
        "level": "INFO"
    },
    "schema": {
        "name": "myproject",
        "datadir": "/etc/mysql-migraterc/data/myproject"
    }
}
JSON (2)
{
    "logging": {
        "level": "INFO"
    },
    "client": "mysql",
    "connection": {
        "user": "root",
        "host": "localhost",
        "password": ""
    },
    "schema": {
        "name": "myproject",
        "datadir": "/etc/mysql-migraterc/data/myproject"
    }
}
Using environment variables

Setting the configuration for the command line is also possible through environment variables:

* prefixed by "db-migrate_".
* using "__" to represent nested properties.

For example:

$ db-migrate_client=mysql db-migrate__connection=mysql://root@localhost db-migrate migrate