Skip to content
Rindle
Reference contents

ReferenceClients & servers

Rindle SQL (@rindle/sql-client)

Run SQL over HTTP with transactions, session consistency, and a Drizzle adapter. No browser client is required.

View as Markdown

Rindle SQL sends SQL statements to a Rindle deployment over HTTP and returns rows. Use it from server code, scripts, or the supported Drizzle adapter. A SQL request returns a snapshot. To receive later changes automatically, register a live query.

SQL requests and live queries can use the same database. You can start with SQL and add synchronization when your application needs it. SQL statements use SQLite syntax and the supported schema types.

Connect and run a query

You need a running Rindle deployment and its server credentials. For local setup, use rindle dev. Rindle Cloud supplies credentials in its Connect panel. Keep the database token on your server.

@rindle/sql-client runs in environments with the standard fetch API and has no runtime dependencies. Install it in your server package:

pnpm add @rindle/sql-client
// scripts/sql-ready.ts
import { createSqlClient } from "@rindle/sql-client";

const sql = createSqlClient({
  url: process.env.RINDLE_URL!,
  authToken: process.env.RINDLE_DATABASE_TOKEN!,
});

try {
  const response = await sql.execute("select 1 as ready");
  console.log(response.result.rows); // [[1n]]: integer results default to bigint
} finally {
  sql.close();
}

rindle dev injects both variables locally. Rindle Cloud’s Connect panel supplies the same pair in production. The URL is the application ingress. Callers do not need to know which process owns the write master.

Authentication

authToken is a trusted credential with database-wide access. It does not provide per-user row authorization. Keep it in server code. A browser connects through your API server, which authorizes its operations.

RINDLE_DATABASE_TOKEN is the one application-facing server credential. The unified edge uses it for trusted SQL and API-server traffic. Private replication credentials remain an infrastructure detail and are not application configuration.

Synced-app mutations

The API server uses this transport internally for authoritative optimistic mutations. Configure its rindle option with the same URL and token, or use rindle: {} to read the environment. It derives the SQL and query-control clients. The API server guide shows the complete configuration with query and mutator registries.

This keeps routine application setup to one URL and one token. Server-only mutator code can use tx.sql to execute or query raw SQL in the mutation transaction. A scoped mutator can use scope.sql for deliberate work outside that transaction. Import createSqlClient directly when ordinary SQL is itself the operation — scripts, migrations, ORM integration, admin work, or unrelated service code. The advanced API-server sql option still accepts an already-created, caller-owned session for testing or custom lifecycle management.

Pure-write mutators stay on a one-request path. A mutator that reads lazily opens an interactive mutation transaction at its first read, so its accumulated write prefix, reads, later writes, and watermark commit share one transaction. A thrown business rule rolls that transaction back before the server commits the watermark alone. The browser’s optimistic queue then advances.

The underlying trusted-server methods are executeMutation, beginMutation, and rejectMutation. Each accepts { clientId, mid } from the browser mutation envelope. The client does not accept lmid: that is authoritative server state, returned in MutationReceipt only after the mutation effects (or an lmid-only rejection) commit. These explicit methods are separate from ordinary execute/begin, so generic SQL never accidentally enters the optimistic mutation protocol.

Consistency and read-your-writes

Reads default to session consistency. Persist getSessionCursor() with an end-user session and seed the next request with client.session(cursor) to preserve read-your-writes across serverless invocations without mixing different users’ fences.

This function assumes a user table with string id and name columns. Its caller loads the previous cursor from that user’s session and saves the returned cursor for the next request:

// server/rename-user.ts
import type { SqlClient } from "@rindle/sql-client";

export async function renameUser(
  sql: SqlClient,
  previousCursor: string | null,
  id: string,
  name: string,
): Promise<string | null> {
  const session = sql.session(previousCursor);
  await session.execute({
    sql: "update user set name = ? where id = ?",
    args: [name, id],
  });
  return session.getSessionCursor();
}

The client-wide consistency setting is a read default. It never turns writes into consistency errors. A per-call consistency option is explicit read intent, so the server rejects it when its own SQL classifier finds a write or DDL statement. Reads are answered by the write authority itself — the replicator master in a fleet, the daemon in standalone. The TypeScript response identifies it in routing.servedBy ("master" or "standalone"); the underlying JSON wire field is routing.served_by. session and strong validate the canonical w:<seq> fence against the current head, while eventual deliberately skips the fence. A fleet follower refuses the entire /v1/sql surface with a 409 naming the master: its local commit counter cannot fence cursors minted in the master’s sequence. Durable timeline ancestry across restore events is not yet encoded in this sequence-only cursor format.

Transactions and retries

Use withTransaction for statements that must commit together. This example assumes an account table with id and balance columns:

await sql.withTransaction(async (tx) => {
  await tx.execute({ sql: "update account set balance = balance - ? where id = ?", args: [10, 1] });
  await tx.execute({ sql: "update account set balance = balance + ? where id = ?", args: [10, 2] });
});

The authoritative master executes interactive transactions on its HCTree connection pool. Multiple transactions can run concurrently while successful commits still receive one total journal order. Disjoint work can commit in parallel. withTransactionRetry is the opt-in callback replay surface for an OCC conflict.

Interactive transaction callbacks are not replayed unless explicitly requested. Use withTransactionRetry when the callback is safe to re-run after an OCC conflict.

On a standalone daemon, write transactions serialize on the single wal2 writer, but a transaction opened with readOnly: true is backed by its own wal2 reader snapshot: paging a long report holds no lock a write must wait for, and its snapshot stays stable while writes commit beside it. Each held snapshot pins wal2 checkpointing, so open read-only transactions are capped (RINDLE_READ_TXN_SESSIONS, default 4). Past the cap, a begin answers 503 read transaction capacity exhausted rather than queueing — the same busy shape as the master’s exhausted write-session pool — so close an open transaction or back off and retry.

One-shot writes keep one idempotency key across the client’s bounded automatic transport retries. If those attempts are all exhausted, the returned TRANSPORT_ERROR is outcome-unknown. Invoking the method again creates a new logical operation and a new key. An exhausted server-declared request retry is likewise downgraded at this API boundary because the one-shot key is not exposed for a later invocation. Use a declared migration identity or the typed transaction API when an application must recover an operation across a longer outage.

Inside a typed transaction, an exhausted request-scope statement or commit keeps its operation ID: retry the same statement/batch or commit() call. A different statement or commit is refused while a statement retry is pending. rollback() remains available to abandon the transaction safely.

Drizzle

@rindle/sql-client/drizzle supplies the small structural client consumed by drizzle-orm/libsql 0.44.7. It is not a general @libsql/client implementation. The supported Drizzle peer is pinned exactly because this is a runtime structural seam, not a libSQL wire promise. Drizzle’s 0.44.7 libsql entry point itself imports its optional @libsql/client peer eagerly. So an application using that entry point must install @libsql/client even though Rindle does not use its transport.

Install the compatible ORM and its eager peer:

pnpm add drizzle-orm@0.44.7 @libsql/client
import { drizzle } from "drizzle-orm/libsql";
import { createDrizzleClient } from "@rindle/sql-client/drizzle";

const client = createDrizzleClient({
  url: process.env.RINDLE_URL!,
  authToken: process.env.RINDLE_DATABASE_TOKEN!,
});
const db = drizzle(client);

// Use db with your Drizzle table definitions.
// Call client.close() when the owning application shuts down.

Top-level write, deferred, and read transactions are mapped to Rindle’s typed transaction API. Savepoints/nested transactions, embedded replica sync, and the libSQL wire protocol are not supported. Use Rindle’s migration workflow instead of Drizzle’s libSQL migrator.

Ordinary integer result columns become safe JavaScript numbers. For exact 64-bit columns, import rindleBigint from @rindle/sql-client/drizzle and use it in your Drizzle schema:

import { sqliteTable, text } from "drizzle-orm/sqlite-core";
import { rindleBigint } from "@rindle/sql-client/drizzle";

export const externalRecord = sqliteTable("external_record", {
  id: rindleBigint("id").primaryKey(),
  title: text("title").notNull(),
});

rindleBigint emits BIGINT DDL and uses JavaScript bigint for binds and direct column results. SQLite expression results such as max(id) have no declared column type, so they use the safe-number path and reject unsafe integers. Use the native SQL client with intMode: "bigint" for exact expression results. An injected SqlClient must use the lossless bigint mode, not number or string mode. These SQL column types still have the live-query restriction below.

Declared v1 value bounds

  • Safe integral number binds use SQLite’s INTEGER storage class. Fractional and larger numeric number values use REAL. Use bigint when a larger integer is intended.
  • bigint binds preserve the full signed 64-bit range. Columns declared exactly BIGINT or INT8 store and replicate these values without converting them to floating point. Their non-null cells must use SQLite’s INTEGER storage class.
  • Ordinary INTEGER and other number columns use the engine’s floating-point representation. Integer writes must round-trip through f64 exactly or fail with VALUE_UNSUPPORTED. Use an exact BIGINT/INT8 column when that bound is insufficient.
  • Maintained queries currently reject an int64 column in their required columns, including primary keys, filters, ordering, and correlations. Exact SQL storage support does not imply browser live-query support. See Schema and migrations.
  • Binary/Blob bind values fail locally with VALUE_UNSUPPORTED. No byte-to-text coercion occurs.
  • Columns declared BOOLEAN currently accept only canonical SQLite integer cells 0, 1, or NULL on replicated writes. Other integer storage values fail with VALUE_UNSUPPORTED instead of being silently normalized to a boolean by the IVM engine.
  • Results use bigint integers by default. intMode: "number" rejects integers outside JavaScript’s safe range, and intMode: "string" returns tagged integers as decimal strings.
  • Infinity and -Infinity use the v1 tagged JSON representation. NaN is unsupported.

Regular expressions

SQLite ships no built-in regexp, so stock SQLite answers x REGEXP y with no such function. Rindle registers one, which also lights up the infix operator:

select id, title from issue where title regexp '(?i)^\[urgent\]';

regexp(pattern, text) is the only registered function. Note the argument order — pattern first, haystack second — which is what SQLite’s infix rewrite produces.

The dialect is the Rust regex crate, not ECMAScript. It accepts inline flags ((?i)), Unicode property classes (\p{Greek}), and POSIX classes ([[:alpha:]]), and treats \d/\w/\s as Unicode rather than ASCII. It rejects backreferences and lookaround outright — a pattern that works in JavaScript is not guaranteed to work here, or to mean the same thing. There is no backtracking, so a caller-supplied pattern cannot drive the server into exponential match time.

Either NULL argument yields NULL, regardless of the other argument, so a regex predicate over a nullable column filters that row out instead of failing the statement. An invalid pattern is an error. REAL arguments are rejected rather than coerced, because SQLite and Rust do not render floats identically and a silent difference in rendering would be a silent difference in what matched; cast explicitly if you mean to match on a float’s text.

The function is resolved on every read surface over a database — /execute-sql-read, mutator SQL, and rindle db against a local file — so a query you check in the CLI behaves the same way when you send it. Referencing it from a partial index’s WHERE, an expression index, or a CHECK is allowed but is a commitment about the file rather than the query: SQLite needs the function to compile any write touching that table, so ordinary tools that don’t register it — including stock sqlite3 — can still read the database but can no longer write to it. Generated columns are not supported by Rindle’s replicated schema envelope. regexp is a SQL-only facility: registered queries are incrementally maintained by the engine’s own filter set (see supported query shapes), which has no regex operator, so a regex predicate belongs in a raw read rather than a maintained view.

Schema

Rindle SQL writes go through the same replicated schema envelope as the rest of the engine, so a table must have a declared primary key and use supported column types — see the schema page for the DDL subset. id TEXT PRIMARY KEY and Drizzle’s table-level primaryKey({ columns }) are both accepted. What is rejected is an actual NULL primary-key cell.

Use rindle migrate apply for versioned deploy migrations. Migration files can contain pure DDL or pure DML, but never both in one file. The CLI and master bind their kind and checksum to the apply-once identity. The public SqlClient.migrate() method remains a DDL-only primitive. See Schema & migrations for destructive DDL, data backfills, and limits.

For ad-hoc work, the CLI is the same client contract without application code:

rindle sql "select count(*) from issue"
rindle sql --file scripts/seed.sql

Call sql.close() when its owner shuts down. It rejects new work and aborts outstanding fetches. The platform owns connection pooling and keep-alive behavior.