Core features

Usage metering

Measure the rows that SQLite reads and writes for each tenant, including failed statements and live-query reads.

Sirannon can report the rows that SQLite reads and writes for each statement. These counts describe database work, so a query that scans many rows and returns one row reports more work than a lookup by primary key.

Row counts require Sirannon 0.3.5 or later, the better-sqlite3 driver with rowCounts: true, and the matching @delali/sirannon-sqlite package. Other drivers omit the counts.

Enable counts on the driver

Install the native SQLite build beside Sirannon:

pnpm add -E @delali/sirannon-db @delali/sirannon-sqlite

onQueryComplete receives the database identifier with each statement's counts. Save the complete example below as meter-usage.ts. It creates a temporary database file, inserts a small product catalogue, and prints the metrics for a lookup and a scan.

meter-usage.ts
import { mkdtemp, rm } from 'node:fs/promises'
import { tmpdir } from 'node:os'
import { join } from 'node:path'
import { type QueryMetrics, Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
type UsageSample = Pick<QueryMetrics, 'sql' | 'rowsReturned' | 'rowsRead' | 'rowsWritten'>
 
const directory = await mkdtemp(join(tmpdir(), 'sirannon-usage-'))
const reports: QueryMetrics[] = []
const sirannon = new Sirannon({
  driver: betterSqlite3({ rowCounts: true }),
  metrics: { onQueryComplete: (report: QueryMetrics): void => { reports.push(report) } },
})
 
try {
  const db = await sirannon.open('catalogue', join(directory, 'catalogue.db'))
  await db.execute('CREATE TABLE products (id INTEGER PRIMARY KEY, price INTEGER NOT NULL)')
  await db.executeBatch('INSERT INTO products (id, price) VALUES (?, ?)', [
    [1, 1200],
    [2, 2400],
    [3, 3600],
  ])
 
  reports.length = 0
  await db.query('SELECT id FROM products WHERE id = ?', [2])
  await db.query('SELECT id FROM products WHERE price = ?', [2400])
 
  console.log(JSON.stringify(reports.map((report: QueryMetrics): UsageSample => ({
    sql: report.sql,
    rowsReturned: report.rowsReturned,
    rowsRead: report.rowsRead,
    rowsWritten: report.rowsWritten,
  })), (key: string, value: unknown): unknown =>
    typeof value === 'number' ? Number(value.toFixed(2)) : value, 2))
} finally {
  try {
    await sirannon.shutdown()
  } finally {
    await rm(directory, { recursive: true, force: true })
  }
}

The primary-key query can locate its row directly. The price query has no index on price, so SQLite examines the table. Both queries return one row, but rowsReturned alone cannot describe the difference in work.

If the native package is absent, incompatible, or cannot count rows, open fails with ROW_COUNTS_UNSUPPORTED. A host that enables metering therefore refuses to open a database through an engine that cannot measure it. sqliteBindingPath accepts a build compiled from the release's native/sqlite directory.

Keep missing counts separate from zero

rowsRead: 0 means that SQLite counted no reads. An absent rowsRead means that Sirannon has no count, either because the driver does not support it or because the worker ended before reporting its work.

Apply the same distinction to rowsWritten. Defaulting missing fields to zero can understate usage. Record an unmeasured statement separately, and choose a billing policy for that case before charging tenants.

Sirannon calls metrics callbacks synchronously and ignores their errors and returned promises. Keep the callback short, and send reports to a bounded collector whose failures your application monitors. An in-process array, as used in this example, illustrates the reports; it cannot retain a billing record across a process failure.

Understand what SQLite counts

SQLite counts visits to table and index rows, including scans and lookups, under the rules in the released core contract. Each inserted, updated, or deleted table row counts as a write, as does each inserted index entry. Counts remain exact up to Number.MAX_SAFE_INTEGER for one statement.

Counts include user triggers and foreign-key actions. Sirannon excludes the work inside its change-capture triggers, although SQLite can examine more rows when a table has triggers. Consequently, enabling change capture can increase reads for multi-row writes even when the capture bookkeeping itself contributes no count.

Sirannon measures each statement inside a transaction. For replication, the primary reports writes that it executes, including forwarded writes, while each replica reports its reads. Open the engine's separate writer through a driver with rowCounts: true too.

Account for failures and late completions

A failed statement can consume reads before SQLite rejects it. Sirannon includes those counts in its report with error: true.

After WRITER_WORKER_TIMEOUT, the writer can continue executing. Sirannon waits for its answer before reporting that statement's metrics, then sets error: true and includes the measured counts. The duration extends to that answer. If the worker crashes, exits, or closes first, Sirannon reports the statement without counts.

The caller's timeout and the metrics callback can therefore occur at different times. Reconcile a write before retrying it, as the writer-worker guide explains.

Include live queries without counting replication twice

Every read made by a live query reaches onQueryComplete with source: 'live', including its first query, later queries, and probes of changed rows. Other statements leave source absent.

Sirannon reports no row counts for change delivery, device sync, backups, or applying another node's replicated changes. HTTP and WebSocket query responses contain no usage counts; the server-side callback receives them.

To encrypt the same database, enable encryption: true alongside rowCounts: true and supply a key at open. Both capabilities use @delali/sirannon-sqlite; the encryption guide covers key storage and recovery.