React Native offline-first: 5s logging with SQLite

• Keep “5-second set logging” fast, offline.
• Use SQLite transactions + a tiny write queue.
• Optimistic UI with a rollback path.
• One schema trick that cut my writes in half.

Context

I’m building a fitness app. React Native + Expo. SQLite for local storage. Offline-first because gyms have garbage reception.

The core interaction is set logging. Tap weight, tap reps, hit save. Done. If that takes more than a couple seconds, people stop using it.

My first attempt was “just write to SQLite on every tap”. Brutal. UI jank. And I hit a fun Expo SQLite error mid-set: database is locked.

So I rebuilt the flow around one rule.

UI updates instantly. Writes happen in order. In a single transaction. Always.

1) I split “what the user sees” from “what I persist”

I keep a local in-memory row for the current set. That’s what the UI binds to.

Then I persist only on “Save set”. Not on every tap.

This sounds obvious. I still got it wrong at first.

I was persisting weight and reps separately. Two writes per set. Sometimes three. When you’re logging 18 sets, that’s a lot of churn.

So I made a single set_events row. One insert. Append-only.

-- schema.sql
-- One row per completed set. Append-only.
-- I don't update rows during a workout.

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS workouts (
  id TEXT PRIMARY KEY,
  started_at INTEGER NOT NULL,
  ended_at INTEGER
);

CREATE TABLE IF NOT EXISTS set_events (
  id TEXT PRIMARY KEY,
  workout_id TEXT NOT NULL,
  exercise_id TEXT NOT NULL,
  set_index INTEGER NOT NULL,
  weight_kg REAL NOT NULL,
  reps INTEGER NOT NULL,
  created_at INTEGER NOT NULL,
  dirty INTEGER NOT NULL DEFAULT 1,
  FOREIGN KEY (workout_id) REFERENCES workouts(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_set_events_workout
ON set_events(workout_id, created_at);

Append-only means fewer locks. Fewer “oops I updated the wrong row”. And sync later is simpler because every set is an event.

One thing that bit me — REAL for weight is fine, but don’t store 0.1 plates and expect perfect math. I store what the user typed. I don’t sum floats for anything critical.

2) I serialize writes. Because SQLite will.

Expo SQLite is fast. But it’s still SQLite. One writer at a time.

My bug was multiple async writes from different components.

• Set screen saving.
• Workout timer updating.
• Background sync marking rows clean.

Sometimes they overlap. Then SQLite throws database is locked.

So I added a tiny write queue. Single concurrency. No fancy library.

// db/writeQueue.ts
import * as SQLite from 'expo-sqlite';

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

let chain = Promise.resolve();

// Ensures all writes run sequentially.
export function enqueueWrite(fn: (db: SQLite.SQLiteDatabase) => Promise): Promise {
  const run = chain.then(() => fn(db));
  // Keep the chain alive even if a write fails.
  chain = run.then(() => undefined, () => undefined);
  return run;
}

export async function withTx(fn: (tx: SQLite.SQLiteDatabase) => Promise) {
  return enqueueWrite(async (db) => {
    await db.execAsync('BEGIN IMMEDIATE');
    try {
      const res = await fn(db);
      await db.execAsync('COMMIT');
      return res;
    } catch (e) {
      await db.execAsync('ROLLBACK');
      throw e;
    }
  });
}

BEGIN IMMEDIATE is intentional.

It grabs the write lock early. If it can’t, I’d rather fail fast than stall the UI for 900ms mid-set.

And yeah, I spent 4 hours “fixing” this by sprinkling await everywhere. Most of it was wrong.

3) I do optimistic UI. But I keep a rollback key.

If the UI waits for SQLite, it feels broken.

So when the user hits “Save set”, I immediately append a set to the UI list.

Then I persist in the background. If it fails, I remove that optimistic item and show an error.

I don’t love rollback UX. But it happens rarely, and it beats lag.

// features/sets/saveSet.ts
import { randomUUID } from 'expo-crypto';
import { withTx } from '../../db/writeQueue';

export type SetEvent = {
  id: string;
  workoutId: string;
  exerciseId: string;
  setIndex: number;
  weightKg: number;
  reps: number;
  createdAt: number;
  optimistic?: boolean;
};

export async function saveSetEvent(input: Omit) {
  const id = randomUUID();
  const createdAt = Date.now();

  await withTx(async (db) => {
    await db.runAsync(
      `INSERT INTO set_events
       (id, workout_id, exercise_id, set_index, weight_kg, reps, created_at, dirty)
       VALUES (?, ?, ?, ?, ?, ?, ?, 1)`,
      [id, input.workoutId, input.exerciseId, input.setIndex, input.weightKg, input.reps, createdAt]
    );
  });

  return { id, createdAt };
}

In the component, I keep two operations:

• addOptimisticSet(set)
• replaceOptimisticId(tempId, realId) or removeOptimistic(tempId)

I’m not pasting my whole Zustand store here. The pattern matters more than my state shape.

Also: don’t generate ids with Math.random().

I did. Sync conflicts were hilarious. Not in a good way.

4) I batch “workout screen” queries so the UI stays snappy

Another mistake.

I was querying the DB after every save to rebuild the whole list.

That’s safe. It’s also slow.

Now I do this:

• UI list updates from memory.
• DB query runs on screen focus, and on app resume.

And the query is one select. Not N selects.

// features/workouts/getWorkoutSets.ts
import * as SQLite from 'expo-sqlite';

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

export type DbSetRow = {
  id: string;
  exercise_id: string;
  set_index: number;
  weight_kg: number;
  reps: number;
  created_at: number;
};

export async function getWorkoutSets(workoutId: string) {
  // One query. Ordered for rendering.
  const rows = await db.getAllAsync(
    `SELECT id, exercise_id, set_index, weight_kg, reps, created_at
     FROM set_events
     WHERE workout_id = ?
     ORDER BY created_at ASC`,
    [workoutId]
  );

  return rows;
}

This keeps “Save set” lightweight.

The screen still stays correct because I refresh on focus/resume. And because the app is offline-first, I assume the DB is the source of truth eventually — just not for every single tap.

One more thing that saved me.

I stopped doing setState([...sets, newSet]) in a loop. I use functional updates. Otherwise React drops frames when the list is long.

Results

On my Android test device (Pixel 6a), I timed set logging with performance.now() around the Save press.

Before: median 620ms from tap to UI update, with random spikes around 1,900ms when the app was also syncing.

After the write queue + optimistic UI: median 38ms to UI update, and I couldn’t reproduce the spikes. The SQLite write still takes time (usually 12–40ms), but it’s off the critical path.

The database is locked error went from “every gym session” to 0 times in 9 sessions.

Key takeaways

• Don’t persist every tap. Persist completed actions.
• SQLite wants one writer. Give it one.
• Append-only tables make offline sync calmer.
• Optimistic UI is fine if you can rollback.
• Refresh from DB on focus/resume, not constantly.

Closing

If you’re doing offline-first in React Native: do you serialize SQLite writes with a queue like this, or do you rely on a library to manage write concurrency for you?