Live queries

A change subscription tells you which rows changed. A live query tells you the answer. db.live runs the read once, then maintains its rows from the change events that follow, so the statement runs a second time only in the three cases this page sets out. It builds one file, live.ts, step by step, then covers the network form and the React hooks.

Sirannon writes the database file into a directory you create yourself, so make one before you run any of the code below.

mkdir -p data

Open a live query and read the result

live(sql, params?, options?) watches the statement's table, reads once, and subscribes from that read's position, so no change slips between the read and the subscription.

It returns a handle rather than an array of rows. The handle has three methods: getState() returns the current result, subscribe(listener) reports each change to it, and close() ends the query. Start live.ts with a seeded orders table, open a live query over the pending ones, and print what it holds.

import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
interface PendingOrder {
  id: number
  total: number
}
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('shop', './data/shop.db')
 
await db.execute(
  'CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, total INTEGER NOT NULL, status TEXT NOT NULL)'
)
await db.execute('DELETE FROM orders')
await db.executeBatch('INSERT INTO orders (id, total, status) VALUES (?, ?, ?)', [
  [1, 4999, 'pending'],
  [2, 12500, 'shipped'],
  [3, 890, 'pending'],
])
 
const orders = await db.live<PendingOrder>(
  'SELECT id, total FROM orders WHERE status = ? ORDER BY id',
  ['pending']
)
 
const state = orders.getState()
console.log(JSON.stringify(state, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2))

The shipped order never appears, because the live query holds the answer to the statement you gave it. getState() returns one of three shapes, and status tells you which:

Opening a live query needs write access, because watching a table installs triggers. live on a read-only database fails with READ_ONLY.

Listen for updates

Reading getState() once gives you the answer at that moment. subscribe(listener) calls your listener whenever the answer changes, and returns a function that stops the calls. Render from getState() inside the listener and you never have to look at what the update carries.

Replace the single read with a subscription, then make two writes: one new pending order, and one that moves an existing order out of the result. settle counts the updates the listener has seen and waits for both, so the example prints a settled result instead of racing it.

import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
interface PendingOrder {
  id: number
  total: number
}
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('shop', './data/shop.db')
 
await db.execute(
  'CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, total INTEGER NOT NULL, status TEXT NOT NULL)'
)
await db.execute('DELETE FROM orders')
await db.executeBatch('INSERT INTO orders (id, total, status) VALUES (?, ?, ?)', [
  [1, 4999, 'pending'],
  [2, 12500, 'shipped'],
  [3, 890, 'pending'],
])
 
const orders = await db.live<PendingOrder>(
  'SELECT id, total FROM orders WHERE status = ? ORDER BY id',
  ['pending']
)
 
const state = orders.getState() 
console.log(JSON.stringify(state, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2))
let updates = 0
 
const stop = orders.subscribe(() => {
  updates++
  const state = orders.getState()
  if (state.status === 'ready') {
    console.log(`${state.rows.length} pending orders${state.revalidating ? ' (refreshing)' : ''}`)
  }
  if (state.status === 'error') {
    console.error(state.error.message)
  }
})
 
const settle = async (count: number): Promise<void> => {
  while (updates < count) await new Promise((resolve) => setTimeout(resolve, 25))
}
 
await db.execute('INSERT INTO orders (id, total, status) VALUES (?, ?, ?)', [4, 2400, 'pending'])
await db.execute('UPDATE orders SET status = ? WHERE id = ?', ['shipped', 1])
await settle(2)
 
stop()
await orders.close()

Each write produced one update, because Sirannon groups changes by the transaction that made them and reports each transaction once. Every update carries a kind that says why the result moved:

Apply the splices yourself

Code that keeps its own copy of the result reads update.ops instead of the whole row list, so a change to one row moves one row. Each entry is { op: 'insert', index, row }, { op: 'update', index, row }, or { op: 'delete', index }, and applying them in order leaves you holding exactly what the query holds.

Seed that copy from the first read before you subscribe. The operations describe what changed after the subscription, so a copy that starts empty stays one whole result behind forever.

import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
interface PendingOrder {
  id: number
  total: number
}
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('shop', './data/shop.db')
 
await db.execute(
  'CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, total INTEGER NOT NULL, status TEXT NOT NULL)'
)
await db.execute('DELETE FROM orders')
await db.executeBatch('INSERT INTO orders (id, total, status) VALUES (?, ?, ?)', [
  [1, 4999, 'pending'],
  [2, 12500, 'shipped'],
  [3, 890, 'pending'],
])
 
const orders = await db.live<PendingOrder>(
  'SELECT id, total FROM orders WHERE status = ? ORDER BY id',
  ['pending']
)
 
let updates = 0
 
const first = orders.getState() 
const held: PendingOrder[] = first.status === 'ready' ? [...first.rows] : []
 
const stop = orders.subscribe(() => { 
const stop = orders.subscribe((update) => { 
  updates++
  if (update.kind === 'ops') {
    for (const op of update.ops) {
      if (op.op === 'insert') held.splice(op.index, 0, op.row)
      else if (op.op === 'update') held[op.index] = op.row
      else held.splice(op.index, 1)
    }
    return
  }
 
  updates++
  const state = orders.getState()
  if (state.status === 'ready') { 
    console.log(`${state.rows.length} pending orders${state.revalidating ? ' (refreshing)' : ''}`)
  }
  if (state.status === 'ready') { 
    held.splice(0, held.length, ...state.rows)
  }
  if (state.status === 'error') {
    console.error(state.error.message)
  }
})
 
const settle = async (count: number): Promise<void> => {
  while (updates < count) await new Promise((resolve) => setTimeout(resolve, 25))
}
 
await db.execute('INSERT INTO orders (id, total, status) VALUES (?, ?, ?)', [4, 2400, 'pending'])
await db.execute('UPDATE orders SET status = ? WHERE id = ?', ['shipped', 1])
await settle(2)
 
stop()
console.log(JSON.stringify(held, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2)) 
await orders.close()

held matches the query's own rows: order 4 arrived through an insert operation, and order 1 left through a delete. The second branch covers a rows update, which replaces the whole list rather than splicing it, so held is refilled from getState().rows there. stop() removes your listener, and close() ends the query and drops the temporary table it owns.

How a change becomes a splice

Each live query owns one temporary probe table whose columns match the declared types and collations of the base table. For every transaction, Sirannon writes the row before and after each change into that table, then runs the statement's own WHERE clause and select list over those rows. Affinity, collation, and ORDER BY therefore behave exactly as they do when you read the base table, so a changed row takes the position a fresh read would have given it. Closing the query drops that table.

Sirannon runs the read a second time in three cases:

• A transaction carries more changes than the result has rows, where re-reading costs less than probing each change.
• A LIMIT window loses a row and the held rows cannot say which row should take its place.
• Buffered changes for one transaction pass maxTransactionChanges, 10,000 by default, or an internal byte bound of 16 MB.

revalidating is true for the duration of that read. rereadJitterMs, 25 ms by default, bounds a random delay before the read starts, which keeps many live queries on one database from re-reading in lockstep. Both values are LiveQueryOptions, the third argument to live.

What a live query can maintain

A live query maintains the result of a single-table statement, because a splice has to name one row in one table. live fails with CDC_ERROR for a join, an aggregate, GROUP BY, HAVING, DISTINCT, a compound SELECT, a window function, a subquery, or LIMIT without ORDER BY, since a window with no order has no defined membership.

It fails for the same reason on a statement whose answer can change without a change event, such as one calling random(), changes(), or a clock function like datetime('now'). Those rows would go stale while the query still reported them as current.

For anything outside that set, subscribe to the table with change data capture and recompute what you need.

Run one over the network

A remote live query runs over a registered read, so no statement crosses the network. The server holds the result and sends the splices that maintain it, and the client applies them in order.

import { operationRef } from '@delali/sirannon-db'
import { SirannonClient } from '@delali/sirannon-db/client'
 
const ordersByStatus = operationRef<{ status: string }, { id: number; total: number }>('ordersByStatus')
 
const client = new SirannonClient('http://localhost:9876', { transport: 'websocket', autoReconnect: true })
const db = client.database('orders')
 
const orders = await db.live(ordersByStatus, { status: 'pending' })
 
orders.subscribe(() => {
  const state = orders.getState()
  if (state.status === 'ready') console.log(`${state.rows.length} pending orders`)
})

The rows arrive with the subscription reply rather than in a separate read, so no change falls between the two messages. The handle behaves as the local one does: the same three states, the same update kinds, the same close().

Three things differ over a network:

• A live query needs the WebSocket transport and fails with TRANSPORT_ERROR over HTTP.
• While the connection is down the query holds its rows and reports revalidating. The transport subscribes again on reconnection and the server sends the rows afresh, so a client resumes by subscribing rather than from a cursor.
• The client echoes the registry digest its generated file carries. After REGISTRY_MISMATCH it re-reads /capabilities once and subscribes again, and fails the query when that second attempt is refused too. That is how a rolling deploy reaches the client.

Render one in React

@delali/sirannon-db/react wraps a live query in useSyncExternalStore, so a component re-renders when the result changes and the query closes when the component unmounts.

import { useCommand, useLiveQuery } from '@delali/sirannon-db/react'
import type { RemoteDatabase } from '@delali/sirannon-db/client'
import { orders } from './generated/operations'
 
export const OrderList = ({ db }: { db: RemoteDatabase }) => {
  const pendingOrders = useLiveQuery(db, orders.reads.ordersByStatus, { status: 'pending' })
  const placeOrder = useCommand(db, orders.writes.placeOrder)
 
  if (pendingOrders.status === 'pending') return <Spinner />
  if (pendingOrders.status === 'error') return <ErrorPanel error={pendingOrders.error} />
 
  return (
    <>
      <OrderTable rows={pendingOrders.rows} stale={pendingOrders.revalidating} />
      <NewOrderForm onSubmit={placeOrder} />
    </>
  )
}

useLiveQuery returns the same three states the core API returns, which is what drives the three branches above. It takes the same rereadJitterMs and maxTransactionChanges options, plus enabled: false to hold a query closed until you need it. useCommand returns a callback that stays stable across renders and runs a registered write, so passing it straight to onSubmit causes no re-render of its own.

The hooks compare arguments by value, so an inline object such as { status: 'pending' } re-renders without reopening the query. orders.reads.ordersByStatus and orders.writes.placeOrder come from code generation, which is what makes pendingOrders.rows typed here.

Supply Spinner, ErrorPanel, OrderTable, and NewOrderForm from your own component set. NewOrderForm calls onSubmit({ total }) when the form is submitted.