expo-sqlite

npx expo install expo-sqlite

// src/db/migrate.ts
import type { SQLiteDatabase } from "expo-sqlite";
 
const DATABASE_VERSION = 1;
 
export async function migrateDbIfNeeded(db: SQLiteDatabase) {
  const { user_version: current } = await db.getFirstAsync<{ user_version: number }>(
    "PRAGMA user_version"
  );
 
  if (current >= DATABASE_VERSION) return;
 
  if (current === 0) {
    await db.execAsync(`
      PRAGMA journal_mode = WAL;
      PRAGMA foreign_keys = ON;
      CREATE TABLE inspections (
        id TEXT PRIMARY KEY NOT NULL,
        title TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'draft',
        updated_at INTEGER NOT NULL
      );
    `);
  }
 
  await db.execAsync(`PRAGMA user_version = ${DATABASE_VERSION}`);
}

// app/_layout.tsx (excerpt)
import { SQLiteProvider } from "expo-sqlite";
import { migrateDbIfNeeded } from "@/db/migrate";
 
<SQLiteProvider databaseName="field.db" onInit={migrateDbIfNeeded}>
  <Stack />
</SQLiteProvider>

// src/db/inspections.ts
import type { SQLiteDatabase } from "expo-sqlite";
 
export type Inspection = {
  id: string;
  title: string;
  status: "draft" | "submitted";
  updated_at: number;
};
 
export async function listInspections(db: SQLiteDatabase): Promise<Inspection[]> {
  return db.getAllAsync<Inspection>(
    "SELECT id, title, status, updated_at FROM inspections ORDER BY updated_at DESC"
  );
}
 
export async function upsertInspection(db: SQLiteDatabase, row: Inspection) {
  await db.runAsync(
    `INSERT INTO inspections (id, title, status, updated_at)
     VALUES (?, ?, ?, ?)
     ON CONFLICT(id) DO UPDATE SET title = excluded.title, status = excluded.status, updated_at = excluded.updated_at`,
    row.id, row.title, row.status, row.updated_at
  );
}

npx expo install expo-sqlite

// app/_layout.tsx
import { Stack } from "expo-router";
import { SQLiteProvider, useSQLiteContext } from "expo-sqlite";
import { migrateDbIfNeeded } from "@/db/migrate";
 
export default function RootLayout() {
  return (
    <SQLiteProvider databaseName="field.db" onInit={migrateDbIfNeeded}>
      <Stack />
    </SQLiteProvider>
  );
}

// src/db/migrate.ts
import type { SQLiteDatabase } from "expo-sqlite";
 
const DATABASE_VERSION = 2;
 
export async function migrateDbIfNeeded(db: SQLiteDatabase) {
  let { user_version: version } = await db.getFirstAsync<{ user_version: number }>(
    "PRAGMA user_version"
  );
 
  if (version >= DATABASE_VERSION) return;
 
  if (version === 0) {
    await db.execAsync(`
      PRAGMA journal_mode = WAL;
      PRAGMA foreign_keys = ON;
      CREATE TABLE inspections (
        id TEXT PRIMARY KEY NOT NULL,
        title TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'draft',
        updated_at INTEGER NOT NULL
      );
      CREATE TABLE answers (
        id TEXT PRIMARY KEY NOT NULL,
        inspection_id TEXT NOT NULL REFERENCES inspections(id) ON DELETE CASCADE,
        prompt TEXT NOT NULL,
        value TEXT,
        updated_at INTEGER NOT NULL
      );
      CREATE INDEX idx_answers_inspection ON answers(inspection_id);
    `);
    version = 1;
    await db.execAsync("PRAGMA user_version = 1");
  }
 
  if (version === 1) {
    await db.execAsync(`
      ALTER TABLE inspections ADD COLUMN synced INTEGER NOT NULL DEFAULT 0;
    `);
    version = 2;
    await db.execAsync("PRAGMA user_version = 2");
  }
}

// src/db/inspections.ts - typed queries with tagged template API
import type { SQLiteDatabase } from "expo-sqlite";
 
export type Inspection = {
  id: string;
  title: string;
  status: string;
  synced: number;
  updated_at: number;
};
 
export async function listUnsynced(db: SQLiteDatabase) {
  const sql = db.sql;
  return sql<Inspection>`SELECT * FROM inspections WHERE synced = 0 ORDER BY updated_at ASC`;
}
 
export async function markSynced(db: SQLiteDatabase, id: string) {
  await db.runAsync("UPDATE inspections SET synced = 1 WHERE id = ?", id);
}

// app/(tabs)/inspections/index.tsx
import { useSQLiteContext } from "expo-sqlite";
import { useCallback, useState } from "react";
import { FlatList, Pressable, Text, View } from "react-native";
import { useFocusEffect } from "expo-router";
import { listUnsynced } from "@/db/inspections";
 
export default function InspectionsScreen() {
  const db = useSQLiteContext();
  const [rows, setRows] = useState<Awaited<ReturnType<typeof listUnsynced>>>([]);
 
  useFocusEffect(
    useCallback(() => {
      let cancelled = false;
      listUnsynced(db).then((data) => {
        if (!cancelled) setRows(data);
      });
      return () => { cancelled = true; };
    }, [db])
  );
 
  return (
    <View style={{ flex: 1, padding: 16 }}>
      <Text style={{ fontWeight: "600", marginBottom: 8 }}>
        Pending sync ({rows.length})
      </Text>
      <FlatList
        data={rows}
        keyExtractor={(item) => item.id}
        renderItem={({ item }) => (
          <Pressable style={{ paddingVertical: 8 }}>
            <Text>{item.title}</Text>
            <Text style={{ color: "#6b7280" }}>{item.status}</Text>
          </Pressable>
        )}
      />
    </View>
  );
}

// src/db/transactions.ts
import type { SQLiteDatabase } from "expo-sqlite";
 
export async function saveInspectionBundle(
  db: SQLiteDatabase,
  inspection: { id: string; title: string; updated_at: number },
  answers: { id: string; prompt: string; value: string }[]
) {
  await db.withExclusiveTransactionAsync(async () => {
    await db.runAsync(
      `INSERT INTO inspections (id, title, status, synced, updated_at)
       VALUES (?, ?, 'draft', 0, ?)
       ON CONFLICT(id) DO UPDATE SET title = excluded.title, updated_at = excluded.updated_at`,
      inspection.id, inspection.title, inspection.updated_at
    );
    for (const a of answers) {
      await db.runAsync(
        `INSERT INTO answers (id, inspection_id, prompt, value, updated_at)
         VALUES (?, ?, ?, ?, ?)
         ON CONFLICT(id) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at`,
        a.id, inspection.id, a.prompt, a.value, inspection.updated_at
      );
    }
  });
}

onInit (SQLiteProvider) or first openDatabaseAsync:
 
1. PRAGMA user_version → current
2. if current < TARGET:
     run SQL for current → current+1
     PRAGMA user_version = current+1
3. repeat until current === TARGET

// 1. Tagged template (auto-escape, inferred types)
const rows = await db.sql<Inspection>`SELECT * FROM inspections WHERE status = ${"draft"}`;
 
// 2. Prepared statement (reuse + escape)
const stmt = await db.prepareAsync("SELECT * FROM inspections WHERE id = ?");
try {
  const result = await stmt.executeAsync<Inspection>([id]);
  return result.getFirstAsync();
} finally {
  await stmt.finalizeAsync();
}
 
// 3. runAsync / getAllAsync with ? placeholders
await db.runAsync("DELETE FROM inspections WHERE id = ?", id);