The queries we need to test

My Cordova application is using Ionic (and implicitly AngularJS) and it implements two SQL queries: insertPerson and selectPerson. They can be executed through an AngularJS service named DatabaseService.

    angular.module('starter', ['ionic', 'ngCordova'])
        .service('DatabaseService', function($ionicPlatform, $cordovaSQLite, LoggingService, $q) {
            var db;
            window.document.addEventListener('deviceready', function() {
                db = $cordovaSQLite.openDB({
                    name: "mydb",
                    bgType: 1
                });
            }, false);

            this.insertPerson = function(firstname, lastname, address) {
                var query = "INSERT INTO person (firstname, lastname, address) VALUES (?, ?, ?)";
                var args = [firstname, lastname, address]
                var promise = $cordovaSQLite.execute(db, query, args)
                    .then(function(result) {
                        return result.insertId;
                    });
                return promise;
            };

            this.selectPerson = function(id) {
                var query = "SELECT * FROM person WHERE id = ?";
                var promise = $cordovaSQLite.execute(db, query, [id])
                    .then(function(result) {
                        var person = result.rows.item(0);
                        return person;
                    });
                return promise;
            };

            return this;
        });

Replacing sqlitePlugin with Web SQL

The key to the whole thing is to replace the window.sqlitePlugin object with one whose openDatabase function returns the equivalent of Web SQL.

window.sqlitePlugin = {};  
window.sqlitePlugin.openDatabase = function() {  
  return window.openDatabase('mydb', '1.0', 'myDatabase', 10000000);
};

Creating the database schema

We will need a function that takes in an array of DDL queries to be executed in order to create the database schema.

var processQueries = function(db, queries, dbname) {  
  db.transaction(function(tx) {
    for (var idx = 0; idx < queries.length; idx++) {
      tx.executeSql(queries[idx], [], 
        function() {
          console.log('done');
        },
        function(tx, err) {
          console.log('error: ' + err.message);
        }
      );
    }
  });
};

We can fetch and process these DDL queries from an online storage.

$.ajax({
  url: 'https://cdn.rawgit.com/apandichi/demos/eaf2109abc17f90f6cad8340e0b491eca90aa766/db-migrations-cordova/www/spec/db-schema.sql',
  type: 'get',
  async: false,
  success: function(response) {
    var db = openDatabase('mydb', '1.0', 'myDatabase', 10000000);
    var queries = response.split(';\n');
    processQueries(db, queries, 'myDatabase');
  },
  error: function(response) {
    console.log("error!", JSON.stringify(response));
  }
});

Jasmine setup

The replacement of sqlitePlugin and the creation of the database schema needs to happen just once before running all the Jasmine tests in your suite. This means that you'll have to wrap those code snippets above into a Jasmine beforeAll function. As of version 2.1 of Jasmine, this function is available out of the box. If you're using an earlier version, you can add jasmine-beforeAll to your test dependencies, which will enable the needed hook.

Also, in my case, I need to inject the DatabaseService into the test, to manually trigger the deviceready event, and to provide an implementation (the Q library) to the AngularJS $q promise service.

    var DatabaseService;

    beforeEach(function() {        
      angular.mock.module("starter");
      angular.mock.module(function ($provide) {
        $provide.value('$q', Q);
      });

      inject(function(_DatabaseService_) {
        DatabaseService = _DatabaseService_;
        helper.trigger(window.document, 'deviceready');
      });
    });

The actual Jasmine test

The following test will insert a person into the database, retrieve the person record based on the inserted id, and it will make assertions on the retrieved result.

    it("should save person into the database", function() {
      runs(function() {
        var promise = DatabaseService.insertPerson("Jon", "Arbuckle", "Somewhere in the US")
          .then(function (insertId) {
            return DatabaseService.selectPerson(insertId)
          });
        return promise;
      }, function(result) {
        expect(result.firstname).toBe("Jon");
        expect(result.lastname).toBe("Arbuckle");
        expect(result.address).toBe("Somewhere in the US");
      });

See the full example

You can check out the full code example on Github: db-migrations-cordova. It builds upon the previous article about database migrations in Cordova.
The most relevant files for this blogpost are:

• www/js/app.js - the AngularJS application
• www/spec/index.js - the Jamine test file
• www/spec.html - an HTML test runner. Load this in a browser that supports Web SQL, such as Google Chrome. You should be able to see the passing test, and to inspect the Web SQL tables with Chrome's Dev Tools.

In the Java ecosystem, there are tools and libraries that can help you automate this process: Flyway, MyBatis Migrations, Liquibase, etc. For Cordova, the only relevant things I found out there are a blogpost about migrating WebSQL databases (a dead standard, remotely relevant) and persistencejs, a mapper library which provides a plugin to support migrations and which can be used with Cordova. Seeing as I don't trust ORMs that much, I wanted to implement my own solution.

Before diving into code, note that in order to avoid nested callbacks (aka callback hell), I decided to use ngCordova's sqlite plugin wrapper which returns a promise for each API method, allowing me to neatly chain the SQL operations. This also means that we'll have to use AngularJS.

Migration process overview

Each new version of the app will introduce a set of changes to the database (creating new tables, adding new columns to existing tables, etc) as a collection of DDL statements associated with a version number.

  var version1 = {
    versionNumber: 1,
    queries: [
      "CREATE TABLE IF NOT EXISTS person(id INTEGER PRIMARY KEY NOT NULL, firstname VARCHAR(100), lastname VARCHAR(100))",
      "CREATE TABLE IF NOT EXISTS pet(id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(100))"
    ]
  };

  var version2 = {
    versionNumber: 2,
    queries: [
      "ALTER TABLE person ADD address VARCHAR(100)",
      "ALTER TABLE pet ADD ownerId INTEGER"
    ]
  };

We will also need a migration history table which will keep a record of all the migration steps that were already applied to the database of the device. We'll wrap this in a function that will return a promise resolving to the version number 0 after executing successfully.

  var createVersionHistoryTable = function() {
    var query = "CREATE TABLE IF NOT EXISTS version_history(versionNumber INTEGER PRIMARY KEY NOT NULL, migratedAt DATE)";
    var promise = $cordovaSQLite.execute(db, query, [])
    .then(function() {
      var versionNumber = 0;
      return versionNumber;
    });
    return promise;
  };

Before running the migration, the app will need to find out what schema version is currently installed on the device, so that it can skip the steps that have been already applied.

  var selectCurrentVersion = function() {
    var query = "SELECT MAX(versionNumber) AS maxVersion FROM version_history";
    var promise = $cordovaSQLite.execute(db, query)
      .then(function(res) {
        var maxVersion = res.rows.item(0).maxVersion;
        return maxVersion;
    });
    return promise;
  };

These two functions are the initial steps that need to be performed everytime a new app is installed. (Actually, running them every time the app starts seems to be a good enough compromise.) The easiest way to go about this would be:

  createVersionHistoryTable().then(selectCurrentVersion)
    .then(function(maxVersion) {
      console.log("The current version is: " + maxVersion);
    })

But what if there were 10 initial steps to be performed, instead of two? Would I write 10 consecutive thens? I could, but I think there's a more elegant way to chain promises using Array.reduce. You'll later see how this benefits us.

  var initialSteps = [
    createVersionHistoryTable,
    selectCurrentVersion
  ];

  initialSteps.reduce(function(previous, current) {
    return previous.then(current);
  }, $q.when())
  .then(function(maxVersion) {
      console.log("The current version is: " + maxVersion);
  })

The above snippet is equivalent to the one above it. If I had 10 initial steps, I would just add them to the array. $q is just Angular's implementation of promises/deferred objects. $q.when() stands for the initial value of the reduce operation. It's basically a noop for promises.

In a similar way, we will need to be able to execute in chain an array of queries. Each version will have a different array of queries.

  var executeInChain = function(queries) {
    var promise = queries.reduce(function(previous, query) {
      return previous.then(function() {
        return $cordovaSQLite.execute(db, query, [])
      });
    }, $q.when());
    return promise;
  };

After running a migration step, we need to store into the version history table the version number associated with the queries array that we just ran.

  var storeVersionInHistoryTable = function(versionNumber) {
    var query = "INSERT INTO version_history (versionNumber, migratedAt) VALUES (?, ?)";
    var promise = $cordovaSQLite.execute(db, query, [versionNumber, new Date()])
      .then(function(res) {
        console.log("Stored version in history table: " + versionNumber);
        return versionNumber;
      });
    return promise;
  };

For each version object, we need to check the current version number, execute the queries if the version was not applied yet, store the version number in the history table and return a promise that can be chained together with the rest of the other versions.

  var versions = [
    version1,
    version2
  ];

  var migrationSteps = versions.map(function(version) {
    return function(currentVersion) {
      if (currentVersion >= version.versionNumber)
        return $q.when(currentVersion);

      var promise = executeInChain(version.queries).then(function() {
        console.log("Version "+version.versionNumber+" migration executed");
        return version.versionNumber;
      })
      .then(storeVersionInHistoryTable);

      return promise;
    };
  });

An important thing to note here is that we pass the current version number from one promise to the next, to determine if a certain step needs to be applied or not.

Let's put it all together, including the initial steps related to the history table:

  var steps = initialSteps.concat(migrationSteps);
  steps.reduce(function(previous, current) {
    return previous.then(current);
  }, $q.when())
  .then(function() {
    console.log("All migrations executed");
  })
  .catch(function(error) {
    console.error(JSON.stringify(error));
  });

Now, when we'll need a new version, all that needs to be done is to declare it, and add it to the array. Everything else should work outside the box.

  var version3 = {
    versionNumber: 3,
    queries: [
      // whatever
    ]
  };

  var versions = [
    version1,
    version2,
    version3 // new version, yay!
  ];

Conclusions

You can see a working example in its entirety in my demos GitHub repo. Note that it uses the Ionic framework, although it's not a must. On the other hand, AngularJS is required because of the ngCordova dependency.

With a little bit more work, I think this could be converted into a separate library. The end user of this library would only need to declare the versions array and run a migrate() API function when the device is ready. In the end, it would be similar to what persistencejs is offering in its migration plugin, without the ORM stuff.

One downside of all this is that rollback is not supported. If one of the DDL statements defined in any migration step has a syntax error, the database might be left in a state that needs manual intervention. So be sure to double check the correctness of your queries!