SqlRite

SQL-first persistence for Node.js. A zero-dependency wrapper over the built-in node:sqlite DatabaseSync. SQL lives in .sql files tagged with comment markers; SqlRite turns each tagged block into a JavaScript method.

• Requires Node >=25.0.0, npm >=11.1.0.
• No runtime dependencies.
• Two facades over one core: async (Worker thread) and sync.

Install

npm install @possumtech/sqlrite

Model

• Input: one or more directories of .sql files containing tagged blocks.
• Output: an object whose methods are generated from those tags.
• No implicit methods (find, save, …). Every operation is an explicit SQL block.

Tags

A block runs from its tag line to the next tag (or end of file). Empty blocks are skipped.

Tag	Maps to	Behavior
-- INIT: <name>	(runs at open)	Executed once when the DB opens. Use idempotent DDL (CREATE TABLE IF NOT EXISTS) and PRAGMAs. Supports $var templating via the params option.
-- EXEC: <name>	db.<name>(params)	db.exec() of the block, with optional $var string templating. Trusted, developer-authored SQL only — see EXEC.
-- PREP: <name>	db.<name>.{all,get,run}(params)	Prepared statement. The only parameterized path for runtime values.

-- INIT names are not deduplicated. Duplicate -- EXEC/-- PREP names emit a warning; the last definition wins.

File ordering

Directories are scanned recursively for .sql files. Files are sorted by basename, numerically (001-*.sql before 002-*.sql), across all directories merged into one execution plan.

Usage

Async (Worker thread)

DB operations run in a dedicated Worker; methods return Promises. Construct only via open() — the constructor throws otherwise.

import SqlRite from "@possumtech/sqlrite";

const sql = await SqlRite.open({ path: "data.db", dir: "sql" });

await sql.addUser.run({ name: "Alice", meta: { theme: "dark" } });
const user = await sql.getUserByName.get({ name: "Alice" });

await sql.close(); // or: await using sql = await SqlRite.open(...)

Sync

import { SqlRiteSync } from "@possumtech/sqlrite";

const sql = new SqlRiteSync({ dir: ["migrations", "src/users"] });
const users = sql.getUserByName.all({ name: "Alice" });
sql.close(); // or: using sql = new SqlRiteSync(...)

new SqlRiteSync() does not register custom functions. Use the async SqlRiteSync.open() if you pass functions (registration is async).

Entry points

Import	Export
@possumtech/sqlrite	default SqlRite (async), named SqlRiteSync
@possumtech/sqlrite/sync	default SqlRiteSync
@possumtech/sqlrite/core	default SqlRiteCore (static utilities)

PREP statements

A -- PREP method exposes three modes:

Mode	Use	Returns (sync / async)
.run(params)	INSERT/UPDATE/DELETE	{ changes, lastInsertRowid }
.get(params)	single row	row object or undefined
.all(params)	multiple rows	array of row objects

• Use named parameters ($name, :name, or @name). The JS interface takes an object; keys map to parameter names. Leading $/:/@ on keys is stripped, so { name } binds $name.
• Object and array parameter values are JSON.stringify-ed on input. Output is not parsed — call JSON.parse() yourself.

-- PREP: addUser
INSERT INTO users (name, meta) VALUES ($name, $meta);

-- PREP: searchUsers
SELECT * FROM users WHERE name REGEXP $pattern;

bigint reads

Integer columns are read as JS number by default; a value above 2^53 − 1 throws on read rather than losing precision. Append the bigint flag to a -- PREP tag to read that statement's integer columns as BigInt instead:

-- PREP: feeBalance bigint
SELECT SUM(amount) AS total FROM ledger WHERE account = $account;

The flag is scoped to that one statement. The returned value is a BigInt (typeof total === "bigint"): arithmetic cannot mix BigInt and number, and JSON.stringify throws on BigInt (supply a replacer). For a connection-wide default, pass readBigInts: true in options (it passes through to DatabaseSync). Passing a BigInt as a parameter already works without the flag.

EXEC (trusted SQL)

-- EXEC runs db.exec() (one or more statements) after $var templating. It is for developer-authored SQL with constant or developer-supplied inputs — DDL, PRAGMAs, maintenance. It is not a parameterized path: templated values are string-interpolated (string values are single-quote escaped; numbers, booleans, and null are inlined; identifiers are not handled). Do not pass untrusted input through EXEC. For runtime values, use -- PREP with .run().

-- EXEC: insertKv
INSERT INTO kv (key, val) VALUES ($key, $val);

sql.insertKv({ key: "role", val: "admin" }); // val is escaped, not bound

Transactions

transaction(calls) runs a list of -- PREP statements atomically: it issues BEGIN, runs each call with bound parameters, then COMMIT; any error triggers ROLLBACK and the error is rethrown (async: rejects). It is the bound, runtime-safe primitive — the values are parameters, not interpolated. In the async facade the whole batch is one Worker round-trip.

await sql.transaction([
  { name: "debit",  params: { id: from, amt } },
  { name: "credit", params: { id: to,   amt } },
]);
// both commit, or neither does

Each call is { name, params, mode }. name must be a -- PREP statement (an unknown name throws and rolls back); mode is "run" (default), "get", or "all". transaction returns the array of per-call results. Only -- PREP statements participate; -- EXEC is the templated, non-transactional path.

INIT templating

-- INIT blocks support $var substitution from the params option (same templating rules as EXEC).

-- INIT: configure
PRAGMA cache_size = $cacheSize;

await SqlRite.open({ dir: "sql", params: { cacheSize: 5000 } });

Configuration

Option	Type	Default	Description
path	string	":memory:"	SQLite database file path.
dir	string | string[]	"sql"	Directories scanned for .sql files.
functions	string | string[]	—	Module paths for custom SQL functions.
params	object	—	$var substitutions for -- INIT blocks.

All other keys pass through to the node:sqlite DatabaseSync constructor (e.g. readOnly, timeout, allowExtension). Unknown keys are ignored; invalid option types throw at construction.

Connection posture

SqlRite applies these via PRAGMA on every connection: journal_mode = WAL, synchronous = NORMAL.

It also sets these DatabaseSync options, each overridable by passing the same key in your own options:

Option	SqlRite default	Effect
enableForeignKeyConstraints	true	Enforces foreign keys.
enableDoubleQuotedStringLiterals	false	Rejects double-quoted string literals (a misspelled "identifier" errors instead of becoming a string).
defensive	true	Blocks SQL that can corrupt the file: writable_schema, journal_mode=OFF, schema_version, shadow-table writes.

Built-in SQL functions

• REGEXP — col REGEXP $pattern using JavaScript RegExp. Compiled patterns are cached per connection. A NULL subject yields no match.
• uuid() — crypto.randomUUID(). Usable as a column default: id TEXT PRIMARY KEY DEFAULT (uuid()).

Custom SQL functions

Point functions at JS modules. Each module's filename becomes the SQL function name. Functions are registered before any SQL block loads, so they are available in -- INIT and prepared statements. Modules resolve dependencies from the host app's node_modules.

// db/getTokens.js
export const deterministic = true;   // optional; enables query optimization
export default (text) => text.length; // required; the handler

const sql = await SqlRite.open({ dir: "sql", functions: ["./db/getTokens.js"] });

-- PREP: longPosts
SELECT * FROM posts WHERE getTokens(body) > 1000;

Type generation

node scripts/codegen.js [dir]   # writes SqlRite.d.ts

Generates TypeScript declarations for the dynamically generated methods from the .sql files in dir (default "sql").

Known limits

• Integer columns are read as JS number unless a statement opts into BigInt (see bigint reads); without it, a value above 2^53 − 1 throws on read rather than losing precision.
• transaction() composes -- PREP statements only. -- EXEC and -- INIT are templated, not bound, and do not participate in transaction().
• The async facade processes one Worker message at a time; calls are serialized, not concurrent.

Agent operations

• Discover methods: grep for -- PREP: / -- EXEC:.
• Discover schema: grep for -- INIT:.
• Add an operation: add a tagged block to a .sql file, then call db.<name> (run codegen.js to refresh types).
• Bind runtime values with -- PREP + an object of named parameters; never interpolate untrusted input through -- EXEC.
• Group dependent writes with transaction([{ name, params }, …]) for atomicity.
• Read integers beyond 2^53 with a bigint-flagged -- PREP.

License

MIT © @wikitopian