313 lines
14 KiB
Markdown
313 lines
14 KiB
Markdown
# @wrnexus/db
|
|
|
|
## Rollout-safe migrations
|
|
|
|
Run `wrnexus db check` in CI before deployment. The analyzer reports stable
|
|
diagnostics for drops, renames, type changes, new/enforced required columns,
|
|
and potentially blocking index creation, with an expand/backfill/switch/contract
|
|
recommendation. `wrnexus db migrate` blocks critical issues in pending
|
|
migrations. `--allow-breaking` is an explicit operator override; already-applied
|
|
migrations do not block later releases.
|
|
|
|
> 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
|
|
|
|
```bash
|
|
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`:
|
|
|
|
```ts
|
|
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`
|
|
|
|
```ts
|
|
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()` — idempotently rejects new top-level work, drains active queries and
|
|
transactions, then closes the underlying pool.
|
|
|
|
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()`. Registry shutdown clears
|
|
registrations first, attempts every open database, and reports close failures
|
|
together with `AggregateError` instead of leaking later pools.
|
|
|
|
```ts
|
|
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 `_wrn_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, options?)` — apply all pending (each in a transaction); returns applied names.
|
|
- `rollback(db, dir, options?)` — 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:
|
|
|
|
```ts
|
|
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:
|
|
|
|
```ts
|
|
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:
|
|
|
|
```ts
|
|
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 },
|
|
);
|
|
```
|
|
|
|
For deployments, `{ dryRun: true }` reports pending names without applying
|
|
their SQL, `signal` cancels safely between migrations, and the default
|
|
database-backed lock prevents concurrent deploy runners. A live lock produces
|
|
`WRN-DB-MIGRATION-LOCKED`; crash-stale locks expire after `lockTimeoutMs` (five
|
|
minutes by default). Disable it with `lock: false` only when an external deploy
|
|
coordinator already guarantees exclusivity.
|
|
|
|
```ts
|
|
const pending = await migrate(db, "app/db/migrations", { dryRun: true });
|
|
await migrate(db, "app/db/migrations", {
|
|
signal: shutdownController.signal,
|
|
lockTimeoutMs: 10 * 60_000,
|
|
});
|
|
```
|
|
|
|
MongoDB (document API):
|
|
|
|
```ts
|
|
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 connected 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.
|
|
|
|
## Repository and transaction helpers
|
|
|
|
Repositories accept an immutable equality `scope`, normally `{ column: "tenant_id", value:
|
|
ctx.tenant.id }`. The scope is injected into every read, count, update and delete, while create
|
|
overwrites any caller-supplied tenant value. This makes accidental cross-tenant CRUD through the
|
|
repository API fail closed.
|
|
|
|
```ts
|
|
import { createRepository, retryTransaction, databaseHealth, batch } from "@wrnexus/db";
|
|
|
|
const users = createRepository<User>(db, {
|
|
table: "users",
|
|
allowedColumns: ["email", "name", "active"],
|
|
});
|
|
|
|
const user = await users.require(42);
|
|
await users.update(42, { active: true });
|
|
```
|
|
|
|
Repository identifiers are validated, writes may be restricted to an allowlist, and values always use query parameters. Infrastructure packages remain helper-only and do not add UI dependencies to server code.
|
|
|
|
## 0.8 repository and transaction helpers
|
|
|
|
```ts
|
|
import { createRepository, databaseHealth, firstOrThrow, retryTransaction } from "@wrnexus/db";
|
|
|
|
const usersRepo = createRepository<User>(db, {
|
|
table: "users",
|
|
allowedColumns: ["email", "name", "active"],
|
|
maxListLimit: 250,
|
|
});
|
|
|
|
const users = await usersRepo.all({
|
|
orderBy: "name",
|
|
direction: "asc",
|
|
limit: 50,
|
|
offset: 0,
|
|
});
|
|
```
|
|
|
|
Repository SQL identifiers are validated and values remain parameterized. Placeholder generation is dialect-aware: PostgreSQL uses `$1`, `$2`, and SQLite/MySQL use `?`. List limits are bounded.
|
|
|
|
`retryTransaction()` retries recognized serialization, deadlock, and database-lock errors by default. Supply `shouldRetry` for application-specific retryable errors; ordinary validation or business errors are not retried automatically.
|
|
|
|
## Reusable seed helpers
|
|
|
|
```ts
|
|
import { addSeedData, removeSeedData, runSeedQuery, getDb } from "@wrnexus/db";
|
|
|
|
await addSeedData(getDb, [{ code: "free", credits: 100 }], "plans", { conflict: "ignore" });
|
|
await removeSeedData(getDb(), { code: "legacy" }, "plans");
|
|
await runSeedQuery(getDb(), "UPDATE plans SET credits = ? WHERE code = ?", [200, "free"]);
|
|
```
|
|
|
|
Table and column identifiers are validated, values are always parameterized, and empty bulk
|
|
deletes are refused unless `{ all: true }` is explicit.
|