Change data capture

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.

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.

watch.ts

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

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.

watch.ts

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.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')
  .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 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.

watch.ts

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

watch.ts

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') 
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.