@silgi/db

The first-party db module. Add it to modules and set its options under the db config key. SQL through one small API: every ${value} of a tagged query is a bound parameter, never text. No query builder.

// silgi.config.ts
export default defineConfig({
  modules: ["@silgi/db"],
  db: {
    connections: {
      default: { connector: "bun-sql", url: { runtimeConfig: "databaseUrl" } },
      cache: { connector: "bun-sqlite", path: ".data/cache.sqlite" },
    },
  },
});

default

The module, as defineModule({ meta: { name: "db", configKey: "db" } }). Its setup checks the options (a wrong value is a build error naming the key), gives default the preset's own database when none is configured (bun-sqlite at .data/db.sqlite on bun, static and under silgi dev, node-sqlite at the same path on node, d1 with binding DB on cloudflare), refuses @silgi/db/server in client code, closes every open connection when the server drains, and fails the build when a connection's connector cannot run on the preset (bun-sql and bun-sqlite run on bun and static, node-sqlite on node, d1 on cloudflare, client on every preset) or when a d1 binding has no d1_databases entry in the app's wrangler config: db.connections.default uses the d1 binding "DB", which the app's wrangler config does not declare; add a d1_databases entry with binding "DB".

DbConfig, Connection

type Connection = (
  | { connector: "bun-sql"; url?: string | { runtimeConfig: string }; dialect?: "postgres" | "mysql" | "sqlite" }
  | { connector: "bun-sqlite"; path: string }
  | { connector: "node-sqlite"; path: string }
  | { connector: "d1"; binding: string }
  | { connector: "client"; dialect: "postgres" | "mysql" | "sqlite"; client: string }
) & { dev?: Connection };

dev replaces the connection under silgi dev and in the prerender of every preset, so a Postgres app develops on a SQLite file and a D1 app prerenders without Cloudflare; the build still bundles the connection's own connector. dev has no dev of its own (db.connections.default.dev.dev is not allowed; a dev connection has no dev of its own). Migrations, silgi db and useDatabase() under silgi dev all run on the dev connection; a node-sqlite dev runs as bun-sqlite. dev must be bun-sql, bun-sqlite, node-sqlite or client, since it runs on Bun. Before a build's prerender the migration files are applied to each dev connection, whatever db.migrate says; db.migrate: "build" still applies them to the connection itself. The production server bundle holds no dev connection.

bun-sql is new Bun.SQL(url): Postgres, MySQL, MariaDB or SQLite. Without url it reads Bun.SQL's own env default (DATABASE_URL); { runtimeConfig: "databaseUrl" } reads runtimeConfig.databaseUrl when the connection opens, and a literal URL with a password logs a build warning. dialect comes from a literal URL's scheme, else "postgres". bun-sqlite is bun:sqlite on path (against the working directory, its directory created, WAL); ":memory:" is allowed. node-sqlite is DatabaseSync of node:sqlite (Node ≥ 24) on path, the same way; under silgi dev it runs as bun-sqlite on the same file. d1 is the D1 binding env[binding] of cloudflare:workers; under silgi dev its first query fails db.connections.default uses d1, which silgi dev reaches only through the app's wrangler (C-669); set db.connections.default.dev to a bun-sqlite connection. client runs the app's own driver from the file client names (see DatabaseClient).

DatabaseClient

interface DatabaseClient {
  query(text: string, params: unknown[]): Promise<{ rows: Record<string, unknown>[]; changes?: number; lastInsertId?: number | string }>;
  reserve?(): Promise<{ query: DatabaseClient["query"]; release(error?: Error): void | Promise<void> }>;
  end?(): Promise<void>;
}

The shape a client connection's file adapts its driver (pg, mysql2 or any other) to, so Silgi depends on none of them. client is a path relative to the root, its default export defineDatabaseClient(() => client); the build bundles it into a chunk imported at the connection's first query and fails when it does not resolve: Cannot find database client "./server/db/x.ts" for db.connections.analytics. The factory runs once per process, at the first query; close() (and the server's drain) calls end(). query gets the text with the dialect's placeholders ($1 … $n on postgres, ? on mysql and sqlite) and the values in order, and returns the rows, the rows written (changes, 0 when left out) and the inserted id (read on mysql and sqlite). exec sends mysql and sqlite text one statement at a time, split at ; outside quotes and comments, and postgres text whole. transaction, batch and migrations run on a reserve()d connection (BEGIN, START TRANSACTION on mysql, BEGIN IMMEDIATE on sqlite), since a BEGIN sent to a pool lands on any connection; without reserve they throw transaction needs a client with reserve(); the client of db.connections.analytics has none (migrations need … for a migration run). release(error) gets the error that left the connection in an unknown state (a failed ROLLBACK, a failed migration run), so the pool drops it: pg's release(error), mysql2's destroy(). With mysql2, bind values through execute, a prepared statement, and pass a Uint8Array as a Buffer: its query escapes values into the text, where a ? inside quotes takes one. A factory whose client has no query fails once and is not called again.

Database, Query, Transaction

interface Database {
  readonly name: string;
  readonly dialect: "sqlite" | "postgres" | "mysql";
  sql<T = Record<string, unknown>>(strings: TemplateStringsArray, ...values: SqlValue[]): Query<T[]>;
  first<T = Record<string, unknown>>(strings: TemplateStringsArray, ...values: SqlValue[]): Query<T | undefined>;
  run(strings: TemplateStringsArray, ...values: SqlValue[]): Query<{ changes: number; lastInsertId?: number | string }>;
  exec(text: string): Promise<void>;
  batch(queries: Query<unknown>[]): Promise<unknown[]>;
  transaction<R>(fn: (tx: Transaction) => Promise<R>): Promise<R>;
  close(): Promise<void>;
}
type Transaction = Omit<Database, "transaction" | "close">;
interface Query<R> extends PromiseLike<R> {
  readonly text: string;
  readonly values: readonly SqlValue[];
}
type SqlValue = string | number | bigint | boolean | null | Date | Uint8Array | readonly SqlValue[];

A Query runs when it is first awaited, once; text holds the dialect's placeholders ($1 on postgres, ? on sqlite and mysql). first adds no LIMIT. run gives the rows written and the last inserted id (none on postgres; use returning). exec runs text with no parameters, the one place SQL is built from strings. batch runs its queries in order as one atomic unit and resolves to their results in order: D1's batch (one round trip) on d1, a transaction on the others. transaction commits when fn resolves and rolls back and rethrows when it throws; on SQLite it holds the process's one connection, so other statements wait. Calling db.sql (not tx.sql) inside it throws db.sql was called inside db.transaction; use the tx argument, and db.transaction throws transaction cannot be nested; use the tx argument. On postgres a failed statement aborts the transaction: when fn catches it, transaction rejects transaction was rolled back: a statement in it failed, which aborts a postgres transaction; let the error end the transaction function, or run the statement in tx.batch. D1 has no interactive transactions: on d1, transaction rejects transaction is not available on the d1 connector: D1 has no interactive transactions; use batch([...]).

Values: a boolean binds as 1/0 on sqlite and mysql, a Date as ISO text on sqlite, an array only on postgres (= any(${ids})). undefined, NaN and plain objects are refused before the query is sent: db.sql value 2 is undefined; pass null.

Migrations

server/db/migrations/<number>_<name>.sql (0001_init.sql) belongs to default, server/db/migrations/<connection>/ to another connection; each layer's folder joins the app's, and a module adds a folder in the build hook db:migrations:dirs(dirs: Array<{ connection: string; dir: string }>). Files apply once each, in number order, each in its own transaction (on MySQL statement by statement, as MySQL commits DDL), and are recorded in _silgi_migrations(id, name, checksum, applied_at) with the SHA-256 of the file (CRLF read as LF). A run holds one connection and a lock (each file's BEGIN IMMEDIATE on SQLite, an advisory lock on Postgres, GET_LOCK on MySQL), so two instances starting at once apply each file once; a lock not obtained in 60 s fails Migrations of "default" waited 60 s for another instance's lock.

A failing file stops the run and keeps the earlier ones: Migration 0002_add_tags.sql failed: <driver message>. An applied file whose content changed fails before anything runs: Migration 0001_init.sql changed after it was applied. An applied file that is gone is a warning. 0002 and 00002 are one number; a pending file with the content of an applied one whose file is gone fails Migration 0003_add.sql was applied as 0002_add.sql; give it its old name back, and a pending file numbered below the highest applied one fails Migration 0002_mid.sql is numbered below 0003_add.sql, which is already applied; renumber it after 0003. A UTF-8 BOM is dropped. On MySQL a file runs one statement at a time, split at ; outside quotes and comments. A file name outside <number>_<name>.sql, two files with one number, or one file name in two folders fail the build.

Wrangler applies a d1 connection's migrations: a cloudflare build copies its files to .output/migrations/<connection>/ and sets migrations_dir on its d1_databases entry of .output/wrangler.json when the app's entry has none (a relative one the entry has is rewritten to resolve from .output/), for wrangler d1 migrations apply <database> --remote --config .output/wrangler.json. migrate: "build" or "start" with a d1 connection fails the build: db.connections.default uses d1, whose migrations wrangler applies; build writes them to .output/migrations/default.

The module adds the task db:migrate, payload { connection?: string }: await runTask("db:migrate") applies the pending files of every connection (or of one) on the server's connections, under the same locks, whatever migrate says, and resolves to the files it applied, { default: ["0002_add_tags.sql"] }; it skips d1 connections, and { connection } naming one rejects db.connections.default uses d1; run wrangler d1 migrations apply or list. A call while a run is pending joins it.

@silgi/db/server

useDatabase

function useDatabase(name?: DatabaseName | (string & {})): Database;

The connection name (default "default"), one per process, opened at its first query. A name the config does not have throws No database "analytics": set db.connections.analytics in silgi.config.ts; a query after close() rejects with Database "default" is closed.

defineDatabaseClient

function defineDatabaseClient(factory: () => DatabaseClient | Promise<DatabaseClient>): typeof factory;

The default export of a client connection's file:

// server/db/analytics-client.ts
import { Pool } from "pg";
import { defineDatabaseClient } from "@silgi/db/server";
import { ANALYTICS_URL } from "#silgi/env/private";

export default defineDatabaseClient(() => {
  const pool = new Pool({ connectionString: ANALYTICS_URL });
  const on = (q: Pick<Pool, "query">) => async (text: string, params: unknown[]) => {
    const result = await q.query(text, params);
    const last = Array.isArray(result) ? result.at(-1) : result; // several statements: one result each
    return { rows: last?.rows ?? [], changes: last?.rowCount ?? 0 };
  };
  return {
    query: on(pool),
    async reserve() {
      const connection = await pool.connect();
      return { query: on(connection), release: (error?: Error) => connection.release(error) };
    },
    end: () => pool.end(),
  };
});

A file that does not export it fails at the first query: The database client of db.connections.analytics must export default defineDatabaseClient(() => client); a client without query, or a query that resolves to no rows array, fails naming the connection too.

Row, DatabaseName, TableName, DefaultDatabase, SilgiDatabases

import type { Row } from "@silgi/db"; // also exported by "@silgi/db/server"

interface SilgiDatabases {} // typegen fills it: SilgiDatabases[<connection>] = { dialect, tables }
type DatabaseName = keyof SilgiDatabases; // string while typegen has written none
type DefaultDatabase = "default"; // any DatabaseName when the app has no "default" connection
type TableName<C extends DatabaseName = DefaultDatabase> = keyof SilgiDatabases[C]["tables"];
type Row<T extends TableName<C>, C extends DatabaseName = DefaultDatabase> = SilgiDatabases[C]["tables"][T];

silgi typegen and silgi build read each connection's migrations in order and write .silgi/types/db.d.ts: CREATE TABLE, ALTER TABLE … ADD [COLUMN], DROP COLUMN, RENAME TO and RENAME [COLUMN] … TO, and DROP TABLE are read; any other statement is ignored. db.sql<Row<"todos">>select * from todostypes the rows, and `useDatabase` autocompletes the configured connection names while accepting any `string`. A column without `NOT NULL` or `PRIMARY KEY` adds `| null`;serialand identity columns are not null. On SQLite aPRIMARY KEYcolumn is not null only asINTEGER PRIMARY KEYor in aSTRICTorWITHOUT ROWID` table.

A mysql bigint is number | string. An unknown type is unknown and logs one warning naming the connection, file, table and column: db "analytics": 0002_odd.sql: column "events"."mood" has type "citext", which typegen does not read; Row types it unknown. A CREATE TABLE … AS, a LIKE and an ALTER TABLE … MODIFY | CHANGE | ALTER COLUMN … TYPE | … NULL are not read and warn. The types are a reading of the migrations, not of the database; a table created another way has no type. No query builder and no inferred query result: the caller names the row type.

@silgi/db/cli

runDbCommand

function runDbCommand(args: DbCommand, silgi: SilgiContext): Promise<number>;
interface DbCommand {
  command: "migrate" | "status" | "new";
  name?: string;        // the migration name of `new`
  connection?: string;  // one connection instead of every one
  out(line: string): void;
}

The body of silgi db: the core CLI makes the context (the dev config, or the production one with --prod) and calls it, so the core holds no @silgi/db code.

A failure exits 1 with its message. migrate and status on a d1 connection print db.connections.default uses d1; run wrangler d1 migrations apply or list and go on. new takes a-z, 0-9 and _ only: Migration name "Add users!" may hold only a-z, 0-9 and _. Without the module: silgi db needs @silgi/db (bun add @silgi/db, then add it to modules).