Offline-First Sync with SQLite and REST in React Native

Mobile apps fail when the network does. A typical React Native app that depends on a REST API for every screen load shows a spinner while the user stares at an empty screen, then either works or errors based on connectivity. Offline-first flips that: the local database is the single source of truth, the server is a replication target. This article walks through implementing that architecture with SQLite as the local store and a REST API as the remote—covering the queue mechanics, conflict resolution, and the edge cases that will bite you if you skip them.

The core architecture: local mutations and a sync queue

Every write your app performs—creating a note, updating a task, deleting a contact—goes to SQLite first. Immediately after the local transaction commits, you enqueue a mutation object into a persisted queue. That mutation contains three things: a type (e.g., "NOTE_CREATE"), a payload (the full record data or a diff), and a client_timestamp (monotonic local clock value). The queue must survive app kills, so it lives in SQLite in a dedicated sync_queue table.

CREATE TABLE IF NOT EXISTS sync_queue (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  mutation_type TEXT NOT NULL,
  payload TEXT NOT NULL,
  client_timestamp INTEGER NOT NULL,
  status TEXT DEFAULT 'pending',
  retries INTEGER DEFAULT 0,
  created_at TEXT DEFAULT (datetime('now'))
);

The sync engine runs as a background process. It polls the queue for status = 'pending' entries ordered by client_timestamp ASC, sends each to the REST API, and on success updates the local record with the server's response (typically a server-assigned timestamp and any server-generated fields). On failure it either retries with backoff or marks the mutation as failed.

Reads are always local. Your UI calls SELECT ... FROM notes WHERE id = ? and renders the result immediately. If you want to refresh data from the server, that happens as a silent background operation that updates local rows, which then trigger a re-render via your state management layer (React Query, Zustand, or plain setState with a subscription to SQLite changes). The user never waits for the network to show data.

Choosing a SQLite library: expo-sqlite vs react-native-sqlite-storage vs WatermelonDB

The right library depends on your React Native setup and how much sync infrastructure you want to own.

expo-sqlite is the simplest path if you are already on Expo. SDK 50 introduced a synchronous API that lets you run queries in the same JS thread without Promise overhead, which simplifies the write-then-enqueue pattern. The older expo-sqlite/async is fine too, but you need to handle the async gap carefully—don't enqueue a mutation before the SQL write is confirmed.

react-native-sqlite-storage gives you more control: you can open separate database connections per thread, and it supports FTS5 for full-text search. The tradeoff is configuration. If you've already navigated the maze of Adopting React Native New Architecture: Fabric and TurboModules, you know the linking story for this library on the new architecture is still maturing in late 2024.

WatermelonDB is the heavy option. Its built-in sync protocol expects a specific pull/push API on your backend: a pull endpoint that returns records changed since a given timestamp with a pagination cursor, and a push endpoint that accepts an array of mutations. If your backend can conform to that shape, WatermelonDB handles queue persistence, conflict detection, and lazy loading for you. If your API is already established with a different protocol, you end up writing an adapter layer that negates the benefit.

I typically reach for expo-sqlite with the synchronous API for new projects. The code is straightforward, the dependency is stable, and you keep full control over the sync protocol on both sides.

Designing the sync protocol: timestamps, incremental sync, and pagination

Your REST API needs two endpoints: one for pulling changes from the server, one for pushing local changes to the server. They should be idempotent and designed for incremental sync.

The pull endpoint returns records modified after a known point. The client sends a last_synced_at parameter (a server timestamp from the previous successful pull) and a limit (e.g., 100). The server responds with an array of records where updated_at > last_synced_at, plus a next_cursor if there are more pages.

GET /api/sync/pull?last_synced_at=2024-11-01T10:00:00Z&limit=100
Response:
{
  "records": [...],
  "next_cursor": "2024-11-01T10:05:30Z",
  "has_more": false
}

On the client, after processing each page, you update a sync_cursor value stored in a local _sync_meta table. The next pull starts from that cursor. This avoids re-downloading the same records.

For pushes, each mutation carries a client-generated UUID (mutation_id) and a monotonic local counter. The server uses the mutation_id for idempotency—if it receives the same mutation twice (e.g., due to a retry), it returns the existing result instead of applying it again.

POST /api/sync/push
Body:
{
  "mutations": [
    {
      "mutation_id": "a1b2c3d4-...",
      "type": "NOTE_UPDATE",
      "payload": { "id": 42, "title": "New title", "updated_at": "2024-11-01T09:59:00Z" },
      "client_clock": 17
    }
  ]
}
Response:
{
  "results": [
    {
      "mutation_id": "a1b2c3d4-...",
      "status": "applied",
      "server_timestamp": "2024-11-01T10:02:00Z",
      "server_record": { ... }
    }
  ]
}

The server processes mutations in order of client_clock. The response includes the server-assigned timestamp for each mutation; the client stores that as the record's last_server_update and uses it as last_synced_at during the next pull.

Conflict resolution strategies: last-write-wins vs CRDTs

Conflicts happen when a user edits a record offline and someone (or another device) edits the same record on the server. The two versions diverge and need to be reconciled.

Last-write-wins (LWW) is the simplest: take the record with the later updated_at timestamp. If the server timestamp is newer, the server version wins and the local changes are discarded. If the local timestamp is newer, the local changes overwrite the server version.

The problem with naive LWW: if a user's device clock is ahead of the server clock by even a few seconds, their offline writes will always win, potentially overwriting legitimate server-side changes. Mitigate this by not trusting device timestamps for conflict resolution. Use the client timestamp only for ordering the local queue. The server should assign its own timestamp to every mutation and use that for LWW. If the server receives a mutation with a created_at that is older than its current record's updated_at, reject it with a 409 Conflict and return the current server version. The client then rolls back its optimistic update and replaces the local record with the server's version.

CRDTs (via libraries like Automerge or Yjs) allow concurrent edits to merge without data loss. Instead of storing the current state, you store an append-only operation log. Merging two logs produces a deterministic result that includes all edits. The tradeoff is memory: the operation log for a single document can grow to many megabytes over time, and you need a compaction strategy. For most productivity apps (notes, tasks, contacts), LWW with a grace period is sufficient. I reserve CRDTs for collaborative editing where multiple users actively work on the same piece of data, like a real-time text document.

Handling network state and queue persistence

You need to know when to flush the queue. Use @react-native-community/netinfo to subscribe to connectivity changes.

import NetInfo from '@react-native-community/netinfo';
 
NetInfo.addEventListener((state) => {
  if (state.isConnected && state.isInternetReachable) {
    syncEngine.flushQueue();
  }
});

Don't flush the queue on every connectivity change immediately—throttle it. If the user walks through a tunnel and connectivity flickers, you'll hammer the API with rapid sync attempts. A 5-second debounce is usually enough.

The queue itself should live in SQLite, not AsyncStorage. AsyncStorage has a size limit (around 6 MB on Android via the default implementation), and it lacks transactional guarantees. If your app crashes between writing a mutation to AsyncStorage and writing the corresponding SQL change, you have an orphan mutation. With SQLite, you can wrap both operations in a transaction:

async function writeNote(note: Note) {
  const db = await getDb();
  await db.execAsync(`
    BEGIN TRANSACTION;
    INSERT INTO notes (id, title, body, updated_at) VALUES (?, ?, ?, ?);
    INSERT INTO sync_queue (mutation_type, payload, client_timestamp) VALUES (?, ?, ?);
    COMMIT;
  `, [note.id, note.title, note.body, note.updated_at, 'NOTE_CREATE', JSON.stringify(note), getClock()]);
}

If the app crashes between the two inserts, the transaction rolls back. No orphan mutations.

Implement exponential backoff for failed mutations. After the first failure, wait 2 seconds. After the second, 4 seconds. After 5 failures, mark the mutation as failed and surface it to the user with a "Sync failed" indicator. Don't block the rest of the queue on one failed mutation—skip it and process others. The failed mutation becomes a separate retry job.

This queue-based approach mirrors the pattern I wrote about in Expo EAS Build Profiles That Keep CI Reproducible—predictable, deterministic behavior even when external conditions change.

Schema migrations in an offline-first world

Local schema migrations are dangerous because they can break pending sync mutations. If you add a NOT NULL column to a table, but a queued mutation from an older app version doesn't include that column, the INSERT will fail.

Version your local schema in a _migrations table. On app launch, run any pending migrations forward-only. Use ALTER TABLE ADD COLUMN instead of recreating tables. If you must drop a column, do it by creating a new table without that column, copying data, and dropping the old table, but only after you've flushed or discarded all pending mutations that reference the dropped column.

The expo-sqlite migration API in SDK 50+ integrates well here. Define migrations as numbered steps, and each step runs inside a transaction:

import { migrate } from 'expo-sqlite/migrations';
 
const migrations = [
  {
    version: 1,
    statements: [
      'CREATE TABLE IF NOT EXISTS notes (id TEXT PRIMARY KEY, title TEXT, body TEXT, updated_at TEXT)',
      'CREATE TABLE IF NOT EXISTS sync_queue (...)',
    ],
  },
  {
    version: 2,
    statements: [
      'ALTER TABLE notes ADD COLUMN tags TEXT DEFAULT "[]"',
    ],
  },
];
 
await migrate(db, migrations);

Never run destructive migrations (DROP TABLE, DROP COLUMN) on a table that might have pending mutations referencing it. Detect pending mutations for that table first, and either block the migration or flush those mutations to the server first. A safer pattern is to mark the offending column as deprecated and stop writing to it, then drop it in a future version after the sync queue has cleared naturally.

Failure modes and gotchas: queue overflow, stale data, and race conditions

If a user is offline for a week, their queue can grow to thousands of mutations. This is a problem because flushing that many mutations on reconnect will take minutes and might timeout. Set a hard limit—I use 1000 entries—and when the queue exceeds that, reject new writes and show a non-dismissable warning: "Too many offline changes. Please connect to the internet to sync before making more edits."

Race condition: a user edits a record offline, and while the mutation is in flight, the same record is updated on the server. The client sends its mutation, and the server either rejects it (409 Conflict) or accepts it and returns the server's version. In either case, the local state the user is looking at becomes stale. Use optimistic updates: apply the user's edit to the local database immediately, but keep a copy of the original record. When the server responds, if the mutation was accepted, replace the optimistic record with the server response. If rejected, roll back to the original record and display a "conflict resolved" toast.

Clock skew between the device and server is the most insidious bug. If the device clock is five minutes ahead, and a user creates a record offline with a local created_at of 10:05, but the server's created_at is 10:00, the server might interpret the record as having been created in the future. The fix: never use device timestamps for anything except ordering within the local queue. The server always assigns the authoritative timestamp. On the pull side, use the server-assigned timestamps for last_synced_at. This means your local records have two timestamp columns: local_updated_at (for UI display while offline) and server_updated_at (for sync ordering). When the server responds, overwrite both.

Finally, if you're using a queue-based sync and the API returns a 500 error mid-sync, you risk processing mutations out of order on retry. To prevent this, the sync engine should always pull fresh server state before retrying failed mutations. The pull might discover that the server already applied some of your queued mutations but the response was lost. Your idempotency key (mutation_id) handles that—the server returns the existing result instead of re-applying.

Key takeaways

• Writes go to SQLite first, then enqueue to a persisted sync_queue table. Reads always hit the local database. The network is only used to sync the queue and pull remote changes.
• Use expo-sqlite for new Expo projects unless you need WatermelonDB's sync protocol or FTS5 from react-native-sqlite-storage.
• Design your REST sync protocol around incremental pull (with a last_synced_at cursor) and push (with client-generated idempotency keys). Avoid full re-syncs.
• Last-write-wins with server-assigned timestamps covers most conflict scenarios. Use CRDTs only for collaborative editing on a single document.
• Persist the sync queue in SQLite, not AsyncStorage. Wrap write and enqueue in a single transaction. Implement exponential backoff and a maximum queue size to prevent overflow.
• Schema migrations must be backward-compatible with pending sync mutations. Use ALTER TABLE ADD COLUMN and avoid destructive changes until the queue is empty.