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 (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) {
            function(tx) {

                var sql = "SELECT, e.firstName, e.lastName, e.title, count( reportCount " +
                    "FROM employee e LEFT JOIN employee r ON r.managerId = " +
                    "WHERE e.firstName || ' ' || e.lastName LIKE ? " +
                    "GROUP BY 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);
            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 ( {
            dao.findById(, function(data) {
        } else if (model.managerId) {
            dao.findByManager(model.managerId, function(data) {
        // removed for brevity


Source Code

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

29 Responses to Building Mobile Apps with HTML and a Local Database

  1. staras April 12, 2012 at 10:07 am #

    Thank’s for a new post. LocalDb approach is very usefull. I wonder how difficult is to synchronize local database with server database. I am waiting for a new post about this subject.

  2. josbe April 13, 2012 at 9:53 am #

    Why doesn’t browser version work with iphone ?

  3. pablo April 23, 2012 at 6:22 pm #

    Hi, there is a better approach for the backbone.sync? I am thinking in a bigger app with some other DAOS.

    Maybe using the url property as it would be a RESTFULL app to decide how what DAO function to call?

  4. Calling all Technology Professionals May 1, 2012 at 9:58 am #

    Guys check this out….. If you have ideas on mobile apps for use on phones, submit ur apps a contest that gives you a chance to prove your mettle. Whether you are a professional developer or a student, every1 can submit their apps developed by using the Series 40 Web apps SDK and win.
    Register today to submit ur apps….

  5. Prakash Shrestha May 22, 2012 at 6:45 pm #


    this is very ideal tutorial for mobile application developers. But what I’m exactly looking for is accessing remote MySql database from android application.

    I’d heghly appreciate your answer.

    • Sandi Sawyerr May 30, 2012 at 5:25 am #

      Prakash: I have been working on a few Android apps which will utilize the local SQLite database (using phonegap or native android). My plan is to build in a Synch process (kicked off when device is connected to the Internet).

      The Synch process will then synch data with the MySQL database. I have it figured out in theory, but have not coded this process yet. Back in 2010, I wrote an application that did the same thing with Access databases (synching between a stand-alone tablet pc to a network version).

      I’d love to know more if you have done something similar – or where you are with your coding. Please contact me via my website, Thanks.

  6. Koen June 6, 2012 at 5:04 pm #

    Hello, thank you for sharing so much information.
    I’m going to check out more on github.

  7. Shork September 3, 2012 at 9:29 am #


    i’m try to connect a simple app to existing sqlite db, i’ve create a db and add it to the xcode project (Resources folder) but when i start the simulator i see that the app find the file not in this folder but in a simulator path, how can set te real dbpath?


    • Shork September 3, 2012 at 9:33 am #

      These are the xCode log:

      2012-09-03 11:31:21.417 staffLocale[2000:13403] Detected docs path: /Users/macbookpro/Library/Application Support/iPhone Simulator/5.0/Applications/69CEC18D-66D4-4B6C-89F5-AA543406FF60/Documents

      2012-09-03 11:31:21.418 staffLocale[2000:13403] using dbPath: /Users/macbookpro/Library/Application Support/iPhone Simulator/5.0/Applications/69CEC18D-66D4-4B6C-89F5-AA543406FF60/Documents/employee.db

      but this path not exist???

    • Amit November 9, 2012 at 6:38 am #

      sir can you please explain where to store local db of phoneGap in android? and how to brows this db file? sir please help… I not getting the phonegap database idea..

      • Tushar December 20, 2013 at 11:38 am #

        Hi Amit,

        Even i’m also stuck for the same problem, if you get to know the solution for this problem, please please mail me at my id i.e.

  8. recommended you read September 9, 2012 at 3:36 am #

    Steve Carell’s face is just funny. Building Mobile Apps with HTML, can a person with little background on programming can just do it?

  9. NewbieAmber October 23, 2012 at 9:43 pm #

    Hi I’m trying to create a simple contact info form for android and ipad that saves the info offline on a database then uploads to a server upon internet connection. This article seems like it is the answers to my prayers but I am too much of a newbie to understand what exactly to do, so for a simpleton like me would you mind “breaking it down” so a baby could do it?

  10. Pete November 21, 2012 at 5:59 am #

    This is definitely very useful for me. Being new into this space though, I am wondering what about the possibilities of somehow syncing a local and a remote db. Does anybody know anything about this?

    For instance, I am building a small quiz app – I’d like the user to be able to use it without an internet connection, but I’d like their results to sync up to the main server when they have access.

  11. Great blog right here! Additionally your site lots up very fast! What host are you using? Can I get your affiliate link on your host? I wish my web site loaded up as quickly as yours lol

  12. sohbet odalari December 21, 2012 at 12:09 am #

    hello nice blog thanks

  13. sac longchamps le pliage March 21, 2013 at 1:50 pm #

    I would like more information about this, because it is very nice., Thanks for sharing

    • Jhon March 17, 2014 at 9:06 am #

      yeah! it is very nice information about mob apps :)

  14. Djiit May 20, 2013 at 7:35 pm #

    Hi Christophe,

    Your tutorials rock ! i learned so much about Backbone.js and Phonegap the last past days thanks to your blog posts :).

    I was wondering if the way you populate the DB during app startup is ok for production use?

    Should I store the data in a JSON file, load it during app start process and populate the DB with the data or is there a better way to do this ?

  15. Kolay Tarifler July 25, 2013 at 9:07 am #

    please more information about this

  16. igg points hack September 15, 2013 at 10:14 am #

    As the admin of this website is working, no uncertainty
    very quickly iit will be famous, due too its feafure contents.

  17. alex November 26, 2013 at 9:30 am #

    Hi, please help
    How do I specified the path of the database?

    I try using html5sql and I coudnt specified path either.


  18. Gonzalo Sosa Rolon February 25, 2014 at 10:24 am #


    I implemented a more generic version using this tutorial. If you want to take a look just go to my github:

  19. Jhon March 17, 2014 at 9:05 am #

    nice information

  20. Addyson March 17, 2014 at 9:09 am #

    how to deploy iphone app?? Please give me a answer.. I need this

  21. I think this is among the so much significant information
    for me. And i’m happy studying your article. But wanna observation on some normal issues, The website taste is ideal, the articles is really great : D.

    Good job, cheers

  22. April 14, 2014 at 2:36 pm #

    A person necessarily assist to make critically articles
    I’d state. This is the very first time I frequented your web
    page and up to now? I surprised with the analysis you made to
    create this actual post extraordinary. Wonderful task!


  1. HTML5 | Pearltrees - April 19, 2012

    [...] Building Mobile Apps with HTML and a Local Database 13 14 12 15 11 16 [...]

  2. Offline Access – SQLite or IndexedDB | Jackson F. de A. Mafra - July 29, 2013

    [...] [...]

Leave a Reply