Testing Cordova SQLite queries with Web SQL

Once you start writing SQL queries for your Cordova application, you need to start thinking about how to make sure they work as expected. You'd want to test them in an automated fashion, but you're not ready to invest into a physical mobile device testing rig and you're not fond of emulators either. You want a fast feedback loop, something like running Jasmine tests in a browser. Luckily for you, the Cordova-sqlite-storage plugin emulates the Web SQL API as closely as possible. Let's see how you can leverage that in order to build a few Jasmine tests that validate your SQL queries.

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.