Skip to content

Email for professionals and teams

4.9 KB
Raw Blame
import type { OpenOptions, QueryResult, SqlExecutor } from "./types";
import { lazyExecutor, normalizeParam } from "./types";

const sqliteParam = (value: unknown) => {
	const normalized = normalizeParam(value);
	return typeof normalized === "boolean" ? (normalized ? 1 : 0) : normalized;
};

/** libSQL / Turso: remote (`libsql://`, `https://`) and local `file:` databases. */
export function openLibsql({ url, authToken }: OpenOptions): SqlExecutor {
	return lazyExecutor("sqlite", async () => {
		const { createClient } = await import("@libsql/client");
		const client = createClient({ url, authToken });
		type Executable = { execute(statement: { sql: string; args: unknown[] }): Promise<{ rows: Array<Record<string, unknown>>; columns: string[]; rowsAffected: number; lastInsertRowid?: bigint }> };
		const executorFor = (target: Executable, transaction?: SqlExecutor["transaction"]): SqlExecutor => ({
			dialect: "sqlite",
			async query(sql, values, mode): Promise<QueryResult> {
				const result = await target.execute({ sql, args: values.map(sqliteParam) });
				const rows = mode === "array" ? result.rows.map((row) => result.columns.map((_, i) => (row as unknown as unknown[])[i])) : result.rows.map((row) => ({ ...row }));
				return { rows, affectedRows: result.rowsAffected, insertId: result.lastInsertRowid === undefined ? undefined : Number(result.lastInsertRowid) };
			},
			transaction,
		});
		return {
			...executorFor(client as unknown as Executable, async (fn) => {
				const tx = await client.transaction("write");
				try {
					const result = await fn(executorFor(tx as unknown as Executable));
					await tx.commit();
					return result;
				} catch (error) {
					await tx.rollback().catch(() => undefined);
					throw error;
				} finally {
					tx.close();
				}
			}),
			close: async () => client.close(),
		};
	});
}

/** A local SQLite file through better-sqlite3 (Node only). */
export function openBetterSqlite3({ url }: OpenOptions): SqlExecutor {
	return lazyExecutor("sqlite", async () => {
		const name = "better-sqlite3";
		const Database = ((await import(/* @vite-ignore */ name)) as { default: new (path: string) => import("better-sqlite3").Database }).default;
		const path = url.replace(/^(file|sqlite):(\/\/)?/i, "") || ":memory:";
		const db = new Database(path);
		db.pragma("journal_mode = WAL");
		db.pragma("foreign_keys = ON");
		return syncSqliteExecutor((sql, values, mode) => {
			const statement = db.prepare(sql);
			if (statement.reader) {
				const rows = mode === "array" ? (statement.raw(true).all(...values) as unknown[]) : (statement.raw(false).all(...values) as unknown[]);
				return { rows, affectedRows: 0 };
			}
			const info = statement.run(...values);
			return { rows: [], affectedRows: info.changes, insertId: Number(info.lastInsertRowid) };
		}, async () => void db.close());
	});
}

/** A local SQLite file through bun:sqlite (Bun runtime only). */
export function openBunSqlite({ url }: OpenOptions): SqlExecutor {
	return lazyExecutor("sqlite", async () => {
		const name = "bun:sqlite";
		const { Database } = (await import(/* @vite-ignore */ name)) as { Database: new (path: string) => BunDatabase };
		const db = new Database(url.replace(/^(file|sqlite):(\/\/)?/i, "") || ":memory:");
		db.exec("PRAGMA journal_mode = WAL");
		db.exec("PRAGMA foreign_keys = ON");
		return syncSqliteExecutor((sql, values, mode) => {
			const statement = db.query(sql);
			if (statement.columnNames.length > 0) {
				return { rows: mode === "array" ? statement.values(...values) : statement.all(...values), affectedRows: 0 };
			}
			const info = statement.run(...values);
			return { rows: [], affectedRows: info.changes, insertId: Number(info.lastInsertRowid) };
		}, async () => db.close());
	});
}

interface BunStatement {
	columnNames: string[];
	values(...params: unknown[]): unknown[];
	all(...params: unknown[]): unknown[];
	run(...params: unknown[]): { changes: number; lastInsertRowid: number | bigint };
}
interface BunDatabase {
	query(sql: string): BunStatement;
	exec(sql: string): void;
	close(): void;
}

/** In-process drivers share one connection, so a transaction is BEGIN/COMMIT around awaited statements. */
function syncSqliteExecutor(run: (sql: string, values: unknown[], mode: "array" | "object") => QueryResult, close: () => Promise<void>): SqlExecutor {
	let chain: Promise<unknown> = Promise.resolve();
	const executor: SqlExecutor = {
		dialect: "sqlite",
		async query(sql, values, mode) {
			return run(sql, values.map(sqliteParam), mode);
		},
		// One connection means one transaction at a time; later callers wait for the earlier to finish.
		transaction(fn) {
			const result = chain.then(async () => {
				run("BEGIN", [], "array");
				try {
					const value = await fn(executor);
					run("COMMIT", [], "array");
					return value;
				} catch (error) {
					run("ROLLBACK", [], "array");
					throw error;
				}
			});
			chain = result.catch(() => undefined);
			return result;
		},
		close,
	};
	return executor;
}