Cordova Sqlite Migrations

A while back, I wrote a post on Getting started with Cordova Sqlite. As I've been working on a hybrid mobile app (more about that coming very soon!), I've found that it is not exactly straightforward how to gracefully handle database migrations... and it took me a bit to get a decent plan in place.

This plan builds off of what we covered in the getting started tutorial mentioned above, so you may want to read through it first to have the context.

DB Versioning with PRAGMA

In sqlite, there are special commands you can execute to get or set some metadata about a database. Coming from MSSQL background, as far as databases go, I'd not heard of these PRAGMA commands, so I am happy to have essentially stumbled upon the documentation for them when looking for info on sqlite migration techniques. One piece of metadata in particular, user_version, is what we'll use for this.

As you may recall from the Getting Started tutorial, we have all of our db initialization in a seedDb function. But, what happens if we need to add/update columns, migrate data, etc... as time goes by and features are added? We could probably get by with parsing the table_info, another PRAGMA command, to perform logic such as if column 'x' exists, rename to column 'y', but that could get clunky and unmanageable very quickly. A better approach is to batch up your changes into what are called migrations - essentially self-contained chunks of changes that should only be ran one time, and for a very specific purpose. Let's create a variable to hold our migrations in the same file that houses our seedDb function:

//widgits.js
var migrations = [];

Notice that our migrations variable is an array - this is important. We need a way to track which migrations have already ran vs those that have not. A very clean way to do this is to use the array's indexes as our guideposts. This is where the user_version can help us - it defaults to 0, the first index in a javascript array, and can be updated to any integer we need (i.e. incremented by 1 as each migration completes). Let's kick off the migrations by checking what our user_version is, and then running the migration at that index, if there is one. This will go at the bottom of our seedDb function so that we can pass in the same transaction it is using - this way if any part of the script fails, there's a full transaction rollback.

//widgits.js seedDb()
function seedDb() {
  //all data init stuff - i.e. object collections to use, etc...
  widgetsDb.transaction(function(trans) {
    //db init stuff - i.e. create tables if they don't exist, etc...

    //finally, after all initial seeding of the db, start our migrations
    trans.executeSql('PRAGMA user_version', function(t, res) {
      var version = res.rows.item(0).user_version;
      //call only the migration matching the version 
      //  if the version is currently 6, then 0-5 have already ran 
      //  and SHOULD NOT be ran again
      kickOffMigration(version, trans);
    });
  });
}

//helper function used to check for the existence of a migration for a version number
//and then kick it off if one exists
function kickOffMigration(version, trans) {
  if (migrations[version] && typeof migrations[version] === 'function'){
    migrations[version](trans);
  }
}

Writing a Migration

You may have realized that we don't currently have any migrations to run - we simply have a variable placeholder for them. Now that we understand how to use PRAGMA user_version to see which migration needs ran, it's time to write a migration!

Each migration has a single responsibility of continuing on the migration process, but that can be broken down into 3 key steps:

• Perform DB manipulations the migration is built for
• Increment the user_version property by 1
• Kick off next migration (if there is one)

The first two steps are important, but let's take a minute to think about the 3rd step. This is something that I almost overlooked, so I think it's worth pointing out its importance.

Let's say a user has installed your app at the very beginning, the db was seeded and life was good. Then, after some time goes by, you release an update with a migration for the db - the seedDb function checks out our current user_version and runs the one migration appropriate for the current version - and life's still good. Same thing happens again, so we have 2 migrations present. Now, a brand new user wants to download your app. Their db gets seeded, the user_version checked, and the first migration is ran. Then the app breaks because your objects and views were expecting the data to look how it should after the second migration. If each migration doesn't fire off the next one, then it can be bad news for someone who is getting all of the migrations at one time! So, let's look at the code:

migrations[0] = function(transaction) {
  var nextVersion = 1;
  //use transaction.executeSql(...) to modify the db or data as needed for this migration
  //then increment the user_version
  transaction.executeSql('PRAGMA user_version = ' + nextVersion);
  //this is migration zero, and we just finished adding our statements to the transaction,
  //so try to kick off the next migration, using our helper
  kickOffMigration(nextVersion, transaction);
};

//maybe a few weeks later you need another migration...
migrations[1] = function(transaction) {
  var nextVersion = 2;
  //same exact process as the first:
  //  1) execute queries to manipulate the db as needed
  //  2) increment user_version
  //  3) kick off the next migration
};

And there you have it - migrations for sqlite! Pretty easy once you break it down!

So, what do you think? This is working for me, at least so far, but maybe there's a better (or more proper) way...? Feel free to share your thoughts via the comments or twitter!

-Bradley