Registered operations

A Sirannon server accepts no SQL from the network. Instead you register each statement it may run under a name, and a caller sends that name with its arguments. The server holds every statement, so a caller reaches only the tables and columns you registered, and a request that asks for anything else has no way to express it.

This page builds one file, operations.ts, step by step, then serves it and calls it. The registry is ordinary server-side code, keyed by database identifier, with a reads map and a writes map under each database.

Name a read

A read carries one statement. statement receives the resolved arguments and returns the SQL and its parameters, so the statement text is fixed in your source and only the parameters vary. Start operations.ts with a read that lists orders by status.

import type { OperationRegistry } from '@delali/sirannon-db'
 
export const operations: OperationRegistry = {
  orders: {
    reads: {
      ordersByStatus: {
        args: ['status'],
        columns: ['id', 'total', 'status'],
        statement: ({ status }) => ({
          sql: 'SELECT id, total, status FROM orders WHERE status = ? ORDER BY id',
          params: [status],
        }),
      },
    },
  },
}

args names every argument a caller may supply, and it is a closed list. A request that supplies an argument you did not declare fails with ARGUMENT_NOT_ALLOWED, and one that leaves a declared argument out fails with MISSING_ARGUMENT. Neither reaches SQLite.

columns names what each row carries. Code generation turns that list into a row type, so declaring it is what gives your client typed rows. A read that declares no columns takes them from its own statement, and only when it declares no arguments at all, because an argument can choose which columns the statement returns. Every other read leaves the row shape open.

Add a write

A write returns one statement or several, and the server runs all of them in one transaction. Add a write that records an order and bumps a counter, so a caller cannot get one without the other.

import type { OperationRegistry } from '@delali/sirannon-db'
 
export const operations: OperationRegistry = {
  orders: {
    reads: {
      ordersByStatus: {
        args: ['status'],
        columns: ['id', 'total', 'status'],
        statement: ({ status }) => ({
          sql: 'SELECT id, total, status FROM orders WHERE status = ? ORDER BY id',
          params: [status],
        }),
      },
    },
    writes: { 
      placeOrder: {
        args: ['total'],
        statements: ({ total }) => [
          { sql: 'INSERT INTO orders (total, status) VALUES (?, ?)', params: [total, 'pending'] },
          { sql: 'UPDATE counters SET orders = orders + 1' },
        ],
      },
    },
  },
}

A registered write replies with one result per statement, in the order the array gives them, so placeOrder above answers with two results. That differs from a statement sent over the wire, which answers with a single result.

Fill an argument from the caller's identity

fromIdentity maps an argument name to a field of the identity your authenticate hook returned, and the server fills that argument itself. This is where ownership and tenancy rules belong: the value never crosses the network, so no caller can choose it.

Scope the write to its owner, and add a read that only ever returns the caller's own orders.

import type { OperationRegistry } from '@delali/sirannon-db'
 
export interface Identity { 
  userId: string
}
 
export const operations: OperationRegistry = { 
export const operations: OperationRegistry<Identity> = { 
  orders: {
    reads: {
      ordersByStatus: {
        args: ['status'],
        columns: ['id', 'total', 'status'],
        statement: ({ status }) => ({
          sql: 'SELECT id, total, status FROM orders WHERE status = ? ORDER BY id',
          params: [status],
        }),
      },
      myOrders: { 
        fromIdentity: { ownerId: 'userId' },
        columns: ['id', 'total', 'status'],
        statement: ({ ownerId }) => ({
          sql: 'SELECT id, total, status FROM orders WHERE owner_id = ? ORDER BY id',
          params: [ownerId],
        }),
      },
    },
    writes: {
      placeOrder: {
        args: ['total'],
        fromIdentity: { ownerId: 'userId' }, 
        statements: ({ total }) => [ 
          { sql: 'INSERT INTO orders (total, status) VALUES (?, ?)', params: [total, 'pending'] },
          { sql: 'UPDATE counters SET orders = orders + 1' },
        ],
        statements: ({ total, ownerId }) => [ 
          { sql: 'INSERT INTO orders (owner_id, total, status) VALUES (?, ?, ?)', params: [ownerId, total, 'pending'] },
          { sql: 'UPDATE counters SET orders = orders + 1' },
        ],
      },
    },
  },
}

Typing the registry as OperationRegistry<Identity> checks each fromIdentity value against the fields of Identity, so a misspelt field name fails to compile rather than filling the argument with undefined at runtime.

Two refusals guard the rest:

• A request that supplies ownerId itself fails with ARGUMENT_NOT_ALLOWED. The server refuses rather than overwriting the value, so a caller learns the argument is not theirs to send.
• A request that carries no identity for a field an operation needs fails with IDENTITY_REQUIRED, which maps to HTTP 401.

myOrders declares no args at all, so the only value reaching its WHERE clause comes from the identity. That is the strongest shape a read can take, and it is worth preferring wherever a caller has no legitimate choice to make.

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

Serve the registry

Pass the registry to createServer and give it the hook that produces the identity. Nothing else turns operations on.

import { RequestDeniedError, Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
import { createServer } from '@delali/sirannon-db/server'
import { type Identity, operations } from './operations'
 
const SESSIONS = new Map<string, Identity>([['Bearer session-amara', { userId: 'u_amara' }]])
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('orders', './data/orders.db')
 
await db.execute(
  `CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY,
    owner_id TEXT NOT NULL,
    total INTEGER NOT NULL,
    status TEXT NOT NULL
  )`
)
await db.execute('CREATE TABLE IF NOT EXISTS counters (orders INTEGER NOT NULL)')
await db.execute('DELETE FROM orders')
await db.execute('DELETE FROM counters')
await db.execute('INSERT INTO counters (orders) VALUES (0)')
await db.executeBatch('INSERT INTO orders (id, owner_id, total, status) VALUES (?, ?, ?, ?)', [
  [1, 'u_amara', 4999, 'pending'],
  [2, 'u_lukas', 12500, 'pending'],
  [3, 'u_amara', 890, 'shipped'],
])
 
const server = createServer<Identity>(sirannon, {
  port: 9876,
  operations,
  authenticate: ({ headers }) => {
    const identity = headers.authorization === undefined ? undefined : SESSIONS.get(headers.authorization)
    if (!identity) throw new RequestDeniedError(401, 'UNAUTHORIZED', 'Invalid or missing token')
    return identity
  },
})
 
await server.listen()

The database key in the registry has to match the identifier you opened, orders in both places here. A name registered under a different database answers UNKNOWN_QUERY, because the lookup runs per database.

Call an operation by name

Both client transports carry named calls. operationRef builds a typed reference from a name, and passing one to query or execute sends the name and arguments rather than SQL. A plain string still means SQL, which this server refuses.

import { operationRef } from '@delali/sirannon-db'
import { SirannonClient } from '@delali/sirannon-db/client'
 
const ordersByStatus = operationRef<{ status: string }, { id: number; total: number; status: string }>('ordersByStatus')
const placeOrder = operationRef<{ total: number }>('placeOrder')
 
const client = new SirannonClient('http://localhost:9876', {
  transport: 'http',
  headers: { Authorization: 'Bearer session-amara' },
})
 
const db = client.database('orders')
 
const pending = await db.query(ordersByStatus, { status: 'pending' })
const results = await db.execute(placeOrder, { total: 4999 })
 
console.log(JSON.stringify({ pending, results }, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2))

pending is typed as the row you declared, and it carries both users' pending orders because ordersByStatus filters on status alone. results holds one entry per statement of the write: the insert that created order 4, and the counter update, which reports its own change count and repeats the row id the connection last inserted.

This client uses the HTTP transport. The same bearer header reaches the server on the WebSocket transport under Node, which attaches headers to the upgrade, while a browser attaches none. A browser socket therefore carries a short-lived ticket through webSocketProtocols, which the security guide covers.

Writing every reference by hand gets old quickly, so code generation emits them from the registry itself and keeps the types in step with the server.

Reach an operation over HTTP or WebSocket

Over HTTP, each kind of operation has one route, with :id and :name URL-encoded.

POST /db/:id/query/:name      { args?, readConcern? }    -> { rows }
POST /db/:id/execute/:name    { args?, writeConcern? }   -> { results }

Over WebSocket, a query or an execute message carrying name and args runs the registered operation instead of a statement:

{ "type": "query", "id": "1", "name": "ordersByStatus", "args": { "status": "pending" } }

The server resolves fromIdentity against the identity authenticate returned for the upgrade request, so one authenticated socket serves every later call on it. A subscribe message naming a registered read opens a live query over the same statement.

Neither form carries SQL, so acceptSql never governs them. It governs the five statement routes, and the query, execute, transaction, batch, and load messages when they carry statement text.

Announce what the server serves

GET /capabilities lists what a server supports and carries the registry digest. It skips the authenticate hook, so a client can read it before it holds a credential. Ask the server above what it serves.

const response = await fetch('http://localhost:9876/capabilities')
 
console.log(JSON.stringify(await response.json(), null, 2))

query.named and the registry object appear once you configure operations. query.sql joins the list once you set acceptSql: true, and the sync.* tokens describe device sync. The client SDK reads this answer once per server and caches it, so a statement sent to a server without query.sql fails with SQL_NOT_ACCEPTED before it leaves the process. The server refuses on its own too, because a hand-written client runs no such check.

The digest is a hash over every registered database identifier, operation kind, operation name, and argument name. It changes whenever the contract a client is generated from changes, and it stays the same when you only edit the SQL inside a statement, because a client never depends on that SQL.

That distinction matters during a rolling deploy. A live query echoes the digest its generated file carries when it subscribes, and a server serving a different one refuses with REGISTRY_MISMATCH. The client then re-reads /capabilities once and subscribes again, which succeeds as soon as it reaches a node running the new registry, and fails the query when that attempt is refused too. Adding an operation leaves existing clients working; renaming or removing one takes two deploys, the same as any other contract change.

What each refusal means

The error codes reference lists these alongside every other code the server can return.