@sigil-dev/plugin-sql

A database for Grimoire: opened once per worker, shared by every request, closed on shutdown — plus a contract for adapters, a conformance kit that proves one behaves, and an official adapter for Bun.SQL.

// sigil.config.ts
import { adapter } from "@sigil-dev/plugin-sql/bun";

export default {
  plugins: [adapter(process.env.DATABASE_URL!, { maxTotal: 20 })],
};

// a route
const users = await locals.db`select id, name from users where id = ${id}`;

// anything else on the server: hooks.server.ts, a websocket, a timer, lib/
import { database } from "@sigil-dev/plugin-sql";

export const byId = (id: number) =>
  database().sql<User>`select * from users where id = ${id}`;

Bun.SQL speaks postgres, mysql and sqlite from one class and infers the dialect from the DSN. That is why this package has no dialect parameter, no placeholder handling and no timestamp rewriting: those are the driver's job, and it already does them.

The connection may be one object, budget included, and a database the app can run without is optional:

adapter({ url: env.DB_URL, idleTimeout: 30, maxTotal: 12 })
adapter(env.USER_DB_URL, { key: "userDb", optional: true })  // hasDatabase("userDb")

max in the connection is refused (it is per worker; use maxTotal), and a missing URL fails the boot naming the database instead of connecting to localhost.

Rows are any unless you name them — db.sql<User> — as with Bun.SQL, so existing as Row[] casts keep compiling. A fragment is a full Promise.

Typing locals.db

// src/app.d.ts
import type { BunSqlHandle } from "@sigil-dev/plugin-sql/bun";
declare global {
  namespace App {
    interface Locals { db: BunSqlHandle }
  }
}
export {};

Per-request transactions

adapter(url, { maxTotal: 20, perRequestTx: true })

Every query made through locals.db during a request — the tagged template, locals.db.sql, unsafe(), a Drizzle builder — and through database() from code the request calls, runs in one transaction. A 5xx response rolls it back; anything else commits. db.transaction(fn) inside it is a savepoint (or joins, for an adapter without one), never a second connection. A write that must survive a rollback uses database().pool().

Outside a request

database(name = "db") returns the database registered under that name, looked up when a query runs, so it is safe at module scope. There, the accessors are the pool and transaction() opens a real transaction. The name is the locals key for adapter()/sql(), and the first argument of createDatabase(name, adapter), which returns the same thing as a value.

A script that runs without the server opens and closes it itself:

const db = createDatabase("db", bunSqlAdapter(url));
await db.open();
await db.sql`insert into users (name) values (${"ada"})`;
await db.close();

db.sql

A query that has been described but not run: building one needs no connection, awaiting one runs it — in the request's transaction if there is one. Fragments compose and every value stays a bound parameter. The driver picks the placeholders, so the same fragment runs on every dialect. .runTx() and .runPool() pick the side explicitly.

Called as a function, the tag is the driver's helper, built lazily too: ${db.sql(ids)} for an IN list, ${db.sql.array(xs, "text")} for = any(...), ${db.sql(row, "a", "b")} for an insert, ${db.sql(name)} for an escaped identifier.

hasDatabase(name) says whether an optional database was registered.

The contract

interface SqlAdapter<C, Tx = C> {
  contract: 1;                 // bump on a breaking change
  name: string;
  create(ctx: WorkerContext): C | Promise<C>;
  close(client: C): void | Promise<void>;
  transaction<R>(client: C, fn: (tx: Tx) => Promise<R>): Promise<R>;
  ping(client: C): Promise<void>;
  capabilities?: {
    query?(target, sql, values, strings): Promise<unknown[]>; // enables db.sql
    savepoint?(tx, fn): Promise<R>;                           // nested transactions
    setLocal?, listen?, instrument?
  };
}

contract is checked when the plugin is constructed, so an adapter written against a different version fails where you configured it rather than inside a transaction where the stack trace points somewhere else.

ping() runs at startup. A database unreachable at boot fails the server start, not the first request — a health check returning 200 while every query fails is worse than a deploy that stops.

The client opens once per worker — in services(), which runs under sigil dev and in each sigil start worker but never in the coordinator or sigil build — and is shared by every request on it. Routes reach it as locals.db, with nothing imported from the config file.

Connection budget

adapter(url, { maxTotal: 20 })                 // 5 per worker on 4 workers

maxTotal is deployment-wide, divided by the real worker count. Without that division every worker opens the full amount, and a 4-worker deployment quietly asks the database for 80 connections and gets refused. The count comes from workerContext(), which reads what the coordinator set at spawn.

Writing an adapter

import { defineSqlAdapter, sql } from "@sigil-dev/plugin-sql";

const myAdapter = defineSqlAdapter<MyClient>({
  contract: 1,
  name: "my-driver",
  create: (ctx) => connect({ pool: Math.ceil(20 / ctx.workers) }),
  close: (db) => db.end(),
  transaction: (db, fn) => db.tx(fn),
  ping: async (db) => { await db.ping(); },
});

Then prove it:

import { runAdapterConformance } from "@sigil-dev/plugin-sql/testing";

runAdapterConformance(myAdapter, {
  exec: (db, q) => db.exec(q),
  count: async (db, t) => Number((await db.query(`select count(*) n from ${t}`))[0].n),
});

The kit checks that transaction commits on success, rolls back and rethrows the same error object on failure, returns the callback's result, and that close releases the client. No type can check any of that, and a transaction that silently does not roll back passes every type check.

Pass isolation: false for a single-connection driver. SQLite through Bun.SQL genuinely has no isolation — measured, in transaction 2 rows / outside 2 rows, against postgres 2 / 1 — so the kit skips that assertion rather than asserting something untrue.

What this replaced

This package previously exported bunSqlite / bunPg / bunMysql and a SqlExecutor with query(sql, params). That layer existed to bridge Bun.sql's positional-parameter API, and every bug in it came from guessing a dialect out of a string: sqlite://app.db became the absolute path //app.db, postgres and mysql were assumed to share a placeholder style when they are opposites, and toISOString() timestamps were rejected by MySQL. It also needed a per-dialect placeholder rewriter, and a timestamp normaliser, both of which Bun.SQL makes unnecessary.

Drizzle

Drizzle wraps a driver rather than replacing one, so the adapter creates Bun.SQL, hands it to drizzle(), and gives you back the Drizzle instance. It is about ten lines in user land, which is why it lives here instead of in a package.

// sigil.config.ts
import { SQL } from "bun";
import { drizzle } from "drizzle-orm/bun-sql";
import { sql as q } from "drizzle-orm";
import { defineSqlAdapter, sql } from "@sigil-dev/plugin-sql";
import * as schema from "./src/db/schema";

// Named, because `Tx` is not re-inferred from a bare `db.transaction(fn)`:
// the callback parameter is contravariant, so TypeScript will not solve for
// it there and falls back to `Tx = C`, which is the unsound version. Naming
// the transaction type is what makes the cast unnecessary.
type DrizzleDb = ReturnType<typeof drizzle<typeof schema>>;
type DrizzleTx = Parameters<Parameters<DrizzleDb["transaction"]>[0]>[0];

const drizzleAdapter = defineSqlAdapter({
	contract: 1,
	name: "drizzle-bun-sql",
	create: ({ workers }) =>
		drizzle({
			client: new SQL({
				url: Bun.env.DATABASE_URL!,
				// 20 is a deployment total, not a per-worker count.
				max: Math.ceil(20 / workers),
			}),
			schema,
		}),
	close: (db) => db.$client.close(),
	transaction: <R>(db: DrizzleDb, fn: (tx: DrizzleTx) => Promise<R>) =>
		db.transaction(fn),
	ping: async (db) => {
		await db.execute(q`select 1`);
	},
	capabilities: {
		// db.transaction() inside a transaction becomes a savepoint.
		savepoint: (tx, fn) => tx.transaction(fn),
	},
});

export default {
	plugins: [sql(drizzleAdapter)],
};

Then in a route:

export const load = async ({ locals }) =>
	locals.db.query.users.findMany({ with: { posts: true } });

tx is not the database

Drizzle's transaction handle has no $client and no batch, so typing the callback's parameter as the client type is false:

transaction: (db, fn) => db.transaction(fn as any)   // what you had to write

That cast is why SqlAdapter has two type parameters:

export interface SqlAdapter<C, Tx = C> { /* ... */ }

Tx defaults to C, which is right for a raw driver — Bun.SQL's transaction handle is a Bun.SQL. For anything that wraps a driver it is wrong, and Kysely has the same shape. With Tx inferred properly, the cast disappears and locals.db is typed as whatever a route actually receives.

Two Drizzle facts, measured

Both against drizzle-orm 0.45.3 with a real Bun.SQL on real MariaDB and real postgres:

• db.execute does not bind parameters. It takes a SQLWrapper; a second argument is silently ignored, so ? reaches the server as literal SQL. The type checker catches it too — "Expected 1 arguments, but got 2."
• db.execute(qSELECT ${n}) is not portable. The bun-sql driver emits $1 placeholders unconditionally, so the identical call on the identical object works on postgres and fails on MySQL with "Unknown column '$1'".

Neither affects the recipe above, because Drizzle's tagged templates are how you write queries and the plugin only ever calls ping. They do mean a Drizzle adapter should not try to expose raw query(sql, params) — which is why runAdapterConformance takes hooks:

runAdapterConformance(drizzleAdapter, {
	exec: async (db, s) => {
		await db.execute(q.raw(s));
	},
	count: async (db, t) => {
		const rows = (await db.execute(
			q.raw(`select count(*) as n from ${t}`),
		)) as Array<{ n: number | string }>;
		return Number(rows[0]?.n ?? 0);
	},
});

The hook parameters are C | Tx, because the kit calls them with both: it runs exec inside adapter.transaction to prove rollback works, and outside it to set up. The row-indexing in count is deliberate — drizzle's execute return shape has changed between drivers and releases, and it is the kind of thing a conformance run catches and a mock never will.

License

MIT