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.

58 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….
    http://bit.ly/ICUrkh

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

    Hi,

    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, http://www.ladysword.com. 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 #

    Hi,

    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?

    Thanks

    • 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. tusharshuklaa@gmail.com

  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.

    Thanks

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

    Hi!

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

    https://github.com/gonzalo-sosa-rolon/html5-mobile-database/blob/master/README.md

  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. http://Jazzywaif8563.Livejournal.com/ 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!

  23. smite how to get free gems May 8, 2014 at 2:51 am #

    Ԝhat a information of un-ambiguity anԀ preserveness of valuable
    knowledge ϲoncerning unpredicted feelings.

  24. pramod May 14, 2014 at 2:59 am #

    Hi,
    Could you please tell me is Indexed DB is best way to access DB from Javascript. What you think of Cordova-sqllite plugin and sqllite as DB in the device. Kindly reply.

    Rgds/Pramod

  25. smite Gems generator May 20, 2014 at 10:50 am #

    With havin so much content do you ever run into
    any issues of plagorism or copyright infringement? My site has a lot of unique content I’ve either created myself or outsourced but it
    appears a lot of it is popping it up all over the
    internet without my agreement. Do you know any ways to
    help prevent content from being stolen? I’d certainly appreciate it.

  26. smite hack May 23, 2014 at 9:52 pm #

    Hey! I know this is kinda off topic nevertheless I’d
    figured I’d ask. Would you be interested in exchanging links or maybe
    guest writing a blog post or vice-versa? My blog addresses a lot of the same topics as
    yours and I feel we could greatly benefit from each other.
    If you are interested feel free to send me an email.
    I look forward to hearing from you! Terrific blog by the way!

  27. Milford May 28, 2014 at 11:41 pm #

    It’s great that you are getting ideas from this article as well
    as from our dialogue made at this place.

  28. Cam4  Hack June 10, 2014 at 9:08 am #

    Hi there are using WordPress for your blog platform?
    I’m new to the blog world but I’m trying to
    get started and set up my own. Do you need any html coding expertise to make your own blog?
    Any help would be really appreciated!

  29. michael kors outlet June 12, 2014 at 4:16 am #

    It is the best time to make some plans for the long run and it is time
    to be happy. I’ve read this publish and if I may just I desire
    to counsel you few fascinating issues or suggestions.

    Perhaps you can write subsequent articles referring to this article.
    I desire to learn even more issues approximately it!

    michael kors outlet

  30. creature-feature.net June 15, 2014 at 10:03 pm #

    Wonderful, what a weblog it is! This wrbsite gives valuable facts to us, keep
    it up.

  31. foods good for heartburn June 16, 2014 at 8:05 am #

    There are many brands available, and they are generally more affordable than mouthpieces.

    Other preventive measures include maintaining
    your weight, avoid alcoholic beverages, and did not wear tight fit clothing around the waist because all these factors can cause
    acid reflux. Patients who wished to use this type of
    remedy should consult their doctors first, since
    aloe vera has aloe-emoin compound that is known to be as a powerful laxative.

  32. critical illness June 20, 2014 at 1:27 pm #

    It is perfect time to make some plans for the future and it’s time to be happy.
    I have read this post and if I could I wish to suggest you
    few interesting things or suggestions. Maybe you could write next articles referring to this article.

    I desire to read even more things about it!

  33. cure for irritable bowel syndrome June 23, 2014 at 4:17 pm #

    Correct production of stomach acid is extremely important to the digestion of
    fibre, fruit, vegetable and protein matter. Most people with these symptoms
    will normally not see a doctor and choose to let nature take its cause.
    Many people who have IBS try to overlook it, but it
    can’t be ignored for long.

  34. Restoration Services June 23, 2014 at 5:04 pm #

    I read this post fully concerning the comparison of hottest and preceding technologies,
    it’s amazing article.

  35. best cold sore medicine over the counter June 25, 2014 at 3:31 am #

    If you have a history ofcardiovascular disease, diabetes, or
    glaucoma, use it only under a doctorssupervision.
    When this happens, you have just stopped – or prevented – another outbreak.

    Most nuts, including peanuts, almonds, cashews and walnuts.

  36. dentist office 33162 June 26, 2014 at 9:51 pm #

    It is not my first time to pay a quick visit this web page, i am
    browsing this website dailly and get good information from here daily.

  37. What a data of un-ambiguity and preserveness of valuable familiarity regarding unexpected feelings.

  38. Jerri June 30, 2014 at 12:22 am #

    Hi, I do think this is an excellent website.

    I stumbledupon it ;) I am going to come back yet
    again since I saved as a favorite it. Money and freeedom iss the greatest way
    to change, may you be rich and continue to guide others.

  39. get your ex back with positive thinking July 6, 2014 at 8:33 am #

    Hi there everyone, it’s my first go to see at this web site, and paragraph is in fact
    fruitful for me, keep up posting such content.

  40. Jodie July 7, 2014 at 2:18 am #

    It’s remarkable to pay a visit this web page and reading the views
    of all friends on thee topic of this article, while I am also keen of
    getting knowledge.

  41. adwords consultant July 9, 2014 at 12:37 am #

    It’s amazing for me to have a site, which is beneficial designed for my knowledge.
    thanks admin

  42. flat roofing in leeds July 11, 2014 at 2:04 pm #

    What’s up to all, how is the whole thing, I think every one is getting more from this site,
    and your views are fastidious in favor of new viewers.

  43. health articles July 15, 2014 at 7:53 pm #

    I every time spent my half an hour to read this
    webpage’s content everyday along with a cup of coffee.

  44. Reinhild July 16, 2014 at 3:48 am #

    I wonder, how one can set the “do not back up” attribute on a webdatabase in iOS. When I open webdatabase the internal filename that is used by the browser/webview of iOS is no guaranteed. Of course it is possible to find out somehow. But is finding out the file name and than setting the “do not backup” a sensible strategy to try. Is there a PhoneGap API or plugin, which can do it? I have not found one, yet.

  45. bumper scuff repair Bradford July 17, 2014 at 3:46 pm #

    Write more, thats all I have to say. Literally, it seems as though
    you relied on the video to make your point.
    You obviously know what youre talking about, why throw away your
    intelligence on just posting videos to your blog when you could be giving us something
    informative to read?

  46. football players July 17, 2014 at 9:08 pm #

    Exceptional post however I was wondering if you could write a litte more on this
    subject? I’d be very thankful if you could elaborate a little bit
    further. Appreciate it!

  47. mohsen July 21, 2014 at 2:58 pm #

    Hello,
    Can we convert the HTML5 web database to a SQL server database?
    Thank you

  48. 10x10 kitchen renovation cost July 23, 2014 at 12:35 am #

    Fantastiс site. Plentƴ of usefսl info here. I am sending it to sօme pals ans additionallƴ sharing in delicious.
    And οf course, thank you to yoսг effort!

  49. home warranty company reviews July 23, 2014 at 6:16 am #

    Congress provided a casualty loss tax deduction to encourage
    investment in real-estate. Generally most of the braces are needed
    to get worn aligners for pretty much a couple of years.
    Not only computers, even their printers, scanners and peripheral devices are highly rated.

  50. Google December 6, 2014 at 9:13 am #

    Always a massive fan of linking to bloggers that I love but do not get a lot of link really like from.

Trackbacks/Pingbacks

  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

    […] http://coenraets.org/blog/2012/04/building-mobile-apps-with-html-and-a-local-database/ […]

  3. Cam4 Token Hack | MyBlog.Directory - May 10, 2014

    […] you are you looking for more іnformation about igg points hack have a look at our […]

Leave a Reply

css.php