node-mysql-migration

This plugin is create to simplify migration of mysql database migration.

Using

Installing

npm install node-mysql-migration - to install util

Setup

# my_db_migrations.js
var mysql = require('mysql');
var migration = require('node-mysql-migration');

migration.migrate(mysql.createConnection({
    host     : 'host',
    user     : 'user',
    password : 'password',
    database : 'database'
}), __dirname + '/migrations');

/migrations - is a folder where all migrations scripts are located. There is no default value for it so you should specify it

File naming convention

migration script shoul have the following name template

V(version name)__name_of_script_separated_with_lower_underline.sql

#example
V1__init_tables.sql
V2__add_new_column.sql

inside migrations file you should wtire migrations script in plain SQL

WARNING

for now migration support only one command in migration script.
If you migration script contains the following

ALTER TABLE `tbl_name`
    ADD COLUMN `column_name` VARCHAR(250);
    
ALTER TABLE `tbl_name`
    ADD COLUMN `column_name1` VARCHAR(250);
    
UPDATE `tbl_name` SET `column_name`="asd";

then migration will fails.
to solve this split such migration into three separate migration
OR
customize your connection settings. use:

migration.migrate(mysql.createConnection({
    host     : 'host',
    user     : 'user',
    password : 'password',
    database : 'database',
    multipleStatements: true // add this to allow multiple queries in single migration file
}), __dirname + '/migrations');

official node-mysql doc

Commands

run npm my_db_migrations.js clean to clean the data base
run npm my_db_migrations.js init to init empty migration scheta table
run npm my_db_migrations.js migrate to apply new migrations to your data base if such exists