WRNexusJS/.publish/db/README.md

@wrnexus/db

The database layer for WrNexus: TS models as the single source of truth for DDL, validation, and result typing, plus a driver-based Db client, migrations, and a sqlc-style query generator.

Part of the WrNexus framework — an SSR-first, Bun-native full-stack web framework.

Overview

@wrnexus/db is the server-side data layer. You describe tables as TypeScript models (the v column builder + table()); those models drive migrations, coerce raw DB rows into typed objects, and feed the query generator. A thin Driver interface is implemented by adapters for SQLite (bun:sqlite), Postgres/MySQL (Bun.SQL), and MongoDB. The Db client adds ergonomics — model-mapped all/one, transactions, createTable, pagination, and batched relation loading. A process-wide registry (getDb/setDb) exposes configured connections to pages and API routes. Reach for it whenever a WrNexus app needs persistence.

Installation

bun add @wrnexus/db

Private package — the machine must be authenticated to the wrnexus npm org (a read token in ~/.npmrc). Requires Bun (Node is not supported).

API

The core entry (@wrnexus/db) is dependency-free; adapters and connectors live in subpaths so importing the core doesn't pull in every driver.

Subpath	Exports
@wrnexus/db	v, table, Column, createDb, createTableSql, the client registry (setDb/getDb/…), migrations, the query generator, and query helpers
@wrnexus/db/connect	connectFromConfig, resolveDbUrl, DbConfig — resolve a config to a live SQL Db
@wrnexus/db/session	sqliteSessionStore — a bun:sqlite session backend for @wrnexus/core
@wrnexus/db/sqlite	sqlite(url?) driver
@wrnexus/db/postgres	postgres(url) driver
@wrnexus/db/mysql	mysql(url) driver
@wrnexus/db/mongo	mongo(url, dbName?) document API

Schema — v, table, Column

table(name, columns) returns a Model<T>. Columns are built with v:

import { v, table } from "@wrnexus/db";

const users = table("users", {
  id: v.id(), // auto-increment primary key
  email: v.text().unique(),
  name: v.text().optional(), // NULLable
  age: v.int().default(0),
  active: v.bool().default(true),
  createdAt: v.timestamp().default("now"), // CURRENT_TIMESTAMP
});

Column builders: v.id, v.text (alias v.string), v.int, v.real (alias v.number), v.bool (alias v.boolean), v.timestamp, v.json. BaseType values are "id" | "text" | "int" | "real" | "bool" | "timestamp" | "json".

Column modifiers (chainable): .optional(), .unique(), .default(value) (use the sentinel "now" for a current-timestamp default), .primaryKey(), .references(table, column = "id"). .coerce(raw) converts a raw DB value to its JS type.

A Model<T> exposes: name, columns, parse(row) (coerces a raw row into a typed T; unknown columns pass through), and describe() (returns each column's ColumnDef, for migrations and the generator).

Driver & client — createDb, Db, Driver

createDb(driver: Driver): Db

A Driver (implemented by adapters) exposes dialect, query(sql, params?), exec(sql, params?), transaction(fn), and close(). createDb wraps it in a Db:

• all<T>(sql, params?, model?) — all rows, mapped through model.parse when a model is given.
• one<T>(sql, params?, model?) — first row or null.
• exec(sql, params?) — Promise<ExecResult> ({ changes, lastInsertId? }).
• tx(fn) — run fn(db) in a transaction; rolls back on throw. Nested tx reuses the current transaction.
• createTable(model) — runs the model's CREATE TABLE IF NOT EXISTS DDL.
• close().

Every query is parameterized (positional params). createTableSql(model, dialect, ifNotExists?) renders CREATE TABLE directly; Dialect is "sqlite" | "postgres" | "mysql".

Client registry — getDb / setDb

A process-wide registry the runtime configures at startup from wrnexus.config.ts (the db setting is the default; databases.<name> entries are named).

• setDb(db) / setDb(name, db) — set the default or a named connection.
• registerDb(name, db) — alias of setDb(name, db).
• getDb(name = "default") — the default or a named Db (throws if unconfigured).
• hasDb(name?), databaseNames(), closeDatabases().

const users = await getDb().all("SELECT * FROM users");
const events = await getDb("analytics").all("SELECT * FROM hits");

Adapters

• @wrnexus/db/sqlite — sqlite(url = ":memory:"). url may be file:./dev.db, a raw path, or :memory:. Built on bun:sqlite; no external service.
• @wrnexus/db/postgres — postgres(url) (e.g. postgres://user:pass@host:5432/db, placeholders $N).
• @wrnexus/db/mysql — mysql(url) (e.g. mysql://user:pass@host:3306/db, placeholders ?). Postgres/MySQL both use Bun's native Bun.SQL client and its pooled begin() for transactions.
• @wrnexus/db/mongo — mongo(url, dbName?). A document API, not SQL: db.collection(model) returns a MongoRepo<T> with find, findOne, insert, insertMany, update, delete, count. Reads are coerced through model.parse (_id is mapped to id). The mongodb driver is imported lazily — install it to use Mongo.

Migrations

Migrations are .sql files (in e.g. app/db/migrations), each split into -- +up and -- +down sections. A file with no markers is treated entirely as up. Applied names are recorded in a _wire_migrations table so each runs once.

• parseMigration(name, content) → Migration ({ name, up, down }).
• loadMigrations(dir) — parse all .sql files, sorted by filename.
• appliedMigrations(db) — applied names, oldest first.
• migrate(db, dir) — apply all pending (each in a transaction); returns applied names.
• rollback(db, dir) — roll back the most recent; returns its name or null.
• status(db, dir) — { name, applied }[] for every migration file.
• scaffoldMigration(dir, name, dialect, models?) — write a new numbered migration; with models it generates CREATE/DROP for every table (referenced tables first via topological sort). Returns the file path.

Query generator (sqlc-style)

Turns annotated SQL into typed TS functions; params and result types are inferred from the models, and rows map back through model.parse when the selected columns are model columns.

• parseQueries(content) → QueryDef[] from -- name: X :one|:many|:exec blocks.
• generateQueriesFile(queries, models, dialect) → the queries.gen.ts source. models is a ModelRef[] ({ varName, model }). Rewrites :name placeholders to positional ($N/?) form.

QueryKind is "one" | "many" | "exec".

Query helpers

• paginate(db, { sql, params?, countSql?, model? }, opts?) — offset pagination. Pass the base SELECT without a LIMIT; it appends the page window and derives total via a COUNT subquery. PageOptions: { page?, perPage?, maxPerPage? } (defaults page 1, perPage 20, maxPerPage 100). Returns Paginated<T> (items, page, perPage, total, totalPages, hasNext, hasPrev).
• loadRelated(db, parents, opts) — load a relation for many parents in ONE query and attach it (no N+1). RelationOptions: { table, foreignKey, as, localKey?, single?, model? } — single: true attaches one child (belongsTo), otherwise an array (hasMany). Table/foreign-key names are validated as identifiers.

Session store

@wrnexus/db/session exports sqliteSessionStore(path = "sessions.db"), a persistent, process-shared SessionBackend (from @wrnexus/core) backed by bun:sqlite (WAL mode). Sessions survive restarts and are shared by every worker on the same file.

Usage

Define models, connect, create tables, and query with typed results:

import { v, table, createDb } from "@wrnexus/db";
import { sqlite } from "@wrnexus/db/sqlite";

const users = table<{ id: number; email: string; name: string | null }>("users", {
  id: v.id(),
  email: v.text().unique(),
  name: v.text().optional(),
  createdAt: v.timestamp().default("now"),
});

const db = createDb(sqlite("file:./dev.db"));
await db.createTable(users);

await db.exec("INSERT INTO users (email) VALUES (?)", ["a@b.com"]);
const list = await db.all("SELECT * FROM users", [], users); // rows typed + coerced
const one = await db.one("SELECT * FROM users WHERE id = ?", [1], users);

await db.tx(async (tx) => {
  await tx.exec("UPDATE users SET name = ? WHERE id = ?", ["Ada", 1]);
});

Resolve a config to a live SQL Db, and register it:

import { connectFromConfig } from "@wrnexus/db/connect";
import { setDb, getDb } from "@wrnexus/db";

setDb(connectFromConfig({ driver: "sqlite", url: "file:./dev.db" }, process.cwd()));
const rows = await getDb().all("SELECT * FROM users");

Run migrations and paginate:

import { migrate, paginate } from "@wrnexus/db";

await migrate(db, "app/db/migrations");
const pageTwo = await paginate(
  db,
  { sql: "SELECT * FROM users ORDER BY id", model: users },
  { page: 2 },
);

MongoDB (document API):

import { mongo } from "@wrnexus/db/mongo";

const mdb = await mongo(process.env.MONGO_URL!, "app");
const repo = mdb.collection(users);
await repo.insert({ email: "a@b.com" });
const active = await repo.find({ active: true });

Configuration

connectFromConfig (and the runtime) read a DbConfig ({ driver, url }) where driver is sqlite | postgres | mysql. resolveDbUrl(url, appRoot?) resolves a relative file:/sqlite: URL against the app root. MongoDB is not a SQL driver — use @wrnexus/db/mongo directly.

Requirements / Notes

• Bun-only. Uses bun:sqlite (SQLite adapter + session store) and Bun.SQL (Postgres/MySQL). Migrations/scaffolding use node:fs/node:path.
• Works with @wrnexus/core — sqliteSessionStore implements its SessionBackend; getDb/setDb are wired by the WrNexus runtime from wrnexus.config.ts.
• The mongodb npm package is an optional, lazily-imported peer — install it only if you use @wrnexus/db/mongo. The core package stays dependency-free.