Live queries

A change subscription tells you which rows changed. A live query tells you the answer. db.live reads once, then maintains its rows from the change events that follow, and Sirannon reads a second time only in the three cases this page sets out. This page builds one file, live.ts, step by step, and 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, which covers every change from that position onward.

It returns a handle. That 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. A listener that renders from getState() needs nothing out of the update itself.

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.

Delivery follows the change feed's polling interval, and the timer behind it holds no process open by itself. Open a live query inside a service or a screen that stays up for as long as somebody reads 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))
orders.subscribe(() => {
  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)
  }
})
 
await db.execute('INSERT INTO orders (id, total, status) VALUES (?, ?, ?)', [4, 2400, 'pending'])
await db.execute('UPDATE orders SET status = ? WHERE id = ?', ['shipped', 1])

Each write produces one update, because Sirannon groups changes by the transaction that made them and reports each transaction once.

Every update includes a kind naming why the result moved:

Apply the splices yourself

Code that keeps its own copy of the result reads update.ops, where 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, which leaves a copy that starts empty one whole result behind for good.

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']
)
 
orders.subscribe(() => { 
const first = orders.getState()
const held: PendingOrder[] = first.status === 'ready' ? [...first.rows] : []
 
orders.subscribe((update) => {
  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)
    }
    console.log(JSON.stringify(held, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2))
    return
  }
 
  const state = orders.getState()
  if (state.status === 'ready') {
    console.log(`${state.rows.length} pending orders${state.revalidating ? ' (refreshing)' : ''}`) 
    held.splice(0, held.length, ...state.rows) 
  }
  if (state.status === 'error') {
    console.error(state.error.message)
  }
})
 
await db.execute('INSERT INTO orders (id, total, status) VALUES (?, ?, ?)', [4, 2400, 'pending'])
await db.execute('UPDATE orders SET status = ? WHERE id = ?', ['shipped', 1])

held matches the query's own rows after both writes, holding order 3 and order 4, because order 4 enters through an insert operation and order 1 leaves through a delete. The second branch covers a rows update, which replaces the whole list, and the code therefore refills held from getState().rows there. subscribe returns a function that 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, and 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 contains more changes than the result has rows, and re-reading then does less work 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 come back with the subscription reply, and no change falls between the two messages. The handle behaves as the local one does, with the same three states, the same update kinds, and the same close().

Three behaviours 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, which is how a client resumes.
• The client echoes the registry digest from its generated file. After REGISTRY_MISMATCH it re-reads /capabilities once and subscribes again, and it fails the query when the server refuses that second subscription too. That is how a rolling deploy reaches the client.

Render one in React

@delali/sirannon-db/react wraps a live query in useSyncExternalStore, which re-renders a component when the result changes and closes the query when that 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, which is why passing it straight to onSubmit causes no re-render of its own.

The hooks compare arguments by value, and an inline object such as { status: 'pending' } therefore 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 a visitor submits the form.