Migrations

As we mentioned in the AJAX discussion, most single-page apps use AJAX to load additional data from the server. Our Scrumtastic application will be no different. It is intended to help us manage multiple software development projects - and that's a good place to start, by defining a Project resource that we can serve through the API.

Our Project resources need to persist even if the server is shut down, so we need some form of persistent storage. We'll utilize sqlite3 for this, building on our database discussion. So let's define the structure for a table containing our project information:

CREATE TABLE projects ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, description TEXT, version TEXT, repository TEXT, license TEXT );

We can use this SQL to create a table to hold our projects. But each time we set up a new Scrumtastic server, we'd need to create a new database and run this SQL command again... this can make our install process very involved, especially as we add additional tables.

One strategy to manage this complexity is to use migrations. These are commands to create database structure that are run exactly once when the application is deployed. The only once caveat is important - as some commands we place in a migration might create additional copies of records in the database, or throw errors when we try to re-create existing database structure.

Implementing Migrations

To my reasoning, writing migrations as SQL files just makes sense - it's the native format for talking to the database. However, many web frameworks create a domain specific language for writing migrations, which typically falls somewhere between the language of the framework and SQL. There's nothing wrong with this approach, but it does require you to learn one more language... albeit a simple one. Ruby on Rails and Django are good examples of this approach.

But if we want to keep migrations as SQL files, we need to store them in a folder. Let's call ours migrations. Also, we need to have our migrations run in a specific order - we can't create a table with a foreign key if the table the key references doesn't yet exist. Many migration systems use a timestamp as part of the migration name to mitigate this - but timestamps are challenging to create by hand. So let's number our migration files, starting with 1-create-projects. This is the same file we've already seen:

CREATE TABLE projects ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, description TEXT, version TEXT, repository TEXT, license TEXT );

Ordering our Migrations

To order our migrations, we can read in the contents of our migrations directory with the fs.readDirSync() function. This gives us an array of filenames. We can further sort this array using the Array.prototype.sort() method. The default sort this method applies is based on Unicode points, which means 10 will come before 3. We can get around this by providing Array.prototype.sort() a custom sorting function, which uses parseInt() to convert the migration names into integers (which conveniently throws away the text), and subtracts them:

var migrations = fs.readdirSync(dir).sort(function(a,b){ return parseInt(b) - parseInt(a); })

Because we want ascending order, we subtract b from a.

Preparing our Migrations Table

We also need to keep track of if a migration has been previously run - remember, running a migration we had previously run can be problematic - causing errors or duplicate records. We need some way of persistently keeping track of which migrations have been run previously... which is exactly what a database table can do.

We'll want to create our table using a command like:

CREATE TABLE IF NOT EXISTS migrations (id INTEGER PRIMARY KEY, filename TEXT NOT NULL);

Here we use the IF NOT EXISTS condition to keep from re-creating the migrations table if we've previously created it. This makes it safe to run this command every time we try migrating.

If we're running this command from JavaScript, we're probably using a db object like the one the Sqlite3 module provides. We want to make sure that it runs before any migrations we make, so we'll leverage the db.serailize() function:

// Seralize all database calls so that migrations // are run in order db.serialize(function(){ // Create the migrations table, if it does not yet exist db.run( "CREATE TABLE IF NOT EXISTS migrations " + "(id INTEGER PRIMARY KEY, filename TEXT NOT NULL);" ); // TODO: Run our migrations in order });

Running the Migrations

Before running the migrations, we want to check to see if the migration has previously been run. If it has, we can ignore it. If it hasn't, we need to read the corresponding SQL file and execute it against our database.

In either case, once we've run or skipped all the migrations, we want to let the user know the migrations have finished. We can do this with a simple counter variable we decrement every time we skip or run a migration.

And, of course, if we run a migration, we also need to save the fact we ran a migration in the migrations table. The finished version of our migrate.js module would look something like this:

"use strict"; /** @module migrate * A module for performing migrations on a database */ module.exports = migrate; /* requires */ var fs = require('fs'); /** @function migrate * Applies the specified directory of migrations (sql files) * on the supplied database, if they have not been previously * applied. The alphanumeric order of the file names is the * order migrations will be applied in. * @param {sqlite3.database} db - the database to migrate * @param {string} dir - the directory of migration sql files * @param {function} callback - a callback to trigger on error or * when finished, with one parameter for the error. */ function migrate(db, dir, callback) { var migrations = fs.readdirSync(dir); var todo = migrations.length; // Seralize all database calls so that migrations // are run in order db.serialize(function(){ // Create the migrations table, if it does not yet exist db.run( "CREATE TABLE IF NOT EXISTS migrations " + "(id INTEGER PRIMARY KEY, filename TEXT NOT NULL);" ); // For all the migrations in the migrations directory migrations.forEach(function(migration){ // Check if the migration was previously run // (i.e. it's in our migrations table) db.get("SELECT id FROM migrations WHERE filename=?;", [migration], function(err, row){ if(err) {return callback(err);} if(!row) { // migration is not in the table, so run it... var sql = fs.readFileSync(dir + "/" + migration, {encoding: 'utf8'}); db.run(sql, [], function(err, result) { if(err) {return callback(err);} // save the migration to the migrations table db.run("INSERT INTO migrations (filename) VALUES (?);", [migration], function(err){ if(err) {return callback(err);} todo--; if(todo == 0) callback(false); }) }); } else { todo--; if(todo == 0) callback(false); } }); }); }); }

Let's place this file in a lib directory - for libraries we have created for Scrumtastic. Now that we've got our migration system in place, when is the appropriate time to run them?

Running Migrations on Server Launch

One possibility is every time the server is launched. This will slow down our server startup, but it will ensure we always have the latest database structure. We can modify our server.js file to accomplish this task:

... // Set up the database var sqlite3 = require('sqlite3').verbose(); var db = new sqlite3.Database('scrumtastic.sqlite3', function(err) { if(err) console.error(err); }); // Run the migrations var migrate = require('./lib/migrate'); migrate(db, 'migrations', function(err){ // Launch the server server.listen(PORT, function(){ console.log("listening on port " + PORT); }); });

Note that here we don't launch the server until the migrations callback finishes - this happens once all migrations have been run or skipped.

Running Migrations as a Script

An alternative to this approach gives the developer more control over when migrations are run. This can be handy when we have a half-written migration we aren't ready to execute, but we need the server running. It also get the server up and running faster.

Basically, we need to write a special script to execute our migrations. Let's name this migrate, and place it in a scripts directory:

// Set up the database var sqlite3 = require('sqlite3').verbose(); var db = new sqlite3.Database('scrumtastic.sqlite3', function(err) { if(err) console.error(err); }); // Run the migrations var migrate = require('../lib/migrate'); migrate(db, 'migrations', function(err){ if(err) console.error(err); else console.log("Migrations complete!"); });

We could run this with the command node scripts/migrate, but npm also has built-in support for user-defined scripts. We simply need to add the command to the scripts in our package.json:

{ "name": "scrumtastic", "version": "1.0.0", "description": "A web app for SCRUM software development", "main": "server.js", "scripts": { "test": "echo \"Error: no test specified\" && exit 1", "migrate": "node scripts/migrate.js" } ... }

Now if we run the command npm run migrate at the terminal, our migration script will execute, running each migration and reporting errors or success. Whenever we add new migrations, we simply need to run this command again to apply them to our database.