Skip to content
Expo Development Foundation
Esc
↑↓navigate↵open⌘Jpreview
On this page

SQLite data boundary

One database handle, versioned migrations, typed queries, validated settings.

Local-first persistence behind a narrow boundary: components never import expo-sqlite directly. All three source apps converge on this shape in two delivery variants — pick one per app and stay consistent.

When to use

  • Relational local data (records with IDs, timestamps, relations) that must survive restarts and work offline.
  • Small preferences (theme mode, sort order) that deserve validation and defaults rather than raw string reads.

When not to use

  • Simple string/flag preferences with no validation needs — the showcase’s SQLite-backed localStorage idiom (/storage) is enough.
  • Synced or multi-device data — this pattern is local-only; sync and conflict policy are a separate pattern, not yet written.
  • Secrets or tokens — those belong in expo-secure-store (see expo-auth), never in SQLite or AsyncStorage.

File map

src/db/<store>.ts        # open singleton, versioned migrations, seed
src/db/queries/*.ts      # OR src/db/repository.ts — typed CRUD per entity
src/db/types.ts          # domain types (camelCase) + Row types (snake_case)
src/domain/*.ts          # pure logic: validation, dates, calcs (no RN imports)
src/state/*.tsx          # context/providers exposing { data, isLoading, error, refresh }

Two variants, same boundary:

  • Singleton (getDatabase()): one lazy openDatabaseAsync, PRAGMA foreign_keys = ON, pending migrations from a MIGRATIONS array, test seam resetting the singleton for :memory: tests.
  • Provider (SQLiteProvider + useSQLiteContext()): store providers own schema self-checks (SCHEMA_VERSION + column probe) and expose { data, isLoading, error, refresh, mutations } context values.

Key excerpts (adapted)

Singleton open with versioned migrations:

let dbPromise: Promise<SQLiteDatabase> | null = null;

export function getDatabase(): Promise<SQLiteDatabase> {
  if (!dbPromise) {
    dbPromise = (async () => {
      const db = await SQLite.openDatabaseAsync('app.db');
      await db.execAsync('PRAGMA foreign_keys = ON;');
      const { user_version } = await db.getFirstAsync<{ user_version: number }>(
        'PRAGMA user_version',
      );
      for (const sql of MIGRATIONS.slice(user_version)) {
        await db.execAsync(sql);
      }
      await db.execAsync(`PRAGMA user_version = ${MIGRATIONS.length}`);
      return db;
    })();
  }
  return dbPromise;
}

Queries take the handle first and map rows to domain types:

export async function listNotes(db: SQLiteDatabase): Promise<Note[]> {
  const rows = await db.getAllAsync<NoteRow>(
    'SELECT * FROM notes ORDER BY created_at DESC',
  );
  return rows.map(toNote); // snake_case columns -> camelCase domain type
}

Settings are validated key-value with defaults, never raw reads at call sites:

export async function getSortOrder(): Promise<SortOrder> {
  const raw = await getSetting('sort_order');
  return raw === 'oldest' ? 'oldest' : DEFAULT_SORT; // unknown -> default
}

Live demo

The showcase/ app implements this page in miniature: src/db/notes.ts (singleton, migrations, Row mappers, validated sort setting) behind the (patterns)/data-boundary route, which renders loading / error / empty / list states with refresh. Open it from the showcase index with Expo Go.

Anti-patterns

  • Importing expo-sqlite in a component or screen file.
  • CREATE TABLE without a version gate — second launch with a changed schema must migrate, never crash or silently recreate.
  • Reading settings as unvalidated strings (if (value === 'true') scattered across call sites); parse once behind a typed getter.
  • Storing tokens in SQLite/AsyncStorage — expo-secure-store only.
  • Business logic with React Native imports — keep domain/ pure so it stays testable without a runtime.

Provenance

Application labels are anonymized because the production source checkouts are private. They document observed SDK versions and file shapes, not publicly reproducible sources.

  • Production Expo 53 app A src/db/*: db.ts singleton, per-entity queries/*.ts, migrations.ts, storage/settings.ts.
  • Production Expo 57 app B src/db/* + src/domain/*: database.ts singleton with test seam, repository.ts with Row mappers, pure domain modules, state/data-context.tsx delivery.
  • Production Expo 57 app C src/data/*: provider variant — memory-store.tsx ({ memories, isLoading, error, refresh, mutations }, schema self-check), settings-store.tsx (typed settings with defaults + validation).

Was this page helpful?