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

# Change data capture

> Subscribe to row-level insert, update, and delete events as they commit, with filters and ordered sequence numbers.

URL: https://sirannon.sondelali.com/docs/change-data-capture
Section: Core features
Version: 0.3 (@delali/sirannon-db@0.3.0, latest)

Change data capture reports every INSERT, UPDATE, and DELETE on a table as it commits. Sirannon installs SQLite triggers that record each change into a tracking table, and it polls that table at an interval you set, which is why capture works on every driver, including the ones whose engine offers no change hook of its own. This page builds one file, `watch.ts`, step by step. Open a database, create an `orders` table, watch it, subscribe to every change, and then narrow the subscription with a filter. Each code block holds the whole file.

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 database and watch a table

Start `watch.ts` with the driver, a Sirannon instance, an open database, and the `orders` table itself. `watch(table)` installs the triggers and starts the polling loop for that table, which means the table has to exist first; watching a missing table raises `CDCError`. Call `watch` once per table before you subscribe.

```ts title="watch.ts"

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, customer TEXT NOT NULL, status TEXT NOT NULL)'
)

await db.watch('orders')
```

`watch` is idempotent per table, and calling it again while the table is already watched reuses the triggers already installed. When you no longer need events, `await db.unwatch('orders')` removes the triggers and stops polling.

## Subscribe to every change

With the table watched, `db.on(table).subscribe(handler)` delivers each committed change to your handler. The call returns a subscription whose `unsubscribe()` method stops delivery. Subscribe to every order change and write three statements against the table.

Sirannon delivers on the polling interval, and the timer behind it never holds a process open on its own. The handler therefore receives events for as long as your own service stays up, while a script that ends straight after these writes exits before the next poll.

```ts title="watch.ts"

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, customer TEXT NOT NULL, status TEXT NOT NULL)'
)

await db.watch('orders')
// [!code ++:11]

db.on('orders')
  .subscribe((event) => {
    console.log(`Order ${event.row.id}: ${event.type}`)
  })

await db.execute('INSERT INTO orders (customer, status) VALUES (?, ?)', ['Amara Okonkwo', 'pending'])
await db.execute('INSERT INTO orders (customer, status) VALUES (?, ?)', ['Lena Fischer', 'shipped'])
await db.execute('UPDATE orders SET status = ? WHERE customer = ?', ['shipped', 'Amara Okonkwo'])
```

The handler runs three times, once for each statement, in the order the statements commit.

| Statement | `event.type` | What the handler reads |
| --- | --- | --- |
| The insert for Amara Okonkwo | `insert` | order 1, with `status` as `pending` |
| The insert for Lena Fischer | `insert` | order 2, with `status` as `shipped` |
| The update to Amara Okonkwo's order | `update` | order 1 as it now reads, with `oldRow` holding the `pending` version |

Each event describes one row-level change:

- `event.type` is `'insert'`, `'update'`, or `'delete'`.
- `event.row` is the current row as a plain object.
- `event.oldRow` is the previous row, present for updates and deletes.
- `event.seq` is a <Tooltip tip="A monotonic sequence number only ever increases, so the event with the lower number committed first.">monotonic</Tooltip> sequence number you can use to order events.

## Filter to the changes you care about

A handler that runs for every row can do work you never needed. `filter({ column: value })` narrows the subscription to rows matching the given column values, and the handler therefore runs only for those changes. Add a filter so that the subscription fires only for orders in the `shipped` status.

```ts title="watch.ts"

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, customer TEXT NOT NULL, status TEXT NOT NULL)'
)

await db.watch('orders')

db.on('orders')
  .filter({ status: 'shipped' }) // [!code ++]
  .subscribe((event) => {
    console.log(`Order ${event.row.id}: ${event.type}`)
  })

await db.execute('INSERT INTO orders (customer, status) VALUES (?, ?)', ['Amara Okonkwo', 'pending'])
await db.execute('INSERT INTO orders (customer, status) VALUES (?, ?)', ['Lena Fischer', 'shipped'])
await db.execute('UPDATE orders SET status = ? WHERE customer = ?', ['shipped', 'Amara Okonkwo'])
```

A filtered subscription reports membership of the set the filter describes, and the type you receive therefore names the change in that membership. The handler receives order 2 as an insert, because the statement creates it as `shipped`. It receives order 1 as an insert too, because the update moves that row into the set, and its `oldRow` is `undefined` since no earlier event delivers it. A row that leaves the set reaches the handler as a delete whose `row` is empty, while an update between two rows that both match stays an update with both `row` and `oldRow`. The filter excludes order 1's first insert as `pending`, and the handler never runs for it.

## Tune the polling pipeline

Two `DatabaseOptions` fields on the `open` call set the capture latency and bound the growth of the tracking table:

- `cdcPollInterval` sets the polling interval in milliseconds and defaults to 50.
- `cdcRetention` sets how long captured changes stay in the tracking table and defaults to one hour.

Pass them when you open the database. Here the poll interval drops to 20 ms for lower delivery latency, and retention shortens to fifteen minutes.

```ts title="watch.ts"

const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('shop', './data/shop.db') // [!code --]
// [!code ++:4]
const db = await sirannon.open('shop', './data/shop.db', {
  cdcPollInterval: 20,
  cdcRetention: 15 * 60 * 1000,
})

await db.execute(
  'CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, status TEXT NOT NULL)'
)

await db.watch('orders')

db.on('orders')
  .filter({ status: 'shipped' })
  .subscribe((event) => {
    console.log(`Order ${event.row.id}: ${event.type}`)
  })

await db.execute('INSERT INTO orders (customer, status) VALUES (?, ?)', ['Amara Okonkwo', 'pending'])
await db.execute('INSERT INTO orders (customer, status) VALUES (?, ?)', ['Lena Fischer', 'shipped'])
await db.execute('UPDATE orders SET status = ? WHERE customer = ?', ['shipped', 'Amara Okonkwo'])
```

A lower poll interval reduces delivery latency, and it runs the polling query more frequently. Retention bounds the growth of the tracking table. A subscriber that falls further behind than the retention window therefore misses the changes already aged out.

Sirannon checks every table and column name against a strict identifier allowlist, and it installs a trigger only for a name on that list.

## Change data capture over the network

The same subscriptions work remotely. The [server](/docs/server) dispatches change events to WebSocket subscribers in real time, and the [client SDK](/docs/client-sdk) restores subscriptions automatically after a reconnect. The `onCDCEvent` [metrics callback](/docs/hooks-metrics-and-lifecycle) reports the same activity to your own monitoring.

## When you want the current answer

A subscription delivers each change and leaves you to work out what it means for the rows on screen. When what you keep is the result of a query, a [live query](/docs/live-queries) maintains that result from these same events, and you read the current rows straight from it.