Best way to synchronize local HTML5 DB (WebSQL Storage, SQLite) with a server (2 way sync)

All we need is an easy explanation of the problem, so here it is.

I am developing a mobile web application (for iPhone & Android) with a local database (using html5 webstorage) so my app is still usable when the user is offline.

This is working perfectly, but I want to save the local data on a server. So I need to synchronize the local DB with a DB on a server. The synchronisation can only be one way, but in the future, I would like to synchronize it in the both way (server <-> local DB).

This requierement looks very common (or will be common in the future for mobile web app), but I can’t find a library doing that.

I know google is doing that in their mobile web app (ex. gmail), and I found the WSPL project a google project but without source to download.

If I can’t find a solution, I will create a library to do that, as one way sync doesn’t look difficult, but I wonder if there are other solutions.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

  • I created a small JS lib named WebSqlSync to synchronize a local WebSql DB with a server (client <-> server). Very easy to use and to integrate in your code :

https://github.com/orbitaloop/WebSqlSync

  • The open source project QuickConnect contains a JS library to synchronize the local HTML5 SQLite DB to a server DB (MySQL or other) :

http://quickconnect.pbworks.com/Using-Enterprise-Synchronization

To use this lib, you need to use the DataAccessObject of the framework to access your DB. It works by storing all the SQL request applied to the DB (except select of course) , and sending them to the server. It’s great to manage deletion, but it’s a little heavy if you have a lot of updates, and the server need to use the same SQL language…

  • Another project from QuickConnect is a native SQLite synch (in Objective C for iOS or Mac OS and in Java for Android) :

http://www.quickconnectfamily.org/qcdbsync/
(I think it store also the history of all the SQL requests)

  • And i just found another promising JS library : persistenceJS

https://github.com/zefhemel/persistencejs

“persistence.js is an asynchronous Javascript object-relational mapper library. You can use it in the browser, as well on the server (and you can share data models between them).”

They have a DB synch module: DOC of persistence.synch.js

(works with HTML5 DB SQLite or Google Gears on the client, and MySQL on the server)

  • And there is also Impel.inTouch. It looks very easy to use (with php files included), but you must use the Mootools framework in the client side :

http://impel.simulacre.org/api/Impel.inTouch

  • Sencha has also a synchronisation service: Sencha.io. Looks great, but it’s dependent of the Sencha Touch framework:

http://www.sencha.com/products/io/

Method 2

I have developed a generic sync solution called WebSqlSync.

It’s not dependant of any framework.
It’s available here :
https://github.com/orbitaloop/WebSqlSync

Extract of the README file :

WebSqlSync

Automatically synchronize a local WebSql database (SQLite in the navigator) to a server. (2 way sync : client <-> server)

Very easy to integrate to your existing app and very easy to use (2 functions to call : initSync and syncNow)

Usage

Initialize

You need to initialize the lib (at each startup for example).

It will automatically create 2 tables (if they don’t already exists, one to store all the new or modified elements (table new_elem) and one to store the date of the last sync (table sync_info). It will also create SQLite triggers in order to watch the INSERT or UPDATE on the tables you want to synchronize (to automatically insert the modified elements in the new_elem table):

DBSYNC.initSync(TABLES_TO_SYNC, webSqlDb, sync_info, 'http://www.myserver.com', callBackEndInit);

Where TABLES_TO_SYNC is the list of table that you want to sync with the server, ex :

TABLES_TO_SYNC = [
    {tableName : 'table1', idName : 'the_id'},
    {tableName : 'table2'} //if idName not specified, it will assume that it's "id"
];

Synchronize

To start the synchronization, you need to call the syncNow function. You can call it every X seconds, or after some changes for example :

DBSYNC.syncNow(callBackSyncProgress, function(result) {
     if (result.syncOK === true) {
         //Synchronized successfully
     }
});

And that’s all you need to do on the client. On the server side, you will need to code your own solution (but it’s not complicated). And there are some example inPHP & Java. Again, contributions are welcome.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply