Skip to content
Chif3n
4 min read

Debugging Offline-First SQLite Sync in React Native

Solving race conditions and schema migrations when syncing local SQLite databases with cloud endpoints on intermittent mobile networks.

Building for Cameroon means building for intermittent networks. When a user on a 3G connection sends a form, they may not get a response — but they expect the data to be there when they reconnect.

This is the exact problem we hit building LifeDrop’s blood donor coordination flow. Here is the architecture that finally worked, and the two failure modes that burned us before we got there.

The Core Problem

Most React Native apps treat the server as the source of truth. When you are offline, that assumption collapses. You need a local queue that survives app restarts, background kills, and partial syncs.

We chose expo-sqlite for the local store and a REST endpoint for the cloud write. The naive approach:

1. User submits form
2. POST to API
3. On success, mark as synced
4. On failure… retry? When?

That last question is where things fall apart.

Failure Mode 1: Race Conditions on Reconnect

When the device reconnects, every queued operation fires simultaneously. Two donation events for the same donor ID land at the server out of order. The cloud record ends up with the wrong timestamp.

The fix is a serial sync queue with a mutex lock.

lifedrop-syncdb/schema.ts
import * as SQLite from 'expo-sqlite';
 
export const db = SQLite.openDatabaseSync('lifedrop.db');
 
export function initSchema() {
db.execSync(`
CREATE TABLE IF NOT EXISTS sync_queue (
id INTEGER PRIMARY KEY AUTOINCREMENT,
table_name TEXT NOT NULL,
operation TEXT NOT NULL CHECK(operation IN ('INSERT','UPDATE','DELETE')),
payload TEXT NOT NULL,
created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
attempts INTEGER NOT NULL DEFAULT 0,
synced_at INTEGER,
error TEXT
);
 
CREATE INDEX IF NOT EXISTS idx_sync_queue_synced
ON sync_queue (synced_at);
`);
}

Failure Mode 2: Schema Migrations Breaking Synced Records

When we shipped v2 of the donor form, we renamed a field. The sync queue held serialised payloads from v1. When those finally flushed, the server rejected them because the field name no longer matched.

The fix: version your payloads in the queue, and run a migration step in initSchema() when the app boots.

// In db/schema.ts — run this before any queue access
export function runMigrations(currentVersion: number) {
  if (currentVersion < 2) {
    db.execSync(`
      UPDATE sync_queue
      SET payload = json_replace(payload, '$.donorID', json_extract(payload, '$.donor_id'))
      WHERE json_extract(payload, '$.donor_id') IS NOT NULL;
    `);
  }
}

SQLite’s json_extract and json_replace work on stored text in-place, so you can patch the queue without deserialising in JavaScript.

What Shipped

The combination of a serial flush mutex, per-row attempt tracking, and payload versioning gave us a sync queue that survives:

  • App backgrounded mid-sync
  • Network drops mid-batch
  • Server returning 5xx during partial sync
  • Schema changes between app versions

Pending counts now surface in the UI via useSyncStatus, so field workers know exactly what is waiting to sync when they walk into a clinic with WiFi.

All writing

Comments

Comments coming soon. Set up Giscus on the repo to enable them.