Client-server synchronization over REST

Question

This question is about what I think is a very common problem in Android / iOS development but I haven't found any "standard" solution yet.

Let's say we have a fairly normal REST API. The server database contains (among others) the tables countries and towns with a 1:N relationship.

The client (mobile app) wants to maintain a local snapshot of these two tables. So that when it's offline, it can do queries that would be normally done over REST, e.g.: "get a list of Austrian towns with population >= 100"?

How to approach this?

First problem: consistency. The client should have a snapshot of the two tables. If the client downloads updates of the towns table and goes offline, some town may reference a country that's not in the local copy of the countries table.

Second problem: the client should only download the new / deleted / changed rows. Ditch the REST and use some custom RPC call like get_updates_since(...)?

Third problem: how should local changes to the client's copy of the database (possibly made offline) be synchronized with the server? Custom RPC calls?

Answer 1

I don't think there's a silver bullet but the pattern you're looking for is caching. In past projects I have copied items from the server to local storage (SQLite or flat file), and maintained meta-data about the entries to update/upload/purge based on simple rules. It's not a simple problem and ends up being a lot of code.

Consistency: in your example, either ensure you download the countries table first, or make the two operations atomic - e.g., make copies of the "new" tables and only replace your cached versions if both copies complete successfully (doubles up on storage).

Only download new/deleted/changed: Yep, this requires client/server integration - timestamp all the records (with GMT), ask for metadata from the server, and walk through your local metadata deciding what to do. (Hint UUIDs on the rows are helpful).

Synchronize local changes: Yes, more client/server integration. See above paragraph.

Handling all the exceptions and edge cases is challenging. Take a look at the problems with iCloud sync'ing CoreData. Perhaps not a fair comparison, as Apple is trying to solve this for a totally distributed database, but interesting reading nonetheless.

Answer 2

Currently I am working on the same task - building the Android app which would sync info with a central SQL database using Azure Mobile Services. The sync strategy is to support bidirectional sync from multiple clients to ensure data consistency whereas only incremental changes will be exchanged.

Let me provide my solution to your problems. I recently wrote a blog post about sync algorithm to support this scenarios.

The sync logic will be managed on the client side because of REST API communication. Each table participating in the sync process will have the corresponding REST Api methods for CRUD operations.

For your first problem (consistency) – the solution is to download data on the client in correct order. E.g. first parents, then children to avoid problems with referential integrity. In case of network problem the client would have to sync again to get the rest of data.

Second problem (download only incremental changes) – the solution is timestamp (as Kevin mentioned in his answer below). But I would suggest using globally incremented value maintained on the server side to avoid issues with timestamp differences between clients and server. The rowversion of SQL Server is quite good candidate for this.

Third problem (data integration from the client) – using Dirty flag in the client tables would help to differentiate the records required for upload.

You might also need to introduce the Delete flag on both sides to handle deletes between multiple clients.