Database migrations with node db-migrate

To continue with the research on database migrations started with the post about liquibase I will provide a small tutorial for node’s db-migrate on how to database migrations with node db-migrate

I am much more used to this kind of tool in which the developer’s responsibility of providing migrations is done in the same language of development. The fundamentals are the same. It seems to me that liquibase can be a bit more powerful when handling severe branching, but the simplicity of these kind of framework tools makes them desirable

I will use the same schema and setup as on the previous post, please check it out there on the post about liquibase

sample_db_schema

Setting up node db-migrate

interested in db migrations with liquibase?

Don’t miss this article
Managing database schema changes with liquibase on an existing database tutorial

The setup is as simple as it could, simply install the dependencies


sudo npm install db-migrate
# I will be working with mysql
sudo npm install mysql

And configure your database (in case you haven’t done that already)


Working with migrations

Once set up, it is time to start creating the migrations. The commands are quite simple

First, create your migration. Give it a meaninful name. The tool appends the creation date to the file name. The command is the following


node_modules/db-migrate/bin/db-migrate --env test --migrations-dir=./migrations create add_category_to_project

To upgrade or downgrade a certain migration use the following commands


node_modules/db-migrate/bin/db-migrate --env test --migrations-dir=./migrations up add_category_to_project -v
node_modules/db-migrate/bin/db-migrate --env test --migrations-dir=./migrations down

You could always check the version of your schema by looking at the SELECT * FROM migrations; table

Migration anatomy

A migration has to support upgrading from the previous version of the schema, and going back to it. The hardest part is to go back to it, as you could probably destroy data in the process. It is highly desirable that the downgrade code is working, you will have better conflict solving


exports.up = function(db, callback) {
db.runSql(
'DROP VIEW IF EXISTS complete_project',
[],
function(err) {
if (err) { callback(err); return; }

db.runSql(
'CREATE VIEW complete_project AS ( \
SELECT project.id, project.name, project_data.description \
FROM project \
JOIN \
project_data ON project_data.project_id = project.id \
);',
[],
callback
);
}
);
};

exports.down = function(db, callback) {
callback();
};

Migration granularity

For the sake of simplicity I have divided in both examples the migrations in an unnatural way. The granularity of the migrations should be such that allows the environment to be stable on each migration step. In our example tutorial, adding the new column category_name to the table project should involve also dropping the constraint of the foreign key and rebuilding the view. Side note, on a heavily object oriented project I would not have views but well, for the example should work

Working with others

Some simple directives when working with other

  • Every single change must be added to a migration
  • It is responsibility of the developers to do this, and to coordinate that the migration scripts work in any installation
  • It is not acceptable that your migration works on your environment but not on the rest of the developers’ (it would likely mean that it would not run in production)
  • All migrations MUST have the downgrade code!! very important for merge handling

Take a look at the sample code for this node_db_migrate_tutorial

 

Bytefilia