Skip to main content
Back to Blog
Technical10 min read

Building Offline-First React Native Apps with Expo SQLite

Offline-first means the app works without network connectivity and syncs when it comes back. This article walks through the approach Styrby uses: Expo SQLite for local persistence, a command queue for offline actions, and a sync protocol for reconciliation. The code examples are from our actual implementation.

Setting Up Expo SQLite

Expo SQLite ships with Expo SDK 54+. It provides synchronous and asynchronous APIs for SQLite operations. For offline-first apps, use the async API to avoid blocking the UI thread.

import * as SQLite from "expo-sqlite";

// Open (or create) the database
const db = await SQLite.openDatabaseAsync("styrby.db");

// Create tables on first launch
await db.execAsync(`
  CREATE TABLE IF NOT EXISTS sessions (
    id TEXT PRIMARY KEY,
    agent_type TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active',
    project TEXT,
    total_cost_usd REAL DEFAULT 0,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    synced_at TEXT
  );

  CREATE TABLE IF NOT EXISTS offline_queue (
    id TEXT PRIMARY KEY,
    operation TEXT NOT NULL,
    payload TEXT NOT NULL,
    created_at TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
  );

  CREATE INDEX IF NOT EXISTS idx_sessions_updated
    ON sessions(updated_at);

  CREATE INDEX IF NOT EXISTS idx_queue_status
    ON offline_queue(status);
`);

The Data Access Layer

Wrap SQLite operations in a repository pattern so the rest of the app does not interact with SQL directly:

interface Session {
  id: string;
  agentType: string;
  status: string;
  project: string | null;
  totalCostUsd: number;
  createdAt: string;
  updatedAt: string;
  syncedAt: string | null;
}

class SessionRepository {
  constructor(private db: SQLite.SQLiteDatabase) {}

  async getAll(): Promise<Session[]> {
    return this.db.getAllAsync<Session>(
      "SELECT * FROM sessions ORDER BY updated_at DESC"
    );
  }

  async getById(id: string): Promise<Session | null> {
    return this.db.getFirstAsync<Session>(
      "SELECT * FROM sessions WHERE id = ?",
      [id]
    );
  }

  async upsert(session: Session): Promise<void> {
    await this.db.runAsync(
      `INSERT INTO sessions (id, agent_type, status, project,
        total_cost_usd, created_at, updated_at, synced_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)
       ON CONFLICT(id) DO UPDATE SET
        status = excluded.status,
        total_cost_usd = excluded.total_cost_usd,
        updated_at = excluded.updated_at,
        synced_at = excluded.synced_at`,
      [
        session.id, session.agentType, session.status,
        session.project, session.totalCostUsd,
        session.createdAt, session.updatedAt, session.syncedAt,
      ]
    );
  }
}

The Offline Command Queue

When the user performs an action while offline, it goes into a queue instead of failing:

import { nanoid } from "nanoid";

class OfflineQueue {
  constructor(private db: SQLite.SQLiteDatabase) {}

  async enqueue(operation: string, payload: object): Promise<string> {
    const id = nanoid();
    await this.db.runAsync(
      `INSERT INTO offline_queue (id, operation, payload, created_at, status)
       VALUES (?, ?, ?, ?, 'pending')`,
      [id, operation, JSON.stringify(payload), new Date().toISOString()]
    );
    return id;
  }

  async getPending(): Promise<QueueItem[]> {
    return this.db.getAllAsync<QueueItem>(
      "SELECT * FROM offline_queue WHERE status = 'pending' ORDER BY created_at ASC"
    );
  }

  async markSynced(id: string): Promise<void> {
    await this.db.runAsync(
      "UPDATE offline_queue SET status = 'synced' WHERE id = ?",
      [id]
    );
  }

  async markFailed(id: string, reason: string): Promise<void> {
    await this.db.runAsync(
      "UPDATE offline_queue SET status = 'failed' WHERE id = ?",
      [id]
    );
  }

  async cleanup(): Promise<void> {
    // Remove synced items older than 7 days
    await this.db.runAsync(
      `DELETE FROM offline_queue
       WHERE status = 'synced'
       AND created_at < datetime('now', '-7 days')`
    );
  }
}

Usage from a UI action:

async function bookmarkSession(sessionId: string, label: string) {
  if (isOnline) {
    // Direct API call
    await api.bookmarkSession(sessionId, label);
  } else {
    // Queue for later
    await offlineQueue.enqueue("bookmark_session", { sessionId, label });
    // Update local state immediately for responsive UI
    await sessionRepo.updateBookmark(sessionId, label);
  }
}

Sync on Reconnect

When connectivity returns, the sync process runs in three phases:

import NetInfo from "@react-native-community/netinfo";

// Listen for connectivity changes
NetInfo.addEventListener((state) => {
  if (state.isConnected && !wasPreviouslyConnected) {
    syncManager.performSync();
  }
  wasPreviouslyConnected = state.isConnected ?? false;
});

class SyncManager {
  async performSync(): Promise<void> {
    // Phase 1: Drain the offline queue
    const pending = await offlineQueue.getPending();
    for (const item of pending) {
      try {
        await this.executeQueueItem(item);
        await offlineQueue.markSynced(item.id);
      } catch (error) {
        // Item-level failure does not block the queue
        await offlineQueue.markFailed(item.id, String(error));
      }
    }

    // Phase 2: Pull server updates
    const lastSync = await this.getLastSyncTimestamp();
    const updates = await api.getUpdatedSince(lastSync);

    for (const session of updates.sessions) {
      await sessionRepo.upsert({
        ...session,
        syncedAt: new Date().toISOString(),
      });
    }

    // Phase 3: Update sync timestamp
    await this.setLastSyncTimestamp(new Date().toISOString());

    // Cleanup old synced queue items
    await offlineQueue.cleanup();
  }

  private async executeQueueItem(item: QueueItem): Promise<void> {
    const payload = JSON.parse(item.payload);
    switch (item.operation) {
      case "bookmark_session":
        await api.bookmarkSession(payload.sessionId, payload.label);
        break;
      case "approve_permission":
        await api.approvePermission(payload.sessionId, payload.requestId);
        break;
      // ... other operations
    }
  }
}

Conflict Resolution

Styrby uses server-wins conflict resolution. When a local record and a server record for the same entity have different values, the server version replaces the local one. This works because:

  • Most data flows CLI to server to mobile. Conflicts are rare.
  • Configuration changes happen infrequently and typically from one device.
  • Implementing CRDTs or operational transforms adds complexity that is not justified by the frequency of conflicts in this use case.

Data Retention

The local database should not grow indefinitely. Styrby keeps 30 days of session metadata and 7 days of message content locally. Older data is fetched on demand from the server when online.

// Run periodically (e.g., on app launch)
async function pruneLocalData(db: SQLite.SQLiteDatabase): Promise<void> {
  await db.runAsync(
    "DELETE FROM session_messages WHERE created_at < datetime('now', '-7 days')"
  );
  await db.runAsync(
    "DELETE FROM sessions WHERE created_at < datetime('now', '-30 days')"
  );
}

Testing Offline Behavior

Test offline scenarios by mocking the network state:

  • Enqueue actions while "offline"
  • Verify local state updates immediately
  • Toggle to "online" and verify the queue drains
  • Introduce server errors and verify failed items are handled
  • Verify conflict resolution picks the server version

Expo's testing tools let you simulate network conditions. In development, airplane mode on a physical device gives the most realistic test environment.

Ready to manage your AI agents from one place?

Styrby gives you cost tracking, remote permissions, and session replay across five agents.

Start FreeView Pricing