Building an offline-first app with Expo, Supabase and WatermelonDB

Tired of apps freezing when the Wi-Fi drops? Do you wish you could jot down notes or update tasks even on a bumpy aeroplane ride? This feature generates challenges that all modern app developers struggle with. This is why Offline-first or Local-first app architectures are gaining popularity.

‍In this post, we start exploring how you can achieve offline-first architecture by using two popular tools: WatermelonDB (Local database for React Native/Expo) and Supabase (Open-source Firebase Alternative).

‍

Are you looking to build an offline first Expo app or need support integrating WatermelonDB into your existing Expo and Supabase app? Book a free call with one of our experts to find out how Morrow can help.

‍

What is Offline-First architecture?

‍

Traditional app architectures are heavily tied to a backend API to operate. From the moment they launch, they need to pull data from the backend servers/databases to operate. And throughout their lifecycle, they constantly rely on backend connections to update their data and operate.

Consequently, the user experience relies heavily on the quality of the internet connection. We are all familiar with the “loading spinner” while the app loads the next page. And if it happens to get offline, such apps break and cannot function.

Some solutions rely on caching the data locally, but these only ease the problem and do not resolve it.

Offline-first architecture, on the other hand, shifts the responsibility for the data lifecycle on the front end and has the backend only as a backup/synchronisation role. 

The idea is that the main application only interacts with a local database for reading, creating, updating, and deleting data. Then, a service that runs locally ensures that the data is synchronised with a backend service when is changes or at frequent intervals.

As a consequence, UX is ultra fast (data is being read/written from and to the local database), but also the ability to work offline (user can interact with the data normally, and when back online, the synchronisation happens in the background).

However, not all applications fit the offline-fist architecture, or some parts of an application cannot be offline first. Also, synchronisation comes with its challenges (like conflict resolution).

But if you decide that Offline-First is fit for your next project, let’s see how WatermelondDB and Supabase can help you achieve this.

‍

See our WatermelonDB plugin tutorial here.

‍

WatermelonDB Sync Protocol

‍

WatermelonDB is a fast and reactive database solution for applications that use SQLite under the hood. But for our case, it also supports Offline-first out of the box, which we will use to sync with a Supabase database in the backend. Supabase is also using PostgreSQL under the hood, which is also an SQL-based database.

‍

Overview of the Sync Protocol

‍

WatermelonDB has its sync protocol that takes care of much of the work in the front end. We only have to take care of the communication between the front and the backend and the handling of data in the backend.

An overview of the process is the following:

1. The synchronisation process is kicked off in the front end. This can be done manually, at specific intervals, or when we decide to initiate it (for example, when a record changes, etc). 

2. WatermelonDB calls a ‘pull’ function (provided by us) with the last_pulled_at timestamp (kept automatically by watermelonDB). 

3. The ‘pull’ function reaches out to the backend and fetches the latest changes (updates, creations, deletions) since last_pulled_at.

4. WatermelonDB updates the local database with the changes. It also resolves conflicts. If a record was updated both on the server and locally since last_pulled_at, the local entry takes precedence.

5. Then, WatermelonDB calls the “push” function (provided by us) with the latest local changes. The “push” function should send those changes to the backend, and they should be stored/merged in the backend.

6. Once this is done successfully, WatermelonDB considers that the sync was successful and updates the timestamps and internal sync information accordingly.

As we have seen, we have to provide the implementation of the “pull” and “push” functionality.

‍

Pull functionality

‍

The signature of the “pull” function is 

To keep things simple for this demo, we won’t be handling schemaVersion and migrations. 

Our pull function should take lastPulledAt epoch timestamp and return the backend changes in the format of SyncPullResult.

SyncPullResult is an object of type:

So we have to return the results in SyncDatabaseChangeSet format and the timestamp from the backend server, when these updates were taken.

The SyncDatabaseChangeSet type is a record of tables with created, updated, and deleted information per table.

‍

Push functionality

‍

The signature for the “push” function is

So, WatermelonDB will provide us with the changes in the same form (SyncDatabaseChangeSet) and the timestamp of last_pulled_at. Our function should push those changes to the backend and handle them accordingly. We don’t have to return anything.

Notice the signature also has an undefined. This means that we are not obliged to implement a “push” function. This applies in cases where we want to only pull changes from our database as a cache. But this is not the case with our demo here.

‍

Schema of sync implementation

‍

If we add in our tables/models the following fields: 

Then WatermelonDB will track and update them automatically, with the timestamps on the front end.

We most probably want to keep track separately on the server side when something was created and/or updated. So, on the server-side (only) database on each sync-able table, we will also add the following fields:

‍

Deletion of records

‍

You might have noticed that SyncDatabaseChangeSet, only communicates the IDs of the deleted records and not the whole records. Deleted records are automatically handled by WatermelonDB, so our server needs only to send the IDs of the deleted records. 

But to efficiently handle deleted records on our side in the server, we can’t just delete a record. We can’t easily keep track of it. It is gone. So, the approach we will take here is not to delete a record but to mark it as deleted instead.

So, in our schema, we’ll add also:

So we can, in addition, track when it was deleted.

‍

Making the Example App Sync to Supabase

‍

Our example here builds on top of the initial Quick Intro to WatermelonDB example. If you’re not familiar with it, check it out:

https://www.themorrow.digital/blog/how-to-use-watermelondb-with-react-native-expo

The source code for our example here can be found at:

https://github.com/morrowdigital/watermelondb-plugin-example

The app we built, in the initial example, was a simple app where the user could track his favourite board game titles and the minimum number of players needed to play.

Here is the schema for this small database.

Now, we’ll proceed to add synchronisation with a Supabase project in the backend. We will add the ability to update and delete games in the front end (creation ability is already there), but also create, update, and delete games in the backend (to cover the case of incoming changes from a second device or other system).

‍

How to set up the Supabase project

‍

Create a Supabase project via the Supabase dashboard.

Next, we must create the database and the necessary functions to interact with the clients. For each SQL query we’ll present below, you can:

1. Run the query from Supabase SQL Editor.

2. Make sure that the function was created via the Database > Functions menu:

Timestamp functions:

‍

This script creates two functions that convert between epoch number (used by WatermelonDB) and timestamp (used by Supabase)

‍

Board_games Table:

‍

This script will initialise the board_games table in Supabase with the proper data fields needed for sync (created_at, updated_at, deleted_at, server_created_at, last_modified_at).

‍

A word of caution: The above script creates the table without an RLS (Row Level Security) policy. This means that anyone with the correct Supabase keys can modify any row. So, this table is not production-ready. We do this to keep this demo simple and to the point of basic syncing practices. To update it to production-ready, we need to create an RLS policy and insert the authentication state of our user into our syncing logic. This is beyond the scope of this demo.‍

‍

Create_game Function:

‍

This script creates a function allowing us to easily create a new board game record. We should note that although we store the client-side tracking field (created_at and updated_at) we automatically set the server-side ones (server_created_at and last_modified_at), as we discussed earlier. 

See our Guide to Expo’s Environmental Variables (Expo v49).

‍

Update_game function:

‍

This script updates an existing game record. The logic here is the same. We pass through the updated_at sent by the client, and we keep our server-side updated with the last_modified_at field.

‍

Pull Function:

‍

This function gets the last_pulled_at timestamp from the client and gathers up any changes of the board_games table into respective created, updated, and deleted fields of the JSON object that are returned. This function will be called by WatermelonDB’s sync.

We should note the following here.

We always return an empty array in the created key. All of our updates (new rows and updated rows) are returned in the updated key. This makes things simpler for us on the server side as we only check for last_modified_at and the deleted_at fields. WatermelonDB can accept new rows inside the updated key and will create new records on the client without a problem. 

We return only the IDs of the deleted rows. Deleted rows are the ones that are modified after the timestamp, and they have a deleted_at timestamp. As we will see below, when we mark a record as deleted, we also update its last_modified_at field.

The function returns the results in the format that the WatermelondDB ‘pull’ function expects it. This is unnecessary as we could do further processing in the client-side ‘pull’ function since we are also writing it. But for simplicity, we do this here on the server side.

‍

Push Function:

‍

This function handles the “push” results from the client side. We receive the JSON object and we start processing for the board_games table, each of the entries.

For created we call the create_game function. For deleted IDs, we update the deleted_at and last_modified_at of each respective row. Finally, we call the update_game function for the updated entries.

Our server is now set up. On the client side, we add the Supabase client.

And set up our Supabase client with our Supabase project URL and key.

We are done on the Supabase side. Now, to modify WatermelonDB.

‍

How to set up WatermelonDB

‍

First, we have to update the schema and model for our table:

Schema.ts

And the corresponding model:

‍

Model.ts

As we mentioned above, synchronisation is not triggered automatically. This will have to be handled by the implementation app. In our demo, we do it manually via UI so that we can test and monitor the synchronisation mechanism and how it works.

‍

App.tsx

The sync function is as follows:

Sync.ts

We call the synchronize function from WatermelonDB, and we provide it with our push and pull functions. Remember that push is optional.

In the pull function, all the action happens with this line:

If you take a look at our pull Supabase function, you’ll see that if the lastPulledAt is 0, then all the records of the table will be pulled. This is the case when we call synchronize the first time. So, on the first sync, we download all the records. 

There is also the Turbo Login mode for a more efficient first pull, but this is beyond the scope of this tutorial. 

(see: https://watermelondb.dev/docs/Sync/Frontend#advanced-adopting-turbo-login)

And on the push function, the main action happens at this line:

One thing we should also note is the use of the option:

Remember that our server sends new server-side created rows as updated. If we omit this option, WatermelonDB will still handle this, but it will complain each time it creates a new record as part of the update.

‍

WatermelonDB plugin example

‍

You can find a fully working example here:

https://github.com/morrowdigital/watermelondb-plugin-example

To run it, you must create a Supabase project and follow the instructions above.

Then, create a .env file in the root of the project folder.

By running the example, you can create games, update games (Increase the number of players), and delete games.

By pressing ‘SYNC’, you should be able to see pulling and pushing happening in the console.

And also see your changes being replicated in the Supabase table.

Where to go from here

‍

The truth is that we only scratched the surface of Offline-First with this tutorial. The Offline-First architecture is not a trivial subject, and mastering it requires delving deeper into data synchronisation, conflict resolution, and edge-case handling.

As we saw, though, WatermelonDB’s Sync protocol resolves quite a number of problems for us.

Here's what you'll want to look into next:

Creating RLS policies, and making sure that our sync happens only when the user is authenticated.

Handle edge cases and develop a more robust version of our sync protocol. For example, we did not handle errors in the backend, nor any network connection errors with retries, etc. WatermelonDB’s documentation has quite a few recommendations on how to possibly handle these cases. See:

• https://watermelondb.dev/docs/Sync/Intro
• https://watermelondb.dev/docs/Implementation/SyncImpl

We hope this article helped you get an idea of how we can do Offline-First with WatermelonDB and Supabase. 

Happy Coding!

Are you looking to build an offline first Expo app or need support integrating WatermelonDB into your existing Expo and Supabase app? Book a free call with one of our experts to find out how Morrow can help.

‍

Read next: "Keep your Expo SDK updated for project success".