rqlite Dialect

Overview

rqlite is SQLite made highly available: every node holds
a real SQLite database, and writes are replicated between nodes through Raft.
Its SQL is SQLite's SQL, so RqliteDialect extends SQLiteDialect and
inherits every pure SQL builder — DDL, JSON helpers, ON CONFLICT upserts,
LIMIT/OFFSET, double-quoted identifiers — unchanged.

What differs is the wire. rqlite has no driver and no connection: it is an
HTTP API, and a statement is a JSON array POSTed to an endpoint. This dialect
talks to it with the global fetch, so there is no npm package to install.

Property	Value
Dialect name	`rqlite`
Extends	`SQLiteDialect`
Driver	none — global `fetch`
Default port	4001 (the HTTP API)
Cloud SDK	none
Local runtime	self-hosted; no image in this repo

Quick start

Install

$ npm install ts-prorm-orm

$ pnpm add ts-prorm-orm

$ yarn add ts-prorm-orm

$ bun add ts-prorm-orm

No driver package: the dialect talks to the service over fetch.

Connect

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'rqlite',
  host: 'rqlite-1.internal',
  dialectOptions: {
    level: 'weak', // read consistency
  },
});

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'rqlite',
  url: 'rqlite://rqlite-1.internal:4001',
  dialectOptions: {
    level: 'weak', // read consistency
  },
});

A URL is folded into host, port, database, username and password before the dialect is built; any of those passed alongside it wins.

Define a model

import { DataTypes } from 'ts-prorm-orm';

const User = db.define('User', {
  id: { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  email: { type: DataTypes.STRING(255), allowNull: false, unique: true },
  name: { type: DataTypes.STRING(100) },
});

await db.sync(); // CREATE TABLE — see Generated SQL below

Query

import { Op } from 'ts-prorm-orm';

await User.create({ email: 'ada@example.com', name: 'Ada' });

const page = await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

Generated SQL

Each block is the statement the model call above it hands the dialect's query(), captured with the driver stubbed out. The model layer inlines the values.

Create table

db.sync() for the model in the Quick start:

CREATE TABLE

Insert, update and delete

await User.create({ email: 'ada@example.com', name: 'Ada' });
await User.update({ name: 'Grace' }, { where: { email: 'ada@example.com' } });
await User.destroy({ where: { id: 1 } });

Upsert

await User.upsert({ id: 1, email: 'ada@example.com', name: 'Ada Lovelace' });

upsert() also reads the row by primary key before and after the write, to return the instance.

Pagination

await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

Transaction

await db.transaction(async (t) => {
  await User.create({ email: 'grace@example.com', name: 'Grace' }, { transaction: t });
  await User.update({ name: 'Grace Hopper' }, { where: { email: 'grace@example.com' }, transaction: t });
});

[!WARNING] No savepoints
Nested transactions need savepoints, and rqlite has none. Run the work in a single transaction, or restructure it so nothing nests.

[!CAUTION] Not atomic
Each statement is its own request: a failure part-way through leaves the earlier statements applied. transaction({ requireAtomic: true }) refuses this dialect instead.

Type mapping for the portable descriptors: STRING → VARCHAR(255), auto-increment INTEGER → INTEGER, BOOLEAN → INTEGER, DATE → DATETIME, UUID → TEXT, JSON → TEXT. The type comparison lines the major engines up side by side.

Connection

import { Prorm } from 'ts-prorm-orm';

const db = new Prorm({
  dialect: 'rqlite',
  host: 'rqlite-1.internal',
  port: 4001, // the default
  username: 'mary', // HTTP basic auth, when the cluster has users configured
  password: process.env.RQLITE_PASSWORD,
  dialectOptions: {
    ssl: true, // https://
    level: 'weak', // read consistency — see below
  },
});

Or construct it directly:


const rq = new Prorm({
  dialect: 'rqlite',
  url: 'https://rqlite-1.internal:4001',
  username: 'mary',
  password,
});
await rq.connect(); // probes /readyz, so a wrong URL or password fails here

Any node will do: a node that is not the Leader forwards writes to it.

Option	Meaning
`url`	Base URL of a node. Wins over `host`/`port`/`ssl`.
`host`, `port`, `ssl`	Build the URL: `http(s)://host:port`.
`username`, `password`	HTTP basic auth.
`level`	Default read consistency: `none`, `weak`, `strong`, `linearizable`, `auto`.
`freshness`, `freshnessStrict`	With `level: 'none'`, bound how stale a node may be (`'1s'`).
`forwardTimeout`	`?timeout=` — how long a node may take forwarding to the Leader (`'10s'`).
`timeoutMs`	Client-side abort for one HTTP request (default 30 s).
`fetch`	Replace the fetch implementation (tests).

What differs from SQLite

RqliteDialect extends SQLiteDialect (sqlite) and
overrides 23 of its methods (Object.getOwnPropertyNames(RqliteDialect.prototype),
computed by the scratch test); everything else is inherited unchanged.

Area	Overridden
Connection and execution	`commitTransaction`, `connect`, `disconnect`, `getConnection`, `isConnected`, `query`, `queryStream`, `rollbackTransaction`, `runRebuildSteps`, `startTransaction`
DDL and schema	`attachDatabase`, `detachDatabase`
Introspection	`getDatabaseVersion`, `readFlags`
Other	`authHeader`, `batch`, `post`, `queryString`, `send`, `setPragma`, `shapeRows`, `shapeWrite`, `status`

How statements travel

Statement	Endpoint	Body
`SELECT`, `WITH`, `EXPLAIN`, `PRAGMA name`	`POST /db/query?level=…&blob_array`	`[["SELECT … WHERE a = ?", 1]]`
Writes, DDL, `PRAGMA name = value`	`POST /db/execute`	`[["INSERT … VALUES (?, ?)", "x", 2]]`
A write with `RETURNING`	`POST /db/request`	same
`batch()`	`POST /db/execute?transaction`	one element per statement

Bind values always travel as JSON parameters next to the statement. Positional
(?) and named (:name) parameters both work — rqlite binds :name from an
object. Booleans become 1/0, Dates ISO strings, objects JSON, and a
Buffer a byte array (rqlite's BLOB parameter form). BLOBs are read back with
?blob_array and returned as Buffers.

rqlite reports a failing statement with an error field under HTTP 200;
the dialect checks every result and throws RqliteError (with status and
statementErrors) either way.

Read consistency

await rq.query('SELECT * FROM accounts WHERE id = ?', { replacements: [7], level: 'linearizable' });

weak — rqlite's default and its own recommendation — reads from the Leader
without a Raft round-trip. none reads the local node's SQLite; strong sends
the read through the Raft log; linearizable confirms leadership with a quorum
first. An unknown level is refused, because rqlite would silently fall back to
weak.

Transactions: there are none to hold open

rqlite documents explicit BEGIN, COMMIT, ROLLBACK, SAVEPOINT and
RELEASE as having undefined behaviour in a cluster. Its only atomic unit is
one request sent with ?transaction — a list of statements decided up front.

So, exactly as the Cloudflare D1 dialect does, startTransaction() returns a
bookkeeping shell: the statements inside run immediately, one request each, and
rollback() logs that nothing could be undone. Code that needs atomicity uses
batch(), built from the query builders' own output:

await rq.batch([
  rq.buildUpdateQuery('accounts', { balance: 90 }, { id: 1 }),
  rq.buildUpdateQuery('accounts', { balance: 110 }, { id: 2 }),
]);
// POST /db/execute?transaction — both apply, or neither does

batch(statements, { queue: true, transaction: false, wait?: true }) sends a
queued write (?queue) — acknowledged before it is applied. Queue together
with transaction is refused: the caller could never learn whether the
transaction committed.

A table rebuild (SQLite's addForeignKey and friends) is sent as one
?transaction request, so the PRAGMA defer_foreign_keys at its head holds
for every step.

Streaming

A query returns its whole result set; there is no cursor. queryStream() pages
with LIMIT/OFFSET through the shared helper, as the Turso dialect does.

What it refuses

Asked for	What happens
`PRAGMA journal_mode`, `wal_checkpoint`, `wal_autocheckpoint`, `synchronous`	Refused before sending — rqlite prohibits them (it owns the WAL and the disk)
`PRAGMA foreign_keys = …`	Accepted and dropped — enforcement is the `rqlited -fk` flag, set on every node
`attachDatabase()` / `detachDatabase()`	Refused — an attached file is on one node's disk, outside what Raft replicates
`.advanced`	Refused — it drives better-sqlite3's synchronous API
`batch()` containing a read	Refused — `/db/execute` does not answer reads
`batch({ queue: true, transaction: true })`, `wait` without `queue`	Refused
An unknown `level`	Refused

Diagnostics

status() returns the node's /status document (Raft state, Leader, store
size). getDatabaseVersion() returns sqlite_version().

References

rqlite HTTP API

Read consistency

Security (basic auth)

Capabilities

Computed from the code — capabilitiesOf() (src/transaction-support.ts),
whereSupportOf() (src/query-builders/expression-render.ts),
sqlObjectCapabilitiesOf() (src/sql-objects/capabilities.ts) and the
builders — by the scratch test described in the
capability matrix.

Capability	`rqlite`
Transactions atomic	no — each statement is its own request; `requireAtomic` refuses it
Savepoints	no — a nested `transaction()` throws
Isolation	up to `SERIALIZABLE`
Transaction modes	read-only no; deferrable no; constraint deferral no; SQLite locking `type`
RETURNING	not emitted; `returning` is accepted and ignored
Upsert	`INSERT … ON CONFLICT (…) DO UPDATE`
JSON column	`TEXT` (`JSONB` → `TEXT`)
Vector column	refused — The VECTOR column type is not supported on SQLite. SQLite has no vector type; use Turso/libSQL (F32_BLOB) or the sqlite-vec extension
Array column	`TEXT`
WHERE builder	32 operator keys; `$and`/`$or`/`$not`; `$exists`: yes; `$in` subquery: yes; expression operands: yes
SQL objects	family `sqlite`: triggers yes; procedures no; functions `driverUdf`; materialized views emulated (`app`); sequences no; enums `check`; RLS no
Streaming	`queryStream()` present; `LISTEN` absent; `createExtension()` present

Testing

tests/dialects/rqlite.test.ts exercises the dialect with a mocked driver, so nothing touches a server.

Overview#

Quick start#

Install#

Connect#

Define a model#

Query#

Generated SQL#

Create table#

Insert, update and delete#

Upsert#

Pagination#

Transaction#

Connection#

What differs from SQLite#

How statements travel#

Read consistency#

Transactions: there are none to hold open#

Streaming#

What it refuses#

Diagnostics#

References#

Capabilities#

Testing#

See also#

Overview

Quick start

Install

Connect

Define a model

Query

Generated SQL

Create table

Insert, update and delete

Upsert

Pagination

Transaction

Connection

What differs from SQLite

How statements travel

Read consistency

Transactions: there are none to hold open

Streaming

What it refuses

Diagnostics

References

Capabilities

Testing

See also