Simple Offline Data Synchronization for Mobile Web and PhoneGap Applications

Being able to work offline is an expected feature of mobile applications. For data-driven applications, it means that you — the developer — will have to store (a subset of) your application data locally, and implement a data synchronization mechanism that keeps your local and server data in sync.

In this article, I describe a simple data synchronization strategy that uses the device’s (or browser’s) SQLite database. The implementation currently leverages the Web SQL API (even though the W3C is no longer actively maintaining the spec) because both iOS and Android support it, but they don’t support IndexedDB, the official alternative. However, the API described below — getLastSync(), getChanges(), applyChanges() — defines a generic synchronization contract, and the solution can be expanded and made “pluggable”: You could create different synchronization objects, each providing a different implementation of these methods. You could then choose which object to plug in based on the context and the platform your application is running on.

Try it in the Playground

Before looking at the code, you can try some offline syncing in this a hosted playground:

  1. Open the Offline Client Playground in Chrome or Safari (they both support Web SQL).
  2. Click the Synchronize button.
  3. Look at the log (the textarea in the middle of the screen): Because it’s the first time you use the application, all the employees have been downloaded from the server and inserted in your local SQLite database.
  4. Clear the log, click the Synchronize button, and look at the log again: because you now have an up-to-date local version of the data, the server didn’t return any change and your local database remains unchanged.
  5. In another tab, open the Server Admin PlayGround.
  6. Modify an existing employee and click Save. (Don’t worry, it’s using your own session-based data set).
  7. Go back to the Offline Client tab, click Synchronize, and notice that the server returned one change, and that it was applied to your local database.
  8. Go back to the Server Admin tab and modify (create, update, delete) other employees. Switch back to the Offline Client tab, click Synchronize, and see how these changes are applied to your local database.
  9. You can also use the Resources Tab in the Chrome Developer Tools to inspect your local database.

Server API

The only piece of infrastructure you need at the server side is an API that returns the items that have changed (created, updated, or deleted) since a specific moment in time expressed as a timestamp.

Here is the RESTful API call used in my application: 10:20:56

The format of the data returned by the server is up to you and is part of the contract between the client and the server. In this application, the server returns the changes as an array of JSON objects. The server-side technology (RoR, PHP, Java, .NET, …) and database system (SQL, NoSQL, …) you use to generate the list of changes is also totally up to you. I provide a simple PHP implementation as part of the source code. That implementation manages a session-based data set that provides an isolated and transient playground. In a real-life application, you’d obviously get the data from some sort of database.

Client API

At the client side, our synchronization API consists of three methods.


A method that returns a timestamp to be used as the query parameter for the next synchronization request. A common practice is to persist a timestamp after each synchronization request. But things can go wrong and the timestamp itself can get out-of-sync. I prefer to “recalculate” the lastSync timestamp before each synchronization request.

getLastSync: function(callback) {
        function(tx) {
            var sql = "SELECT MAX(lastModified) as lastSync FROM employee";
            tx.executeSql(sql, this.txErrorHandler,
                function(tx, results) {
                    var lastSync = results.rows.item(0).lastSync;


This is a wrapper around an Ajax call to the server-side API that returns the items that have changed (created, updated, or deleted) since a specific moment in time defined in the modifiedSince parameter.

getChanges: function(syncURL, modifiedSince, callback) {

        url: syncURL,
        data: {modifiedSince: modifiedSince},
        success:function (changes) {
        error: function(model, response) {



A method that persists the changes in your local data store. Notice that SQLite supports a convenient “INSERT OR REPLACE” statement so that you don’t have to determine if you are dealing with a new or existing employee before persisting it.

applyChanges: function(employees, callback) {
        function(tx) {
            var l = employees.length;
            var sql =
                "INSERT OR REPLACE INTO employee (id, firstName, lastName, title, officePhone, deleted, lastModified) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";
            var e;
            for (var i = 0; i < l; i++) {
                e = employees[i];
                var params = [, e.firstName, e.lastName, e.title, e.officePhone, e.deleted, e.lastModified];
                tx.executeSql(sql, params);
        function(tx) {

Synchronization Logic

With these server and client APIs in place, you can choreograph a data synchronization process as follows:

sync: function(syncURL, callback) {

    var self = this;
        self.getChanges(syncURL, lastSync,
            function (changes) {
                self.applyChanges(changes, callback);


Final Notes

  • This solution currently supports unidirectional (server to client) data synchronization. It could easily be expanded to support bidirectional synchronization.
  • This solution currently implements “logical deletes”: items are not physically deleted from the table, but the value of their “deleted” column is set to true.
  • As mentioned above, you could replace the Web SQL implementation with another data access strategy. For example, take a look at Brian Leroux’ Lawnchair for another local persistence solution.

Source Code

The source code is available in this GitHub repository.

68 Responses to Simple Offline Data Synchronization for Mobile Web and PhoneGap Applications

  1. Sunil May 16, 2012 at 8:18 pm #

    Hi Christophe,

    I am trying to build an app with technoligies Javascript,spring and nosql flavour.
    Found the backbone fraemwork good to start with instead of jquery etc. I am very new to java scripting.
    I have some queries, which i hope i can post

    1. Is backbone the right start for beginner of javascripting, I am good in java and spring.
    2. I am not able to find a comprehensive tutorial for backbone framework.

  2. Joshua May 17, 2012 at 8:57 am #

    Hi Christophe,
    I Want to say finding your blog was a real joy and i learn a lot. it made me take backbone js seriously. I WOuld like to request that you PLEASE take a look at meteor and do some tutorials using backbone, bootstrap and meteor. or bootstrap and meteor. I am very much interested in the reactive nature of meteor but can’t seem to find enough information right now.
    Thanks a plenty (a lot)

  3. Jacob Mumm May 17, 2012 at 1:11 pm #

    Hey Christophe,

    This is very useful technology. I had to get over this hurdle a year and a half ago, but I didn’t want to have to write the synchronization mechanism. I ended up going with Persistence.js, which abstracts WebSQL storage and provides a sync mechanism with a server through Node.js.

    Anyway, what I really wanted to bring up was that WebSQL on iOS is no longer 100% reliable for offline storage. Apple has just changed how it views this data. Instead of being permanent, it is viewed now as simple caching which can be discarded at any time should some other app require some space. I believe there are some solutions being discussed with PhoneGap and within the Persistence.js communities, but I’m not sure if a good one has arisen yet.

    Food for thought. Great article, though. I’m definitely going to file this one away somewhere.

  4. Adam May 20, 2012 at 10:58 pm #

    I am actually doing an application that has to keep in sync 7 tables, one of them will contain over 7.000 rows, so it’s a bit of a challenge thanks for this article it is a great starting point.

  5. Sadiknoorani May 26, 2012 at 6:52 pm #

    Hi add me in facebook with new iPhone 4

  6. madhusudan July 30, 2012 at 7:31 am #

    hi, is there any option in flex mobile, to work in offline status? again when the apps is going online the local db/cached data sync with server data base. is there any mechanism?

    • Marcin Perlak October 31, 2012 at 8:23 am #

      Hi, you should have a look at – it gives you the capability to work offline with full bi-directional sync to data on the server. It also handles data synchronization conflicts.

  7. trikarai August 27, 2012 at 9:54 am #

    how to make a delete button on every data? so no need to reset all data, i want just delete 1 data?

    can you give example..? or add it n your code ??

    • trikarai August 29, 2012 at 5:07 am #

      done and solved :)


  8. trikarai August 27, 2012 at 10:29 am #

    how to add delete button after last modified column and add delete function ??

    can it happen ??

  9. Marilize September 13, 2012 at 10:49 am #

    Hi I wnat to add syncing functionality to my Phonegap app, but I just have a few questions regarding the example provided:
    1. What would happen if connectivity to the server is lost while busy with the sync?
    2. Also is it possible to cancel a sync while it is busy?
    3. Will this work on iOS, Android & Blackberry?


    • Marcin Perlak October 31, 2012 at 8:26 am #

      Marilize, like I mentioned on of the replys above – have a look at It solves the problems with data sync between mobile apps and server data or business systems.
      1. Each synchronization is fully transactional so if the connection is lost nothing will happen and the data integrity will be kept
      2. It is possible to cance the sync
      3. Mobeelizer works currently on iOS, Android, Windows Phone and Titanium

  10. Jstoff September 27, 2012 at 8:27 pm #

    You just saved me days of worry and lack of sleep with this post! This is exactly what I need!

  11. Christian Schmidt October 18, 2012 at 10:26 am #

    I have a question about your server running script. In your “api” folder, you got the “index.php” where u simulate a Database export right? I created a Database and wanted to get the coloms:
    Your code:
    ” $_SESSION['employees'] = array(
    (object) array(“id” => 1, “firstName” => “John”, …”
    My code:
    “$object =(object) array(“id” => $id, “lastModified” => $lastModified, …”
    That $id is from my MySql database… my Problem is, that i only got the last entry (row) from the database. When i try to put my objects from the array in a collection:
    while( $t = mysql_fetch_array($r)) {
    $rows[] = $t;
    for($i = 0; $i $id, “lastModified” => $lastModified, “deleted” => $deleted);
    $_SESSION['employees'] = array($myCollection);
    I got an error that sayes “Undefined property: GenericCollection::$deleted”.
    I think that my code is correctly, because $myCollection is just
    (object)array(“id” =>1, “lastModified” => …
    (object)array(“id” =>2, “lastModified” => …
    (object)array(“id” =>3, “lastModified” => …
    and so on… but he does not finde that $delete….
    I hope u understand my Problem… Do you have any Idea why that Problem is?
    Sorry for my english,
    Best Regards,

  12. Berguiga November 3, 2012 at 8:57 am #

    thank you for this tuto, I want to copy multiple table on my device it works with this code? if for each table I use the same script.

  13. Berguiga November 3, 2012 at 8:59 am #

    I want to copy multiple table on my device it works with this code? if for each table I use the same script.

  14. Elf Sternberg November 7, 2012 at 5:54 pm #

    A timestamp? REALLY?

    Hmm. A business traveler makes an entry in California, updates it on the plane, and when he arrives in New York his phone updates with the local network time and discovers that entry wrecked.

    You go to sleep one night, while your ISP suffers a minor problem that requires rebooting your servers. In the middle of all that, their NTP servers reset to the epoch, wiping your databases out in the process.

    Using timestamps for synchronization is negligence. See for 36 reasons why timestamps are unreliable.

    • Jorge November 30, 2012 at 12:00 am #

      Hi Elf,

      Thanks for pointing out the issue about using timestamps. Do you have an alternative solution that you can share with us?

      • Josh April 11, 2013 at 6:27 pm #

        My thoughts exactly. Criticism without a solution is a waste of a post. GMT on all platforms with a check on to make sure the new update time is not less than the old update time is an acceptable solution in most cases. Syncing with a timeserver is even better if you really need time sensitive updates.

        • jim July 11, 2014 at 10:40 am #

          An alternative to timestamps is using a counter that increments every time a row is updated. This counter is just an integer, so it won’t be affected by timezones.

    • Jungle April 18, 2013 at 9:05 pm #

      1. This post is about unidirectional updates so solution as is doesn’t consider crud operations from device.
      2. Device’s timezone doesn’t affect sync because mechanism doesn’t operate with device current time but uses last modified timestamp coming from db.
      3. One night your ISP may suffer a huge problem and your server will be unavailable for quite some time. This all could happens but reliability of solution should costs not more than business impact of the failure.

      Solution described here is very simple and could works well for many use cases.
      Thanks for nice article!

  15. Stephan November 8, 2012 at 10:56 pm #

    Can you share the ../api/employees file on source code?

  16. Ah Wirayudha November 11, 2012 at 7:22 am #

    hai… how to change on click function to on device ready for phone… ???

    *sorry i still learning.. :D

  17. December 1, 2012 at 5:12 am #

    Nice post. I learn something new and challenging on blogs I stumbleupon every
    day. It will always be useful to read content from other writers and use a little something from other web

  18. sash December 6, 2012 at 11:26 am #

    Hi Christophe

    Your blog is really helpful…thanks for writing it down :) :)

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

    to under what licensing we can use the source code you published on GitHub. Can you please clarify?

  20. Rachelle December 28, 2012 at 11:35 am #

    please i uploaded a sample of the api and client-app to my webserver but the database is not displaying. and it works perfectly on my localserver. i have the feeling my api url is what is giving the trouble
    here is a sample of the api url, is this how it should be?

  21. Sam January 30, 2013 at 12:43 am #

    Good job. I also worked on Web SQL Sync and I create this:

    It support bi-directional sync and the logic is different.

    Hope this help


    • Rachelle February 1, 2013 at 4:41 pm #

      Please is there a PHP implementation of Websql sync, i don’t understand java

  22. Dmitry April 29, 2013 at 12:04 am #

    Hi there,

    Tried this and worked fine, but… how do you integrate it with actual backbone.js collection?
    E.g. how to make backbone collection get the data using the local storage used in this example ?


  23. Anneleen May 18, 2013 at 2:28 pm #

    Question, I changed the url in app.js to a url on my website containing the files in api, but when I want to sync, I get an undefined error. According to me, this should just work like it does when everything is local. Can you help me?

  24. Rigel May 19, 2013 at 2:28 pm #

    Hi ,

    What is the safest way to implement offline username password authentication in phongap / emberjs.

    I am noob.

    Thank you for the above article.

  25. hermes paris May 26, 2013 at 7:01 am #

    Your article Simple Offline Data Synchronization for Mobile Web and PhoneGap Applications | Christophe Coenraets write very well, thank you share!

  26. Akash May 31, 2013 at 7:50 pm #

    Hello I have the same issue whereby in the source downloaded I get a undefined error and uploaded to awardspace I still get the undefined error.

    Thank you

  27. Ak June 6, 2013 at 9:28 am #


    Does anyone know where the dbconnection is defined. I find that is the crux of getting the database to work but I am unable to crack where the connection is being made.

    p.s. this is why people are getting the undefined error.

  28. Chiaka July 24, 2013 at 4:24 am #

    Mr Christophe Coenraets I am one of your fans. Your skills and Training series are wonderful and impressive. Please Christophe I need your help. how can I connect the ” directory-rest-php sample ” and the ” offline-sync sample” ie. using MySQL as datebase in the ” offline-sync sample ” please thank you for quick reply.

  29. Mike Speranza August 1, 2013 at 3:44 am #

    You cannot use a local timestamp reliably for modifiedSince as others have pointed out.
    The way I solved it was to send a timestamp from the server with each sync.
    Each sync is therefore a snapshot “as of” server_timestamp. When the client requests data, it passes back the last sync server_timestamp. Therefore, you compare server timestamp against another server timestamp.

    Here’s an article I wrote describing a similar mechanism and using a server generated timestamp. It uses backbone.js on the client, but other than that, the sync mechanism is quite generic.

  30. ravi August 10, 2013 at 5:51 am #

    i was created database in phonegap html page using cordova . when i started app its creating new database and previous values are gone.

  31. Fahrul August 21, 2013 at 3:46 am #

    hi chris,

    Please help me, share source code ../api/employees

    Thank You

  32. Deepan September 3, 2013 at 6:59 am #

    Hi ,

    I have Forked : this link and Downloaded

    deployed in my xampp server , but Server-app folder not working.

    it would be better any other repo with Db Connections , not Session storage.

    Find me Correct way

  33. Anna September 9, 2013 at 5:01 am #

    If you edit data and store it localy (html5 local), will this be wiped via an update off the app?

  34. pieczatki drewniane October 19, 2013 at 3:13 am #

    What wass clearer was that the experience of being in the wilderness,
    out under the stars, or surrounded by the natural worldd of mountains, plants, rivers,
    and seas had a positive effect on those who did it. For
    a school project on the Celts, or even for a Celtic art project, you could use this as your
    main paper, and the one below for a great title page.

    Therefore, your creativeness will be explored further.

    When you are done, you have a rubber stamp for geocaching orr letterboxing.
    The doors to the Stampaway USA show officially open at 9 AM.
    The variety of stamps available accommodate various aopearances to
    any given page.

  35. mortgage broker atlanta April 20, 2014 at 1:25 pm #

    Hey there just wanted to give you a quick heads up.

    The words in your article seem to be running off the screen in Ie.

    I’m not sure if this is a format issue or something to do with web browser compatibility but I thought I’d post to let
    you know. The style and design look great though!
    Hope you get the problem resolved soon. Thanks

  36. Film Romatis April 22, 2014 at 6:46 pm #

    Undeniably believe that which you stated. Your favorite justification
    seemed to be on the net the simplest thing to
    be aware of. I say to you, I certainly get irked while people think about worries that they just don’t know about.
    You managed to hit the nail upon the top and also defined out the whole thing without having side effect , people could take a signal.
    Will probably be back to get more. Thanks

  37. Gaurav April 28, 2014 at 5:29 am #


    I am trying to implement synchronization in my app. I am having doubt that when I connect to internet how will my app will know that internet is connected so it can fire sync event in background without opening my application.


  38. Sergio May 7, 2014 at 11:49 am #

    Hello, I’m trying to use this example for an app that I’m developing, I’m not an expert but I’m crazily stressed trying to sync my app with info that I’ve got in an database on my server. So I was testing this example separating the folders one on my computer and one on my localserver that worked but only when the client side ran on the localserver, when I tried it out of the server alerted “undefined”. Second case, I tried putting the server side on my remote server and the client side on my local server: I got the same alert. What’s the thing that I’m not getting here. HEEEEELP!!!!

  39. hearing aids new May 11, 2014 at 7:14 pm #

    Link exchange is nothing else however it is
    simply placing the other person’s blog link on your page at appropriate place and other person will also do similar in favor of you.

  40. Frederick May 15, 2014 at 1:29 pm #

    They usually have their own private clinic will seoo
    backlinks earn up to $55, 000 to be a psychologist.
    The sequelae oof child sexual abuse can be very exciting at times with a student.
    To start with, here’s a short list of what sport and performance psychology is not only
    unsustainable it also causes a distraction to the group.
    The shup was a great experience in many ways. Several clinical psychologists operate in hospitals wherein they work together with medical specialists and othber experts in order too give interventions for
    mental or interpersonal reational problems.

  41. Real Racing 3 Hack Free May 17, 2014 at 4:38 pm #

    Ever since Real Racing 3 is out, we will supply you with many ways for the best conditions and also have the most entertaining with Real Racing 3.

    All of that using an eyesight all the way to the way
    to cut down any real cash purchase in the adventure.

    Real Competition is a very wonderful racer
    on any smartphone base, and it’s completely free, so there
    is not any justification not to ever test it. I’ve performed
    Real Racing 3 around 30 working hours 100 % now, let
    me give some easy methods to find the farthest feasible in your quickest time frame and without
    paying a thing.

    Real Racing 3 from Electronic digital Arts can be purchased for Android and iOS items.

    It is a Highly really rushing performance with many
    exceptional driving a car science and every auto appears unique.

    It may be a tough game for people who are employed to the twitchy-driving a vehicle gaming applications, as this
    is nearer to Gran Turismo or Forza line with respect to traveling

    I’ll give you the download links then strategies and tips, as this is not a tutorial of the game.

  42. Is Skin Whitening Forever a Scam May 18, 2014 at 11:35 pm #

    Excellent beat ! I wish to apprentice even as you amend your website, how can i subscribe for a weblog website?

    The account helped me a applicable deal. I have been tiny bit familiar of this your broadcast provided vivid transparent idea

  43. Al May 26, 2014 at 9:54 am #

    First of all I would like to say fantastic blog!

    I had a quick question that I’d like to ask if you do not mind.
    I was interested to know how you center yourself and clear your thoughts before writing.
    I have had a hard time clearing my mind in getting my ideas out there.
    I do take pleasure in writing however it just seems like the first 10 to 15 minutes are generally wasted just
    trying to figure out how to begin. Any suggestions or hints?


  44. Trupti May 31, 2014 at 3:53 am #

    Nice . I have a question:

    I am newbie for phonegap and sqlite dabase. I want to install this app in my android device. But I have question about files.

    Where should I put files of server-app. When I click on synchronise button , it gives “undefined” in lnsert box as error.

    Can you please help?

  45. induced pluripotent stem cells June 6, 2014 at 6:06 pm #

    Fine way of describing, and pleasant piece of writing to obtain facts regarding my presentation subject matter, which i am
    going to present in college.

  46. Rafaela June 17, 2014 at 2:53 am #

    Everyone loves what you guys are up too. This type
    of clever work and reporting! Keep up the good works
    guys I’ve included you guys to my own blogroll.

  47. gervaisb July 8, 2014 at 3:19 am #

    Interesting post but what if the datas are initially created by the client ? You can’t rely on the id because many “local records” can have the same id on different clients.

  48. July 13, 2014 at 10:17 am #

    Hey there, just simply ended up being mindful of your blog via Bing, determined that it is definitely educational. I am about to be aware of belgium’s capital. Let me take pleasure in cleaning soap proceed the following later on. Many other individuals may very well be benefited from your current crafting. Many thanks!

  49. Ash July 31, 2014 at 6:32 am #


    I am new to Phonegap and Javascript. I downloaded your source codes and running them on my MAMP server. When I inspect the resources, I do see the database created. However, nothing else happens. If I want it to sync with my MAMP’s database, what should I type in syncURL? and when I click on Sync with Server, it returns an alert ‘undefined’. I am not sure how to add the api file too, as I don’t see it in the folders and where should the files stored anyways? I could really use some help. Newbie, sorry. :/

    Thank you.

  50. ragw October 18, 2014 at 6:09 am #

    Hi Every one,

    in Sync project we are change one html to other html database not appear. is any way to show data in entire app

  51. stanley wanyama July 3, 2012 at 11:27 am #

    I have tried to deploy both apps but in vain. briefly explain how to make it interact with server. only employ directory (local jqm) works fine but this for offline data has failed


  1. Bits And Pieces of The Application « …..troubledblogger's blog - May 19, 2012

    [...] Simple Offline Data Synchronization for Mobile Web and PhoneGap Applications ( [...]

  2. Links for May 25th through May 26th — Vinny Carpenter's blog - May 27, 2012

    [...] Simple Offline Data Synchronization for Mobile Web and PhoneGap Applications – In this article, I describe a simple data synchronization strategy that uses the device’s (or browser’s) SQLite database. The implementation currently leverages the Web SQL API (even though the W3C is no longer actively maintaining the spec) because both iOS and Android support it, but they don’t support IndexedDB, the official alternative [...]

  3. Employee Directory is now available on the App Store - June 26, 2012

    [...] The offline synchronization strategy used in the application is documented in a previous post: Simple Offline Data Synchronization for Mobile Web and PhoneGap Applications. [...]

  4. Snappy Web Applications - Adriel Blog | Adriel Blog - August 2, 2012

    [...]… [...]

  5. Confluence: Akula - November 6, 2012

    Data Sync General Information…

    This is information that went into researching wha…

  6. Sync Resources | Crux - September 30, 2013

    [...]… [...]

  7. DBConnect Slim Framework Synchronization for Mobile | Technology & Programming - November 17, 2013

    [...]… [...]

  8. ¿Cómo puedo hacer que mi aplicación web o móvil funcione offline? | Un sitio Mnemoniaco - October 12, 2014

    […] meditar sobre el asunto, diseñar la arquitectura y los planes de acción al sincronizar, en este y este enlace (están en inglés) podemos ver algunos enfoques sobre cómo manejar este aspecto de […]

Leave a Reply