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.

  • staras

    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.

  • josbe

    Why doesn’t browser version work with iphone ?

  • Pingback: HTML5 | Pearltrees()

  • pablo

    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?

  • Calling all Technology Professionals

    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

  • Prakash Shrestha

    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.

    • 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.

  • Koen

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

  • Shork

    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

      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

      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

        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

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

  • NewbieAmber

    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?

  • 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.

  • 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

  • hello nice blog thanks

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

    • Jhon

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

  • Djiit

    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 ?

  • please more information about this

  • Pingback: Offline Access – SQLite or IndexedDB | Jackson F. de A. Mafra()

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

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

    I try using html5sql and I coudnt specified path either.

    Thanks

  • Gonzalo Sosa Rolon

    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

  • Jhon

    nice information

  • Addyson

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

  • 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

  • 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!

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

  • Pingback: Cam4 Token Hack | MyBlog.Directory()

  • pramod

    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

  • 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.

  • 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!

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

  • 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!

  • 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

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

  • 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.

  • 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!

  • 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.

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

  • 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.

  • 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.

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

  • 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.

  • 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.

  • 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.

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

  • 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.

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

  • Reinhild

    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.

  • 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?

  • 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!

  • mohsen

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

  • 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!

  • 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.

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

  • Nic

    Hi, is it possible for other web/mobile apps to access another app’s local database so that they can have shared storage?

  • Create Your Own Business Mobile App & Sell Your Products
    Its Greate App For Sell Online Via Mobile And It Has Good Feature Upload & Update Products .
    Send notification Of latest Offer of Your Customer. Create Now

    Appresive App

  • Fabrics we use in our form; The first-class micro-interlock; flex-fit, anti-bacterial, it has a thermo-balance, and immediately drying. This gives you the freedom to move your soccer jerseys of the match; It is designed stylish enough to be worn on a daily basis. Our models are produced in two types as my special jerseys and other digital printed.

  • hi i want to develop a android app, which give new movie related information like imdb.
    So how can i manage my data. How can i start develop such an app.
    I am new to this world of android development please guide me

  • zdeno

    Hi all,

    I have a question about this Mobile Apps with HTML and a Local Database part.
    Is it possible to make there local iphone notification? Or notification I can create only for native apps(pure objective c)?

    Thanks,
    Zdeno

  • Ilaj

    I would like that these gurus make at least some comprehensive, clear and briefly explained example “how to make jQM app that create, populate, retrieve, edit and delete records”… everyone chat how its fun to code mobile devices but to find some usable and working example is like to find out an emerald! Weird.

  • Paul Ambler

    You’re so trendy! I do not expect I have in fact look at anything equal to this before. So fantastic to disclose an individual with the really first guidelines on this subject. most definitely, we value you beginning this up. personal injury services

  • Nice Post – See Milk delivery App in Indore

  • Paul Ambler

    I just want to claim that this blog post is incredibly wonderful as well as a very helpful short write-up. I will absolutely guarantee to be reviewing your blog site more. You made an asset yet I can not assist however ask yourself, what pertaining to the contrary side?. http://thewellgroco.com/what-is-gmod-and-what-are-gmod-mods/

  • Shine Brand Seeds is a Brand of Rise Agro Infra Pvt. Ltd. We are dealing in hybrid vegetable seed and field crops quality seeds.

  • JOHN CENA

    You’ve got a true ability for composing content that is unique. I like the way you think and how you represent your views in this report. I agree with your way of believing. Thanks for sharing. ! Continue the superb work. Best Barbershop

  • JOHN CENA

    The mind-boggling Article is genuinely informational and innovative to refresh us as frequently as conceivable with new updates. It was really significant. thankful Garage Door Repair Company

  • JOHN CENA

    The extraordinary site you arrived! It would be ideal if you continue refreshing, I will def read more. It’ll be in my bookmarks so better update! See more

  • JOHN CENA

    Howdy! An obligation of appreciation is all together for the tremendous information you have given! You have addressed crucial centers! See more

  • JOHN CENA

    I visit your blog often and endorse it to the individuals who expected to improve their knowledge without any problem. The way of creating is splendid and moreover, the substance is a decision. An obligation of appreciation is all together for that astuteness you give the peruses. See more

  • JOHN CENA

    I am uncommonly happy to discover your post as it will be on top of my get-together of most cherished destinations to visit. visit this website

  • JOHN CENA

    Hey, you used to compose awesomely, yet the last scarcely any posts have been kind of exhausting… I miss your extraordinary works. Past a few posts are slightly out of track! please! Click here

  • JOHN CENA

    This is really respectable and instructive, containing all information moreover extraordinarily influences the new development. An obligation of appreciation is all together for sharing it, See more

  • JOHN CENA

    I found this post while chasing down some connected information on location search…It is a respectable post. Keep posting and overhaul the information. Professional Roofing Expert

  • JOHN CENA

    Actually an incredible expansion. I have perused this heavenly post. Much obliged for sharing the data about it. I truly like that. Much appreciated so a ton for your gather. Concrete Lifting Foam Company

  • JOHN CENA

    This article contains incredible unique reasoning. The instructive substance here demonstrates that things aren’t so highly contrasting. I feel more intelligent from simply understanding this. Golsen Family Dentistry

  • JOHN CENA

    The heavenly article, an obligation of appreciation is all together for gathering this! This is unmistakably one phenomenal post. An obligation of appreciation is all together for the significant information and encounters you have so given here. Estate Attorney Review

  • JOHN CENA

    I am glad to be here and this superb article. I’ve found here a great deal of fascinating data for the insight I need. all that you give to us, it was extremely useful and valuable, much obliged for sharing this marvelous post. NC Planning Review

  • JOHN CENA

    Your blog is extremely compelling and important. I truly like it without a doubt. I expectation you will before long share your next post about this conversation. Much obliged for sharing and continue sharing. see more

  • JOHN CENA

    I have you bookmarked your site page to try the fresh out of the box new effects you post. official link

  • JOHN CENA

    What a post ! I am super grateful for the maker and essayist for quite a decent blog and putting helpful data. this keyword

  • JOHN CENA

    Awesome blog entry. This is total wizardry from you! I have never observed a more great post than this one. You’ve truly filled my heart with joy today with this. I trust you keep this up! see more

  • JOHN CENA

    The informative substance here exhibits that things aren’t so profoundly differentiating. I feel keener on essentially getting this. show this website

  • JOHN CENA

    For quite a while I am visiting your blog and discovered it truly great, I should state that this webpage has excellent substance. Much obliged for proceeding with the great work! read more

  • JOHN CENA

    Extraordinary rousing article. I truly appreciate perusing your post due to its extraordinary data for me. Much appreciated for sharing this awesome post! a company

  • JOHN CENA

    As I was looking through a similar inquiry again yet this time discovered your blog arbitrarily and it is the most happening website with loads of good online journals. It would be ideal if you have an update soon. visit this link

  • JOHN CENA

    The Superbly composed article, if just all bloggers offered a similar substance as you, the web would be an obviously better spot. Astounding substance you distributed on your blog entry. A debt of gratitude is in order for sharing this! a company

  • JOHN CENA

    Extraordinary site. I acknowledged perusing your own articles. That truly is actually a phenomenal report for me by and by. I’ve investigated it and I’m foreseeing perusing new substance articles. Keep up the phenomenal work! visit this link

  • JOHN CENA

    This article is useful for each guest. I value your work. I will return to the site to peruse your sites. Much obliged for posting. Continue posting. a company

  • JOHN CENA

    Great post !!An amazing offer! I just sent this to a collaborator who has been leading a little exploration on this. this keyword

  • JOHN CENA

    Hi. I have perused the greater part of your posts. This post is likely where I got the most valuable data for my examination. visit for more information

  • yuvaraj singh

    Thanks for sharing this informative content
    Leanpitch provides online training in Devops during this lockdown period everyone can use it wisely.
    Devops Online Training

  • Akash

    Keeto is your personalized financial fitness tracking, credit score improvement & Money Manager app. Get an Instant loan, Cashe Loan & Moneytap (via Partners).

  • JOHN CENA

    Unprecedented post yet I was pondering whether you could make fairly more regarding this matter? I’d be thankful in the event that you could clarify genuinely further. Justified, despite any trouble Check Out

  • JOHN CENA

    I feel extremely appreciative that I read this. It is extremely useful and enlightening and I truly took in a ton from it. https://insighthikingequipment.hpage.com

  • JOHN CENA

    Thankful for making the push to inspect this, I feel firm about this and value focusing fundamentally more regarding this matter. If conceivable, as you gain authority, would you mind invigorating your page with fundamentally more nuances? It’s especially valuable for me. sewerexpertscompany.cabanova.com

css.php