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

# Usage metering

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

URL: https://sirannon.sondelali.com/docs/usage-metering
Section: Core features
Version: 0.4 (@delali/sirannon-db@0.4.1, latest)

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:

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

```ts title="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 })
  }
}
```

```json result
[
  {
    "sql": "SELECT id FROM products WHERE id = ?",
    "rowsReturned": 1,
    "rowsRead": 1,
    "rowsWritten": 0
  },
  {
    "sql": "SELECT id FROM products WHERE price = ?",
    "rowsReturned": 1,
    "rowsRead": 3,
    "rowsWritten": 0
  }
]
```

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](https://github.com/assetcorp/sirannon-db/blob/v0.4.1/packages/spec/02-core.md#row-counts). 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](/docs/change-data-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](/docs/distributed-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](/docs/writer-worker#what-the-deadline-means) explains.
## Include live queries without counting replication twice

Every read made by a [live query](/docs/live-queries) 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](/docs/encryption) covers key storage and recovery.
