Building Mobile Apps with HTML and a Local Database

After my recent post, Crafting Native Looking iOS Apps with HTML, a number of you asked for an offline version that would use a Local Database (instead of the simple in-memory store) and provide a mechanism to automatically keep the local database in sync with a server database.

I’ll save automatic data synchronization strategies for a future post, but here is the first step: an “offline” version of the application that uses the device’s or the browser’s local database as its data provider. This version still uses Backbone.js as its architectural framework. Backbone.js makes it easy to change its default data access mechanism (which assumes RESTful services). You just replace the default Backbone.sync implementation and provide your own data access logic: in this case, some local SQL logic.

Web SQL vs IndexedDB

As you probably know, there have been two competing database APIs for HTML. From the W3C web site:

• The Web SQL specification defines an API for storing data in databases that can be queried using a variant of SQL. This specification is no longer in active maintenance and the Web Applications Working Group does not intend to maintain it further.
• The Indexed Database specification defines APIs for a database of records holding simple values and hierarchical objects. It is a working draft, and “work in progress”.

Even though the W3C is no longer actively maintaining the spec, this application uses the Web SQL API because, as a mobile app, its two main target platforms are iOS and Android, which both currently support Web SQL but not IndexedDB. More detailed platform support information can be found on caniuse.com (Web SQL and IndexedDB).

Chrome, Safari, and Opera on the desktop also support Web SQL, which means that you can run the application in these browsers. Try it here. For example, using the Chrome Developer Tools you could debug the application and inspect the database as shown in this screenshot:

Firefox and IE don’t support Web SQL. You could easily create an alternative version of EmployeeDAO (described below) that uses IndexedDB instead. You could also create a version of the application that uses either Web SQL or IndexedDB depending on the platform it’s running on.

Code Highlights

The source code is available in the localdb folder of the backbone-directory repository on GitHub. Here is a quick walkthrough…

The data access logic is encapsulated in EmployeeDAO, which also has a “populate” function to populate the employee table with sample data.

directory.dao.EmployeeDAO = function(db) {
    this.db = db;
};

_.extend(directory.dao.EmployeeDAO.prototype, {

    findByName: function(key, callback) {
        this.db.transaction(
            function(tx) {

                var sql = "SELECT e.id, e.firstName, e.lastName, e.title, count(r.id) reportCount " +
                    "FROM employee e LEFT JOIN employee r ON r.managerId = e.id " +
                    "WHERE e.firstName || ' ' || e.lastName LIKE ? " +
                    "GROUP BY e.id ORDER BY e.lastName, e.firstName";

                tx.executeSql(sql, ['%' + key + '%'], function(tx, results) {
                    var len = results.rows.length,
                        employees = [],
                        i = 0;
                    for (; i < len; i = i + 1) {
                        employees[i] = results.rows.item(i);
                    }
                    callback(employees);
                });
            },
            function(tx, error) {
                alert("Transaction Error: " + error);
            }
        );
    },

    findById: function(id, callback) {
        // removed for brevity
    },

    findByManager: function(managerId, callback) {
        // removed for brevity
    },

    populate: function(callback) {
        // removed for brevity
    }
});

Models are annotated with a “dao” attribute to indicate which data object to use to access their underlying data.

directory.models.Employee = Backbone.Model.extend({

    dao: directory.dao.EmployeeDAO,

});

directory.models.EmployeeCollection = Backbone.Collection.extend({

    dao: directory.dao.EmployeeDAO,

    model: directory.models.Employee,

});

With that infrastructure in place, you can then override Backbone.sync to access data from the local database instead of RESTful services:

Backbone.sync = function(method, model, options) {

    var dao = new model.dao(directory.db);

    if (method === "read") {
        if (model.id) {
            dao.findById(model.id, function(data) {
                options.success(data);
            });
        } else if (model.managerId) {
            dao.findByManager(model.managerId, function(data) {
                options.success(data);
            });
        } 
        // removed for brevity
    }

};

Source Code

The source code is available in the localdb folder of the backbone-directory repository on GitHub.