SQLite with expo-sqlite

expo-sqlite 6,418 bundles its own SQLite 4,756 , the same version on every device. Wrap the app in <SQLiteProvider databaseName="booknest.db" onInit={migrate}> and any screen gets the open database from useSQLiteContext(). Schema changes go in onInit, versioned with SQLite's own user_version counter:

src/storage/db.ts: a versioned migration (excerpt)TypeScript

export async function migrate(db: SQLiteDatabase) {
  const { user_version: v } =
    (await db.getFirstAsync<{ user_version: number }>('PRAGMA user_version'))!;
  if (v < 1) {
    await db.execAsync(`
      PRAGMA journal_mode = WAL;
      CREATE TABLE notes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        book_id INTEGER NOT NULL,
        page INTEGER NOT NULL DEFAULT 0,
        body TEXT NOT NULL,
        created TEXT NOT NULL DEFAULT (datetime('now'))
      );
      CREATE INDEX notes_by_book ON notes (book_id);
      PRAGMA user_version = 1;`);
  }
  console.log('database at version', Math.max(v, 1));
}

A later release adds if (v < 2) { ... } below, so every install, however old, walks forward one step at a time. The reading-notes modal from Reading Notes Screen now saves and lists notes:

src/app/notes/[id].tsx: reading and writing notes (excerpt)JavaScript

  const load = useCallback(async () => {
    setNotes(await db.getAllAsync<Note>(
      'SELECT * FROM notes WHERE book_id = ? ORDER BY id DESC', bookId));
  }, [db, bookId]);
  const add = async () => {
    const r = await db.runAsync('INSERT INTO notes (book_id, body) VALUES (?, ?)',
      bookId, text.trim());                          // ? placeholders: never build SQL by hand
    console.log('note', r.lastInsertRowId, 'saved for book', bookId);

Output

 LOG  database at version 1
 LOG  note 1 saved for book 3

After a force-stop and relaunch, the note "The cardamom bread on page 88" was still listed under Salt and Saffron. WAL mode (hence the booknest.db-wal file beside the database) lets reads continue while a write is in progress. Use withTransactionAsync() to group writes, so a failure leaves nothing half-written.

MENU