Yet Another Strategy to Sync Local Database with Paging API

When talking about syncing local data with the remote data source, it’s about Insert, Delete and Update entity in the local database according to the response of API. Considering creating, deleting or updating occur in an arbitrary position of a paging API, the synchronization becomes a little complicated. You can simply delete all of them first and then re-create entities again, or create new entities using brute force with a specific merge policy, or manipulate the local database under the instruction on the API which indicates which data need to be created, updated or deleted.

The strategy in this story is lightweight and in a more refined way, it tries to figure out which data need to be created, updated or deleted without any extra instructions but a plain paging API. It’s applicable to common database clients cooperating with paging API, e.g. iOS Core Data and Android Room.

Let’s have an entity called user with userId and name columns in both local and remote data source in which the same primary key and sort logic applied, a JSON API supports pagination with pageIndex and pageSize parameters. In this story, I use userId as the primary key and sort the data with userId in ascending order.

A common technique when importing data is to follow a “find-or-create” pattern, where you set up some data from which to create a managed object, determine whether the managed object already exists, and create it if it does not.

You can use a combination of IN predicate and sorting to reduce multiple fetches to a single fetch. This find-or-create pattern resolve O(n^2) problem.

The following pseudo-code explains the find-or-create pattern.

users = find user with userId in [1, 2, 3, 4]aSet = Set collection of users.userIdloop item in json:
if aSet excludes item.userId:
create user with item

We will use the same way to insert entities in the subsequent pages.

If the first page responds empty list, we simply remove all local data.

delete all users

If a subsequent page responds empty list, we simply remove users after the offset.

delete users with offset = pageIndex * pageSize orderby userId asc

If the page is not fulfilled as the specific size required, we simply remove all after the last userId on the page. In a more complicated scenario, a part of users on the page are hidden in the API, we have to remove them with a range constraint.

It’s a good practice to put delete logic before find-or-create pattern, it reduces the number of records. When the size of the deleted set is large, it boosts performance significantly.

maxId = last userId in current page (is #7)delete users with userId > maxId or (userId > 5 and userId < 7 and userId not in [5, 7])

When creating entities with a find-or-create pattern, we already have had the fetched result in handy, so we just update them with the related data.

users = find user with userId in [5, 6, 7, 8]aSet = Set collection of users.userIdloop item in json:
if aSet includes item.userId:
update user changed properties with item

Now it’s time to put them together. The second page returns only 3 users: #5, #6, #9 (less than page size 4), #6 has a new name that needs to be updated. This is a most complicated case, we have to deal with insert, delete, update and find (find-or-create) database operations (CRUD).

maxId = last userId in current page (is #9)delete users with userId > maxId or (duserId > 5 and userId < 9 and userId not in [5, 6, 9])#find-or-create
users
= find user with userId in [5, 6, 9]
aSet = Set collection of users.userIdloop item in json:
if aSet includes item.userId:
update user changed properties with item
else:
create user with item

After retrieving data from Remote Data Source in Repository, we will use this lightweight strategy to keep consistency in the local data source.

If you enjoyed this story, please give it a clap and share to help others find it!

Mobile developer@NZ. iOS, Android, React

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store