---
title: SQLite data boundary
description: One database handle, versioned migrations, typed queries, validated settings.
---

# SQLite data boundary

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:

```ts
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:

```ts
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:

```ts
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).
