All Posts

Proof of concept: expo-sqlite integration with CR-SQLite

Aug 10, 2023 by

Avatar of Alan Hughes

Alan Hughes

CR-SQLite syncing two devices with a todo list

We're releasing an alpha version of the expo-sqlite library that includes a proof-of-concept iOS integration with CR-SQLite, an SQLite extension that "allows merging different SQLite databases together that have taken independent writes" that makes up part of the Vulcan toolchain.

We're also releasing an example project that uses CR-SQLite with TinyBase, a reactive datastore for local first apps. It demonstrates what is known as local-first architecture. For a comprehensive look at this approach and why we think it matters, check out Johannes Schickling's "Local-first app development" talk at App.js 2023, the Ink & Switch "Local-first software" essay that coined the term, and the local-first community at LFW.dev.

Convergent, Replicated, SQLite (CR-SQLite)

CR-SQLite is a run-time loadable extension for SQLite and libSQL. It allows merging different SQLite databases together that have taken independent writes.

With CR-SQLite, your users can all write to their own app's local SQLite database while offline. They can then come online and merge their databases together, without conflict.

In other (much more technical) words, CR-SQLite adds multi-master replication and partition tolerance to SQLite via conflict-free replicated data types (CRDTs) and/or causally ordered event logs.

For more information, check out the CR-SQLite README.

Integrating expo-sqlite and CR-SQLite

CR-SQLite adds functionality that allows us to request a set of changes from an SQLite database, and then we can insert those changes to another copy of the SQLite database, likely running on another device.

It works in two steps:

  1. Convert tables we would like to sync to CRRs (conflict-free replicated relations) with the following command: 
    SELECT crsql_as_crr('todo');
  2. Then, request the changes from the database, send them to our server, and insert them in to the receiving peer's database: 
    SELECT * FROM crsql_changes

The results can be merged in any order (they are commutative) and the databases will always converge on the same state given the same set of operations.

Example project

The example project is a todo list: expo/todo-sync-example. There are two parts:

  1. Client: A React Native app that uses expo-sqlite and TinyBase to store the user's todo list.
  2. Server: A Node server that uses PartyKit to open a websocket and listen for changes on any of the connected clients. When it receives a change, they are pushed to the other clients which will merge them into their local databases.

To run the example project, clone the example repo, check out the initial-poc branch, and run yarn to install its dependencies (this project is a Yarn Classic workspace).

After that, start the server:

cd apps/server
yarn start

Then, start the app:

cd apps/mobile
npx expo run:ios

We now have the app running in the iOS simulator. Let's start a second instance of the app on another simulator so that we can see the sync in action:

npx expo run:ios -d # Select another device from the prompt

Make some changes, add and delete todos, mark them as complete, or delete everything. No matter which device you use, you will see both stay in sync.

How it works

The first step is to set up the TinyBase persister.

// App.tsx
import { useCreatePersister } from 'tinybase/lib/ui-react';
import { createExpoSqlitePersister } from './app/store';

function TodoList() {
  // ...

  useCreatePersister(
    store,
    (store) =>
      createExpoSqlitePersister(store, db, {
        mode: 'tabular',
        tables: {
          load: { todo: { tableId: 'todo', rowIdColumnName: 'id' } },
          save: { todo: { tableName: 'todo', rowIdColumnName: 'id' } },
        },
      }),
    [db],
    async (persister) => {
      await persister.startAutoLoad();
      await persister.startAutoSave();
    }
  );
}

The createExpoSqlitePersister() function allows TinyBase to interact with the underlying data store, which is expo-sqlite in this case. As we make changes to our store, changes will be persisted in the local SQLite database. This is everything we need to set up persisting our data locally.

Next, we need to notify the server of our changes. We'll use the useSync() hook and the onDatabaseChange() listener provided by expo-sqlite.

First, create a socket:

// apps/mobile/app/useSync.ts
import { useEffect, useRef } from 'react';
import PartySocket from 'partysocket';

export function useSync() {
  const socket = useRef(createPartySocket()).current;
  // ...
}

Then let's connect our server:

// apps/mobile/app/useSync.ts

export function useSync() {
  // ...
  useEffect(() => {
    const handleMessage = (e: MessageEvent<string>) => {
      if (!syncEnabled) return;
      handleMessageAsync(e);
    };

    socket.addEventListener('message', handleMessage);

    if (syncEnabled) {
      // Send an init message to get the latest changes
      socket.send('init');
    }

    return () => {
      socket.removeEventListener('message', handleMessage);
    };
  }, [socket, syncEnabled]);
}

async function handleMessageAsync(e: MessageEvent<string>) {
  const data = JSON.parse(e.data);
  const rows = data[0].rows;

  for (const row of rows) {
    const { pk, ...rest } = row;
    const sql = `INSERT INTO crsql_changes ("table", 'pk', 'cid', 'val', 'col_version', 'db_version', 'site_id') VALUES (?, ${pk}, ?, ?, ?, ?, ?)`;
    try {
      await db.execAsync(
        [
          {
            sql,
            args: Object.values(rest),
          },
        ],
        false
      );
    } catch (e) {
      console.log(e);
    }
  }
}

We register a handler for the message event, and when we receive it, we insert the results into our database. Finally, we use another effect to set up the onDatabaseChange() event listener so that we are notified when the database has changed. When we receive an update event, we request our changes from the crsql_changes table and send the results. Allowing enabling and disabling of the sync is optional. Also, note that the queries used here will be improved in future versions so users won't have to know about these details.

// apps/mobile/app/useSync.ts

export function useSync() {
  // ...
  useEffect(() => {
    const maybeSendChanges = async () => {
      if (syncEnabled) {
        const changes = await requestChanges();
        socket.send(JSON.stringify(changes));
      }
    };

    // Subscribe to changes
    const subscription = db.onDatabaseChange(async (result) => {
      if (result.tableName.includes('__crsql_')) return;
      maybeSendChanges();
    });

    // Also maybe send them right away, in case changes happened while sync was
    // disabled
    maybeSendChanges();

    return () => subscription.remove();
  }, [syncEnabled]);
}

async function requestChanges() {
  return await db.execAsync(
    [
      {
        sql: `SELECT "table", quote(pk) as pk, cid, val, col_version, db_version, site_id FROM crsql_changes WHERE db_version > -1`,
        args: [],
      },
    ],
    false
  );
}

Implementation note: a real-world application would rarely want to use WHERE db_version > -1 because this will select the entire set of changes from the crsql_changes table, rather than only the changes that have been applied since the most recent sync (for example: WHERE db_version > ?last_sent_version). We also left out WHERE site_id IS NULL, which is likely be used in order to ensure we only select changes that occurred on the local client, rather than re-sending changes received from a recent sync. The code is simplified here for the sake of this proof of concept, where we have not set up an state persistence on the sync server. We'll continue to iterate on the main branch to create an example that better represents a real app.

What's next

We plan to begin investing heavily in our SQLite bindings, expo-sqlite, working with Matt Wonlaw on integrating seamlessly with CR-SQLite, and coordinating with James Pearce on a TinyBase persister. We're big believers in local-first architecture, and you should expect to see more work from us in this space in the future.

We'll continue to evolve this example to address many of the current limitations and to push more of the generic implementation details into related libraries. In particular, we plan to update it to:

  • Support the CR-SQLite extension on Android.
  • Incrementally sync changes between clients, rather than syncing the entire set of changes on each message.
  • Incrementally write to SQLite from TinyBase.
  • Demonstrate persistence on the syncing server, and examples of how you can leverage hosted SQLite services.

Send us your feedback on Discord, @expo, Threads, or Bluesky.