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:

http://coenraets.org/offline-sync/api/employees?modifiedSince=2012-03-01 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.

getLastSync()

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) {
    this.db.transaction(
        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;
                    callback(lastSync);
                }
            );
        }
    );
}

getChanges()

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) {

    $.ajax({
        url: syncURL,
        data: {modifiedSince: modifiedSince},
        dataType:"json",
        success:function (changes) {
            callback(changes);
        },
        error: function(model, response) {
            alert(response.responseText);
        }
    });

}

applyChanges()

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) {
    this.db.transaction(
        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.id, e.firstName, e.lastName, e.title, e.officePhone, e.deleted, e.lastModified];
                tx.executeSql(sql, params);
            }
        },
        this.txErrorHandler,
        function(tx) {
            callback();
        }
    );
}

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;
    this.getLastSync(function(lastSync){
        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.

52 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?
    thanks..

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

      Hi, you should have a look at mobeelizer.com – 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 :)

      thanks

  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?

    Thanks!
    M

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

      Marilize, like I mentioned on of the replys above – have a look at mobeelizer.com. 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 #

    Hi,
    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:
    Code:
    while( $t = mysql_fetch_array($r)) {
    $rows[] = $t;
    }
    for($i = 0; $i $id, “lastModified” => $lastModified, “deleted” => $deleted);
    $myCollection->addObject($i,$object);
    }
    $_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,
    Chris

  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 http://infiniteundo.com/post/25326999628/falsehoods-programmers-believe-about-time 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.

    • 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. http://www.hotels-ouarzazate.com/photos 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
    sites.

  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, http://name.mysite.com/dbsync/api/employees 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:
    https://github.com/orbitaloop/WebSqlSync

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

    Hope this help

    Sam

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

    Cheers,
    Dmitry

  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 #

    Hello,

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

  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.

    http://backbonefu.com/2013/03/implementing-live-updates-with-backbone-js/

  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 https://github.com/ccoenraets/offline-sync 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. 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

Trackbacks/Pingbacks

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

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

  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

    [...] http://coenraets.org/blog/2012/05/simple-offline-data-synchronization-for-mobile-web-and-phonegap-ap… [...]

  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

    [...] http://coenraets.org/blog/2012/05/simple-offline-data-synchronization-for-mobile-web-and-phonegap-ap… [...]

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

    [...] http://coenraets.org/blog/2012/05/simple-offline-data-synchronization-for-mobile-web-and-phonegap-ap… [...]

Leave a Reply