React Native offline-first: SQLite write-ahead log

• Turn on SQLite WAL in Expo. It fixes “database is locked”.
• Batch writes in one transaction. Fewer fsyncs. Faster UI.
• Add a busy timeout + retry. Stops random write failures.
• Measure it. I logged real timings per write batch.

Context

My fitness app needs 5-second set logging. No spinning loaders. No “try again” toast. And it has to work on airplane mode.

I use Expo + SQLite because I need real queries. Joins. Indexes. Counting volume per workout. AsyncStorage fell over fast.

Then I hit the classic wall: concurrent reads + writes. Search is running. The workout screen is inserting sets. And suddenly I get SQLITE_BUSY: database is locked.

I wasted 4 hours blaming React renders. Most of it was wrong. SQLite was doing exactly what I asked. I just hadn’t configured it for a mobile app that’s always doing something.

1) I stop fighting concurrency. I enable WAL.

SQLite defaults can be conservative. WAL (write-ahead logging) changes the locking model. Readers don’t block writers the same way.

On mobile, that’s the difference between “feels instant” and “random lock errors at the worst moment”.

I enable WAL once on startup. I also set a busy_timeout. Without it, a short lock turns into a hard failure.

// db.ts (Expo + expo-sqlite)
import * as SQLite from 'expo-sqlite';

export const db = SQLite.openDatabaseSync('gym.db');

export function initDb() {
  // WAL improves read/write concurrency.
  db.execSync('PRAGMA journal_mode = WAL;');

  // Wait up to 2 seconds when the DB is busy.
  db.execSync('PRAGMA busy_timeout = 2000;');

  // Durability vs speed. NORMAL is fine for app data.
  db.execSync('PRAGMA synchronous = NORMAL;');

  // Basic schema for set logging.
  db.execSync(`
    CREATE TABLE IF NOT EXISTS sets (
      id TEXT PRIMARY KEY NOT NULL,
      workout_id TEXT NOT NULL,
      exercise_id TEXT NOT NULL,
      reps INTEGER NOT NULL,
      weight_kg REAL NOT NULL,
      created_at_ms INTEGER NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_sets_workout ON sets(workout_id, created_at_ms);
  `);
}

I learned the hard way that WAL isn’t “optional tuning”. If you’re doing offline-first + live search, you’re doing concurrent DB work. WAL makes that survivable.

2) I batch writes. One transaction. No drama.

My original implementation inserted one set per tap. One INSERT per tap. It worked… until I added auto-save for notes, timer ticks, and background sync metadata.

Then the lock errors got spicy.

The fix was boring. Batch writes inside a transaction. It reduces lock churn and fsync overhead. And it’s easier to retry.

This is the exact helper I use.

// writeBatch.ts
import { db } from './db';

type SetRow = {
  id: string;
  workoutId: string;
  exerciseId: string;
  reps: number;
  weightKg: number;
  createdAtMs: number;
};

export function insertSetsBatch(rows: SetRow[]) {
  if (rows.length === 0) return;

  db.withTransactionSync(() => {
    const stmt = db.prepareSync(
      `INSERT INTO sets (id, workout_id, exercise_id, reps, weight_kg, created_at_ms)
       VALUES (?, ?, ?, ?, ?, ?)`
    );

    try {
      for (const r of rows) {
        stmt.executeSync([
          r.id,
          r.workoutId,
          r.exerciseId,
          r.reps,
          r.weightKg,
          r.createdAtMs,
        ]);
      }
    } finally {
      stmt.finalizeSync();
    }
  });
}

This changed the feel immediately.

Not because batching is magic. Because I stopped forcing SQLite to constantly acquire/release locks while the UI is also querying.

3) I treat SQLITE_BUSY like a normal thing. Retry it.

Even with WAL + batching, you can still hit SQLITE_BUSY.

Example. User is scrolling exercise history (big query). At the same moment, they log a set. The write wants a lock. The read is still running. Boom.

busy_timeout helps. But I still saw failures on older Android devices.

So I added a tiny retry wrapper. Only for SQLITE_BUSY. Not for everything.

// retrySqliteBusy.ts
const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));

export async function retrySqliteBusy(
  fn: () => T,
  opts: { retries?: number; baseDelayMs?: number } = {}
): Promise {
  const retries = opts.retries ?? 5;
  const baseDelayMs = opts.baseDelayMs ?? 20;

  let lastErr: unknown;

  for (let i = 0; i <= retries; i++) {
    try {
      return fn();
    } catch (e: any) {
      lastErr = e;
      const msg = String(e?.message ?? e);

      // expo-sqlite throws different shapes depending on platform.
      const isBusy = msg.includes('SQLITE_BUSY') || msg.includes('database is locked');
      if (!isBusy || i === retries) throw e;

      // Small exponential backoff.
      await sleep(baseDelayMs * Math.pow(2, i));
    }
  }

  throw lastErr;
}

I call it around the transaction.

Not everywhere. Just write paths.

I also log when it happens. If retries are constant, something else is wrong.

4) I measure the write path. Otherwise I’m guessing.

If you want sub-100ms UI, you can’t “feel” performance. You have to time it.

I added a timing wrapper around the batch insert. It prints duration and batch size.

This is how I caught a dumb mistake: I was inserting sets one-by-one again because my queue flush function split arrays wrong. Brutal.

// timedInsert.ts
import { insertSetsBatch } from './writeBatch';
import { retrySqliteBusy } from './retrySqliteBusy';

type SetRow = Parameters[0][number];

export async function insertSetsBatchTimed(rows: SetRow[]) {
  const t0 = global.performance?.now?.() ?? Date.now();

  await retrySqliteBusy(() => {
    insertSetsBatch(rows);
    return true;
  });

  const t1 = global.performance?.now?.() ?? Date.now();
  const ms = Math.round((t1 - t0) * 100) / 100;

  console.log(`[sqlite] inserted ${rows.length} sets in ${ms}ms`);
}

On my Pixel 7, a batch of 10 inserts is usually 2ms to 6ms. On an older Moto G (test device), it’s more like 8ms to 22ms.

More important: it doesn’t randomly fail anymore.

Results

Before WAL + batching, I could reproduce lock errors in under 60 seconds: start a workout, spam log 30 sets, then open history search in parallel. I’d see SQLITE_BUSY about 6 to 12 times per session.

After the changes, I did the same stress test for 20 minutes. I saw SQLITE_BUSY 2 times total, and both succeeded after 1 retry. No dropped set logs. No “tap again”.

Write timings also got predictable. My largest flush (48 set rows after an offline session) completed in 41ms on my slow Android test phone.

Key takeaways

• Turn on WAL early. Startup code. Not “later”.
• Use one transaction per flush. Not one insert per tap.
• Add busy_timeout, then still handle SQLITE_BUSY with retries.
• Time your writes and print batch size. You’ll catch dumb queue bugs fast.
• If you’re seeing constant retries, you’ve got a long-running query somewhere. Fix that next.

Closing

If you’re using Expo SQLite: are you relying only on busy_timeout, or do you also retry SQLITE_BUSY in code with backoff?