@bun-and-butter/sqlite

import { buildSQLite, SQLiteError } from "../src/sqlite";

// Start with `buildSQLite()` when a small SQLite connection with good defaults
// is all you need.
//
// Without any options, the package opens an in-memory database and applies the
// default settings for a long-running process. From there on, the returned value
// is a normal Bun SQL client and can be used with `sql\`\`` like any other Bun
// SQL connection.

interface DemoRow {
	message: string;
}

try {
	// No path is passed here, so `buildSQLite()` falls back to `:memory:`.
	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<
			DemoRow[]
		>`SELECT message FROM messages ORDER BY rowid;`;

		console.log(rows[0]?.message);
	} finally {
		await sqlite.close();
	}
} catch (error) {
	// `SQLiteError` is reserved for invalid package configuration.
	if (error instanceof SQLiteError) {
		console.error("Invalid SQLite configuration:", error.message);
	} else {
		// Query failures and runtime SQLite failures come from Bun unchanged.
		console.error("Bun SQL runtime error:", error);
	}
}

import {
	AutoVacuum,
	buildSQLite,
	Mode,
	Optimize,
	SQLiteError,
	Synchronous,
} from "../src/sqlite";

// Use this version when you want to see the full surface area of the package in
// one place.
//
// The connection below is still created against `:memory:` so the snippet stays
// self-contained, but it also shows how the package maps typed options to SQLite
// PRAGMAs, how lifecycle hooks fit into the connection flow, and how normal Bun
// SQL queries continue to work on the returned client.

interface DemoTaskRow {
	id: number;
	title: string;
	done: number;
}

try {
	// Each option here overrides one of the defaults and makes the resulting
	// SQLite setup explicit.
	const sqlite = await buildSQLite({
		path: ":memory:",
		mode: Mode.ReadWriteCreate,
		autoVacuum: AutoVacuum.None,
		optimize: Optimize.Periodically(60_000),
		synchronous: Synchronous.Normal,
		busyTimeout: 5_000,
		cacheSize: -65_536,
		foreignKeys: true,
		tempStoreMemory: true,
		walAutoCheckpoint: 1_000,

		// Hooks make it easy to attach setup and cleanup logic directly to the
		// connection lifecycle.
		onAfterConnect: async (db) => {
			const [journalMode] = await db<
				{ journal_mode: string }[]
			>`PRAGMA journal_mode;`;

			console.log(
				"Connected to SQLite with journal mode:",
				journalMode?.journal_mode,
			);
		},
		onBeforeClose: async () => {
			console.log("Closing SQLite connection...");
		},
		onAfterClose: async () => {
			console.log("SQLite connection closed.");
		},
	});

	try {
		// Once the connection is created, the returned client is used exactly like
		// normal Bun SQL.
		await sqlite`
			CREATE TABLE tasks (
				id INTEGER PRIMARY KEY,
				title TEXT NOT NULL,
				done INTEGER NOT NULL DEFAULT 0
			);
		`;

		await sqlite`
			INSERT INTO tasks (title, done)
			VALUES (${"Write docs"}, ${1}),
			       (${"Ship release"}, ${0}),
			       (${"Refill coffee"}, ${0});
		`;

		const tasks = await sqlite<
			DemoTaskRow[]
		>`SELECT id, title, done FROM tasks ORDER BY id;`;

		console.log("All tasks:", tasks);

		// Querying the PRAGMAs back is a simple way to inspect how the connection
		// was configured at runtime.
		const [busyTimeout] = await sqlite<
			{ timeout: number }[]
		>`PRAGMA busy_timeout;`;
		const [cacheSize] = await sqlite<
			{ cache_size: number }[]
		>`PRAGMA cache_size;`;
		const [foreignKeys] = await sqlite<
			{ foreign_keys: number }[]
		>`PRAGMA foreign_keys;`;

		console.log("busy_timeout:", busyTimeout?.timeout);
		console.log("cache_size:", cacheSize?.cache_size);
		console.log("foreign_keys:", foreignKeys?.foreign_keys);
	} finally {
		// `close()` runs the configured cleanup logic and then closes the Bun SQL
		// connection.
		await sqlite.close();
	}
} catch (error) {
	// Configuration problems are reported as `SQLiteError`.
	if (error instanceof SQLiteError) {
		console.error("Invalid SQLite configuration:", error.message);
	} else {
		// Runtime failures stay as normal Bun SQL / SQLite errors.
		console.error("Bun SQL runtime error:", error);
	}
}

import { buildSQLite, SQLiteError } from "../src/sqlite";

// Use this shape for processes that keep a SQLite connection open for a longer
// time, for example a web service or a worker process.
//
// The important point here is that the package defaults already target this kind
// of process. The example therefore keeps the options small and relies on those
// defaults instead of repeating them explicitly.

interface VisitorRow {
	route: string;
	hits: number;
}

const sqlite = await buildSQLite({
	path: ":memory:",

	onAfterConnect: async () => {
		console.log("SQLite service connection is ready.");
	},
	onBeforeClose: async () => {
		console.log("Service shutdown: draining SQLite connection.");
	},
	onAfterClose: async () => {
		console.log("Service shutdown complete.");
	},
});

try {
	// Long-lived processes typically create or verify their schema during startup.
	await sqlite`
		CREATE TABLE route_hits (
			route TEXT PRIMARY KEY,
			hits INTEGER NOT NULL DEFAULT 0
		);
	`;

	// This stands in for a route handler or service method.
	// Each call records one request for the given route.
	async function recordRequest(route: string): Promise<void> {
		await sqlite`
			INSERT INTO route_hits (route, hits)
			VALUES (${route}, 1)
			ON CONFLICT(route)
			DO UPDATE SET hits = route_hits.hits + 1;
		`;
	}

	// This helper reads the current counters, for example to expose them through
	// an API endpoint or an admin screen.
	async function listRouteHits(): Promise<VisitorRow[]> {
		return sqlite<
			VisitorRow[]
		>`SELECT route, hits FROM route_hits ORDER BY route;`;
	}

	await recordRequest("/health");
	await recordRequest("/users");
	await recordRequest("/users");

	console.log("Route counters:", await listRouteHits());

	// A real service would usually keep running after this point and reuse the
	// same connection for future requests.
} catch (error) {
	if (error instanceof SQLiteError) {
		console.error("Invalid SQLite configuration:", error.message);
	} else {
		console.error("Bun SQL runtime error:", error);
	}
} finally {
	await sqlite.close();
}

import { buildSQLite, Optimize, SQLiteError, Synchronous } from "../src/sqlite";

// Use this configuration for work that starts, does one focused job, and exits
// again shortly afterwards.
//
// Typical examples are:
// - scripts
// - import jobs
// - migration helpers
// - one-off parsers
//
// In that kind of process, `Optimize.OnExit` is usually a better fit than a
// periodic optimize timer because the connection is not expected to stay alive
// for long.

interface ImportedUserRow {
	id: number;
	email: string;
}

try {
	const sqlite = await buildSQLite({
		path: ":memory:",

		// Optimize once at shutdown instead of on an interval.
		optimize: Optimize.OnExit,

		// `full` is a reasonable choice for small jobs where durability matters more
		// than raw throughput.
		synchronous: Synchronous.Full,

		onAfterConnect: async () => {
			console.log("Import job started.");
		},
		onAfterClose: async () => {
			console.log("Import job finished.");
		},
	});

	try {
		// The rest of the code is ordinary Bun SQL.
		await sqlite`
			CREATE TABLE imported_users (
				id INTEGER PRIMARY KEY,
				email TEXT NOT NULL UNIQUE
			);
		`;

		for (const email of ["[email protected]", "[email protected]"]) {
			await sqlite`
				INSERT INTO imported_users (email)
				VALUES (${email});
			`;
		}

		const users = await sqlite<
			ImportedUserRow[]
		>`SELECT id, email FROM imported_users ORDER BY id;`;

		console.log("Imported users:", users);
	} finally {
		// Closing the connection triggers the `Optimize.OnExit` behavior.
		await sqlite.close();
	}
} catch (error) {
	if (error instanceof SQLiteError) {
		console.error("Invalid SQLite configuration:", error.message);
	} else {
		console.error("Bun SQL runtime error:", error);
	}
}