Sync React Native Offline app data from WatermelonDB to PostgreSQL

Originally published and sponsored by logrocket blog

This post can be seen as a sequel to a previous post in which I wrote about building an offline weight tracking app with React Native. However, that doesn’t mean you need to read that whole post first.

We will be using the code we wrote throughout that post as the starting point of this one, but the goal of this post is to demonstrate how to connect WatermelonDB’s sync feature with an AdonisJs server-side API application.

Exploring WatermelonDB’s offline functionality

As mentioned above, as a starting point, we will be using an app that we build throughout another blog post. Before we proceed, let’s create a folder that will contain both the React Native and AdonisJs apps, then please clone the repository from here and check out branch v1 :

mkdir weightress && cd $_
git clone <https://github.com/foysalit/weightress-app-blog.git> app && cd $_
git checkout v1
yarn

Once you have the code and all its dependencies installed, you should be able to launch the app on the platform of your choice using either your device or an emulator. I’m using a physical Android device for testing, so I’ll write about the steps for that particular case, but the steps should be similar for other cases, too.

After connecting the device, simply run yarn start in one window of your terminal and run yarn android on another. This should launch the app on your device.

If you already have an app built with WatermelonDB that you want to sync data with, feel free to use that, but keep in mind that your data structure will be different than ours. As such, tables, schema, etc. will need to be adjusted accordingly.

To summarize the current state of things, we have an app that lets us input some data into a table named weights, and we want to synchronize this data with a server somewhere through REST API calls. Since this is a very common requirement for offline apps — to be able to keep data in sync outside of the device — WatermelonDB comes with this feature out of the box.

Let’s see how we can make use of it. Let’s create a file named sync.ts inside the data/ folder and put in the following code:

import {synchronize} from '@nozbe/watermelondb/sync';
import {database} from './database';
// your_local_machine_ip_address usually looks like 192.168.0.x
// on *nix system, you would find it out by running the ifconfig command
const SYNC_API_URL = 'http://<your_local_machine_ip_address>:3333/sync';
export async function sync() {
  await synchronize({
    database,
    pullChanges: async ({lastPulledAt}) => {
      const response = await fetch(SYNC_API_URL, {
        body: JSON.stringify({lastPulledAt}),
      });
      if (!response.ok) {
        throw new Error(await response.text());
      }

      const {changes, timestamp} = await response.json();
      return {changes, timestamp};
    },
    pushChanges: async ({changes, lastPulledAt}) => {
      const response = await fetch(
        `${SYNC_API_URL}?lastPulledAt=${lastPulledAt}`,
        {
          method: 'POST',
          body: JSON.stringify(changes),
        },
      );
      if (!response.ok) {
        throw new Error(await response.text());
      }
    },
  });
}

We are using the synchronize function from WatermelonDB (which needs a database instance), a pullChanges function, and a pushChanges function. The database instance is already exported from the database.ts file, so we import that and pass it down.

pullChanges is what brings in data from a remote source and updates the local DB with the remote data. In the body of the function, we make a GET request to a SYNC_API_URL endpoint with the lastPulledAt timestamp provided to use by WatermelonDB. The URL points to <your_local_machine_ip_address>:3333/sync, which will be available once we build out the Adonis app.

Click here to see the full demo with network requests

Note : We cannot use localhost:3333 here from React Native code because our app will be running on a different device, and the localhost for that device is not the same as the localhost for your machine where the Adonis app is running. This is why you have to use an IP address instead.

Notice that in the request response, we expect to receive an object with changes and timestamp properties. This is what we must return from the pullChanges function call in order to be compatible with WatermelonDB’s sync implementation.

pushChanges is the function that sends our local data to a remote source. Naturally, we make a POST request to our SYNC_API_URL, and in the body of the request, we attach the changes variable made available to us by WatermelonDB.

Notice that we are not returning anything from the function, but we are throwing an error if the API request does not return an OK status. This is crucial because by throwing an error, we ensure that if the push somehow broke on the server end, WatermelonDB will do its thing to make sure data integrity is maintained.

Generating our API app with AdonisJs

Adonis comes with a handy boilerplate generator command. To run it, make sure you’re inside the weightress/ folder and run the command yarn create adonis-ts-app api. You will get a few questions; provide answers like we have below, and you will be left with a directory named api.

Adonis uses a package called Ace for building and running CLI commands. However, to start using that, you need to first compile the code. Don’t worry — it’s as simple as running a yarn command. Simply run cd api && yarn build and you should see some output like this:

Now the first thing we need in this app is to replicate the data structure we have in WatermelonDB into our server database. In this case, I’m choosing MySQL as the database and Lucid as the ORM to communicate with the database.

Again, AdonisJs comes in handy to bootstrap everything related to the database. Run the following commands to bring it all together:

yarn add @adonisjs/lucid@alpha
node ace invoke @adonisjs/lucid
yarn build

The second command will ask you to choose a DB provider. I’m choosing MySQL, but feel free to pick your preferred DB since it won’t make much difference in the context of this post. This is the output from my terminal window, for reference:

Now, to connect to the MySQLdb, we need to add the credentials as env variables. Adonis makes it quite easy to handle those. Open up the .env file and you should see something like this:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_USER=lucid
DB_PASSWORD=
DB_NAME=lucid

Adjust the values of these variables to match your local or remote MySQL database’s credentials to make sure the app can connect to it.

Before we start writing app code, let’s make sure our database is all set to support the data from our app. When it comes to schema management, it’s always better and easier to do it through migrations, so we will start by creating one of those: node ace make:migration weights. This will drop a new file in the database/migrations/ folder. Open up that file and replace the content with the following code:

import BaseSchema from '@ioc:Adonis/Lucid/Schema'

export default class Weights extends BaseSchema {
  protected tableName = 'weights'

  public async up () {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')
      table.double('weight')
      table.string('note')
      table.timestamps(true)
    })
  }

  public async down () {
    this.schema.dropTable(this.tableName)
  }
}

When migrations are run, this will create a new table in our MySQL database named weights with an auto-incrementing id column; a weight column that can hold floating point numbers; a string column named note; and a column called timestamps, which automatically added columns by Adonis named created_at and updated_at.

As you can see, this is almost exactly the same structure as we have for our weights table schema in WatermelonDB, except for some MySQL-specific things like the PRIMARY KEY column. You can match it against the schema code in the data/schema.ts file in the code for the React Native app.

Finally, don’t forget to run yarn build && node ace migration:run in order to run this migration on your database.

Now, to communicate/manipulate this data from the Adonis app, we need a model. Let’s make that by running node ace make:model weight, which will generate a new file in app/models/Weight.ts. Replace the code in that file with this:

import { DateTime } from 'luxon'
import { BaseModel, column } from '@ioc:Adonis/Lucid/Orm'

export default class Weight extends BaseModel {
  @column({ isPrimary: true })
  public id: number

  @column()
  public weight: number

  @column()
  public note: string

  @column.dateTime({ autoCreate: true })
  public createdAt: DateTime

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  public updatedAt: DateTime
}

The only new lines are the weight and the note column definitions. This ensures that both properties are publicly accessible in code from this model. This is also pretty similar to the model definition we had for WatermelonDB; you can match that against the file data/weight.ts.

Building our REST API endpoints for sync

So far, we have been building “under-the-hood machinery” in our API. Now it’s time to use this machinery to build our REST API endpoints for sync, which we are expecting from the React Native app’s side. If you recall, we only need one endpoint at /sync, but it has to be able to serve both GET and POST requests.

We begin by adding these two lines in the start/routes.ts file:

Route.get('sync', 'SyncsController.push');
Route.post('sync', 'SyncsController.pull');

This tells Adonis to make the /sync endpoint available and to use the push method for GET requests and the pull method for the POST request, both from a mysterious SyncsController class. We will get to the controller right away, but notice that the naming might be a bit confusing here.

If you think from the API server’s database perspective, however, the GET request is generated by the pullChanges call from WatermelonDB, which is the remote data from this database’s POV. So, from this side, it’s the same as pushing data from this side to a remote source.

Similarly, the pushChanges call from WatermelonDB generates a POST request, which is similar to a pull action from the server database’s POV. It’s a bit dizzying, so don’t think too hard about it.

Let’s look at the SyncsController now. Controller methods are triggered when a request is received at the designated endpoint in route definitions. Let’s generate our sync controller by running node ace make:controller sync, which will create a new file at app/Controllers/Http/SyncsController.ts.

Now open up that file and place in this code:

import { DateTime } from 'luxon'
import {RequestContract} from '@ioc:Adonis/Core/Request'
import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'
import Weight from 'App/Models/Weight'

const getSafeLastPulledAt = (request: RequestContract) => {
  const lastPulledAt = request.input('lastPulledAt')
  if (lastPulledAt !== 'null') {
    return DateTime.fromMillis(parseInt(lastPulledAt)).toString()
  }
  return DateTime.fromMillis(1).toString()
}

export default class SyncsController {
  public async pull ({ request }: HttpContextContract) {
    const changes = request.input('changes')

    if (changes?.weights?.created?.length > 0) {
      await Weight.createMany(changes.weights.created.map(remoteEntry => ({
        note: remoteEntry.note,
        weight: remoteEntry.weight,
        watermelonId: remoteEntry.id,
        createdAt: DateTime.fromMillis(parseInt(remoteEntry.created_at)),
      })))
    }

    if (changes?.weights?.updated?.length > 0) {
      const updateQueries = changes.weights.updated.map(remoteEntry => {
        return Weight.query().where('watermelonId', remoteEntry.id).update({
          note: remoteEntry.note,
          weight: remoteEntry.weight,
        })
      })
      await Promise.all(updateQueries)
    }

    if (changes?.weights?.deleted?.length > 0) {
      await Weight.query().where('watermelon_id', changes.weights.deleted).exec()
    }
  }
  public async push ({request}: HttpContextContract) {
    const lastPulledAt = getSafeLastPulledAt(request)
    const created = await Weight.query().where('created_at', '>', lastPulledAt).exec()
    const updated = await Weight.query().where('updated_at', '>', lastPulledAt).exec()
    return {
      changes: {
        weights: {
          created,
          updated,
          deleted: [],
        },
      },
      timestamp: Date.now(),
    }
  }
}

Alright, there’s a lot going on here, so let’s unpack this one method at a time. The pull method receives an object with a property named changes, which is an object. This object will contain all the DB changes that happened since the last sync, keyed by the name of every table/collection.

So in our case, we should see the key weights. For each table, we would have three array properties: created, containing all the new entries; updated, containing all the modified entries; and deleted, containing all the deleted entries since the last sync (in our case, from the weights table).

So, if there are any entries in the created input, we map through all those entries in the array, and using the Weight.createMany() method, we insert all those entries into our server-side database.

Notice that we are converting created_at into a DateTime instance; WatermelonDB timestamps are Unix timestamps containing only numbers, which are not compatible directly with Lucid ORM’s datetime fields. Notice that we are also inserting the id field coming from WatermelonDB, and we see why that is needed in the next lines.

When updating on the server side, our only reference point between a client entry and a server entry is the WatermelonDB-generated ID, which is why we saved it on the server-side DB when syncing created entries. So for updating, we loop through all the entries in the updated input array and update the entries using Weight.query().where('watermelonId', remoteEntry.id).update.

There is a bit of a performance concern here because for every entry in the updated array, we are running one update query against our database. This can clog up our database real fast as the user base grows, so keep an eye on that. Finally, we wait for all those queries to finish using Promise.all.

Deleting is a little bit different. WatermelonDB sends only the IDs of the deleted entries, so all we have to do is delete all entries that match those IDs.

The push endpoint has a similar interface to pull. It will have to return a changes property and a timestamp property. The timestamp property is very important because this will be saved by WatermelonDB after a successful pull from the client.

The next time it requests another pull, it will send the timestamp previously saved to let our server know when the last successful sync happened. That’s why, when requesting a pull, WatermelonDB sends a 'lastPulledAt' input. For the first-ever sync, it will be set to null, so to translate this input, we have a helper function called getSafeLastPulledAt.

Within changes, there should be one property for each table/collection in our database; so, in our case, it only contains weights. Each of these will contain created, updated, and deleted properties. For the purposes of this post, since our UI does not permit deleting entries within the app yet, we will only be tackling created and updated syncs.

Using the lastPulledAt timestamp, we query all the entries from our database to find the entries updated or created since then and send it in the response. And that’s all we need for our server-side app.

Implementing the sync indicator UX

In the beginning of this post, we only defined the sync function that triggers the synchronization process. To make all of this work, however, we still need to trigger the process by invoking the sync function. This could be done in several ways — on demand, automatically, or even a combination of both.

For the purposes of this post, we will only build the automatic approach by triggering sync every time the app is opened. As the sync happens, we should also show the user some kind of indication that the sync is in process. We will build all of this in a component. Create a new file in components/sync-indicator.tsx and put in the following code:

import React, {useEffect, useState} from 'react';
import {View, Text} from 'react-native';
import {syncStyles} from './styles';
import {sync} from '../data/sync';

const SyncIndicator = () => {
  const [syncState, setSyncState] = useState<string>('Syncing data...');

  useEffect(() => {
    sync()
      .then(() => setSyncState(''))
      .catch(() => setSyncState('Sync failed!'));
  });

  if (!syncState) {
    return null;
  }

  return (
    <View style={syncStyles.container}>
      <Text style={syncStyles.text}>{syncState}</Text>
    </View>
  );
};

export default SyncIndicator;

It’s a pretty straightforward component with only one local state, syncState. It’s initially set to Syncing data… because, on open, this component will be loaded every time, and we will trigger the sync.

As soon as the component is loaded into view, we fire off the sync() function using useEffect, and when that finishes without error, we set syncState to an empty string to mark it as complete. If the sync promise throws an error, we set the syncState to Sync failed! to let the user know.

Following this logic, if syncState is empty, we simply don’t render anything. Otherwise, we display the syncState text with some styles imported from styles.ts file. Let’s look at the style definition:

export const syncStyles = {
  container: {
    paddingVertical: 5,
    alignItems: 'center',
    backgroundColor: primaryColor,
  },
  text: {
    color: '#FFFFFF',
  },
};

This will set a background color on the syncing state text to make it easily visible. Now all we have to do is put this component in our app. Open up the App.tsx file and render the component on top of the ScrollView area:

import Creator from './components/creator'; 
// import the sync indicator
import SyncIndicator from './components/sync-indicator';
// … previous code
<StatusBar /> 
<SafeAreaView>
  // render the sync indicator
  <SyncIndicator />
  <ScrollView contentInsetAdjustmentBehavior="automatic">

Now if you open the app and keep an eye on the terminal window where the AdonisJs app is running, you should see some small text on top of your screen that says Syncing Data… , and it eventually disappears, meaning the sync was successful. You could also change your SYNC_API_URL to a random URL to simulate a sync failure. In that case, you’d see a Sync Failed! message.

Here’s a quick screenshot from my device:

Testing the offline sync behavior

Time to take it for a test-drive. Offline-supported apps are a bit hard to test, especially since there are so many possible edge cases that can occur from various scenarios. Here’s how you can test and verify that the basic offline behavior is working.

Turn on airplane mode to cut off all network connectivity on your device. Then add a new weight entry and confirm that the entry shows up on the chart. This will also trigger the Sync failed! error to appear because it tried to sync the new entry with the server.

Now close your app, turn off airplane mode, and reopen the app. You should see the Syncing Data… message in the sync indicator, which will eventually disappear. On the server side, if you check your database, the entry added during airplane mode should be visible.

The entire AdonisJs app can be found here.

Where to go from here

First of all, congrats! You are now the proud owner of a weight tracking app that has full offline support. However, you will probably agree that this will probably not beat Google Fit or Apple Health. Luckily, there are a lot of things that can polish this up to compete with professionally built weight tracking platforms.

Here are some bite-sized ideas that you can build into this app to improve it:

1. Offline sync can be error-prone. Just telling your users a sync failed isn’t helpful at all. You could build a detailed error viewer to show users what went wrong during sync and how they can overcome the error to sync their data.
2. With offline apps, syncing with a server is nice, but at the end of the day, you have to assume that users would want to take their data home with them instead of keeping it on a server somewhere. So build a way to export all their data and import it back into the app.
3. Sometimes, sync can fail partially. For instance, if the user adds five entries in offline mode, for some reason, during sync, four of them might be saved in the DB, but one might fail. Having a list of failed entries could be beneficial because you could then ask the user to trigger sync for the failed items only or ask them to reinsert those failed entries.

If you build one or any of the above into the app — or even more than that — feel free to give me a shout and I’d be happy to review your code. Godspeed!