Change data capture

Change data capture watches a table for INSERT, UPDATE, and DELETE events in real time. Sirannon installs SQLite triggers that record each change into a tracking table, then polls that table at a configurable interval, so capture works on every driver without native hooks. This page builds one file, watch.ts, step by step. You open a database, watch an orders table, subscribe to every change, then narrow the subscription with a filter. Each step highlights exactly what changed.

Open a database and watch a table

Start watch.ts with the driver, a Sirannon instance, and an open database. watch(table) installs the triggers and starts the polling loop for that table. Call it once per table before you subscribe.

import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('shop', './data/shop.db')
 
await db.watch('orders')

watch is idempotent per table: calling it again while the table is already watched reuses the existing triggers. 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 log what happened.

import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('shop', './data/shop.db')
 
await db.watch('orders')
 
const subscription = db 
  .on('orders')
  .subscribe((event) => {
    console.log(`Order ${event.row.id} was ${event.type}d`)
  })

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 monotonic sequence number you can use to order events.

Filter to the changes you care about

A handler that runs for every row often does unnecessary work. filter({ column: value }) narrows the subscription to rows matching the given column values, so the handler only runs for those changes. Add a filter so the subscription fires only when an order reaches the shipped status.

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

The filter matches on the current row, so an update that moves an order into shipped is delivered, and changes to orders in any other status are skipped before your handler runs.

Tune the polling pipeline

Capture latency and tracking-table growth are controlled by two DatabaseOptions fields on the open call:

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

const db = await sirannon.open('shop', './data/shop.db') 
const db = await sirannon.open('shop', './data/shop.db', { 
  cdcPollInterval: 20,
  cdcRetention: 15 * 60 * 1000,
})

A lower poll interval reduces delivery latency at the cost of more frequent polling queries. Retention bounds the tracking table's growth: a subscriber that falls further behind than the retention window misses the changes that have already aged out.

Table and column names pass a strict identifier allowlist before any trigger is installed, so CDC never becomes an injection path.

CDC over the network

The same subscriptions work remotely. The server dispatches change events to WebSocket subscribers in real time, and the client SDK restores subscriptions automatically after a reconnect. CDC activity is also observable through the onCDCEvent metrics callback.