How do I…?

Task-by-task recipes in every client language. Each snippet uses only APIs that exist in the shipped drivers — where a driver cannot do something, this page says so rather than showing code that will not compile.

The drivers live in drivers/; the Rust one is in-tree at crates/skaidb-driver. For the SQL itself see QUERY_SYNTAX.md.

Driver capability matrix

Read this first — the drivers differ a lot, and the differences decide which recipes below apply to you.

The rows that are green everywhere need no thought: all eight speak TLS, take a seed list, prepare statements server-side, and bind arrays and documents as typed values. What still differs, and may decide your choice:

• Mid-session recovery — a connection dying while in use — now works everywhere. The statement that hit the dead socket still fails: it may already have executed, and silently retrying an ambiguous write is worse than an error. The next statement re-dials (walking the seed list), re-authenticates, re-enters the session database, and re-prepares. Go differs only in mechanism: database/sql discards the connection via driver.Validator/driver.ErrBadConn and retries on a fresh one, and only when the request never reached the wire.
• Pooling ships in all eight. maxsize bounds the connections kept idle, not the number checked out: a burst creates extras and the surplus is closed on return. Go's is database/sql's own.
• No driver has a transaction API. Transactions are SQL statements you send on one connection; see below.
• Cancellation and timeouts: Go honours the context.Context given to QueryContext/ExecContext — a deadline becomes a socket deadline and a cancel unblocks the in-flight read, surfacing as context.DeadlineExceeded / context.Canceled. The interrupted connection is retired rather than reused, since its stream is mid-frame. Elsewhere use the driver's connect/read timeouts.
• CALL needs nothing from the driver. CREATE PROCEDURE and CALL are ordinary statements, and CALL p(?) binds through the same prepared path as any query, so the row is green everywhere by construction rather than by eight implementations. See below.
• Bulk insert in one round-trip is the one row still red: database/sql has no shape for it, so Go sends a row per call — use a multi-row VALUES statement instead.

How do I connect?

Every driver authenticates with SCRAM-SHA-256 automatically, including verifying the server's signature back. Omit user/password against a server with auth disabled. skaidb is leaderless, so any node will do.

import skaidb
conn = skaidb.connect(host="db1", port=7000, user="skaidb",
                      password="secret", database="app")

const { Client } = require('skaidb');
const client = new Client({ host: 'db1', port: 7000, user: 'skaidb', password: 'secret' });
await client.connect();

import (
    "database/sql"
    _ "skaidb.org/drivers/go"
)
db, err := sql.Open("skaidb", "skaidb://skaidb:secret@db1:7000/?consistency=quorum")
defer db.Close()

try (Skaidb.Connection conn = Skaidb.connect("skaidb://skaidb:secret@db1:7000/")) {
    ...
}

conn = Skaidb.connect(host: "db1", port: 7000, user: "skaidb", password: "secret")

$db = new Skaidb\Connection('db1', 7000, 'skaidb', 'secret');

using var conn = new SkaidbConnection("Host=db1;Port=7000;User=skaidb;Password=secret");
conn.Open();

use skaidb_driver::Client;
let mut client = Client::connect_with("db1:7000", "skaidb", "secret")?;

…over TLS?

All of them. The server name must match a SAN on the server's certificate — skaidb's own certs carry DNS:skaidb, which is the default, so it rarely matches the address you dialled.

conn = skaidb.connect(host="db1", port=7443, user="skaidb", password="secret",
                      tls=True, tls_ca="/etc/skaidb/skai-ca.crt",
                      tls_server_name="skaidb")

const client = new Client({
  host: 'db1', port: 7000, user: 'skaidb', password: 'secret',
  tlsCa: '/etc/skaidb/skai-ca.crt',        // or tlsInsecure: true for dev
});
await client.connect();

// TLS is configured in the DSN.
db, _ := sql.Open("skaidb",
    "skaidb://skaidb:secret@db1:7000/?tls_ca=/etc/skaidb/skai-ca.crt")
// dev only, encrypts but authenticates nothing:
//   "skaidb://skaidb:secret@db1:7000/?tls_insecure=true"

use skaidb_driver::{Client, TlsConfig, TlsVerify};
let tls = TlsConfig::new(TlsVerify::CaFile("/etc/skaidb/skai-ca.crt".into()), "skaidb")?;
let seeds = vec!["db1:7443".to_string(), "db2:7443".to_string()];
let mut client = Client::connect_many_tls(&seeds, "skaidb", "secret", Some(tls))?;

…with failover across several nodes?

All of them. Give the driver a seed list and it tries each until one both connects and authenticates — a node accepting TCP while unhealthy must not swallow the attempt. The order is shuffled per connect, so many clients spread across the cluster instead of stampeding the first entry. skaidb is leaderless, so there is no primary to find.

conn = skaidb.connect(seeds=["db1", "db2:7000", "db3"], database="app")

new Client({ seeds: ['db1:7000', 'db2:7000', 'db3:7000'], user, password });

sql.Open("skaidb", "skaidb://u:p@db1:7000,db2:7000,db3:7000/app")

Skaidb.connect("skaidb://u:p@db1:7000,db2:7000,db3:7000/app");

Skaidb.connect(seeds: ["db1:7000", "db2:7000"], user: "u", password: "p")

new Skaidb\Connection('db1', 7000, 'u', 'p', 'QUORUM', 10.0, null, false, null,
                      false, 'skaidb', ['db1:7000', 'db2:7000']);

new SkaidbConnection("User=u;Password=p;Seeds=db1:7000,db2:7000");

What happens mid-session. Every driver now recovers a connection that dies while in use: the statement that hit the dead socket fails, and the next one re-dials through the seed walk, re-authenticates, re-enters the session database and re-prepares. The failed statement is not retried for you, because it may already have executed. Go leans on database/sql: a broken socket is discarded through driver.Validator and its replacement is dialled through the seed walk, and the driver returns driver.ErrBadConn — which makes the pool retry the statement transparently — only when the request never reached the wire. If the request was sent and the reply was lost, the outcome is genuinely unknown, so the error surfaces instead: retrying could apply an UPDATE ... SET n = n + 1 twice. A primary-key insert is an upsert and so is always safe to repeat.

How do I choose a database?

Every driver selects it while connecting, which matters more than it looks: USE is per-connection session state, so with a pool (Go's database/sql opens connections behind your back) setting it once by hand leaves later connections in the default database. Connecting with it set runs USE on every dial, and a name that does not exist fails the connection instead of being ignored.

conn = skaidb.connect(host="db1", user="u", password="p", database="app")

const client = new Client({ host: 'db1', user: 'u', password: 'p', database: 'app' });

db, _ := sql.Open("skaidb", "skaidb://u:p@db1:7000/app")   // path = database

Skaidb.connect("skaidb://u:p@db1:7000/app");

Skaidb.connect(host: "db1", user: "u", password: "p", database: "app")

new Skaidb\Connection('db1', 7000, 'u', 'p', 'QUORUM', 10.0, 'app');

new SkaidbConnection("Host=db1;User=u;Password=p;Database=app");

Rust sends the statement itself, which is also the fallback anywhere:

USE app

Sent like any other statement, e.g. in Node:

await client.query('USE app');

You can also qualify names inline (app.users) and skip USE entirely.

How do I run a query with parameters?

Never concatenate untrusted input. Placeholders differ per driver — see the matrix. Every driver now sends parameters as typed values over the server's prepared-statement path, so full type fidelity (including arrays and documents) is preserved. Statement kinds the server declines to prepare — DDL and session statements — fall back to client-side quoting, where only scalars can be represented.

-- What every snippet below sends. `?` is the wire placeholder; Node.js and
-- Ruby write `$1` and rewrite it to this before sending.
SELECT id, name FROM users WHERE id = ?

# Rows are tuples.
cur = conn.cursor()
cur.execute("SELECT id, name FROM users WHERE id = ?", (1,))
print(cur.fetchone())          # (1, 'Ada')

// Rows are objects.
const res = await client.query('SELECT id, name FROM users WHERE id = $1', [1]);
console.log(res.rows[0].name);

rows, err := db.Query("SELECT id, name FROM users WHERE age > ?", 40)
defer rows.Close()
for rows.Next() {
    var id int
    var name string
    rows.Scan(&id, &name)
}

// Parameter indexes are 1-based.
Skaidb.ResultSet rs = conn.prepare("SELECT id, name FROM users WHERE age > ?")
                          .setInt(1, 40)
                          .executeQuery();
while (rs.next()) System.out.println(rs.getString("name"));

res = conn.exec_params("SELECT id, name FROM users WHERE id = $1", [1])
res.each { |row| puts row["name"] }

$stmt = $db->prepare('SELECT id, name FROM users WHERE id = ?');
$stmt->execute([1]);
foreach ($stmt->fetchAll() as $row) { echo $row['name']; }

using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT id, name FROM users WHERE id = ?";
cmd.Parameters.Add(1L);
using var reader = cmd.ExecuteReader();
while (reader.Read()) Console.WriteLine(reader.GetString(1));

use skaidb_proto::Response;
use skaidb_types::Value;
let mut sel = client.prepare("SELECT id, name FROM users WHERE id = ?")?;
if let Response::Rows { columns, rows } = client.execute_prepared(&mut sel, &[Value::Int(1)])? {
    for row in rows { println!("{} {}", row[0], row[1]); }
}

How do I insert, update and delete?

Same call shape as a query; the result carries an affected-row count instead of rows.

INSERT INTO users (id, name) VALUES (?, ?);
UPDATE users SET name = ? WHERE id = ?;
DELETE FROM users WHERE id = ?;

cur.execute("INSERT INTO users (id, name) VALUES (?, ?)", (1, "Ada"))
print(cur.rowcount)

const res = await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [1, 'Ada']);
console.log(res.rowCount);

res, _ := db.Exec("INSERT INTO users (id, name) VALUES (?, ?)", 1, "Ada")
n, _ := res.RowsAffected()          // LastInsertId() always errors — use INSERT … RETURNING (below)

long affected = conn.prepare("INSERT INTO users (id, name) VALUES (?, ?)")
                    .setInt(1, 1).setString(2, "Ada").executeUpdate();

n = conn.exec_params("INSERT INTO users (id, name) VALUES ($1, $2)", [1, "Ada"]).cmd_tuples

$ins = $db->prepare('INSERT INTO users (id, name) VALUES (?, ?)');
$ins->execute([1, "O'Brien"]);
echo $ins->rowCount();

cmd.CommandText = "INSERT INTO users (id, name) VALUES (?, ?)";
cmd.Parameters.Add(1L); cmd.Parameters.Add("Ada");
int affected = cmd.ExecuteNonQuery();

let mut ins = client.prepare("INSERT INTO users (id, name) VALUES (?, ?)")?;
client.execute_prepared(&mut ins, &[Value::Int(1), Value::String("Ada".into())])?;

Inserting a row whose primary key already exists overwrites it — that is the upsert contract, not an error.

…with a foreign key

Declare the relationship on the child table; the referenced columns must be the parent's primary key or one of its UNIQUE indexes:

CREATE TABLE users  (PRIMARY KEY (id));
CREATE TABLE orders (PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE);

An INSERT or UPDATE whose user_id has no matching user fails with a foreign key violation — a constraint-class error in every driver, the same class as a UNIQUE collision — and writes nothing. DELETE FROM users takes the orders with it here; with the default RESTRICT it would refuse while orders point at the user. A NULL or absent user_id is never checked. Add or remove the constraint later with ALTER TABLE orders ADD CONSTRAINT … FOREIGN KEY … / DROP CONSTRAINT …, and list them with SHOW FOREIGN KEYS ON orders. On a cluster the coordinator enforces the constraint wherever the rows live, and ADD CONSTRAINT checks every member's rows before the DDL lands anywhere — see CLUSTERING.md for the one race the two-step check-then- write leaves open.

…with NOT NULL, DEFAULT and CHECK

The table stays schema-less — a column item constrains a field, it does not type it (name TEXT NOT NULL is a parse error):

CREATE TABLE orders (PRIMARY KEY (id),
  user_id NOT NULL,
  qty NOT NULL DEFAULT 1 CHECK (qty > 0),
  created DEFAULT now(),
  CONSTRAINT total_matches CHECK (total IS NULL OR total >= qty));

INSERT INTO orders (id, user_id) VALUES (…) lands with qty = 1 and a per-row created; an INSERT that omits user_id, names qty as NULL, or gives qty = 0 fails with a not null violation / check violation (constraint-class errors, like a UNIQUE collision) and writes nothing. A CHECK whose expression is unknown — total absent here — passes. UPDATE is gated the same way; an upsert checks the row you proposed and then the merged one. Add or remove pieces later with ALTER TABLE orders ALTER COLUMN qty SET DEFAULT 2, … DROP NOT NULL, … ADD CONSTRAINT … CHECK (…) [NOT VALID] / DROP CONSTRAINT …; SET NOT NULL and a plain ADD CHECK scan the existing rows first (cluster-wide on a cluster) and refuse when any violates. SHOW CONSTRAINTS ON orders lists every kind in one view, and SHOW CREATE TABLE orders prints them back inline.

…with an auto-increment id

SERIAL (or GENERATED … AS IDENTITY) on a column gives it a sequence, and RETURNING hands the generated value back — the INSERT then answers with a result set, so read it through the driver's query path, not its execute/affected-count path:

CREATE TABLE users (PRIMARY KEY (id), id SERIAL);
INSERT INTO users (name) VALUES (?) RETURNING id;

cur.execute("INSERT INTO users (name) VALUES (?) RETURNING id", ("Ada",))
new_id = cur.fetchone()[0]

const res = await client.query('INSERT INTO users (name) VALUES ($1) RETURNING id', ['Ada']);
const newId = res.rows[0].id;

var newID int64
err := db.QueryRow("INSERT INTO users (name) VALUES (?) RETURNING id", "Ada").Scan(&newID)
// db.Exec would discard the returned row and LastInsertId() always errors

Skaidb.ResultSet rs = conn.prepare("INSERT INTO users (name) VALUES (?) RETURNING id")
                          .setString(1, "Ada").executeQuery();
rs.next(); long newId = rs.getLong("id");

new_id = conn.exec_params("INSERT INTO users (name) VALUES ($1) RETURNING id", ["Ada"])[0]["id"]

$ins = $db->prepare('INSERT INTO users (name) VALUES (?) RETURNING id');
$ins->execute(["Ada"]);
$newId = $ins->fetch()['id'];

cmd.CommandText = "INSERT INTO users (name) VALUES (?) RETURNING id";
cmd.Parameters.Add("Ada");
long newId = (long)cmd.ExecuteScalar();

let mut ins = client.prepare("INSERT INTO users (name) VALUES (?) RETURNING id")?;
if let Response::Rows { rows, .. } = client.execute_prepared(&mut ins, &[Value::String("Ada".into())])? {
    let new_id = &rows[0][0];
}

id SERIAL is id GENERATED BY DEFAULT AS IDENTITY: an INSERT that supplies its own id keeps it (the counter does not move), so bulk loads and migrations from another database just work. GENERATED ALWAYS AS IDENTITY refuses a supplied value unless the statement says INSERT INTO users (id, name) OVERRIDING SYSTEM VALUE VALUES (…). Options go in parentheses — id GENERATED ALWAYS AS IDENTITY (START WITH 1000 INCREMENT BY 10) — and RETURNING * returns the whole row as it landed, defaults included. On a cluster every coordinator hands out values from the same counter (one linearizable round per value), so ids are unique and dense cluster-wide; a failed insert still consumes its value, as in Postgres. Behind the column sits an ordinary sequence, users_id_seq, which SHOW SEQUENCES lists and nextval('users_id_seq') / setval(…) drive directly; CREATE SEQUENCE makes a free-standing one for anything else that needs a counter (ticket DEFAULT nextval('tickets')). The full grammar is in QUERY_SYNTAX.md.

…with a computed column

GENERATED ALWAYS AS (expr) STORED keeps a column in step with the rest of the row on every write — the server computes it, stores it, and refuses any attempt to set it directly:

CREATE TABLE orders (PRIMARY KEY (id),
  qty NOT NULL DEFAULT 1,
  price NOT NULL,
  total GENERATED ALWAYS AS (qty * price) STORED NOT NULL);
INSERT INTO orders (id, price) VALUES (1, 5) RETURNING total;   -- 5
UPDATE orders SET qty = 10 WHERE id = 1;                        -- total is now 50
CREATE INDEX orders_total ON orders (total);                    -- it is a stored field

The expression sees the row as it is about to land — defaults applied, UPDATE assignments made, the ON CONFLICT DO UPDATE merge done — and may read the row's other columns and nothing else (no now(), nextval, aggregates or other generated columns). INSERT … (total), UPDATE … SET total and ON CONFLICT DO UPDATE SET total are refused, NOT NULL and CHECK on the column judge the computed value, and a generated column cannot be the primary key or a distribution / partition column. To add one to a table that already has rows, ALTER TABLE orders ALTER COLUMN total ADD GENERATED ALWAYS AS (qty * price) STORED — every row is rewritten (cluster-wide, from the node you ran it on); … SET EXPRESSION AS (…) changes the formula the same way and … DROP EXPRESSION turns the column back into ordinary data, values kept. Drivers and ORMs must leave the column out of their insert/update sets and read it back like any other field.

How do I use a subquery?

IN (SELECT …), EXISTS (SELECT …) and a scalar (SELECT …) work anywhere an expression goes, in reads and in writes:

SELECT name FROM cust WHERE id IN (SELECT cust_id FROM orders WHERE total > 100);
DELETE FROM orders WHERE cust_id NOT IN (SELECT id FROM cust);
SELECT id, total, (SELECT avg(total) FROM orders) AS mean FROM orders;

Each subquery runs once, before the statement, and is folded into it as a literal list, boolean or value — so the statement that runs is an ordinary one and keeps its index scans and pushdown. Subqueries are uncorrelated: an inner column that names the outer query's alias (WHERE o.cust_id = c.id inside the subquery) is refused rather than misread, and a large membership set belongs in a JOIN. Drivers need nothing new; parameters inside a subquery bind like any other.

How do I keep a query as a view?

A view names a SELECT so the application (and its grants) can treat the result as a table:

CREATE VIEW open_orders AS
  SELECT id, cust, qty * price AS total FROM orders WHERE status = 'open';
SELECT total FROM open_orders WHERE id = 42;        -- a point lookup on orders
GRANT SELECT ON open_orders TO reporting;           -- the body runs with definer rights

A simple body like this is inlined into every query that names it, so point lookups, index scans and ORDER BY … LIMIT behave exactly as on the table. A grouped or joined body runs first and the query reads its rows. A view exposes only its projection (status above is not reachable through it), cannot be written through, and keeps the tables it names from being dropped or renamed. When the body is expensive, store its result instead and refresh it on a schedule:

CREATE MATERIALIZED VIEW spend WITH (refresh = '5m') AS
  SELECT cust, sum(qty * price) AS total FROM orders GROUP BY cust;
SELECT total FROM spend WHERE cust = 'ann';         -- reads the stored snapshot
REFRESH MATERIALIZED VIEW spend;                    -- or right now; affected = rows

Reads never block on a refresh (they see the union of the old and new rows while it runs), and SHOW VIEWS reports each view's kind, refresh schedule, last refresh and row count. Drivers need nothing new: a view is queried like a table, and REFRESH is an ordinary statement whose affected count is the new row count.

For a one-off query, a WITH clause is the same thing scoped to the statement:

WITH spend AS (SELECT cust, sum(qty * price) AS total FROM orders GROUP BY cust)
SELECT cust FROM spend WHERE total > 100 ORDER BY total DESC;

How do I insert many rows quickly?

Python and Rust ship the whole batch in one round-trip:

cur.executemany("INSERT INTO people (id, name, age) VALUES (?, ?, ?)",
                [(1, "Ada", 36), (2, "Linus", 54), (3, "Margaret", 80)])
print(cur.rowcount)             # total affected

let rows: Vec<Vec<Value>> = (0..1000i64)
    .map(|i| vec![Value::Int(i), Value::String(format!("v{i}"))]).collect();
let affected: u64 = client.execute_batch(&mut ins, rows)?;

Each row still autocommits individually: if one fails, the server names it and earlier rows stay applied, so make the statement idempotent.

await client.batch('INSERT INTO people (id, name) VALUES ($1, $2)', rows);

q.executeBatch(rows);          // List<Object[]>

conn.exec_batch("INSERT INTO people (id, name) VALUES ($1, $2)", rows)

$db->prepare('INSERT INTO people (id, name) VALUES (?, ?)')->executeBatch($rows);

cmd.ExecuteBatch(rows);        // IReadOnlyList<IReadOnlyList<object?>>

Go has no batch API, because database/sql has no shape for one. Use a multi-row VALUES statement, which is one round-trip and works everywhere:

INSERT INTO people (id, name) VALUES (1, 'Ada'), (2, 'Linus'), (3, 'Margaret')

Looping single inserts also works but pays a round-trip per row.

How do I choose a consistency level?

ONE, QUORUM (default) or ALL. Reads at ONE answer from one replica and may lag a beat; QUORUM is the safe default. See CLUSTERING.md.

Per connection, everywhere:

conn = skaidb.connect(..., consistency="ONE")

db, _ := sql.Open("skaidb", "skaidb://u:p@db1:7000/?consistency=one")   // DSN only

conn.setConsistency(Skaidb.CONSISTENCY_ONE);

Per statement, where supported:

-- The SQL form is per-CONNECTION, not per-statement: it changes the session
-- default until you change it back. Available everywhere, including Go and
-- Java, which have no per-statement API.
SET CONSISTENCY ONE

cur.set_consistency("ALL")                               # per cursor

await client.query({ text: 'SELECT ...', consistency: 'ALL' });

conn.exec_params("SELECT * FROM t WHERE id = $1", [1], consistency: :one)

$db->prepare('SELECT * FROM t WHERE id = ?')->setConsistency(Skaidb\Connection::ONE);

cmd.Consistency = SkaidbConsistency.One;

client.execute_with("SELECT * FROM t", Consistency::One)?;

Go and Java cannot vary it per statement. In Go open a second *sql.DB with a different DSN; in Java set it on the connection before the statement — but note that field is mutable and shared, so do not do it from two threads on one connection.

How do I page through a big table?

Use the keyset cursor: AFTER (<last-pk>) resumes the primary-key walk by stored key position, not by an SQL comparison. That matters on a schema-less table whose keys mix types, where WHERE id > ? silently skips rows whose key type differs from the cursor's. It needs bare columns, a single-column primary key, ORDER BY that key, and a LIMIT; it pages at any consistency and placement — a full-copy node at ONE walks its own shard, and every other cluster route serves each page as a bounded, LWW-merged distributed read seeded strictly after the cursor.

-- First page, then every page after it. The cursor value is the last row's
-- primary key from the previous page.
SELECT id, status FROM events ORDER BY id LIMIT 1000;
SELECT id, status FROM events ORDER BY id LIMIT 1000 AFTER (?);

last = None
while True:
    if last is None:
        cur.execute("SELECT id, status FROM events ORDER BY id LIMIT 1000")
    else:
        cur.execute("SELECT id, status FROM events ORDER BY id LIMIT 1000 AFTER (?)", (last,))
    rows = cur.fetchall()
    if not rows:
        break
    for r in rows:
        ...
    last = rows[-1][0]

The same loop in any other language is the same two statements. Server-side this is cheap: an eligible unbounded SELECT is executed page by page, so the node holds one page rather than the whole result.

Or stream, letting the server push chunks while you iterate. Every driver supports it, and it is the better tool for an export: you hold one chunk instead of the whole result.

for row in conn.stream("SELECT id, pad FROM big ORDER BY id"):
    ...

for await (const row of client.stream('SELECT id, pad FROM big ORDER BY id')) { }

rows, _ := db.QueryContext(skaidb.WithStreaming(ctx), "SELECT id, pad FROM big")

try (Skaidb.RowStream s = conn.stream("SELECT id, pad FROM big")) {
    while (s.next()) { /* s.getObject("pad") */ }
}

conn.stream("SELECT id, pad FROM big") { |row| }

foreach ($db->stream('SELECT id, pad FROM big') as $row) { }

foreach (var row in conn.Stream("SELECT id, pad FROM big")) { }

for row in client.query_stream("SELECT id, pad FROM big")? { let row = row?; }

Measured on 10 000 rows of ~900 B, peak memory during the read drops from 9–19 MB buffered to under 1 MB streamed, depending on the language.

Whether the server also avoids materialising depends on the statement. An eligible plain SELECT — bare columns or a lone SELECT *, a single-column primary key, a pageable filter, and either no ORDER BY or ORDER BY <pk> ASC (the order the pager already produces) — is executed page by page, so the node holds one page. Measured on a 150 MB result, the node grew 53 MB paging versus 323 MB buffering. Anything else (ORDER BY another column or DESC, GROUP BY, joins, LIMIT, DISTINCT) is built server-side first and merely delivered in chunks. The pager works at any consistency and placement — QUORUM (the default) included; only the lone-SELECT * form still needs consistency ONE on a full-copy node, because its up-front column discovery reads the local shard. skaidb_query_stream_total carries a path="keyset"|"buffered" label so you can tell which you got.

Three things to know. Streaming takes no parameters — the opcode carries SQL text — so bind values yourself or use a parameterless statement. The connection is busy until the stream ends, so no other statement may run on it meanwhile; abandoning one early drains the rest so the connection stays usable. And it reliably bounds the client: whether the server also avoids materialising depends on the statement being eligible for the keyset lane described above.

How do I run a transaction?

No driver exposes begin/commit/rollback; they are SQL statements, and which ones work depends on the deployment. Send them on one connection — with a pool, pin it first, or the statements may land on different sockets.

Against a standalone server:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                     -- or ROLLBACK

On a cluster plain BEGIN is refused — there is no distributed coordinator. The equivalent pins one partition of distribute_by tables and commits all-or-nothing through a partition-scoped consensus round:

BEGIN ATOMIC PARTITION (tenant_id = 't1');
INSERT INTO orders (tenant_id, id, total) VALUES ('t1', 9, 42);
UPDATE inventory SET qty = qty - 1 WHERE tenant_id = 't1' AND sku = 'abc';
COMMIT;

Every statement inside may touch only rows whose distribution columns equal the pinned literals. DDL, USE, time-series writes, and tables with UNIQUE or global indexes are refused inside it.

Opening a transaction needs the INSERT privilege on the session's current database — the same grant that lets you write there, so an app scoped to one database needs nothing extra. Each statement inside is still authorized against its own table.

Stop if BEGIN fails. Otherwise every following statement autocommits one by one — you get partial writes where you asked for all-or-nothing. That is the single trap worth guarding in code, and it is why each example below checks the BEGIN before continuing.

The other requirement is one connection for the whole transaction. With a pool, check one out and keep it for the duration; in Go you must pin it explicitly, because database/sql hands out a different socket per call.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = ?;
UPDATE accounts SET balance = balance + 100 WHERE id = ?;
COMMIT;                     -- or ROLLBACK to discard

cur = conn.cursor()                      # one connection, not a pool
try:
    cur.execute("BEGIN")                 # raises if refused — do not continue
except skaidb.Error:
    raise
try:
    cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = ?", (1,))
    cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = ?", (2,))
    cur.execute("COMMIT")
except Exception:
    cur.execute("ROLLBACK")
    raise

await client.query('BEGIN');             // throws if refused — do not continue
try {
  await client.query('UPDATE accounts SET balance = balance - 100 WHERE id = $1', [1]);
  await client.query('UPDATE accounts SET balance = balance + 100 WHERE id = $1', [2]);
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
}

c, err := db.Conn(ctx)                   // REQUIRED: pins one socket
if err != nil { return err }
defer c.Close()
if _, err := c.ExecContext(ctx, "BEGIN"); err != nil {
    return err                           // do NOT continue
}
if _, err := c.ExecContext(ctx, "UPDATE accounts SET balance = balance - 100 WHERE id = ?", 1); err != nil {
    c.ExecContext(ctx, "ROLLBACK")
    return err
}
_, err = c.ExecContext(ctx, "COMMIT")

conn.execute("BEGIN");                   // throws if refused — do not continue
try {
    conn.prepare("UPDATE accounts SET balance = balance - 100 WHERE id = ?")
        .setInt(1, 1).executeUpdate();
    conn.prepare("UPDATE accounts SET balance = balance + 100 WHERE id = ?")
        .setInt(1, 2).executeUpdate();
    conn.execute("COMMIT");
} catch (Skaidb.SkaidbException e) {
    conn.execute("ROLLBACK");
    throw e;
}

conn.exec("BEGIN")                       # raises if refused — do not continue
begin
  conn.exec_params("UPDATE accounts SET balance = balance - 100 WHERE id = $1", [1])
  conn.exec_params("UPDATE accounts SET balance = balance + 100 WHERE id = $1", [2])
  conn.exec("COMMIT")
rescue StandardError
  conn.exec("ROLLBACK")
  raise
end

$db->exec('BEGIN');                      // throws if refused — do not continue
try {
    $s = $db->prepare('UPDATE accounts SET balance = balance - 100 WHERE id = ?');
    $s->execute([1]);
    $s = $db->prepare('UPDATE accounts SET balance = balance + 100 WHERE id = ?');
    $s->execute([2]);
    $db->exec('COMMIT');
} catch (\Throwable $e) {
    $db->exec('ROLLBACK');
    throw $e;
}

using (var b = conn.CreateCommand()) { b.CommandText = "BEGIN"; b.ExecuteNonQuery(); }
try {
    using (var u = conn.CreateCommand()) {
        u.CommandText = "UPDATE accounts SET balance = balance - 100 WHERE id = ?";
        u.Parameters.Add(1); u.ExecuteNonQuery();
    }
    using (var c = conn.CreateCommand()) { c.CommandText = "COMMIT"; c.ExecuteNonQuery(); }
} catch (SkaidbException) {
    using var r = conn.CreateCommand(); r.CommandText = "ROLLBACK"; r.ExecuteNonQuery();
    throw;
}

client.execute("BEGIN")?;                // `?` stops here if it is refused
let out = (|| -> Result<(), DriverError> {
    client.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")?;
    client.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")?;
    client.execute("COMMIT")?;
    Ok(())
})();
if out.is_err() {
    client.execute("ROLLBACK")?;
}
out?;

On a cluster the only change is the opening statement — BEGIN ATOMIC PARTITION (tenant_id = ?) instead of BEGIN; everything else is identical.

Python's conn.commit() is an accepted no-op and conn.rollback() raises — use the SQL statements, not the DB-API methods. Everything outside a transaction autocommits, and REST, the ES gateway and the UI always autocommit.

How do I handle errors and reconnect?

Error surfaces vary from a five-class hierarchy to bare strings. Python, Ruby and Rust let you branch on the cause; Java, PHP and .NET raise exactly one type, so distinguishing causes means reading the message; Go returns plain fmt.Errorf strings prefixed skaidb:.

try:
    cur.execute("SELECT * FROM missing")
except skaidb.ProgrammingError as e:   # server rejected the statement
    ...
except skaidb.OperationalError as e:   # connect/transport failure
    ...

const { SkaidbError } = require('skaidb');
try { await client.query('SELECT 1'); }
catch (e) {
  if (e instanceof SkaidbError) { /* server or protocol error */ }
  else throw e;                 // a mid-query socket error arrives as a plain Error
}

rows, err := db.Query("SELECT * FROM missing")
if err != nil {
    if errors.Is(err, sql.ErrNoRows) { /* stdlib case */ }
    // everything else is a message: strings.Contains(err.Error(), "does not exist")
    return err
}
defer rows.Close()

try {
    conn.query("SELECT * FROM missing");
} catch (Skaidb.SkaidbException e) {
    // one type for every cause — read the message to tell them apart
    System.err.println(e.getMessage());
}

begin
  conn.exec("SELECT * FROM missing")
rescue Skaidb::QueryError => e        # server rejected the statement
  warn e.message
rescue Skaidb::ConnectionError => e   # connect/transport failure
  warn e.message
end

try {
    $db->exec('SELECT * FROM missing');
} catch (\Skaidb\SkaidbException $e) {
    // one type for every cause — read the message to tell them apart
    error_log($e->getMessage());
}

try {
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM missing";
    using var r = cmd.ExecuteReader();
} catch (SkaidbException e) {
    // one type for every cause — read the message to tell them apart
    Console.Error.WriteLine(e.Message);
}

match client.execute("SELECT * FROM nope") {
    Err(DriverError::Server(msg))   => eprintln!("statement failed: {msg}"),
    Err(DriverError::NoEndpoint(m)) => eprintln!("all nodes down: {m}"),
    Err(DriverError::Auth(m))       => eprintln!("auth: {m}"),
    Err(e)                          => eprintln!("{e}"),
    Ok(resp) => { /* ... */ }
}

Every driver recovers a broken connection itself. The statement that hit the dead socket still fails — do not assume otherwise — and the next statement on that connection re-dials through the seed list, re-authenticates, re-enters the session database, and re-prepares. So the normal shape is: catch the error, log it, and carry on; the following call works.

In Go the recovery is database/sql's: a dead socket is discarded through driver.Validator and its replacement dialled from the seed list, and the statement is retried transparently — but only when the driver can prove the request never reached the server. If the request went out and the reply was lost, you get an error saying the statement may or may not have applied, because retrying blindly could apply it twice. The other drivers make the same distinction by never retrying the failed statement themselves.

One consequence worth knowing: a reconnect clears the connection's prepared-statement cache, because a statement id is only valid on the connection that created it. That is handled for you, but it means the first statement after a recovery pays one extra round-trip to re-prepare.

So the retry shape is the same everywhere — try once, and on a transport error try again on the same connection, which will have re-dialled:

for attempt in (1, 2):
    try:
        cur.execute("SELECT count(*) FROM events")
        break
    except skaidb.OperationalError:
        if attempt == 2:
            raise                  # still down after a re-dial

for (const attempt of [1, 2]) {
  try { await client.query('SELECT count(*) FROM events'); break; }
  catch (e) { if (attempt === 2) throw e; }
}

// database/sql already retries when the request never reached the wire, so
// a plain call is usually enough; retry yourself only for reads.
rows, err := db.Query("SELECT count(*) FROM events")
if err != nil {
    rows, err = db.Query("SELECT count(*) FROM events")
}

for (int attempt = 1; attempt <= 2; attempt++) {
    try { conn.query("SELECT count(*) FROM events"); break; }
    catch (Skaidb.SkaidbException e) { if (attempt == 2) throw e; }
}

attempts = 0
begin
  attempts += 1
  conn.exec("SELECT count(*) FROM events")
rescue Skaidb::ConnectionError
  retry if attempts < 2
  raise
end

for ($attempt = 1; $attempt <= 2; $attempt++) {
    try { $db->exec('SELECT count(*) FROM events'); break; }
    catch (\Skaidb\SkaidbException $e) { if ($attempt === 2) throw $e; }
}

for (int attempt = 1; attempt <= 2; attempt++) {
    try {
        using var cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT count(*) FROM events";
        using var r = cmd.ExecuteReader();
        break;
    } catch (SkaidbException) when (attempt == 1) { /* retry on the re-dialled socket */ }
}

let mut out = client.execute("SELECT count(*) FROM events");
if out.is_err() {
    out = client.execute("SELECT count(*) FROM events");   // Client re-dialled
}
let resp = out?;

Retry reads freely. For writes, make the statement idempotent first — see the note below.

Retrying is safe for reads. For writes, make the statement idempotent first — a primary-key insert is naturally an upsert, so re-sending it is harmless, but an UPDATE … SET n = n + 1 is not.

How do I read arrays, documents and NULLs?

skaidb is schema-less: a column can hold a document or an array, and rows in one table need not share fields.

-- `labels` is an Array, `meta` a Document, `note` absent from the row.
-- Every driver decodes these into its own native types — see the table above.
SELECT labels, meta, note FROM people WHERE id = ?

cur.execute("SELECT labels, meta, note FROM people WHERE id = ?", (1,))
labels, meta, note = cur.fetchone()
labels[0]           # 'math'
meta["city"]        # 'London'
note is None        # True

const res = await client.query(
  'SELECT labels, meta, note FROM people WHERE id = $1', [1]);
const row = res.rows[0];
row.labels[0];      // 'math'
row.meta.city;      // 'London'
row.note === null;  // true

// Composites arrive as JSON TEXT — unmarshal them yourself.
var labels, meta string
var note sql.NullString          // NULL-safe: a bare string would fail to scan
rows.Scan(&labels, &meta, &note)
var labelList []string
json.Unmarshal([]byte(labels), &labelList)
var metaMap map[string]any
json.Unmarshal([]byte(meta), &metaMap)

// getObject, not the typed getters: name-based getInt/getLong THROW on NULL
// and the index-based overloads throw NullPointerException.
java.util.List<?> labels = (java.util.List<?>) rs.getObject("labels");
java.util.Map<?, ?> meta  = (java.util.Map<?, ?>) rs.getObject("meta");
boolean missing           = rs.isNull("note");

row = conn.exec_params("SELECT labels, meta, note FROM people WHERE id = $1", [1]).first
row["labels"][0]     # "math"
row["meta"]["city"]  # "London"
row["note"].nil?     # true

$stmt = $db->prepare('SELECT labels, meta, note FROM people WHERE id = ?');
$stmt->execute([1]);
$row = $stmt->fetchAll()[0];
$row['labels'][0];        // 'math'
$row['meta']['city'];     // 'London'
$row['note'] === null;    // true

// Check IsDBNull first: the typed getters throw on NULL.
var labels = (object?[])reader.GetValue(0);
var meta   = (Dictionary<string, object?>)reader.GetValue(1);
bool missing = reader.IsDBNull(2);

use skaidb_types::Value;
match &row[0] {
    Value::Array(items) => println!("first label: {}", items[0]),
    other => println!("not an array: {other}"),
}
match &row[1] {
    Value::Document(doc) => println!("city: {:?}", doc.get("city")),
    other => println!("not a document: {other}"),
}
let missing = matches!(row[2], Value::Null);

Watch NULL handling in the strict languages. Java's name-based getInt/ getLong throw on NULL (guard with isNull first) while the index-based overloads throw NullPointerException; .NET's typed getters throw unless you check IsDBNull first. Go cannot scan NULL into a bare string/int — use the sql.Null* wrappers or a pointer.

Writing composites works everywhere: every driver binds through the server's prepared-statement path, so a list/map goes over the wire as a real Array/Document rather than being squeezed into SQL text (which has no literal form for either). Node and Ruby rewrite their $N placeholders to the positional ? the server expects, duplicating a parameter that is referenced twice.

The one exception is a statement the server declines to prepare — DDL and session statements — which falls back to client-side quoting, where only scalars have a literal form. Build such a value in SQL, or use the REST /insert endpoint, which accepts arbitrary JSON rows.

How do I back up and restore?

Three mechanisms, for three different questions:

A continuous off-site copy → a witness. A standalone node that mirrors chosen databases — or chosen tables within them, via witness.tables / witness.exclude_tables — near-live, stays read-only but fully queryable, and holds tombstone GC on the primary until it has seen every delete. The answer to "the machine/site died".

A point-in-time snapshot → BACKUP TO '/path'. Runs on the server, under the engine lock, so it is fully consistent — and lands on the SERVER's filesystem (on a cluster: the answering node's own shard). Re-running it against the same target is incremental: unchanged files are reused and stale ones removed, so a routine backup moves roughly one flush plus the WAL tail instead of the whole directory, while the target stays a complete restorable snapshot. The matching RESTORE FROM works on a standalone server only; restoring a cluster node is stop → restore the data directory offline → start → let repair converge it.

A pull-to-client backup → skaidbsh backup-pull -o dir/. Streams the connected node's whole data directory to your machine as a tar over HTTP (GET /admin/backup.tar, ADMIN role; curl works too) — no server-side staging copy, and the engine lock is held only for the final consistency cut, not the transfer: the immutable bulk (SSTables, sealed TS blocks) streams unlocked in convergent passes, then a short exclusive pass re-streams whatever changed and a manifest prunes files compaction replaced mid-stream. The extracted directory is a normal backup: open it with skaidbsh --local, or restore it like any BACKUP TO output. On a cluster, pull from each member (each serves its own shard).

A portable, laptop-side dump → skaidbsh export / import. Runs wherever you run the shell, over the ordinary wire protocol, and writes locally:

skaidbsh --host db1 --user u --password … export --all -o ./dump
skaidbsh …                                 export --database app --format csv
skaidbsh …                                 import -i ./dump
skaidbsh …                                 import -i ./dump --table app.orders --no-schema

export writes schema.sql plus one <db>.<table>.jsonl (or .csv) per table. import replays the schema, then loads every data file back with batched multi-row INSERTs — and because a plain INSERT replaces a row whose primary key exists, an import is idempotent: re-running one repairs rather than duplicates.

What to know before trusting it:

• JSON is the fidelity format. A JSON dump round-trips value-identical. CSV cannot distinguish an empty string from NULL, and non-JSON types ride as text — use it for spreadsheets, not restores.
• Types without a JSON shape come back as their JSON forms: timestamps as integers (epoch ms), UUIDs and decimals as strings. The store is schemaless, so they load fine; comparisons against freshly-written rows of the richer type are where the difference shows.
• export reads per table over SELECT: each table is consistent at its read time, but a dump of a live cluster is not one cross-table instant — BACKUP is, per node.
• schema.sql recreates every index exactly: each index's canonical CREATE … IF NOT EXISTS DDL comes verbatim from SHOW INDEXES' definition column, so vector (DIM/metric/QUANTIZED/EMBED), search, geo, UNIQUE and global indexes all come back as themselves.
• Constraints — foreign keys, NOT NULL, DEFAULT and CHECK — ride in a separate constraints.sql, which import replays after the data — so tables load in any order, and every constraint validates against the complete restore (NOT VALID ones stay so).

An arbitrary QUERY as CSV/JSON → skaidbsh -e … --format. export dumps whole tables; for anything shaped by a query — a filtered subset, a join, an aggregate — put the statement on -e (or a file on -f) and pick the format:

skaidbsh …  -e "SELECT id, total FROM app.orders WHERE total > 100" \
            --format csv  > big-orders.csv
skaidbsh …  -e "SELECT * FROM app.events WHERE day = '2026-08-18'" \
            --format json | jq '.user'

csv is RFC-4180 with a header row (arrays/documents as JSON cells); json is NDJSON, one object per row with NULL fields omitted. In either format stdout carries only row data — DDL and mutation acknowledgements print nothing and errors go to stderr, so redirects stay clean even for multi-statement scripts (each row-producing statement appends its output; CSV repeats its header per statement). The CSV lossiness above applies here too: an empty cell is both NULL and the empty string.

How do I pool connections?

All eight have one. Everywhere except Go, maxsize bounds the connections kept idle, not the number checked out: more callers than maxsize simply get extra connections, and the surplus is closed when returned rather than queueing. Every connect option passes through, so pooled connections inherit seed failover, TLS and the session database.

This matters most in Java and .NET, where one connection serializes every statement through a single socket — threads sharing one queue behind each other.

pool = skaidb.pool(seeds=["db1", "db2", "db3"], database="app", maxsize=8)
with pool.connection() as conn:
    conn.execute("SELECT 1")
pool.close()

const { Pool } = require('skaidb');
const pool = new Pool({ seeds: ['db1:7000', 'db2:7000'], database: 'app', maxsize: 8 });
const res = await pool.withConnection((conn) => conn.query('SELECT 1'));
await pool.end();

// database/sql is the pool; these bound it.
db.SetMaxOpenConns(8)          // Go DOES cap checkouts — callers wait
db.SetMaxIdleConns(4)
db.SetConnMaxLifetime(30 * time.Minute)

try (Skaidb.Pool pool = new Skaidb.Pool("skaidb://u:p@db1:7000,db2:7000/app", 8)) {
    long n = pool.withConnection(conn -> {
        Skaidb.ResultSet rs = conn.prepare("SELECT count(*) AS n FROM t").executeQuery();
        rs.next();
        return rs.getLong("n");
    });
}

pool = Skaidb::Pool.new(seeds: ["db1:7000", "db2:7000"], database: "app", maxsize: 8)
pool.with { |conn| conn.exec("SELECT 1") }
pool.close

// Share-nothing per request: worth it in a worker or long-running CLI job.
$pool = new Skaidb\Pool(['db1', 7000, 'u', 'p', 'QUORUM'], 8);
$n = $pool->withConnection(fn ($conn) => $conn->exec('SELECT 1'));
$pool->close();

using var pool = new SkaidbConnectionPool("Host=db1;Port=7000;Database=app", 8);
long n = pool.WithConnection(conn => {
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT count(*) AS n FROM t";
    using var r = cmd.ExecuteReader();
    r.Read();
    return r.GetInt64(0);
});

use skaidb_driver::{Client, Pool};
let eps = vec!["db1:7000".to_string(), "db2:7000".to_string()];
let pool = Pool::new(8, move || Client::connect_many(&eps, "u", "p")?.with_database("app"));
let rows = pool.with(|c| c.execute("SELECT 1"))?;

Go is the exception to the sizing rule: SetMaxOpenConns caps checkouts, so callers block waiting for a free connection instead of opening extras.

How do I turn several statements into one round trip?

Store them as a procedure and CALL it. The body runs server-side, so a fixed sequence costs one round trip instead of N, and the sequence lives with the schema instead of being copy-pasted into every client.

This needs no driver support: CREATE PROCEDURE and CALL are ordinary statements, and CALL p(?) prepares and binds through your driver's normal parameter path. CALL returns the last statement's result set.

CREATE PROCEDURE archive_order(order_id TEXT)
BEGIN
  INSERT INTO archive (id, at) VALUES (order_id, now());
  DELETE FROM orders WHERE id = order_id;
  SELECT id, at FROM archive WHERE id = order_id;
END;

CALL archive_order('o-1');
SHOW PROCEDURES;               -- name, params, definer, created

cur = conn.cursor()
cur.execute("CALL archive_order(?)", ("o-1",))
print(cur.fetchone())          # ('o-1', 1755300000000)

const res = await client.query('CALL archive_order($1)', ['o-1']);
console.log(res.rows[0].id);

rows, err := db.Query("CALL archive_order(?)", "o-1")
defer rows.Close()
for rows.Next() { /* the last statement's rows */ }

Skaidb.ResultSet rs = conn.prepare("CALL archive_order(?)")
                          .setString(1, "o-1")
                          .executeQuery();
while (rs.next()) System.out.println(rs.getString("id"));

res = conn.exec_params("CALL archive_order($1)", ["o-1"])
res.each { |row| puts row["id"] }

$stmt = $db->prepare('CALL archive_order(?)');
$stmt->execute(['o-1']);
foreach ($stmt->fetchAll() as $row) { echo $row['id']; }

using var cmd = conn.CreateCommand();
cmd.CommandText = "CALL archive_order(?)";
cmd.Parameters.Add("o-1");
using var reader = cmd.ExecuteReader();
while (reader.Read()) Console.WriteLine(reader.GetString(0));

use skaidb_proto::Response;
use skaidb_types::Value;
let mut call = client.prepare("CALL archive_order(?)")?;
if let Response::Rows { rows, .. } =
    client.execute_prepared(&mut call, &[Value::String("o-1".into())])?
{
    for row in rows { println!("{}", row[0]); }
}

Three things to know before you lean on it:

• CALL runs with your own privileges. EXECUTE on the procedure is only the gate — you also need every privilege the body's statements need, including those of any procedure it calls. A procedure is not a way to lend access.
• It is not a transaction. Statement 3 can fail after 1 and 2 committed. For all-or-nothing across one partition, CALL inside BEGIN ATOMIC PARTITION — see below.
• Name parameters so they cannot collide with columns. A bare id in the body means the parameter, so WHERE id = id compares the argument with itself. skaidb refuses that at CREATE PROCEDURE when the column is declared, but the habit is worth having: order_id, not id.

The body takes SELECT/INSERT/UPDATE/DELETE and nested CALL (capped at 16 deep) — no DDL, no transaction control, no dynamic SQL. Full detail in PROCEDURES.md.

…with logic in it

A body is not limited to a straight list. It has variables, branches, loops, cursors and exception handlers, so work that would otherwise be a read-modify- write round trip per row happens in one call:

CREATE PROCEDURE reconcile(cutoff INT)
BEGIN
  DECLARE n INT DEFAULT 0;

  SELECT count(*) INTO n FROM orders WHERE total > cutoff;
  IF n = 0 THEN
    RETURN {matched: 0};
  END IF;

  FOR r IN (SELECT id, total FROM orders WHERE total > cutoff) DO
    BEGIN
      INSERT INTO flagged (id, total) VALUES (r.id, r.total);
    EXCEPTION WHEN UNIQUE_VIOLATION THEN
      -- one bad row does not stall the batch
      INSERT INTO rejects (id, why) VALUES (r.id, error_message);
    END;
  END FOR;

  RETURN {matched: n};
END;

RETURN {…} comes back as one row with one column per field, so it reads like any other result:

cur.execute("CALL reconcile(?)", (1000,))
print(cur.fetchone())          # (12,)

Four things to know:

• A WHILE has no iteration cap. It is bounded by your statement's scan budget and deadline — the body inherits one ceiling for the whole call — so WHILE 1 = 1 stops the way a runaway query stops, with a resource-limit error.
• A handler cannot catch that, nor a storage or cluster error. It catches data errors (NOT_FOUND, CONSTRAINT, UNIQUE_VIOLATION, FOREIGN_KEY_VIOLATION, NOT_NULL_VIOLATION, CHECK_VIOLATION, TYPE_ERROR, UNSUPPORTED, OTHERS) and binds error_kind / error_message.
• FOR … IN materializes its query, bounded by the same byte budget as the identical SELECT. To sweep more than that, declare a cursor — a resumable keyset scan over the primary key that pages in constant memory.
• A cursor is not a snapshot. It resumes by position, so rows written while it is open are visible to it. It resumes at any consistency and placement: a full-copy node at ONE walks its own shard, and every other cluster route pages the LWW-merged distributed read strictly after the cursor.

How do I react to changes on a table?

Declare a stream: a standing filter over a table's writes. Every committed change that matches the WHEN predicate is appended to the stream's log, which is an ordinary table you read like any other.

CREATE STREAM big_orders ON orders
  WHEN (total > 1000 AND status != 'draft')
  WITH (start = 'now', retention = '24h');

Read the events by paging the log in position order. id is the position, op the change kind, k the row's primary-key value, ts the write time and doc the row itself:

SELECT id, op, k, ts, doc FROM _stream_big_orders ORDER BY id LIMIT 500;
-- then, from the last id you saw:
SELECT id, op, k, ts, doc FROM _stream_big_orders
  ORDER BY id LIMIT 500 AFTER ('<last id>');

Keep the last id you processed and resume from it — that is the whole consumer protocol, and it is what makes retention a replayable window rather than a queue: any consumer can rewind to any position still inside it. The replay streams server-side (the log's single-column position key keeps it on the keyset lane), so a 24-hour rewind does not materialise on the node.

…by running a procedure automatically

A trigger runs a stored procedure for every change that matches its predicate, so the reaction lives with the schema instead of in a consumer you have to keep running:

CREATE PROCEDURE archive_order(k TEXT, was ANY)
BEGIN
  INSERT INTO archive (id, previous_total) VALUES (k, was)
    ON CONFLICT DO UPDATE SET previous_total = excluded.previous_total;
END;

CREATE TRIGGER big_order ON orders WHEN (total > 1000)
  CALL archive_order(NEW.id, OLD.total);

SHOW TRIGGERS;

The body sees NEW (the row now), OLD (the row before — NULL on an insert), OP (put/exit/delete) and EVENT().

Four things to know:

• It fires from the log, not inline with the write. The write commits first and the trigger runs just after, so a failing trigger can never fail or roll back the write that caused it.
• Delivery is at-least-once. Key your writes on EVENT() — as the ON CONFLICT above does — and a repeat converges instead of duplicating.
• It costs writes. A trigger owns a stream, which roughly doubles the write volume of what it matches, and its pre-images double that again. SHOW TRIGGERS names the stream so you can measure it. Narrow the WHEN.
• Cascade cycles are refused at CREATE TRIGGER — a trigger whose body writes back to a table that leads to its own source would never terminate.

A trigger has no caller, so there is nothing for your client to fetch. To see whether it ran, read the history with an ordinary query — SHOW TRIGGERS for what exists, SELECT job, status, started_at, error FROM _job_runs ORDER BY id DESC for what happened. The same tables cover jobs; see PROCEDURES.md.

…without polling

With the MQTT broker enabled ([mqtt] enabled), each event is also published live to $stream/<db>/<name> as JSON, so any MQTT client can subscribe:

mosquitto_sub -t '$stream/default/big_orders'
{"id":"00001786...-0000000003-...","op":"put","k":2,"ts":1786...,"doc":{...}}

The $ prefix keeps streams out of ordinary # subscriptions, as with $SYS. Subscribing is governed by the usual topic ACLs (GRANT SUBSCRIBE ON TOPIC ...).

The log is authoritative; the live tail is a convenience. Delivery is best-effort (QoS 0): a subscriber that was disconnected, or a node that restarted, misses the live message. Each event carries its id, so the fix is always the same — resume by replaying the log from the last id you processed. That is also why a restart does not re-publish history: the tail resumes at the end of the log rather than flooding subscribers with a retention window they can read themselves.

Every driver wraps the polling loop, so you rarely write it yourself:

for ev in conn.subscribe("big_orders"):        # resume with after="<last id>"
    print(ev["op"], ev["k"], ev["doc"])

for await (const ev of client.subscribe('big_orders')) {
  console.log(ev.op, ev.k, ev.doc);
}

err := skaidb.Subscribe(ctx, db, "big_orders", "", func(ev skaidb.Event) error {
    fmt.Println(ev.Op, ev.Key, ev.Doc)   // composites arrive as JSON text
    return nil
})

conn.subscribe("big_orders", null, ev -> {
    System.out.println(ev.op + " " + ev.key);
    return true;                          // false stops subscribing
});

conn.subscribe("big_orders") { |ev| puts "#{ev['op']} #{ev['k']}" }

foreach ($db->subscribe('big_orders') as $ev) {
    echo $ev['op'], ' ', $ev['k'], PHP_EOL;
}

foreach (var ev in conn.Subscribe("big_orders"))
    Console.WriteLine($"{ev.Op} {ev.Key}");

let mut cursor = String::new();
loop {
    let (events, next) = client.stream_poll("big_orders", &cursor, 500)?;
    for ev in &events { println!("{ev:?}"); }
    cursor = next;
}

Two things to know before you build on it:

• op tells you what happened: put (matches now), exit (it dropped out of the predicate) or delete. A derived copy that applies all three stays in step with the source.
• start = 'earliest' backfills every row that already matches, so you can build a derived copy from scratch — but it runs inline, so the CREATE STREAM takes as long as one pass over the table.
• A stream costs a write. Every matching change writes a second, replicated row, so a predicate matching most writes roughly doubles that table's write volume. Filter narrowly.

SHOW STREAMS lists what exists with live consumer and lag numbers, and DROP STREAM big_orders removes the stream and its log together. Full detail — guarantees, costs and cluster behaviour — in STREAMS.md.

How do I search, rank and query vectors?

These are SQL, so every driver runs them through its ordinary query call. Each feature doc has a From an application section with the same query in all eight languages: SEARCH.md, VECTOR.md, GEO.md, TIMESERIES.md and PROCEDURES.md.

Full-text, ranked, with a highlighted snippet:

SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet FROM articles
WHERE MATCH(body, 'quick brown fox') AND published = true
ORDER BY score() DESC LIMIT 10

Vector similarity — NEAREST returns the k nearest rows, nearest first, with the distance exposed as _distance:

SELECT id, _distance FROM docs NEAREST (embedding, [0.1, -0.2, 0.9], 10)
SELECT id FROM docs NEAREST (embedding, [0.1, -0.2, 0.9], 10) WHERE cat = 'news'

With managed embeddings the query text is embedded for you:

SELECT id FROM docs NEAREST (body, 'natural language query', 10)

Time-series, bucketed:

SELECT time_bucket(5m, ts) AS bucket, avg(value)
FROM cpu WHERE host = 'db1' AND ts >= '2026-01-01'
GROUP BY bucket ORDER BY bucket

Full-text search from code

The query text binds as a parameter, so user input is never concatenated into SQL. score() comes back in a column named score even without an alias; HIGHLIGHT needs one.

SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet
FROM articles WHERE MATCH(body, ?) AND published = true
ORDER BY score() DESC LIMIT 10

cur.execute("SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet "
            "FROM articles WHERE MATCH(body, ?) AND published = true "
            "ORDER BY score() DESC LIMIT 10", ("quick brown fox",))
for id_, title, score, snippet in cur.fetchall():
    print(id_, score, snippet)

const res = await client.query(
  `SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet
   FROM articles WHERE MATCH(body, $1) AND published = true
   ORDER BY score() DESC LIMIT 10`, ['quick brown fox']);
for (const row of res.rows) console.log(row.id, row.score, row.snippet);

rows, err := db.Query(`SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet
    FROM articles WHERE MATCH(body, ?) AND published = true
    ORDER BY score() DESC LIMIT 10`, "quick brown fox")
defer rows.Close()
for rows.Next() {
    var id int
    var title, snippet string
    var score float64
    rows.Scan(&id, &title, &score, &snippet)
}

Skaidb.ResultSet rs = conn.prepare(
        "SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet "
      + "FROM articles WHERE MATCH(body, ?) ORDER BY score() DESC LIMIT 10")
    .setString(1, "quick brown fox")
    .executeQuery();
while (rs.next()) System.out.println(rs.getDouble("score") + " " + rs.getString("snippet"));

res = conn.exec_params(<<~SQL, ["quick brown fox"])
  SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet
  FROM articles WHERE MATCH(body, $1) ORDER BY score() DESC LIMIT 10
SQL
res.each { |row| puts "#{row['score']} #{row['snippet']}" }

$stmt = $db->prepare('SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet
    FROM articles WHERE MATCH(body, ?) ORDER BY score() DESC LIMIT 10');
$stmt->execute(['quick brown fox']);
foreach ($stmt->fetchAll() as $row) { echo $row['score'], ' ', $row['snippet'], PHP_EOL; }

using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet " +
                  "FROM articles WHERE MATCH(body, ?) ORDER BY score() DESC LIMIT 10";
cmd.Parameters.Add("quick brown fox");
using var reader = cmd.ExecuteReader();
while (reader.Read()) Console.WriteLine($"{reader.GetDouble(2)} {reader.GetString(3)}");

let mut q = client.prepare(
    "SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet \
     FROM articles WHERE MATCH(body, ?) ORDER BY score() DESC LIMIT 10")?;
if let Response::Rows { rows, .. } =
    client.execute_prepared(&mut q, &[Value::String("quick brown fox".into())])?
{
    for row in rows { println!("{} {}", row[2], row[3]); }
}

SEARCH('…') takes a bound string the same way, so a query-string UI can pass the user's text straight through.

Vector search from code

The query vector binds as a typed Array — the floats never pass through SQL text — and k binds too. NEAREST requires a vector index on that path; without one the query fails rather than falling back to a scan.

SELECT id, _distance FROM docs NEAREST (embedding, ?, ?) WHERE cat = ?

cur.execute("SELECT id, _distance FROM docs NEAREST (embedding, ?, ?) WHERE cat = ?",
            ([0.1, -0.2, 0.9], 10, "news"))
for id_, distance in cur.fetchall():
    print(id_, distance)

const res = await client.query(
  'SELECT id, _distance FROM docs NEAREST (embedding, $1, $2) WHERE cat = $3',
  [[0.1, -0.2, 0.9], 10, 'news']);
for (const row of res.rows) console.log(row.id, row._distance);

// CheckNamedValue lets a slice through database/sql's type gate.
rows, err := db.Query(
    "SELECT id, _distance FROM docs NEAREST (embedding, ?, ?) WHERE cat = ?",
    []float64{0.1, -0.2, 0.9}, 10, "news")
defer rows.Close()
for rows.Next() {
    var id int
    var distance float64
    rows.Scan(&id, &distance)
}

Skaidb.ResultSet rs = conn.prepare(
        "SELECT id, _distance FROM docs NEAREST (embedding, ?, ?) WHERE cat = ?")
    .setObject(1, java.util.List.of(0.1, -0.2, 0.9))
    .setInt(2, 10)
    .setString(3, "news")
    .executeQuery();
while (rs.next()) System.out.println(rs.getLong("id") + " " + rs.getDouble("_distance"));

res = conn.exec_params(
  "SELECT id, _distance FROM docs NEAREST (embedding, $1, $2) WHERE cat = $3",
  [[0.1, -0.2, 0.9], 10, "news"])
res.each { |row| puts "#{row['id']} #{row['_distance']}" }

$stmt = $db->prepare('SELECT id, _distance FROM docs NEAREST (embedding, ?, ?) WHERE cat = ?');
$stmt->execute([[0.1, -0.2, 0.9], 10, 'news']);
foreach ($stmt->fetchAll() as $row) { echo $row['id'], ' ', $row['_distance'], PHP_EOL; }

using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT id, _distance FROM docs NEAREST (embedding, ?, ?) WHERE cat = ?";
cmd.Parameters.Add(new object[] { 0.1, -0.2, 0.9 });
cmd.Parameters.Add(10);
cmd.Parameters.Add("news");
using var reader = cmd.ExecuteReader();
while (reader.Read()) Console.WriteLine($"{reader.GetInt64(0)} {reader.GetDouble(1)}");

let vec = Value::Array(vec![Value::Float(0.1), Value::Float(-0.2), Value::Float(0.9)]);
let mut q = client.prepare(
    "SELECT id, _distance FROM docs NEAREST (embedding, ?, ?) WHERE cat = ?")?;
if let Response::Rows { rows, .. } =
    client.execute_prepared(&mut q, &[vec, Value::Int(10), Value::String("news".into())])?
{
    for row in rows { println!("{} {}", row[0], row[1]); }
}

With managed embeddings the first bind is a string instead of a vector — same call, the server embeds it for you. Time-series and geo queries are the same shape again with different SQL; see TIMESERIES.md and GEO.md.