@bun-and-butter/sqlite

A Bun-first SQLite helper for creating and configuring Bun SQL connections with sensible defaults, configurable PRAGMAs, typed options, lifecycle hooks, and clear configuration errors.

@bun-and-butter/sqlite is built for the common case where you want to use SQLite through Bun SQL, but do not want to hand-roll the connection setup every time. It gives you a small wrapper around Bun SQL that applies production-friendly defaults, exposes the most important SQLite tuning options through a typed API, and keeps configuration errors separate from normal Bun SQL runtime errors.

Installation

This package is Bun-only.

We do not publish prebuilt artifacts to npm or another package registry. @bun-and-butter/sqlite is installed directly from the GitHub repository and that is the recommended install path for the current version.

For the exact bun add behavior, see the Bun docs on Git dependencies.

bun add git@github.com:bun-and-butter/sqlite.git

Quick Start

import { buildSQLite } from "@bun-and-butter/sqlite";

interface MessageRow {
	message: string;
}

const sqlite = await buildSQLite();

try {
	await sqlite`CREATE TABLE messages (message TEXT NOT NULL);`;
	await sqlite`INSERT INTO messages (message) VALUES (${"Hello from SQLite"});`;

	const rows = await sqlite<
		MessageRow[]
	>`SELECT message FROM messages ORDER BY rowid;`;

	console.log(rows[0]?.message);
} finally {
	await sqlite.close();
}

Why Use It

Using Bun SQL directly is already pleasant, but SQLite connections often need a bit of repeated setup around them:

• pick a sensible synchronous mode
• decide when PRAGMA optimize should run
• enable foreign keys
• configure cache size, busy timeout, WAL checkpointing, and temp storage
• parse sqlite:// paths and optional ?mode=...
• run connection lifecycle hooks
• distinguish invalid configuration from normal SQL runtime failures

buildSQLite(...) keeps that setup small and consistent while still returning a normal Bun SQL client for queries.

What It Handles

The package currently focuses on the connection layer:

• creates a Bun SQL client configured for the SQLite adapter
• accepts plain file paths and Bun-style sqlite:// paths
• supports ro, rw, and rwc modes
• applies sensible defaults for long-running processes
• lets you override important SQLite PRAGMAs with typed options
• supports onAfterConnect, onBeforeClose, and onAfterClose hooks
• provides deleteSQLiteFiles(...) for removing a database together with its -wal and -shm files

Error Handling

The package throws SQLiteError only for configuration problems handled by the package itself, for example:

• an empty path
• an invalid mode
• other invalid connection setup passed into buildSQLite(...)

Everything else is left as a normal Bun SQL or SQLite runtime error. That means query failures, connection failures, and close-time failures are not wrapped or translated.

Defaults

When you call buildSQLite() without overrides, the package uses defaults aimed at long-running processes such as servers, workers, and other application runtimes.

That includes:

• an in-memory database when no path is provided
• mode=rwc
• synchronous = normal
• foreign_keys = on
• busy_timeout = 5000
• cache_size = -65536
• journal_mode = wal for writable connections
• periodic PRAGMA optimize

For short-lived scripts or one-off jobs, you can override those defaults with Optimize.OnExit and any other options you need.

Examples

The repository currently includes four examples:

• examples/demo.ts shows the smallest useful setup with buildSQLite(), a simple query, and close()
• examples/full_demo.ts shows the broader API surface including typed options, hooks, and PRAGMA inspection
• examples/short_living_process.ts shows a configuration tuned for a short-lived script or job
• examples/long_living_process.ts shows how the long-running-process defaults fit a web service style setup

Design Notes

• the returned connection is a normal Bun SQL client
• close() is wrapped so optimize behavior and lifecycle hooks run in the right order
• repeated close() calls are guarded so cleanup does not run multiple times
• configuration is resolved once up front so the runtime code can work with a complete option object

Recommended Pattern

For most applications:

1. start with buildSQLite() or buildSQLite({ path })
2. keep the defaults unless you have a concrete reason to change them
3. use Optimize.OnExit for short-lived jobs
4. use the default optimize behavior for long-running services
5. treat SQLiteError as a configuration problem and all other errors as Bun SQL runtime failures