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

# Registered operations

> Name every read and write the server may run, declare the arguments a caller may pass, fill the rest from the authenticated identity, and let clients invoke them by name, with no SQL on the wire.

URL: https://sirannon.sondelali.com/docs/0.3/registered-operations
Section: Networked access
Version: 0.3 (@delali/sirannon-db@0.3.4, supported)

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.

```ts title="operations.ts"
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](/docs/0.3/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.

```ts title="operations.ts"
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],
        }),
      },
    },
  // [!code --:2]
  },
}
    // [!code ++:11]
    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](/docs/0.3/server#choose-a-port-and-identify-the-caller) 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.

```ts title="operations.ts"
import type { OperationRegistry } from '@delali/sirannon-db'

export const operations: OperationRegistry = { // [!code --]
// [!code ++:5]
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],
        }),
      },
      // [!code ++:8]
      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'],
        // [!code --:2]
        statements: ({ total }) => [
          { sql: 'INSERT INTO orders (total, status) VALUES (?, ?)', params: [total, 'pending'] },
        // [!code ++:3]
        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.

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

```ts title="server.ts"
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.

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

```json result
{
  "pending": [
    {
      "id": 1,
      "total": 4999,
      "status": "pending"
    },
    {
      "id": 2,
      "total": 12500,
      "status": "pending"
    }
  ],
  "results": [
    {
      "changes": 1,
      "lastInsertRowId": 4
    },
    {
      "changes": 1,
      "lastInsertRowId": 4
    }
  ]
}
```

`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](/docs/0.3/security#authenticate-websocket-upgrades) covers.

Writing every reference by hand is repetitive work, so [code generation](/docs/0.3/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.

```text
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:

```json
{ "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](/docs/0.3/live-queries) 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.

```ts title="capabilities.ts"
const response = await fetch('http://localhost:9876/capabilities')

console.log(JSON.stringify(await response.json(), null, 2))
```

```json result
{
  "capabilities": [
    "query.named"
  ],
  "registry": {
    "digest": "40c2fd904f25bbc10e88bf0ad329c1a073b4201042b02399da7e7c8c4d12009a"
  }
}
```

`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](/docs/0.3/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](#serve-the-same-operations-on-every-database) 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](/docs/0.3/live-queries) 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.

```ts title="shared-operations.ts"
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`.

```ts title="customer-server.ts"
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.

```ts title="customer-client.ts"
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))
```

```json result
{
  "harbourFoods": [
    {
      "id": 1,
      "total": 18400
    }
  ],
  "lumenDental": [],
  "lumenIntoHarbour": "FORBIDDEN"
}
```

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

| Code | HTTP | When |
| --- | --- | --- |
| `UNKNOWN_QUERY` | 404 | No operation of that name is registered for that database or in `sharedOperations`. |
| `MISSING_ARGUMENT` | 400 | A declared argument was absent from the request. |
| `ARGUMENT_NOT_ALLOWED` | 400 | The caller supplied an undeclared argument, or one the server fills from identity. |
| `IDENTITY_REQUIRED` | 401 | An operation fills an argument from identity and the request supplies none. |
| `REGISTRY_MISMATCH` | 409 | A live query echoed a registry digest this server does not serve. |
| `SQL_NOT_ACCEPTED` | 403 | A statement reached a server that accepts no SQL over the network. |

The [error codes reference](/docs/0.3/error-codes) lists these alongside every other code the server can return.
