Databases

A page that needs a database should not import a driver in a route. It should reach one through locals, the same as anything else the framework puts there. @sigil-dev/plugin-sql opens the client for you, once per worker, divides the connection budget across those workers, and closes it on shutdown.

bun add @sigil-dev/plugin-sql

#The quick version

import { defineConfig } from "@sigil-dev/grimoire";
import { adapter } from "@sigil-dev/plugin-sql/bun";

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

One import, one call. The adapters live behind their own subpath so a postgres adapter can be added without pulling bun:sqlite into a Deno or Worker build, and the contract stays free of every driver:

import { sql, defineSqlAdapter } from "@sigil-dev/plugin-sql"; // the contract
import { adapter } from "@sigil-dev/plugin-sql/bun";            // one driver

If you need the adapter itself — to name a key, to wrap it in your own plugin, or to hand it to runAdapterConformance — bunSqlAdapter is still there, and adapter is exactly sql(bunSqlAdapter(...)) rather than a second implementation of the lifecycle.

maxTotal is a total for the deployment, not a per-worker number. On four workers each opens five. This is the part that is easy to get wrong by hand: write new SQL({ max: 20 }) inside a plugin and you have asked the database for eighty.

Measured against a real postgres, with GRIMOIRE_WORKER_COUNT=4:

maxTotal can sit in the connection object beside the driver's options, so one object is the whole database. max there is refused: it is Bun.SQL's per-client size, so it would go to the driver in every worker — the old silent 4× — and the error says to use maxTotal.

A missing URL fails the boot and names the database. Bun.SQL with no url connects to localhost as the OS user, and the error that eventually surfaces is about a password. For a database the app can run without, say so:

adapter(Bun.env.USER_DB_URL, { key: "userDb", optional: true })

Without the URL that registers nothing, and code asks hasDatabase("userDb").

export const load = async ({ locals }) => {
	const users = await locals.db`select id, name from users limit 10`;
	return { users };
};

locals.db behaves like the client, so the tagged-template form works directly. Nothing is imported from sigil.config.ts in your routes, which is the point.

To have it typed, declare it once:

import type { BunSqlHandle } from "@sigil-dev/plugin-sql/bun";

declare global {
	namespace App {
		interface Locals {
			db: BunSqlHandle; // Bun.SQL, plus the `sql` tag
		}
	}
}
export {};

For any other adapter it is SqlHandle<YourClient> from @sigil-dev/plugin-sql.

Code that is not a route — hooks.server.ts, a websocket handler, a timer, a shared module in src/lib/server — reaches the same database with database(). See Outside a request.

#What it does for you

It opens where requests are served, and nowhere else. The plugin object is built when sigil.config.ts is imported, which the CLI and sigil build also do, so it does not open there. It opens in its services() hook, which runs in sigil dev and in each sigil start worker — before hooks.server.ts init(), so init can use it.

It fails at startup. After opening, the plugin pings. A database you cannot reach is a configuration problem, and finding it out from a request that already returned 200 is worse than a deploy that stops.

It divides the budget. Each worker receives its real position in the deployment, so maxTotal is what you wrote and not that number times the worker count.

It does not open in the coordinator. sigil start runs a plugin's config() in the coordinator as well as in each worker, and the coordinator serves no requests. An earlier version opened in config(), which produced a pool that was never used and never freed, and on a database already close to its connection limit it crash-looped the worker with "remaining connection slots are reserved for roles with the SUPERUSER attribute". The coordinator never calls services(), so it never opens.

It keeps its connections — so do not set idleTimeout. The pool opens at startup and lives in the worker for its whole life, which is what makes a request cheap. Bun.SQL's idleTimeout closes idle connections, and on a quiet site that means the first page after every lull reopens several at once: measured on a real app, six parallel queries took 207ms after 35s idle against 26ms with the pool kept. Leave it unset unless something between you and the database drops idle sockets.

It is not worth moving the connection to the coordinator. The coordinator is a separate process that proxies HTTP; a socket cannot be shared across processes, so every query would become an extra hop through one process for every worker. The worker already owns a pool that opens at boot — keeping it open is the whole fix.

It closes. On shutdown, in the worker that opened it. A worker cannot close a client the coordinator owns, and the reverse is worse.

It keeps working when the database is gone. A failed ping still closes the client, because a half-open one still holds a socket.

Other plugins can use this database. A plugin reaches it by name with database(), so it spends the same budget instead of opening a second pool — and the order of the plugins array does not matter. plugin-jobs does this by default:

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

export default {
  plugins: [
    adapter(Bun.env.DATABASE_URL, { maxTotal: 12 }),
    jobs({ handlers: { "send-welcome": async (p: { email: string }) => { /* … */ } } }),
  ],
};

Because the queue writes through the database's current scope, a job enqueued in a perRequestTx request is part of that request's transaction: an order that rolls back takes its confirmation email with it.

#Two type parameters (only if you are writing an adapter)

Skip this if you are using bunSqlAdapter — the default is right for a raw driver and there is nothing to name. It matters when your client is a wrapper, which is the Drizzle case below.

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

Tx is what a route receives inside a transaction. It defaults to C, which is right for a raw driver — Bun.SQL's transaction handle is a Bun.SQL — and wrong for anything that wraps a driver, because those hand out a different type inside the transaction. Drizzle and Kysely both do.

// Wrong, and it only compiles with a cast.
transaction: (db, fn) => db.transaction(fn as any)

With Tx named properly the cast goes away. TxOf<typeof adapter> and ClientOf<typeof adapter> read them back when you need to name them elsewhere.

#Recipes

#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. Drizzle is not a dependency of this plugin — bring your own:

bun add drizzle-orm

import { defineConfig } from "@sigil-dev/grimoire";
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";

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 across the deployment, not per worker. Dividing here is
				// the adapter's job — `bunSqlAdapter` does it from
				// `maxTotal`, and an adapter that skips it opens
				// `max * workers` connections and never says so.
				max: Math.ceil(20 / Math.max(1, 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 defineConfig({
	plugins: [sql(drizzleAdapter)],
});

Then a route is an ordinary Drizzle query:

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

With perRequestTx, that query — and locals.db.insert(...), locals.db.execute(...), any builder — runs in the request's transaction. locals.db resolves each property against the transaction when one is in scope, and against the database otherwise.

Two things about that recipe are worth knowing, because they are surprising and neither fails loudly.

tx is not the database. It has no $client and no batch, which is what the second type parameter is for.

Tx is not inferred from a bare db.transaction(fn). The callback parameter is contravariant, and TypeScript will not solve for it there, so it falls back to Tx = C — the unsound version — and the line does not compile. That is why the recipe names DrizzleTx explicitly.

If you write your own adapter over Drizzle, two of its methods will not do what their signatures suggest. Both verified against drizzle-orm@0.45.3:

• db.execute("SELECT ? as n", [7]) does not bind. It takes a SQLWrapper, and a second argument is silently ignored, so the ? reaches the server as literal SQL. The type checker catches this one: "Expected 1 arguments, but got 2."
• db.execute(q\SELECT ${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' in 'SELECT'".

Neither affects the recipe, because this plugin only ever calls ping. They do mean an adapter should not try to expose raw query(sql, params) — which is what the conformance hooks are for:

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

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: exec runs inside adapter.transaction to prove rollback works, and outside it to set up.

Run that against your own adapter and it will tell you whether it is actually correct, rather than whether it typechecks.

/testing is a separate entry point so the kit — and the database it opens — stays out of an application that never runs it. The three subpaths are:

#You should not be writing an adapter

If you reached this page and are about to write create, close and transaction for Bun.SQL by hand, stop — bunSqlAdapter already is that adapter, and the two options above are the whole of it:

// Already written, already tested against sqlite, postgres and mysql.
adapter(Bun.env.DATABASE_URL!, { maxTotal: 20 })

Everything the hand-written version gives you — the budget divided by real worker count, opened in the worker and not the coordinator, pinged at startup, closed on shutdown, contracts checked — is in there.

Write your own when your client is not Bun.SQL: a driver Sigil has no subpath for, or a wrapper that hands out a different type inside a transaction. The Drizzle recipe below is that case, and it is the reason the contract exists. It is a minority case, which is why it is here at all.

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

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

create receives the worker context, so a global budget can be split correctly. If your adapter ignores it, every worker opens a full-size pool and nothing else will tell you.

#Per-request transactions

An app asks for one in the same place it asks for everything else:

export default defineConfig({
	plugins: [adapter(Bun.env.DATABASE_URL!, { maxTotal: 20, perRequestTx: true })],
});

That is the whole API change. Routes keep using locals.db, and everything reached through it is inside the transaction: the tagged template, locals.db.sql, locals.db.unsafe(), a Drizzle builder, and database() from a module the route calls. Nothing in your route files changes, and nothing is imported from sigil.config.ts.

export const load = async ({ locals }) => {
	// inside the transaction
	const cart = await locals.db`select * from cart where id = ${id}`;
	await locals.db`insert into audit (what) values ('viewed cart')`;
	return { cart };
};

It rolls back when the response is a 5xx. A route that throws does not reach the plugin as an error — the server renders a 500 and returns it — so the status is what decides. A 4xx commits: a redirect after a write, or a 404 that logged the miss, is not a failure of the request's own work.

A transaction inside it nests. db.transaction(fn) or locals.db.begin(fn) during the request is a savepoint when the adapter declares one (Bun.SQL does), so it can roll back alone; otherwise it joins the request's transaction. It never opens a second transaction on another connection — that one would commit even when the request rolled back, and it would deadlock the first time it touched a row the request had already written.

It costs a BEGIN/COMMIT per request and holds a connection for the request's duration, so each worker's pool has to be larger than the number of requests it serves at once.

A streamed page commits when its response starts. The transaction ends when the handler returns the response, and a streamed body is still being written after that. A deferred load that resolves later runs on the pool, not in a transaction that has already committed — and an error thrown after the status was sent cannot roll anything back.

A write that must survive a rollback — an audit record of a failed payment — goes through the pool on purpose: database().pool(), or .runPool() on a fragment.

#Outside a request

A route has locals. Most other server code does not: hooks.server.ts, a websocket handler, a setInterval, a job handler, a shared module in src/lib/server. That code reaches the database by name:

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

// The database `adapter(url)` registered. Safe at module scope: nothing
// is looked up until a query runs.
const db = database();

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

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

export const init = async () => {
	// services() has run, so the database is open.
	await database().sql`select 1`;

	setInterval(() => {
		// No request here, so this is the pool — and transaction() opens a
		// real transaction when you need one.
		database()
			.sql`delete from sessions where expires_at < now()`
			.run()
			.catch((e) => console.error("session sweep failed", e));
	}, 60_000);
};

The same byId called from a route runs inside that request's transaction, and called from the timer runs on the pool. Nothing needs threading through: the database knows which one is in scope.

An optional database — adapter(url, { key, optional: true }), which registers nothing when the URL is unset — is checked with hasDatabase(name), which is right as soon as the config has been imported:

export const storiesEnabled = () => hasDatabase("userDb");

database(name) takes the locals key for a database opened by adapter() or sql() — "db" unless you passed one — and the name for one made with createDatabase. An unknown name throws and lists the ones that exist.

A script that runs without the server — a migration, a seed — opens and closes the database itself, because no services() runs:

import { createDatabase } from "@sigil-dev/plugin-sql";
import { bunSqlAdapter } from "@sigil-dev/plugin-sql/bun";

const db = createDatabase("db", bunSqlAdapter(Bun.env.DATABASE_URL!));
await db.open();
await db.transaction(async () => {
	await db.sql`insert into users (name) values (${"ada"})`;
});
await db.close();

One name is one database per process. Every handle created with the same name shares one client. That is what keeps database() working when the dev server re-runs an edited module, and why two different databases need two names.

#A database as a value

createDatabase is the same thing as adapter(), returned as a value you can export and pass to other plugins instead of looking it up by name:

import { createDatabase } from "@sigil-dev/plugin-sql";
export const main = createDatabase("main", drizzleAdapter, { perRequestTx: true });

import { main } from "./src/lib/server/db";

export default defineConfig({
	plugins: [main.plugin, sessions({ db: main })],
});

A plugin can also discover the database rather than be handed it, which matters for a plugin that has to work inside an app that configures its databases itself:

export function sessions(opts: { db?: Database<any, any> } = {}) {
	return {
		name: "sessions",
		async services() {
			// Looked up when a query runs, so the order of the plugins
			// array does not matter.
			const db = opts.db ?? database();
			return { sessions: new SessionStore(db) };
		},
	};
}

Passing db explicitly is better when you own both plugins: nothing depends on the order of the array, and a misordering cannot be a silent bug.

Either way, code can ask which side of the commit it is on:

main.db()             // the request transaction if there is one, else the pool
main.tx()             // throws outside a transaction
main.pool()           // always the pool, deliberately outside it
main.inTransaction()  // boolean
main.transaction(fn)  // a transaction, or a savepoint inside one

tx() throwing rather than falling back is the point of the split. A job enqueue that assumes it is inside the request's transaction, and quietly is not because it ran from a websocket handler or a cron, enqueues jobs for an order that rolled back. Throwing turns a production mystery into a failure you meet the first time you run it.

#db.sql, a query you can define before you can run it

locals.db is a client. db.sql is a query that has been described but not run, which is what a shared query in lib/ has to be — it is imported at module scope, long before a worker opens a connection.

// src/queries/cards.ts — no client, no connection, safe to import anywhere
import { main } from "~/db";

const active = main.sql`deleted_at is null`;
const byUnit = (unit: string) => main.sql`unit = ${unit} and ${active}`;

export const cards = (unit: string) =>
	main.sql<Card>`select * from cards where ${byUnit(unit)} order by id`;

Fragments compose, and every value stays a bound parameter — the interpolated fragment's own values are spliced into the enclosing query's, renumbered. Nothing is ever concatenated into SQL text.

Running it resolves the client at that moment, so the same constant runs inside the request's transaction and is a fresh query every time:

const rows = await cards("leo/need");  // this request's transaction

It is awaitable, so it reads like every other query. run(), runTx() and runPool() are all there when you want to be explicit about which one you meant:

await cards("leo/need");            // the transaction, or the pool
await cards("leo/need").runPool();  // the pool, deliberately
await cards("leo/need").runTx();    // a transaction, or throw

The trade is fetch()'s: a fragment nobody awaits does not run, and says nothing about it. The other side of being awaitable: an async function that returns a fragment runs it, because returning from an async function awaits the value. Build fragments you mean to compose in plain functions.

It is portable. The adapter hands the driver the template parts rather than a string with $1 in it, so the driver picks its own placeholders and the same fragment runs on postgres, MySQL and SQLite.

The driver's helpers are on the tag. Called as a function instead of a tag, db.sql is Bun.SQL's helper — built lazily like a fragment, so it works at module scope and on whichever client runs the query:

db.sql`select * from cards where id in ${db.sql(ids)}`
db.sql`select * from cards where attr = any(${db.sql.array(attrs, "text")})`
db.sql`insert into cards ${db.sql(row, "id", "name")}`
db.sql`select count(*) from ${db.sql(tableName)}`   // an escaped identifier

That last one is the escape hatch for dynamic identifiers: a ${tableName} on its own is a bound value, which is right for a value and wrong for a table name.

locals.db.sql is the same tag. Inside a request you can reach it through locals, and it joins that request's transaction:

const cards = await event.locals.db.sql<Card>`select * from cards`;

Three ways to run one:

A fragment refuses to become a string. `${frag}` throws. The alternative — returning the SQL text — would be wrong, because the values live in values, not in the text, so stringifying either drops the parameters or inlines them unescaped. Interpolate it into another sql template instead.

Only Bun.SQL gets db.sql. It needs an adapter that declares capabilities.query, meaning its driver really binds parameters. Drizzle's execute does not — it ignores the values array — so the Drizzle recipe has no db.sql and does not pretend to. Use the query builder there, which is what you would reach for anyway, and Drizzle's own sql for the rest.

#What does not work this way

Two perRequestTx databases are two transactions. They nest correctly but are not atomic together: if one commits and the other throws, the first stays committed. A read-only replica beside a transactional primary is fine. Two transactional databases that you assume commit together are not.

setLocal needs a transaction. A driver offering capabilities.setLocal — Postgres SET LOCAL, used for row-level security and app.user_id — without perRequestTx is refused at construction. On a pooled connection outside a transaction it is an ordinary SET, and the next borrower of that connection runs with the previous one's app.user_id and silently reads the wrong rows. Both calls typecheck, so nothing else would catch it.