muya-sqlite

A tiny SQLite companion for muya — reactive, paginated, type-safe queries over a SQLite table that stays in sync as data changes.

• Push-driven: subscribes to mutations on the underlying table; views update automatically when anyone calls set/delete/batchSet.
• TanStack-style hook return: data, status, isLoading, isFetching, isStale, isError, error, hasNextPage, fetchNextPage, refetch.
• Optional global cache: opt in with cacheKey. Loaded data + the live subscription persist across mount/unmount cycles, so reopening a search screen is instant — and stays accurate (we keep listening for mutations even when no consumer is mounted).
• React 19 native: built on useSyncExternalStore, concurrent-safe, no QueryClientProvider required.
• Zero peer dep weight: only muya and react.

Install

bun add muya muya-sqlite
# or
npm i muya muya-sqlite

Peer deps: muya >= 2.5.8, react >= 18 < 20.

Quickstart

import { createSqliteState, useSqliteValue } from 'muya-sqlite'
import { bunMemoryBackend } from 'muya-sqlite/dist/esm/table/bun-backend'

interface Person {
  id: string
  name: string
  age: number
}

const people = createSqliteState<Person>({
  backend: bunMemoryBackend(),
  tableName: 'people',
  key: 'id',
  indexes: ['age'],
})

await people.batchSet([
  { id: '1', name: 'Alice', age: 30 },
  { id: '2', name: 'Bob', age: 25 },
])

function PeopleList() {
  const { data, status, hasNextPage, fetchNextPage } = useSqliteValue(people, {
    sortBy: 'age',
    pageSize: 50,
  })

  if (status === 'pending') return <p>Loading…</p>
  if (status === 'error') return <p>Failed to load</p>

  return (
    <>
      <ul>
        {data?.map((p) => (
          <li key={p.id}>{p.name} ({p.age})</li>
        ))}
      </ul>
      {hasNextPage && <button onClick={() => fetchNextPage()}>Load more</button>}
    </>
  )
}

When another part of the app calls people.set({...}) or people.delete(...), the rendered list updates automatically.

API

createSqliteState<Document>(options)

Returns a SyncTable<Document> — a reactive wrapper over the underlying SQLite table.

| Option | Type | Notes | |---|---|---| | backend | Backend \| Promise<Backend> | SQLite backend (e.g. bunMemoryBackend(), expoBackend(db)). | | tableName | string | Required. SQL table name. | | key | keyof Document \| string | Path to the primary key (supports dot-paths e.g. 'person.id'). | | indexes | Array<keyof Document \| string> | Optional. Fields to index. | | disablePragmaOptimization | boolean | Optional escape hatch. |

Returned SyncTable<Document> has:

subscribe(listener)            // listen for mutations
set(doc)                       // upsert
batchSet(docs[])               // upsert many
delete(key)                    // delete one
batchDelete(keys[])            // delete many
deleteBy(where)                // delete matching
clear()                        // remove all
get(key, selector?)            // fetch one
search(options)                // async iterator over the result set
count(options?)                // count matching rows
groupBy(field, options?)       // group + aggregate

useSqliteValue<Document, Selected = Document>(state, options?, deps?)

The reactive hook. Reads from the table, subscribes to mutations, paginates lazily.

const result = useSqliteValue(state, options, deps)

Options (all optional):

| Field | Type | Notes | |---|---|---| | where | Where<Document> | Filter clause (e.g. { age: { gt: 25 } }). | | sortBy | keyof Document \| string | Sort key. | | order | 'asc' \| 'desc' | Sort direction. Default 'asc'. | | limit | number | Max total rows. | | pageSize | number | Rows per fetchNextPage. Default 100. | | select | (doc: Document) => Selected | Projection. Hook will only re-render when the projected value changes (shallow). |

deps is a React-style dependency array. Changing any element re-runs the query from scratch.

Returns UseSqliteResult<Selected | Document>:

| Field | Type | Meaning | |---|---|---| | data | readonly T[] \| null | Current rows. null until first load completes; then a stable array reference that changes on each (re)load and on visible mutations. | | status | 'pending' \| 'success' \| 'error' | 'pending' until the first load finishes, then never 'pending' again. Use isFetching/isStale to detect refetches. | | isLoading | boolean | True only on the very first load (status === 'pending' && data === null). Use to gate full-page spinner / skeleton. | | isFetching | boolean | True during ANY in-flight IO (initial, fetchNextPage, refetch, deps-change refetch, mutation refill). | | isStale | boolean | True when data is present but a refresh is in flight (isFetching && data !== null). Use to dim the list. | | isError | boolean | Sugar for status === 'error'. | | error | Error \| null | The thrown error if any. | | hasNextPage | boolean | False once the iterator is exhausted (lookahead-accurate — flips on the same call that loaded the final page, not on a follow-up empty fetch). | | fetchNextPage | () => Promise<void> | Pull and append the next page. Concurrent calls serialize into a queue. | | refetch | () => Promise<void> | Discard current results and re-run from scratch. |

Loading-state matrix

Pick the right flag for each UX moment:

| Phase | data | status | isLoading | isFetching | isStale | |---|---|---|---|---|---| | Initial mount, never loaded | null | pending | true | true | false | | First load complete | […] | success | false | false | false | | Cache hit on remount (no refetch) | […] | success | false | false | false | | Refetching (deps / refetch / fetchNextPage) | […] | success | false | true | true | | Errored on first load | null | error | false | false | false | | Errored after had data | […] | error | false | false | false |

function MyList() {
  const r = useSqliteValue(state, { cacheKey: 'list' })

  if (r.isLoading) return <Spinner />              // first time only
  if (r.isError) return <Error error={r.error} onRetry={r.refetch} />

  return (
    <ul style={{ opacity: r.isStale ? 0.5 : 1 }}>  // dim while refreshing
      {r.data!.map((row) => <Row key={row.id} {...row} />)}
      {r.hasNextPage && (
        <button onClick={() => r.fetchNextPage()} disabled={r.isFetching}>
          Load more
        </button>
      )}
    </ul>
  )
}

useSqliteCount<Document>(state, options?, deps?)

Reactive row count. Returns number.

const total = useSqliteCount(people, { where: { age: { gte: 18 } } }, [])

options also accepts cacheKey and gcTime — same semantics as useSqliteValue (see Caching below).

clearSqliteCache(state?, cacheKey?)

Drop cached engines.

• clearSqliteCache(state, cacheKey) — drop one entry
• clearSqliteCache(state) — drop every entry for this state
• clearSqliteCache() — drop everything across all states

setSqliteCacheMaxEntries(limit)

Override the per-SyncTable LRU cap (default 100). Pass Infinity to disable LRU eviction.

Caching

By default the hook is per-instance: data is loaded on mount, kept on a ref, and discarded on unmount. Reopening the same screen reloads from scratch.

Set cacheKey to share data across mount/unmount cycles:

function SearchScreen() {
  const result = useSqliteValue(
    documents,
    {
      where: whereClause,
      sortBy: 'createdAt',
      pageSize: 50,
      cacheKey: 'search',          // ← opt in
      // gcTime: 10 * 60_000,      // optional, default 5 min
    },
    [whereClause],
  )
  // ...
}

What this gives you:

• Instant remount: closing the screen and reopening within gcTime (default 5 min) shows the same data immediately, no spinner.
• Always fresh: while no consumer is mounted, the cached engine keeps its state.subscribe listener running. Mutations from anywhere else in the app (other tabs, background jobs, batch imports) update the cached snapshot continuously. On remount you see the latest data, no refetch needed. This is the muya-sqlite advantage — TanStack and SWR are pull-driven, so they always refetch on remount; we don't have to.
• Bounded memory: LRU cap of 100 cached entries per SyncTable (configurable). Each cached entry self-disposes when its gcTime expires.

cacheKey semantics

cacheKey is the literal identity of the cache entry. Deps changes do NOT create new cache entries — they just trigger engine.refetch() on the same entry, while keeping the old data visible (isStale = true) until the new data lands.

// Stable cacheKey: ONE entry that follows the latest filter.
useSqliteValue(state, { cacheKey: 'search', where: whereClause }, [whereClause])
//   filter A → loads, cached at 'search'
//   filter B → SAME entry, refetches; old data stays visible during load
//   close + reopen → instant (cache hit on 'search', latest data)

// Per-deps cacheKey: ONE entry per unique filter (TanStack queryKey style).
useSqliteValue(state, { cacheKey: `search:${filter}`, where: whereClause }, [whereClause])
//   filter A → cached at 'search:A'
//   filter B → cached at 'search:B' (separate entry)
//   filter A again → instant cache hit on 'search:A'

Pick stable when you want "remember the latest". Pick per-deps when you want "instant switch between recent queries" — at the cost of more memory.

gcTime semantics

| Value | Behavior | |---|---| | 5 * 60_000 (default) | Keep alive 5 min after last unmount, then dispose | | 0 | Dispose immediately on last unmount (still cached during remount races) | | Infinity | Never auto-expire; clear manually with clearSqliteCache |

Object-identity caveat in deps

hashDeps keys object references by identity (WeakMap-backed). For your cache key to stay stable across renders, any object/array in your deps must come from a useMemo (or be a stable ref). If a parent rebuilds the array on every render, your cache hash changes every render and the cache misses.

Safest pattern for complex inputs: build the cacheKey from primitives directly, not from object refs.

const cacheKey = `search:${searchText}:${dateStartMs}:${dateEndMs}:${categories.join(',')}`
useSqliteValue(state, { cacheKey, where: whereClause }, [whereClause])

The cacheKey is now a content-based string; identity issues vanish.

Recipes

Filter from input — keep the UI responsive

Wrap the consumer's setState in startTransition so React keeps input snappy while the query reruns:

import { useState, useTransition } from 'react'

function SearchablePeople() {
  const [filter, setFilter] = useState('')
  const [, startTransition] = useTransition()

  const { data, isStale } = useSqliteValue(
    people,
    { where: { name: { like: `%${filter}%` } } },
    [filter],
  )

  return (
    <>
      <input
        onChange={(e) => startTransition(() => setFilter(e.target.value))}
      />
      <ul style={{ opacity: isStale ? 0.5 : 1 }}>
        {data?.map((p) => <li key={p.id}>{p.name}</li>)}
      </ul>
    </>
  )
}

Pagination with a transition

function Page() {
  const [, startTransition] = useTransition()
  const result = useSqliteValue(people, { pageSize: 50 })

  return (
    <button
      onClick={() =>
        startTransition(async () => {
          await result.fetchNextPage()
        })
      }
      disabled={!result.hasNextPage}
    >
      Load more
    </button>
  )
}

Project to a slice

const { data: names } = useSqliteValue(
  people,
  { select: (p) => p.name },
  [],
)
// names: readonly string[] | null

The hook only re-renders when the projected value differs (shallow comparison) — updates to other fields are silently ignored.

Listen for errors

const { isError, error, refetch } = useSqliteValue(people)

if (isError) {
  return (
    <div>
      <p>Failed: {error?.message}</p>
      <button onClick={() => refetch()}>Retry</button>
    </div>
  )
}

Performance notes

• For lists with more than ~1k visible rows, virtualize the renderer (e.g. @tanstack/react-virtual). The hook itself loads chunks lazily, but rendering N row components is the consumer's cost.
• For very large pageSize (>256), the iterator yields to the macro-task queue every 256 rows so the browser can paint and process input mid-load. Below 256 there is zero overhead.
• Inserts that change the visible window currently re-pull pages 1..N to maintain sort order. For heavy-write workloads, prefer smaller pageSize.

Why not TanStack Query?

useInfiniteQuery is pull/cache-driven (query → cache → maybe revalidate). muya-sqlite is push-driven — every mutation streams to subscribers as it happens, so the table itself is the truth. Trying to wrap useInfiniteQuery here would mean either a forced QueryClientProvider peer dep or hand-bridging mutations into a foreign cache. Same return shape, none of the weight. If you genuinely want both, build a thin adapter.

Why not useTransition inside the hook?

useTransition is a consumer concern — "this state change of mine is non-urgent." A library that publishes via useSyncExternalStore doesn't own a setter that would benefit from being marked non-urgent; it just notifies React when the store changes. Put the transition where the urgency decision lives: the consumer's setState. Our async actions (refetch, fetchNextPage) return Promise<void> so React 19's async transitions track them automatically.

License

MIT