SqlRite

SQL-first persistence for Node.js — a zero-dependency wrapper over the built-in node:sqlite. You write SQL in .sql files tagged with comment markers, and SqlRite turns each tagged block into a JavaScript method. No models, no query builder, no implicit find/save — every operation is an explicit SQL block you can read.

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

Full reference — exhaustive tag/option semantics, the design contract, security limits, and maintainer notes — lives in SPEC.md. This page is the quickstart.

Install

npm install @possumtech/sqlrite

How it works

Point SqlRite at one or more directories of .sql files. Each tagged block becomes a method on the returned object.

Tag	Becomes	What it does
-- INIT: <name>	runs at open	DDL / PRAGMA executed once when the DB opens. Use idempotent DDL.
-- PREP: <name>	db.<name>.{all,get,run}(params)	Prepared statement — the only path for runtime or untrusted values.
-- EXEC: <name>	db.<name>(params)	db.exec() of the block. Trusted SQL only — values are string-interpolated, not bound.
-- TX: <name>	db.<name>(params)	-- EXEC wrapped in BEGIN/COMMIT (auto-ROLLBACK on error). Trusted SQL only.

Directories are scanned recursively; files run sorted by basename, numerically (001-*.sql before 002-*.sql). Duplicate EXEC/PREP/TX names warn — the last definition wins.

Usage

Async (default — runs in a Worker thread)

import SqlRite from "@possumtech/sqlrite";

await using 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" });

Construct only via open() — the constructor throws otherwise. Methods return Promises.

Sync

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

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

new SqlRiteSync() cannot register custom functions (registration is async) — use await SqlRiteSync.open() if you pass functions.

Import	Default export	Named
@possumtech/sqlrite	SqlRite (async)	SqlRiteSync
@possumtech/sqlrite/sync	SqlRiteSync	—
@possumtech/sqlrite/core	SqlRiteCore (static utilities)	—

PREP statements

A -- PREP method exposes three modes:

Mode	For	Returns
.run(params)	INSERT/UPDATE/DELETE	{ changes, lastInsertRowid }
.get(params)	one row	row object or undefined
.all(params)	many rows	array of rows

• Bind with named parameters ($name, :name, @name). Pass an object; a leading $/:/@ on keys is optional, so { name } binds $name.
• Object/array values are JSON.stringify-ed on the way in; output is not parsed — call JSON.parse() yourself.
• Integers read as number; a value past 2^53 − 1 throws rather than silently rounding. Opt a statement into BigInt with a bigint flag on its tag — see SPEC.md.

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

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

Configuration

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

SqlRite opens with a hardened, WAL-mode posture (foreign keys on, defensive mode, a non-zero busy_timeout) and exposes curated performance knobs (cacheSize, mmapSize, maxPageCount). Every default is overridable; any other key passes through to node:sqlite. Full tables in SPEC.md.

Built-in SQL functions

• REGEXP — col REGEXP $pattern via JavaScript RegExp, with an optional (?flags) prefix (e.g. (?i)^foo for case-insensitive). Trusted patterns only — RegExp can catastrophically backtrack (ReDoS); see SPEC.md.
• uuid() — crypto.randomUUID(); usable as a column default: id TEXT PRIMARY KEY DEFAULT (uuid()).

Register your own with the functions option — see SPEC.md.

Safety in one breath

• -- PREP is the only place runtime or untrusted values belong — it binds them.
• -- EXEC / -- TX string-interpolate their values — developer-authored SQL only, never untrusted input.
• REGEXP patterns must be trusted (ReDoS).

License

MIT © @wikitopian