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.

  • Sunil

    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.

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

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

  • Pingback: Bits And Pieces of The Application « …..troubledblogger's blog()

    • stanley wanyama

      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

  • Adam

    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.

  • Hi add me in facebook with new iPhone 4

  • Pingback: Links for May 25th through May 26th — Vinny Carpenter's blog()

  • Pingback: Employee Directory is now available on the App Store()

  • madhusudan

    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?

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

  • Pingback: Snappy Web Applications - Adriel Blog | Adriel Blog()

  • trikarai

    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

      done and solved :)


  • trikarai

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

    can it happen ??

  • Marilize

    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?


    • 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

  • Jstoff

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

  • Christian Schmidt

    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,

  • Berguiga

    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.

  • Berguiga

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

  • Pingback: Confluence: Akula()

  • 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

      Hi Elf,

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

      • Josh

        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

          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

      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!

  • Stephan

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

  • Ah Wirayudha

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

    *sorry i still learning.. :D

  • 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

  • sash

    Hi Christophe

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

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

  • Rachelle

    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?

  • Sam

    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

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

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


  • Anneleen

    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?

  • Rigel

    Hi ,

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

    I am noob.

    Thank you for the above article.

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

  • Akash

    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

  • Ak


    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.

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

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

  • ravi

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

  • hi chris,

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

    Thank You

  • 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

  • Anna

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

  • Pingback: Sync Resources | Crux()

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

  • Pingback: DBConnect Slim Framework Synchronization for Mobile | Technology & Programming()

  • 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

  • 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

  • Gaurav


    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.


  • Sergio

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

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

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

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

  • 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

  • Al

    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?


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

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

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

  • gervaisb

    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.

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

  • Ash


    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.

  • Pingback: ¿Cómo puedo hacer que mi aplicación web o móvil funcione offline? | Un sitio Mnemoniaco()

  • ragw

    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

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

  • Pingback: Mobile Apps Offline | mbwmobile()

  • Pingback: Bi-Directional Offline Sync Logic ( WebSql / IndexedDb / LocalStorage on the client side using JayData frameworks and ASP.NET Web Api2 OData REST and Sql Server on the Server) | nick theile - web dev guy()

  • Michael

    This is a nice technique but it fails if data pagination on the client takes place. Also if the order of the items you load is not sorted by the last modification you will get problems.

  • ibrahim

    I am new to this so forgive me for the dum question.. what is the value of “syncURL” ? where that link leads ?and what kind of code is written there ?! i think u already covered that , but please just point to it again, thx for the great work :)

  • hello,
    how can i sync json from url to database and update only when there is update in remote server

  • Martin

    Does Anyone have php files to connect this demo to a mysql database instead of the sandboxed db? How could I do that, can’t get my php developed code to work. Thank you.

  • качество, надежность, экономичность и связанные работы выполняются imalattan.profesyonel команду

  • adesinamark

    Thanks for sharing this!