React Native offline-first: instant search in SQLite

// db/schema.ts
import * as SQLite from "expo-sqlite";

export async function initDb(db: SQLite.SQLiteDatabase) {
  // Base table
  await db.execAsync(`
    PRAGMA journal_mode = WAL;

    CREATE TABLE IF NOT EXISTS exercises (
      id INTEGER PRIMARY KEY NOT NULL,
      name TEXT NOT NULL,
      primary_muscle TEXT NOT NULL,
      equipment TEXT NOT NULL
    );

    -- Full-text index. content=exercises lets us join back.
    CREATE VIRTUAL TABLE IF NOT EXISTS exercises_fts
    USING fts5(
      name,
      primary_muscle,
      equipment,
      content='exercises',
      content_rowid='id',
      tokenize='unicode61 remove_diacritics 2'
    );

    -- Keep FTS in sync.
    CREATE TRIGGER IF NOT EXISTS exercises_ai AFTER INSERT ON exercises BEGIN
      INSERT INTO exercises_fts(rowid, name, primary_muscle, equipment)
      VALUES (new.id, new.name, new.primary_muscle, new.equipment);
    END;

    CREATE TRIGGER IF NOT EXISTS exercises_ad AFTER DELETE ON exercises BEGIN
      INSERT INTO exercises_fts(exercises_fts, rowid, name, primary_muscle, equipment)
      VALUES ('delete', old.id, old.name, old.primary_muscle, old.equipment);
    END;

    CREATE TRIGGER IF NOT EXISTS exercises_au AFTER UPDATE ON exercises BEGIN
      INSERT INTO exercises_fts(exercises_fts, rowid, name, primary_muscle, equipment)
      VALUES ('delete', old.id, old.name, old.primary_muscle, old.equipment);
      INSERT INTO exercises_fts(rowid, name, primary_muscle, equipment)
      VALUES (new.id, new.name, new.primary_muscle, new.primary_muscle, new.equipment);
    END;
  `);
}

// db/search.ts
import * as SQLite from "expo-sqlite";

function ftsPrefixQuery(input: string) {
  // Keep letters/numbers/spaces. Drop punctuation that breaks FTS.
  const cleaned = input
    .toLowerCase()
    .replace(/[^a-z0-9\s]/g, " ")
    .trim();

  if (!cleaned) return "";

  // "bench press" -> "bench* press*"
  return cleaned
    .split(/\s+/)
    .filter(Boolean)
    .map((t) => `${t}*`)
    .join(" ");
}

export async function searchExercises(
  db: SQLite.SQLiteDatabase,
  input: string,
  limit = 30
) {
  const q = ftsPrefixQuery(input);
  if (!q) return [] as any[];

  // bm25() gives decent ranking for free.
  const rows = await db.getAllAsync(
    `
    SELECT e.id, e.name, e.primary_muscle, e.equipment,
           bm25(exercises_fts) AS rank
    FROM exercises_fts
    JOIN exercises e ON e.id = exercises_fts.rowid
    WHERE exercises_fts MATCH ?
    ORDER BY rank
    LIMIT ?;
    `,
    [q, limit]
  );

  return rows;
}

// hooks/useDebouncedValue.ts
import { useEffect, useState } from "react";

export function useDebouncedValue(value: T, delayMs: number) {
  const [debounced, setDebounced] = useState(value);

  useEffect(() => {
    const id = setTimeout(() => setDebounced(value), delayMs);
    return () => clearTimeout(id);
  }, [value, delayMs]);

  return debounced;
}

// screens/ExerciseSearchScreen.tsx
import React, { useEffect, useMemo, useState } from "react";
import { Text, TextInput, View, FlatList } from "react-native";
import * as SQLite from "expo-sqlite";
import { useDebouncedValue } from "../hooks/useDebouncedValue";
import { searchExercises } from "../db/search";

const db = SQLite.openDatabaseSync("gym.db");

export function ExerciseSearchScreen() {
  const [text, setText] = useState("");
  const debounced = useDebouncedValue(text, 120);
  const [rows, setRows] = useState([]);

  // Simple request id. Prevent stale async updates.
  const requestIdRef = React.useRef(0);

  useEffect(() => {
    let alive = true;
    const reqId = ++requestIdRef.current;

    (async () => {
      if (!debounced.trim()) {
        setRows([]);
        return;
      }
      const res = await searchExercises(db, debounced, 30);
      if (!alive) return;
      if (reqId !== requestIdRef.current) return;
      setRows(res);
    })();

    return () => {
      alive = false;
    };
  }, [debounced]);

  const renderItem = ({ item }: { item: any }) => (

      {item.name}

        {item.primary_muscle} • {item.equipment}


  );

  return (



       String(x.id)}
        renderItem={renderItem}
      />

  );
}

// ui/highlight.tsx
import React from "react";
import { Text } from "react-native";

export function HighlightedText({ text, query }: { text: string; query: string }) {
  const tokens = query
    .toLowerCase()
    .replace(/[^a-z0-9\s]/g, " ")
    .trim()
    .split(/\s+/)
    .filter(Boolean);

  if (!tokens.length) return {text};

  // Highlight only the first token prefix. Simple. Predictable.
  const t0 = tokens[0];
  const lower = text.toLowerCase();
  const idx = lower.indexOf(t0);

  if (idx === -1) return {text};

  return (

      {text.slice(0, idx)}
      {text.slice(idx, idx + t0.length)}
      {text.slice(idx + t0.length)}

  );
}