Registered operations

A Sirannon server accepts no SQL from the network. 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.

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 names 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 the fields each row returns. 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 both statements commit together.

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. Set your ownership and tenancy rules here, because the value never crosses the network and 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 const operations: OperationRegistry = { 
export interface Identity {
  userId: string
}
 
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'],
        statements: ({ total }) => [
          { sql: 'INSERT INTO orders (total, status) VALUES (?, ?)', params: [total, 'pending'] },
        fromIdentity: { ownerId: 'userId' },
        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 the compiler rejects a misspelt field name before it can fill that argument with undefined at runtime.

Two refusals guard the rest:

• A request that supplies ownerId itself fails with ARGUMENT_NOT_ALLOWED. The server refuses the request, so a caller learns the argument is not theirs to send.
• A request that supplies 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-maria', { userId: 'u_maria' }]])
 
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_maria', 4999, 'pending'],
  [2, 'u_ravi', 12500, 'pending'],
  [3, 'u_maria', 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 support named calls. operationRef builds a typed reference from a name, and passing one to query or execute sends that name with its arguments. 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-maria' },
})
 
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 holds 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 sends a short-lived ticket through webSocketProtocols, which the security guide covers.

Writing every reference by hand is repetitive work, 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 that supplies name and args runs the registered operation:

{ "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 sends SQL, so acceptSql never governs them. It governs the five statement routes, the query, execute, transaction, batch, and load messages when they send statement text, and a subscribe message that names a table, unless the registry has an onBeforeSubscribe hook.

Announce what the server serves

GET /capabilities lists what a server supports and reports 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 or sharedOperations. query.sql joins the list once you set acceptSql: true, and the sync.* tokens join it once you set acceptDeviceSync: true for 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, including the arguments that fromIdentity fills. 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. The hash covers the shared operations as well, each under a kind of its own, so moving an operation into or out of sharedOperations changes the digest too.

That distinction matters during a rolling deploy. A live query echoes the digest from its generated file 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 it fails the query when the server refuses that subscription too. Adding an operation leaves existing clients working; renaming or removing one takes two deploys, the same as any other contract change.

Serve the same operations on every database

You key the registry by database identifier, which works while you know every database when you deploy. When each customer has a database of their own, that registry would need an entry for every customer, and a customer who signs up after you deploy would have none. sharedOperations holds one reads map and one writes map, and the server serves them on every database, including a database that the registry opens after the server starts. A new customer therefore needs no change to your code and no restart of the server.

shared-operations.ts holds the set. Its type is DatabaseOperations, because it describes the reads and writes of one database.

import type { DatabaseOperations } from '@delali/sirannon-db'
 
export interface Account {
  customer: string
}
 
export const sharedOperations: DatabaseOperations<Account> = {
  reads: {
    openInvoices: {
      columns: ['id', 'total'],
      statement: () => ({ sql: 'SELECT id, total FROM invoices WHERE paid = 0 ORDER BY id' }),
    },
  },
  writes: {
    raiseInvoice: {
      args: ['total'],
      statements: ({ total }) => ({ sql: 'INSERT INTO invoices (total, paid) VALUES (?, 0)', params: [total] }),
    },
  },
}

The server below opens no database when it starts. Its autoOpen resolver maps a customer's identifier to a file under data/customers, and the registry opens that file and applies the migrations list on the first request for that customer. The server executes a shared operation against the database named in the request path, so a token that authenticates one customer could otherwise reach every other customer's file. authenticate therefore compares databaseId with the customer on the token, and it refuses any other database with 403 FORBIDDEN.

import { mkdirSync, rmSync } from 'node:fs'
import { createTenantResolver, RequestDeniedError, Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
import { createServer } from '@delali/sirannon-db/server'
import { type Account, sharedOperations } from './shared-operations'
 
const SESSIONS = new Map<string, Account>([
  ['Bearer session-harbour-foods', { customer: 'harbour-foods' }],
  ['Bearer session-lumen-dental', { customer: 'lumen-dental' }],
])
 
rmSync('./data/customers', { recursive: true, force: true })
mkdirSync('./data/customers', { recursive: true })
 
const sirannon = new Sirannon({
  driver: betterSqlite3(),
  migrations: [
    {
      version: 1,
      name: 'create_invoices',
      up: 'CREATE TABLE invoices (id INTEGER PRIMARY KEY, total INTEGER NOT NULL, paid INTEGER NOT NULL)',
      down: 'DROP TABLE invoices',
    },
  ],
  lifecycle: {
    autoOpen: {
      resolver: createTenantResolver({ basePath: './data/customers' }),
    },
  },
})
 
const server = createServer<Account>(sirannon, {
  port: 9876,
  sharedOperations,
  authenticate: ({ headers, databaseId }) => {
    const account = headers.authorization === undefined ? undefined : SESSIONS.get(headers.authorization)
    if (!account) throw new RequestDeniedError(401, 'UNAUTHORIZED', 'Invalid or missing token')
    if (databaseId !== undefined && databaseId !== account.customer) {
      throw new RequestDeniedError(403, 'FORBIDDEN', "This token opens another customer's database")
    }
    return account
  },
})
 
await server.listen()

Start customer-server.ts, then run the client below. It raises an invoice for Harbour Foods and reads that customer's open invoices. It then reads Lumen Dental's, and last it requests the Harbour Foods invoices with Lumen Dental's token.

import { operationRef } from '@delali/sirannon-db'
import { RemoteError, SirannonClient } from '@delali/sirannon-db/client'
 
const openInvoices = operationRef<Record<string, never>, { id: number; total: number }>('openInvoices')
const raiseInvoice = operationRef<{ total: number }>('raiseInvoice')
 
const connect = (session: string): SirannonClient =>
  new SirannonClient('http://localhost:9876', {
    transport: 'http',
    headers: { Authorization: `Bearer ${session}` },
  })
 
const harbour = connect('session-harbour-foods')
await harbour.database('harbour-foods').execute(raiseInvoice, { total: 18400 })
const harbourFoods = await harbour.database('harbour-foods').query(openInvoices, {})
 
const lumen = connect('session-lumen-dental')
const lumenDental = await lumen.database('lumen-dental').query(openInvoices, {})
const lumenIntoHarbour = await lumen
  .database('harbour-foods')
  .query(openInvoices, {})
  .catch((error: unknown) => (error instanceof RemoteError ? error.code : error))
 
console.log(JSON.stringify({ harbourFoods, lumenDental, lumenIntoHarbour }, null, 2))

No file for Lumen Dental exists when the server starts. The registry creates one on the first request for that customer, so the read returns an empty list. Where operations also holds an entry of the same name for one database, the server looks in that database's own entry first and in sharedOperations second. An entry for one database can therefore add operations there, or replace a shared operation of the same name for that database alone.

What each refusal means

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