> Complete content index: https://sirannon.sondelali.com/llms.txt

# Live queries

> Keep a query result current from change events, applying each change to the rows you already hold instead of running the read again, locally, over the network, and in React.

URL: https://sirannon.sondelali.com/docs/live-queries
Section: Core features
Version: 0.2 (@delali/sirannon-db@0.2.2, latest)

A [change subscription](/docs/change-data-capture) 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.

```bash
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.

```ts title="live.ts"

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))
```

```json result
{
  "status": "ready",
  "rows": [
    {
      "id": 1,
      "total": 4999
    },
    {
      "id": 3,
      "total": 890
    }
  ],
  "revalidating": false
}
```

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:

| Status | Shape | Meaning |
| --- | --- | --- |
| `pending` | `{ status: 'pending' }` | The first read has not finished. |
| `ready` | `{ status: 'ready', rows, revalidating }` | `rows` is the current answer. `revalidating` is `true` while a second read runs, and the rows stay readable throughout. |
| `error` | `{ status: 'error', error }` | The query failed, and `error` says why. |

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.

```ts title="live.ts"

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() // [!code --:2]
console.log(JSON.stringify(state, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2))
let updates = 0 // [!code ++:22]

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()
```

```txt result
3 pending orders
2 pending orders
```

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:

| Kind | Meaning |
| --- | --- |
| `ops` | The splices that produced the new rows, in order. One update per transaction. |
| `rows` | A second read replaced the rows. |
| `revalidating` | A second read is running, and the rows you hold are the last complete answer. |
| `error` | The query failed, and `getState()` carries the error. |

## 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.

```ts title="live.ts"

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() // [!code ++:2]
const held: PendingOrder[] = first.status === 'ready' ? [...first.rows] : []

const stop = orders.subscribe(() => { // [!code --]
const stop = orders.subscribe((update) => { // [!code ++:10]
  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++ // [!code --]
  const state = orders.getState()
  if (state.status === 'ready') { // [!code --:3]
    console.log(`${state.rows.length} pending orders${state.revalidating ? ' (refreshing)' : ''}`)
  }
  if (state.status === 'ready') { // [!code ++:3]
    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)) // [!code ++]
await orders.close()
```

```json result
[
  {
    "id": 3,
    "total": 890
  },
  {
    "id": 4,
    "total": 2400
  }
]
```

`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](/docs/change-data-capture) and recompute what you need.

## Run one over the network

A remote live query runs over a [registered read](/docs/registered-operations), 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.

```ts title="live-client.ts"

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](/docs/registered-operations#announce-what-the-server-serves) 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.

```tsx title="OrderList.tsx"

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](/docs/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.