Networked access

Value encoding over JSON

Send 64-bit integers and BLOBs over HTTP and WebSocket, and decode the tagged envelopes in any language without the client SDK.

Table of Contents

JSON cannot carry two SQLite value types without loss. Numbers pass through IEEE 754 doubles, so an integer beyond 9007199254740991 (2^53 - 1) in either direction gets rounded, and binary data has no JSON representation at all. Sirannon solves both with tagged envelopes: wherever a row or a bind parameter crosses the network, on HTTP and WebSocket alike, large integers and BLOBs are wrapped in a small tagged object. The client SDK encodes and decodes these automatically, so your code only ever handles bigint and Buffer. This page builds one file, values.ts, step by step. You store a 64-bit reference and a binary checksum through the client SDK, look at the raw JSON the server sends, then decode the envelopes in Python without the SDK.

Stand up a ledger

The envelopes only exist on the network, so the demonstration needs a running server. Open a ledger database, give it a transfers table with an INTEGER reference and a BLOB checksum, and serve it on the default port. Clearing the table first keeps reruns reproducible: every run of the finished file serves the same single row. Start values.ts here.

values.ts
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
import { createServer } from '@delali/sirannon-db/server'
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const ledger = await sirannon.open('ledger', './data/ledger.db')
 
await ledger.execute(
  'CREATE TABLE IF NOT EXISTS transfers (id INTEGER PRIMARY KEY, reference INTEGER NOT NULL, checksum BLOB NOT NULL, amount INTEGER NOT NULL)'
)
await ledger.execute('DELETE FROM transfers')
 
const server = createServer(sirannon, { port: 9876 })
await server.listen()

Write and read through the client SDK

Connect a client and insert a transfer whose reference is 9007199254740993, the smallest positive integer a double cannot hold. Pass the reference as a bigint and the checksum as a Buffer; the transport wraps both on the way out and unwraps the row on the way back.

values.ts
import { Sirannon } from '@delali/sirannon-db'
import { SirannonClient } from '@delali/sirannon-db/client'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
import { createServer } from '@delali/sirannon-db/server'
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const ledger = await sirannon.open('ledger', './data/ledger.db')
 
await ledger.execute(
  'CREATE TABLE IF NOT EXISTS transfers (id INTEGER PRIMARY KEY, reference INTEGER NOT NULL, checksum BLOB NOT NULL, amount INTEGER NOT NULL)'
)
await ledger.execute('DELETE FROM transfers')
 
const server = createServer(sirannon, { port: 9876 })
await server.listen()
 
const client = new SirannonClient('http://localhost:9876', { transport: 'http' }) 
const db = client.database('ledger')
 
await db.execute('INSERT INTO transfers (reference, checksum, amount) VALUES (?, ?, ?)', [ 
  9007199254740993n,
  Buffer.from([0xde, 0xad, 0xbe, 0xef]),
  250,
])
 
const rows = await db.query<{ reference: bigint; checksum: Buffer; amount: number }>( 
  'SELECT reference, checksum, amount FROM transfers'
)
console.log(rows[0])
{
  reference: 9007199254740993n,
  checksum: <Buffer de ad be ef>,
  amount: 250
}

The row comes back with native values: the reference as an exact bigint, the checksum as a Buffer, and the amount as a plain number, because integers inside the safe range are never wrapped. In the browser, where Buffer does not exist, blobs decode to Uint8Array, and both Buffer and Uint8Array work as bind parameters.

Look at the raw JSON

The SDK hides the envelopes, so fetch the same rows with plain fetch to see the exact JSON the server sends. The finished file keeps serving after it prints, and the decoders in the next section query it live; stop it with Ctrl+C when you are done.

values.ts
import { Sirannon } from '@delali/sirannon-db'
import { SirannonClient } from '@delali/sirannon-db/client'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
import { createServer } from '@delali/sirannon-db/server'
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const ledger = await sirannon.open('ledger', './data/ledger.db')
 
await ledger.execute(
  'CREATE TABLE IF NOT EXISTS transfers (id INTEGER PRIMARY KEY, reference INTEGER NOT NULL, checksum BLOB NOT NULL, amount INTEGER NOT NULL)'
)
await ledger.execute('DELETE FROM transfers')
 
const server = createServer(sirannon, { port: 9876 })
await server.listen()
 
const client = new SirannonClient('http://localhost:9876', { transport: 'http' })
const db = client.database('ledger')
 
await db.execute('INSERT INTO transfers (reference, checksum, amount) VALUES (?, ?, ?)', [
  9007199254740993n,
  Buffer.from([0xde, 0xad, 0xbe, 0xef]),
  250,
])
 
const rows = await db.query<{ reference: bigint; checksum: Buffer; amount: number }>(
  'SELECT reference, checksum, amount FROM transfers'
)
console.log(rows[0])
 
const response = await fetch('http://localhost:9876/db/ledger/query', { 
  method: 'POST',
  headers: { 'content-type': 'application/json' },
  body: JSON.stringify({ sql: 'SELECT reference, checksum, amount FROM transfers' }),
})
if (!response.ok) {
  throw new Error(`query failed with status ${response.status}`)
}
console.log(await response.text())
{"rows":[{"reference":{"__sirannon_int":"9007199254740993"},"checksum":{"__sirannon_blob":"DEADBEEF"},"amount":250}]}

The reference arrives as {"__sirannon_int":"9007199254740993"}, the checksum as {"__sirannon_blob":"DEADBEEF"}, and the amount as an ordinary JSON number.

The envelope rules

An envelope is a JSON object with exactly one key, either __sirannon_int or __sirannon_blob, whose value is a string. Envelopes only appear in positions where a column value is expected, and a stored TEXT value resembling one serialises as a JSON string, never as an object, so the two cannot collide.

  • An __sirannon_int payload is the exact decimal representation of the integer: an optional leading minus sign followed by one to nineteen digits, with no whitespace, plus sign, or exponent.
  • Only integers outside the safe range are wrapped. Values from -9007199254740991 to 9007199254740991 stay plain JSON numbers, and every other type keeps its natural JSON form.
  • An __sirannon_blob payload is uppercase hexadecimal with two digits per byte, and an empty string encodes an empty BLOB.
  • Envelopes appear in query result rows on both transports, in the row and oldRow fields of change events, in bind parameters (params, per-statement params entries, and paramsBatch), and in subscription filter values, where an integer envelope matches rows holding that exact 64-bit value.
  • The server rejects a malformed envelope in bind parameters instead of binding it. Over HTTP the response is status 400 with code INVALID_REQUEST, and over WebSocket it is an error with code INVALID_MESSAGE.

Decode without the SDK

In JavaScript the client SDK already decodes every envelope, so decoding by hand only comes up outside JavaScript, where a service reads the server's plain HTTP or WebSocket API directly. Any JSON library can handle the format: decode __sirannon_int into a 64-bit signed integer, which every SQLite integer fits, and decode __sirannon_blob into a byte array. In a language without a native 64-bit integer, use an arbitrary-precision type such as Python's int. With values.ts still serving, this Python decoder runs the same query over HTTP with only the standard library and decodes the row. There is no status check here because Python does not need one: urlopen raises HTTPError on any non-2xx response, so a failed query stops the script before it decodes anything.

decode_transfers.py
import json
from urllib import request
 
INT_TAG = '__sirannon_int'
BLOB_TAG = '__sirannon_blob'
 
 
def decode_value(value):
    if isinstance(value, dict) and len(value) == 1:
        if INT_TAG in value and isinstance(value[INT_TAG], str):
            return int(value[INT_TAG])
        if BLOB_TAG in value and isinstance(value[BLOB_TAG], str):
            return bytes.fromhex(value[BLOB_TAG])
    return value
 
 
query = request.Request(
    'http://localhost:9876/db/ledger/query',
    data=json.dumps({'sql': 'SELECT reference, checksum, amount FROM transfers'}).encode(),
    headers={'content-type': 'application/json'},
)
with request.urlopen(query) as response:
    payload = json.load(response)
 
rows = [{column: decode_value(value) for column, value in row.items()} for row in payload['rows']]
print(rows[0])
{'reference': 9007199254740993, 'checksum': b'\xde\xad\xbe\xef', 'amount': 250}

The same rules translate to any language with a 64-bit integer type. In Go the standard library is enough: net/http makes the call, strconv.ParseInt handles the integer envelope, and encoding/hex handles the blob.

decode_transfers.go
package main
 
import (
	"bytes"
	"encoding/hex"
	"encoding/json"
	"fmt"
	"net/http"
	"strconv"
)
 
const intTag = "__sirannon_int"
const blobTag = "__sirannon_blob"
 
func decodeValue(value any) any {
	envelope, ok := value.(map[string]any)
	if !ok || len(envelope) != 1 {
		return value
	}
	if payload, ok := envelope[intTag].(string); ok {
		if number, err := strconv.ParseInt(payload, 10, 64); err == nil {
			return number
		}
	}
	if payload, ok := envelope[blobTag].(string); ok {
		if blob, err := hex.DecodeString(payload); err == nil {
			return blob
		}
	}
	return value
}
 
func main() {
	body := []byte(`{"sql":"SELECT reference, checksum, amount FROM transfers"}`)
	response, err := http.Post("http://localhost:9876/db/ledger/query", "application/json", bytes.NewReader(body))
	if err != nil {
		panic(err)
	}
	defer response.Body.Close()
	if response.StatusCode > 299 {
		panic(fmt.Errorf("query failed with status %s", response.Status))
	}
 
	var payload struct {
		Rows []map[string]any `json:"rows"`
	}
	if err := json.NewDecoder(response.Body).Decode(&payload); err != nil {
		panic(err)
	}
 
	row := payload.Rows[0]
	for column, value := range row {
		row[column] = decodeValue(value)
	}
	fmt.Println(row["reference"], row["checksum"], row["amount"])
}
9007199254740993 [222 173 190 239] 250

The same two branches cover every payload the server produces, because the envelopes are the only non-standard JSON in the protocol. When you build bind parameters without the SDK, apply the rules in reverse: wrap any integer beyond the safe range and every BLOB, and send everything else as plain JSON.

Subscription resumption shows the change-event side of the WebSocket protocol, and integer fidelity covers how each embedded driver returns large integers.