Skip to content

Latest commit

 

History

History

sqlite

README.md

@cutils/sqlite

Embedded SQLite for scriptc: the amalgamation vendored in-package, a ~26-entry C shim over the scriptc span FFI ABI, and a subset-clean TypeScript wrapper (Database / Stmt<TRow> / Transaction / SqliteError). The same source runs under plain Node (via the shipped shared-library build + koffi) and compiles through scriptc build into a single static ELF with the database linked in — no external dependency at either end.

import { Database } from "@cutils/sqlite";
import * as v from "@chttp/val";

const Todo = v.strictObject({
  id: v.number(),
  title: v.string(),
  done: v.number(),
});

const db = Database.open("app.db");
db.exec(
  "CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER DEFAULT 0)",
);

// Validating statements: params and rows are checked by the Standard-Schema
// validator — any compliant library works (val, valibot, ...).
const ins = db.prepare(
  "INSERT INTO todos (title, done) VALUES (:title, :done)",
  Todo,
);
ins.run({ title: "ship it", done: 0 });

const one = db.prepare("SELECT id, title, done FROM todos WHERE id = ?", Todo);
const row = one.getOrNull([1]); // TRow | null

using tx = db.transaction(); // auto-commit on dispose, auto-rollback on throw
db.exec("INSERT INTO todos (title) VALUES ('in tx')");

// Opt out of validation anywhere with the *Raw family.
const raw = db.prepareRaw("SELECT COUNT(*) AS n FROM todos");
const { n } = raw.getOrNull([]);

db.close();

Opening

Database.open(path, opts?)            // file DB; creates by default
Database.openInMemory()               // ":memory:" — fast + transient

opts.readonly opens read-only (implies fileMustExist); opts.fileMustExist fails when the file is absent. Getters: db.path, db.memory, db.readonly, db.inTransaction, db.lastInsertRowid, db.changes.

Prepared statements

db.prepare(sql, schema) validates bind parameters on write and row records on read through schema["~standard"].validate (Standard Schema v1). db.prepareRaw(sql) skips validation entirely. On Stmt<TRow>:

  • run(params) / runRaw → { lastInsertRowid, changes } for write SQL.
  • get(params) → TRow | undefined (see subset quirk below), getOrNull → TRow | null — the subset-strict twin; prefer it in scriptc apps.
  • all(params) / allRaw → TRow[]; getRaw/allRaw/runRaw skip validation on demand.
  • Params bind by position ([v1, v2]) or by name ({ title: "x" }, also ":title", "@title", "$title" — sigils interchangeable).
  • bind(params) makes bindings permanent until the next bind/clear; columns() reports sqlite3_column_* metadata; toString() returns the expanded SQL; finalize() (or using/Symbol.dispose) releases it.

Transactions

db.transaction() returns a Transaction; dispose commits unless the tx (or any nested op) threw or was marked failed — then it rolls back. Nested db.transaction() inside an open tx becomes an automatic savepoint; tx.savepoint(name) gives an explicit one with commit()/rollback().

using/await using are supported in the scriptc subset (see the root AGENTS.md, "using / resource disposal"). The using value must be declared with the class type — Transaction, not a Disposable-typed variable — otherwise disposal throws SC2020 at declaration runtime. This is why using db.prepareRaw(...) is impossible in-subset: Stmt<TRow> is an interface; call finalize() (or [Symbol.dispose]()) explicitly instead.

Errors

All SQLite failures throw SqliteError with code = the SQLite result name ("SQLITE_CONSTRAINT_NOTNULL", "SQLITE_BUSY", ...) and message = sqlite3_errmsg at throw time. Schema failures use code "VALIDATION"; bind misuse "BIND_PARAM"; statement-busy "STMT_BUSY".

Compile to ELF

ELF consumers import the ./elf export and compile the package's shipped JS statically with --npm-static:

import { Database } from "@cutils/sqlite/elf";

The shipped JS emits raw shim_* FFI calls; scriptc resolves them only when the consumer's own program carries the ambient signature-only declarations. Copy the shipped elf/shims.d.ts beside your entry and reference it:

/// <reference path="./shims.d.ts" />
import { Database } from "@cutils/sqlite/elf";

Then build with --npm-static plus the shipped FFI manifest:

cp node_modules/@cutils/sqlite/elf/shims.d.ts ./shims.d.ts
scriptc build app.ts --npm-static @cutils/sqlite \
  --ffi node_modules/@cutils/sqlite/ffi.json

ffi.json is a copy of vendor/manifest.json with libraries rewritten to the package-relative ./build/libsqlite3-shim.a (the tarball ships the archive). Do not pass vendor/manifest.json — its ../build/... path only resolves inside this repo.

macOS, Windows and cross-compiles

The archive in the tarball is built on Linux. For any other target, build one for it with the shipped script (SQLite is compiled with FTS5) and point --ffi at the manifest it writes:

node node_modules/@cutils/sqlite/scripts/build-shim.mjs --out ./shim
scriptc build app.ts --npm-static @cutils/sqlite --ffi ./shim/ffi.json

# cross-compile with zig as the toolchain
CC="zig cc" AR="zig ar" node node_modules/@cutils/sqlite/scripts/build-shim.mjs \
  --out ./shim-win --target x86_64-windows-gnu

Binary size

Indicative scriptc build sizes for a scriptc app (your app's size varies — no perf claims until the bench cell lands):

Build Approx. added size
hello world (scriptc only) ~600 KB
+ @cutils/sqlite (-O2, amalgamation + shim) ~1.6 MB

Better-sqlite3 users: what changed

  • Same shape: prepare/run/get/all, transaction(), pragma, positional + named params, lastInsertRowid/changes.
  • get() returns TRow | undefined like better-sqlite3 — the one documented divergence from the cutils | null rule. getOrNull() is the | null twin.
  • Validation is first-class: prepare(sql, schema) runs any Standard-Schema validator on params AND rows; prepareRaw is the fast path.
  • Results are plain records; INTEGER columns return number when safe, bigint otherwise; BLOB returns Uint8Array; booleans bind as 0/1.
  • No threads, no async, no serialize/parallelize, no extensions (SQLITE_OMIT_LOAD_EXTENSION), no user functions/aggregates — synchronous embedded SQL only.

Node usage

The npm package ships dist/esm. Under Node the emitted shim_* calls resolve on globalThis — an official koffi binding loads build/libcutils_sqlite.so for you:

# one-time, inside the installed package (needs a C toolchain):
npm run build:shared --prefix node_modules/@cutils/sqlite
import "koffi"; // ensure the optional peer is installed
import "@cutils/sqlite/node-binding"; // installs the shim_* globals
import { Database } from "@cutils/sqlite";

koffi is an optional peer dependency — install it when you run under Node; scriptc builds never see it. The .so is linked -Wl,-Bsymbolic so the vendored amalgamation's sqlite3_* symbols can't interpose against a host's sqlite — this is load-bearing, Node ≥ 22 exports its own sqlite3 symbols.

Node ≥ 22 is the runtime floor; the test suite also runs there (no using-syntax — dispose paths are exercised via [Symbol.dispose]()).

Subset quirks

The package is authored in the scriptc compilable subset:

  • get() returning | undefined is the only undefined union in cutils — getOrNull() exists for subset-strict consumers. See docs/subset-quirks.md.
  • schema params are Standard-Schema classes, not interfaces — interface-typed fields erase under the subset; the seam re-declares ~standard as a StdProps class.

See docs/integration.md, docs/schema.md, docs/subset-quirks.md, and examples/todo-app/ for a worked chttp + sqlite app.