Open History to see the last commit
1import * as NodeSqlite from "node:sqlite";
2import * as NodeUtil from "node:util";
3import * as NodeWorkerThreads from "node:worker_threads";
4
5import * as Cache from "effect/Cache";
6import * as Clock from "effect/Clock";
7import * as Duration from "effect/Duration";
8import * as Effect from "effect/Effect";
9import * as Result from "effect/Result";
10import * as Exit from "effect/Exit";
11import * as Fiber from "effect/Fiber";
12import { identity } from "effect/Function";
13import * as Layer from "effect/Layer";
14import * as Option from "effect/Option";
15import * as Predicate from "effect/Predicate";
16import * as References from "effect/References";
17import * as Schema from "effect/Schema";
18import * as Scheduler from "effect/Scheduler";
19import * as Scope from "effect/Scope";
20import * as Semaphore from "effect/Semaphore";
21import * as Context from "effect/Context";
22import * as Stream from "effect/Stream";
23import * as Tracer from "effect/Tracer";
24import * as Reactivity from "effect/reactivity/Reactivity";
25import * as Client from "effect/sql/SqlClient";
26import type { Borrower, Connection } from "effect/sql/SqlConnection";
27import { SqlError, classifySqliteError } from "effect/sql/SqlError";
28import * as Statement from "effect/sql/Statement";
29
30const ATTR_DB_SYSTEM_NAME = "db.system.name";
31
32export interface SqliteClientConfig {
33 readonly filename: string;
34 readonly readonly?: boolean | undefined;
35 readonly spanAttributes?: Record<string, unknown> | undefined;
36 /** Ignored for in-memory databases, which cannot share a second connection. */
37 readonly readerWorker?: boolean | undefined;
38 /** Ignored for in-memory databases. */
39 readonly checkpointWorker?: boolean | undefined;
40}
41
42// SqlClient masks transaction acquisition, so readOnly carries the caller's interruption policy.
43const ReadOnly = Context.Reference<typeof Effect.interruptible | undefined>(
44 "@t3tools/shared/nodeSqliteClient/ReadOnly",
45 { defaultValue: () => undefined },
46);
47
48/** Wrap the whole transaction; existing write transactions retain the writer, and writes on the reader fail as readonly. */
49export const readOnly = <A, E, R>(effect: Effect.Effect<A, E, R>): Effect.Effect<A, E, R> =>
50 Effect.uninterruptibleMask((restore) =>
51 restore(Effect.provideService(effect, ReadOnly, restore)),
52 );
53
54interface Acquired {
55 readonly connection: "writer" | "reader";
56 readonly lockWaitMs: number;
57}
58
59interface ConnectionOwner {
60 readonly fiberId: number;
61 readonly name: string;
62 readonly traceId?: string;
63 readonly spanId?: string;
64}
65
66const connectionOwner = (fiber: Fiber.Fiber<unknown, unknown>): ConnectionOwner => {
67 const current = Context.getOrUndefined(fiber.context, Tracer.ParentSpan);
68 if (current?._tag !== "Span") return { fiberId: fiber.id, name: "untraced" };
69 let span: Tracer.Span = current;
70 while (span.name === "sqlite" || span.name === "sql.transaction") {
71 const parent: Tracer.AnySpan | undefined = Option.getOrUndefined(span.parent);
72 if (parent?._tag !== "Span") break;
73 span = parent;
74 }
75 return { fiberId: fiber.id, name: span.name, traceId: span.traceId, spanId: span.spanId };
76};
77
78const annotateAcquired = (
79 fiber: Fiber.Fiber<unknown, unknown>,
80 acquired: Acquired | undefined,
81 ownerAtWaitStart?: ConnectionOwner,
82) => {
83 const span = Context.getOrUndefined(fiber.context, Tracer.ParentSpan);
84 if (span?._tag !== "Span" || acquired === undefined) return;
85 span.attribute("db.connection", acquired.connection);
86 span.attribute("db.lock_wait_ms", acquired.lockWaitMs);
87 if (ownerAtWaitStart !== undefined) {
88 span.attribute("db.lock_owner_at_start.fiber_id", ownerAtWaitStart.fiberId);
89 span.attribute("db.lock_owner_at_start.name", ownerAtWaitStart.name);
90 if (ownerAtWaitStart.traceId !== undefined) {
91 span.attribute("db.lock_owner_at_start.trace_id", ownerAtWaitStart.traceId);
92 }
93 if (ownerAtWaitStart.spanId !== undefined) {
94 span.attribute("db.lock_owner_at_start.span_id", ownerAtWaitStart.spanId);
95 }
96 }
97};
98
99const TransactionAcquire = Context.Reference<
100 | {
101 acquired?: Acquired;
102 ownerAtWaitStart: ConnectionOwner | undefined;
103 restore: typeof Effect.interruptible | undefined;
104 }
105 | undefined
106>("@t3tools/shared/nodeSqliteClient/TransactionAcquire", { defaultValue: () => undefined });
107
108export class UnsupportedNodeSqliteVersionError extends Schema.TaggedError<UnsupportedNodeSqliteVersionError>()(
109 "UnsupportedNodeSqliteVersionError",
110 {
111 nodeVersion: Schema.String,
112 requirement: Schema.String,
113 },
114) {
115 override get message(): string {
116 return `Node.js ${this.nodeVersion} is missing required node:sqlite APIs. Upgrade to ${this.requirement}.`;
117 }
118}
119
120export class UnsupportedNodeSqliteOperationError extends Schema.TaggedError<UnsupportedNodeSqliteOperationError>()(
121 "UnsupportedNodeSqliteOperationError",
122 {},
123) {
124 override get message(): string {
125 return "Node SQLite does not support executeStream.";
126 }
127}
128
129const checkNodeSqliteCompat = Effect.fn("checkNodeSqliteCompat")(function () {
130 const parts = process.versions.node.split(".").map(Number);
131 const major = parts[0] ?? 0;
132 const minor = parts[1] ?? 0;
133 const supported = (major === 22 && minor >= 16) || (major === 23 && minor >= 11) || major >= 24;
134
135 if (!supported) {
136 return Effect.die(
137 new UnsupportedNodeSqliteVersionError({
138 nodeVersion: process.versions.node,
139 requirement: "Node.js >=22.16, >=23.11, or >=24",
140 }),
141 );
142 }
143 return Effect.void;
144});
145
146// node:sqlite supplies errcode; classifySqliteError expects errno.
147const classifyError = (cause: unknown, message: string, operation: string) => {
148 if (
149 Predicate.hasProperty(cause, "errcode") &&
150 typeof cause.errcode === "number" &&
151 !Predicate.hasProperty(cause, "errno")
152 ) {
153 Object.assign(cause, { errno: cause.errcode });
154 }
155 return classifySqliteError(cause, { message, operation });
156};
157
158// SQLite retains text/blob bindings until rebind or finalization; node:sqlite cannot clear them.
159const LARGE_BINDING_SIZE = 64 * 1024;
160
161const isBlob = (value: unknown): value is ArrayBufferView | ArrayBufferLike =>
162 ArrayBuffer.isView(value) || NodeUtil.types.isAnyArrayBuffer(value);
163
164const bindingSize = (value: unknown): number => {
165 if (typeof value === "string") {
166 return value.length;
167 }
168 return isBlob(value) ? value.byteLength : 0;
169};
170
171const hasLargeBinding = (params: ReadonlyArray<unknown>): boolean => {
172 try {
173 let size = 0;
174 for (const param of params) {
175 if (
176 (typeof param === "object" || typeof param === "function") &&
177 param !== null &&
178 !isBlob(param)
179 ) {
180 // Named parameters may be callable objects; read data properties only to avoid invoking getters twice.
181 for (const property of Object.values(Object.getOwnPropertyDescriptors(param))) {
182 if (!property.enumerable) continue;
183 if (!("value" in property)) return true;
184 size += bindingSize(property.value);
185 }
186 } else {
187 size += bindingSize(param);
188 }
189 }
190 return size > LARGE_BINDING_SIZE;
191 } catch {
192 return true;
193 }
194};
195
196// Inline worker source also runs in bundles and single-executable builds without module files.
197const readerWorkerSource = `
198const { parentPort, workerData } = require("node:worker_threads");
199const { DatabaseSync } = require("node:sqlite");
200let db;
201try {
202 // A read-only open cannot be undone by SQL, unlike PRAGMA query_only.
203 const next = new DatabaseSync(workerData.filename, {
204 readOnly: true,
205 allowExtension: workerData.allowExtension,
206 });
207 next.exec("PRAGMA busy_timeout = 5000;");
208 // Without WAL a reader's shared lock blocks the writer's commits, so refuse.
209 const mode = String(next.prepare("PRAGMA journal_mode").get().journal_mode).toLowerCase();
210 if (mode !== "wal") throw new Error("database uses journal_mode " + mode + ", not wal");
211 db = next;
212 parentPort.postMessage({ type: "ready" });
213} catch (error) {
214 parentPort.postMessage({ type: "unavailable", message: String(error && error.message ? error.message : error) });
215}
216const statements = new Map();
217const prepare = (sql, cached) => {
218 if (!cached) return db.prepare(sql);
219 let statement = statements.get(sql);
220 if (statement === undefined) {
221 statement = db.prepare(sql);
222 if (statements.size >= workerData.prepareCacheSize) statements.delete(statements.keys().next().value);
223 } else {
224 statements.delete(sql);
225 }
226 statements.set(sql, statement);
227 return statement;
228};
229parentPort.on("message", ({ id, sql, params, cached, values, raw, safeIntegers }) => {
230 let operation = "open";
231 try {
232 if (db === undefined) throw new Error("database reader is unavailable");
233 operation = "prepare";
234 const statement = prepare(sql, cached);
235 operation = "execute";
236 statement.setReadBigInts(safeIntegers);
237 if (statement.columns().length > 0) {
238 statement.setReturnArrays(values);
239 parentPort.postMessage({ id, rows: statement.all(...params) });
240 } else {
241 const result = statement.run(...params);
242 parentPort.postMessage({ id, rows: raw ? result : [] });
243 }
244 } catch (error) {
245 parentPort.postMessage({
246 id,
247 error: {
248 operation,
249 message: String(error && error.message ? error.message : error),
250 code: error && error.code,
251 errcode: error && error.errcode,
252 errstr: error && error.errstr,
253 },
254 });
255 }
256});
257`;
258
259interface ReaderRequest {
260 readonly sql: string;
261 readonly params: ReadonlyArray<unknown>;
262 readonly cached: boolean;
263 readonly values: boolean;
264 readonly raw: boolean;
265}
266
267type ReaderStartup =
268 | { readonly type: "ready" }
269 | { readonly type: "unavailable"; readonly message: string };
270
271type ReaderReply =
272 | { readonly id: number; readonly rows: ReadonlyArray<any> }
273 | {
274 readonly id: number;
275 readonly error: {
276 readonly operation: "open" | "prepare" | "execute";
277 readonly message: string;
278 readonly code?: unknown;
279 readonly errcode?: unknown;
280 readonly errstr?: unknown;
281 };
282 };
283
284const readerFailureMessages = {
285 open: "Failed to open database",
286 prepare: "Failed to prepare statement",
287 execute: "Failed to execute statement",
288} as const;
289
290const failed = (cause: unknown, message: string, operation: string) =>
291 new SqlError({ reason: classifyError(cause, message, operation) });
292
293// Read-transaction cleanup succeeds after worker exit because it can no longer change anything.
294const endsReadTransaction = (sql: string) => /^\s*ROLLBACK\b|^\s*COMMIT\b/i.test(sql);
295
296// WAL readers run concurrently; one worker serialized every read behind the slowest transaction.
297const READER_POOL_SIZE = 4;
298
299// Bind transactions to one worker; fail after its exit instead of resuming outside the transaction on a replacement.
300const makeReader = Effect.fnUntraced(function* (
301 options: SqliteClientConfig,
302 pool: { unavailable: boolean },
303) {
304 let current:
305 | {
306 readonly worker: NodeWorkerThreads.Worker;
307 readonly connection: Connection;
308 readonly started: Promise<ReaderStartup>;
309 }
310 | undefined;
311 let nextRequestId = 0;
312
313 const start = () => {
314 const worker = new NodeWorkerThreads.Worker(readerWorkerSource, {
315 eval: true,
316 workerData: {
317 filename: options.filename,
318 allowExtension: false,
319 prepareCacheSize: 200,
320 },
321 });
322 let announce: (startup: ReaderStartup) => void = () => {};
323 const started = new Promise<ReaderStartup>((resolve) => {
324 announce = resolve;
325 });
326 let stoppedMessage: string | undefined;
327 const pending = new Map<
328 number,
329 { readonly endsTransaction: boolean; readonly resume: (reply: ReaderReply) => void }
330 >();
331 const stop = (message: string) => {
332 stoppedMessage = message;
333 announce({ type: "unavailable", message });
334 if (current?.worker === worker) current = undefined;
335 for (const [id, request] of pending) {
336 request.resume(
337 request.endsTransaction
338 ? { id, rows: [] }
339 : { id, error: { operation: "execute", message } },
340 );
341 }
342 pending.clear();
343 };
344 worker.on("message", (reply: ReaderReply | ReaderStartup) => {
345 if ("type" in reply) {
346 announce(reply);
347 if (pending.size === 0) worker.unref();
348 return;
349 }
350 const request = pending.get(reply.id);
351 if (request === undefined) return;
352 pending.delete(reply.id);
353 if (pending.size === 0) worker.unref();
354 request.resume(reply);
355 });
356 worker.on("error", (cause) => stop(`Database reader failed: ${cause.message}`));
357 worker.on("exit", (code) => stop(`Database reader exited with code ${code}`));
358
359 const request = (input: ReaderRequest) =>
360 Effect.withFiber<ReadonlyArray<any>, SqlError>((fiber) => {
361 const endsTransaction = endsReadTransaction(input.sql);
362 if (stoppedMessage !== undefined) {
363 return endsTransaction
364 ? Effect.succeed([])
365 : Effect.fail(
366 failed(new Error(stoppedMessage), readerFailureMessages.execute, "execute"),
367 );
368 }
369 const safeIntegers = Boolean(Context.get(fiber.context, Client.SafeIntegers));
370 return Effect.callback<ReadonlyArray<any>, SqlError>((resume) => {
371 const id = nextRequestId++;
372 pending.set(id, {
373 endsTransaction,
374 resume: (reply) => {
375 if ("rows" in reply) {
376 resume(Effect.succeed(reply.rows));
377 return;
378 }
379 const { operation, message, ...codes } = reply.error;
380 resume(
381 Effect.fail(
382 failed(
383 Object.assign(new Error(message), codes),
384 readerFailureMessages[operation],
385 operation,
386 ),
387 ),
388 );
389 },
390 });
391 if (pending.size === 1) worker.ref();
392 const cached = input.cached && !hasLargeBinding(input.params);
393 // oxlint-disable-next-line unicorn/require-post-message-target-origin -- Node workers do not accept a target origin.
394 worker.postMessage({ id, safeIntegers, ...input, cached });
395 });
396 });
397
398 const connection = identity<Connection>({
399 execute(sql, params, rowTransform) {
400 const effect = request({ sql, params, cached: true, values: false, raw: false });
401 return rowTransform ? Effect.map(effect, rowTransform) : effect;
402 },
403 executeRaw(sql, params) {
404 return request({ sql, params, cached: true, values: false, raw: true });
405 },
406 executeValues(sql, params) {
407 return request({ sql, params, cached: true, values: true, raw: false });
408 },
409 executeValuesUnprepared(sql, params) {
410 return request({ sql, params: params ?? [], cached: false, values: true, raw: false });
411 },
412 executeUnprepared(sql, params, rowTransform) {
413 // Read snapshots need deferred BEGIN; readonly connections reject IMMEDIATE and EXCLUSIVE.
414 const statement = /^\s*BEGIN\b/i.test(sql) ? "BEGIN" : sql;
415 const effect = request({
416 sql: statement,
417 params: params ?? [],
418 cached: false,
419 values: false,
420 raw: false,
421 });
422 return rowTransform ? Effect.map(effect, rowTransform) : effect;
423 },
424 executeStream(_sql, _params) {
425 return Stream.die(new UnsupportedNodeSqliteOperationError());
426 },
427 });
428 current = { worker, connection, started };
429 return current;
430 };
431
432 yield* Effect.addFinalizer(() =>
433 Effect.promise(async () => {
434 await current?.worker.terminate();
435 }),
436 );
437
438 const startupTimeout = Duration.seconds(10);
439
440 const turnOff = (reader: typeof current, reason: string) =>
441 Effect.suspend(() => {
442 if (reader !== undefined) {
443 if (current === reader) current = undefined;
444 void reader.worker.terminate();
445 }
446 if (pool.unavailable) return Effect.undefined;
447 pool.unavailable = true;
448 return Effect.logWarning("SQLite reader worker unavailable; reads use the main connection", {
449 reason,
450 }).pipe(Effect.as(undefined));
451 });
452
453 return Effect.suspend((): Effect.Effect<Connection | undefined> => {
454 if (pool.unavailable) return Effect.undefined;
455 let reader: NonNullable<typeof current>;
456 try {
457 reader = current ?? start();
458 } catch (cause) {
459 return turnOff(undefined, String(cause));
460 }
461 return Effect.flatMap(
462 Effect.withFiber((fiber) => {
463 const restore = Context.get(fiber.context, ReadOnly) ?? identity;
464 return restore(
465 Effect.timeoutOption(
466 Effect.promise(() => reader.started),
467 startupTimeout,
468 ),
469 );
470 }),
471 (startup): Effect.Effect<Connection | undefined> =>
472 // A late ready event must not hand out a worker retired by another acquire timeout.
473 pool.unavailable || current !== reader
474 ? Effect.undefined
475 : Option.isSome(startup) && startup.value.type === "ready"
476 ? Effect.succeed(reader.connection)
477 : turnOff(
478 reader,
479 Option.isSome(startup) && startup.value.type === "unavailable"
480 ? startup.value.message
481 : `worker did not open the database within ${Duration.toMillis(startupTimeout)} ms`,
482 ),
483 );
484 });
485});
486
487const CHECKPOINT_FRAMES = 1000;
488const CHECKPOINT_IDLE_MS = 100;
489const CHECKPOINT_BACKPRESSURE_FRAMES = 32_768;
490
491const readSafeSqliteInteger = (value: unknown) => {
492 const number = typeof value === "bigint" ? Number(value) : value;
493 return typeof number === "number" && Number.isSafeInteger(number) ? number : undefined;
494};
495
496// Evaluated source also works in bundles and single-executable builds.
497const checkpointWorkerSource = `
498const { parentPort, workerData } = require("node:worker_threads");
499const { DatabaseSync } = require("node:sqlite");
500const { statSync } = require("node:fs");
501let db;
502let frames;
503let checkpoint;
504let truncate;
505let journalSizeLimit = -1;
506let generation = 0;
507let nextTruncateId = 0;
508let pendingTruncate;
509let pendingBarriers = 0;
510let idle;
511const initialize = () => {
512 if (db !== undefined) return;
513 // The writer switches to WAL after constructing the client.
514 db = new DatabaseSync(workerData.filename);
515 db.exec("PRAGMA busy_timeout = 5000;");
516 const mode = String(db.prepare("PRAGMA journal_mode").get().journal_mode).toLowerCase();
517 if (mode !== "wal") throw new Error("database uses journal_mode " + mode + ", not wal");
518 // macOS requires F_FULLFSYNC to flush the drive cache.
519 db.exec("PRAGMA checkpoint_fullfsync = ON;");
520 frames = db.prepare("PRAGMA wal_checkpoint(NOOP)");
521 checkpoint = db.prepare("PRAGMA wal_checkpoint(PASSIVE)");
522 truncate = db.prepare("PRAGMA wal_checkpoint(TRUNCATE)");
523};
524const atCeiling = (state) => state.log >= workerData.backpressureFrames;
525const exceedsSizeLimit = () =>
526 journalSizeLimit >= 0 && statSync(workerData.filename + "-wal").size > journalSizeLimit;
527// Retry a WAL past its limits even with admission open, so it resets once a pinning reader closes.
528const schedule = (state, retry) => {
529 if (retry || state.log - state.checkpointed >= workerData.frames) {
530 idle = setTimeout(() => check(undefined, undefined, true), workerData.idleMs);
531 }
532};
533const check = (id, nextJournalSizeLimit, force, nextGeneration = generation) => {
534 initialize();
535 if (Number.isSafeInteger(nextGeneration)) generation = Math.max(generation, nextGeneration);
536 if (Number.isSafeInteger(nextJournalSizeLimit)) journalSizeLimit = nextJournalSizeLimit;
537 let state = frames.get();
538 const oversized = exceedsSizeLimit();
539 let pinned = false;
540 if (force || state.log - state.checkpointed >= workerData.frames || oversized) {
541 const result = checkpoint.get();
542 // A reader's snapshot stopped the backfill; holding writes cannot let it catch up.
543 pinned = result.checkpointed < result.log;
544 state = frames.get();
545 }
546 if (state.checkpointed === state.log && (atCeiling(state) || oversized)) {
547 pendingTruncate = { truncateId: ++nextTruncateId, checkId: id, generation, state };
548 parentPort.postMessage({
549 type: "truncate-request",
550 truncateId: pendingTruncate.truncateId,
551 checkId: id,
552 generation,
553 ...state,
554 });
555 return;
556 }
557 const blocked = !pinned && (atCeiling(state) || oversized) && state.checkpointed < state.log;
558 parentPort.postMessage({ type: "checkpoint", id, generation, ...state, blocked });
559 schedule(state, atCeiling(state) || oversized);
560};
561const finishTruncate = (truncateId) => {
562 if (pendingTruncate === undefined || pendingTruncate.truncateId !== truncateId) return;
563 const pending = pendingTruncate;
564 parentPort.postMessage({ type: "truncate-start", truncateId });
565 // Admission is paused here; a pinned reader must not make truncation wait.
566 db.exec("PRAGMA busy_timeout = 0;");
567 try {
568 truncate.get();
569 } finally {
570 db.exec("PRAGMA busy_timeout = 5000;");
571 }
572 const state = frames.get();
573 pendingTruncate = undefined;
574 parentPort.postMessage({
575 type: "checkpoint",
576 id: pending.checkId,
577 generation: pending.generation,
578 truncateId,
579 ...state,
580 // With writes held, only a reader can stop the reset, and holding them longer would not
581 // release it.
582 blocked: false,
583 });
584 while (pendingBarriers > 0) {
585 pendingBarriers--;
586 parentPort.postMessage("barrier");
587 }
588 schedule(state, atCeiling(state) || exceedsSizeLimit());
589};
590parentPort.on("message", (request) => {
591 clearTimeout(idle);
592 if (request && request.type === "truncate") {
593 finishTruncate(request.truncateId);
594 return;
595 }
596 const isBarrier = request === "barrier";
597 if (pendingTruncate !== undefined) {
598 if (isBarrier) pendingBarriers++;
599 else if (request && request.type === "check") {
600 // Retire the request without labeling the pending snapshot with a newer generation.
601 parentPort.postMessage({
602 type: "checkpoint",
603 id: request.id,
604 generation: pendingTruncate.generation,
605 ...pendingTruncate.state,
606 blocked: true,
607 });
608 }
609 return;
610 }
611 const input = request && typeof request === "object" ? request : {};
612 check(input.id, input.journalSizeLimit, input.force, input.generation);
613 if (isBarrier) {
614 if (pendingTruncate !== undefined) pendingBarriers++;
615 else parentPort.postMessage("barrier");
616 }
617});
618`;
619
620const startCheckpointWorker = Effect.fnUntraced(function* (
621 filename: string,
622 writer: NodeSqlite.DatabaseSync,
623) {
624 const warn = (reason: string) =>
625 Effect.logWarning(
626 "SQLite checkpoint worker unavailable; checkpoints run on the main connection",
627 { reason },
628 );
629 if (typeof writer.isTransaction !== "boolean" || typeof writer.setAuthorizer !== "function") {
630 yield* warn("Node SQLite lacks transaction-state or statement-authorizer support");
631 return undefined;
632 }
633 // These are the writer's first statements, before callers set their own wait.
634 writer.exec("PRAGMA busy_timeout = 5000;");
635 const version = String(writer.prepare("SELECT sqlite_version() AS version").get()?.version);
636 const [major = 0, minor = 0] = version.split(".").map(Number);
637 // Older SQLite treats NOOP as PASSIVE, which would checkpoint on the writer.
638 if (major < 3 || (major === 3 && minor < 51)) {
639 yield* warn(`SQLite ${version} has no NOOP checkpoint`);
640 return undefined;
641 }
642 const frames = writer.prepare("PRAGMA wal_checkpoint(NOOP)");
643 const sizeLimit = writer.prepare("PRAGMA journal_size_limit");
644 const started = Result.try(
645 () =>
646 new NodeWorkerThreads.Worker(checkpointWorkerSource, {
647 eval: true,
648 workerData: {
649 filename,
650 frames: CHECKPOINT_FRAMES,
651 backpressureFrames: CHECKPOINT_BACKPRESSURE_FRAMES,
652 idleMs: CHECKPOINT_IDLE_MS,
653 },
654 }),
655 );
656 if (Result.isFailure(started)) {
657 yield* warn(String(started.failure));
658 return undefined;
659 }
660 const worker = started.success;
661 const runFork = Effect.runForkWith(yield* Effect.context<never>());
662 let stopped = false;
663 let generation = 0;
664 let nextId = 0;
665 let inFlight: number | undefined;
666 let checkAgain = false;
667 let blocked = false;
668 let log = 0;
669 let checkpointed = 0;
670 let unknown = false;
671 let truncate: { id: number; acknowledged: boolean } | undefined;
672 type Waiter = { resume: () => void; cancelled: boolean };
673 const waiters: Array<Waiter> = [];
674 let elected: Waiter | undefined;
675 const gated = () =>
676 !stopped && !writer.isTransaction && (unknown || blocked || truncate !== undefined);
677 const wake = () => {
678 if (!gated() && elected === undefined) {
679 elected = waiters.shift();
680 elected?.resume();
681 }
682 if (waiters.length === 0 && elected === undefined) worker.unref();
683 };
684 const release = (waiter: Waiter) => {
685 waiter.cancelled = true;
686 const index = waiters.indexOf(waiter);
687 if (index !== -1) waiters.splice(index, 1);
688 if (elected === waiter) elected = undefined;
689 wake();
690 };
691 const handBack = (reason: string) => {
692 if (stopped) return;
693 stopped = true;
694 truncate = undefined;
695 try {
696 writer.exec(`PRAGMA wal_autocheckpoint = ${CHECKPOINT_FRAMES};`);
697 } catch {}
698 wake();
699 runFork(warn(reason));
700 };
701 const send = (message: unknown) => {
702 try {
703 // oxlint-disable-next-line unicorn/require-post-message-target-origin -- Node workers do not accept a target origin.
704 worker.postMessage(message);
705 } catch (cause) {
706 handBack(String(cause));
707 }
708 };
709 const acknowledge = () => {
710 if (truncate === undefined || truncate.acknowledged || stopped || writer.isTransaction) return;
711 truncate.acknowledged = true;
712 send({ type: "truncate", truncateId: truncate.id });
713 };
714 const observe = () => {
715 try {
716 const state = frames.get();
717 const nextLog = readSafeSqliteInteger(state?.log);
718 const nextCheckpointed = readSafeSqliteInteger(state?.checkpointed);
719 if (nextLog === undefined || nextCheckpointed === undefined)
720 throw new Error("Invalid WAL frame counts");
721 log = nextLog;
722 checkpointed = nextCheckpointed;
723 unknown = false;
724 // Past the ceiling, hold writes until the worker judges this WAL; a reader can pin it there.
725 if (log >= CHECKPOINT_BACKPRESSURE_FRAMES) blocked = true;
726 } catch {
727 unknown = true;
728 }
729 };
730 const request = (force = false) => {
731 if (stopped || writer.isTransaction) return;
732 if (truncate !== undefined) {
733 acknowledge();
734 return;
735 }
736 if (inFlight !== undefined) {
737 checkAgain = true;
738 return;
739 }
740 if (!force && !gated() && log - checkpointed < CHECKPOINT_FRAMES) return;
741 inFlight = ++nextId;
742 try {
743 send({
744 type: "check",
745 id: inFlight,
746 generation,
747 journalSizeLimit: readSafeSqliteInteger(sizeLimit.get()?.journal_size_limit),
748 force: force || gated(),
749 });
750 } catch (cause) {
751 handBack(String(cause));
752 }
753 };
754 const check = () => {
755 if (stopped || writer.isTransaction) return;
756 generation++;
757 observe();
758 request();
759 wake();
760 };
761 worker.on("error", (cause) => handBack(cause.message));
762 worker.on("exit", (code) => handBack(`exited with code ${code}`));
763 worker.on("message", (message: unknown) => {
764 if (stopped || typeof message !== "object" || message === null || !("type" in message)) return;
765 if (
766 message.type === "truncate-request" &&
767 "truncateId" in message &&
768 typeof message.truncateId === "number"
769 ) {
770 truncate = { id: message.truncateId, acknowledged: false };
771 blocked = true;
772 acknowledge();
773 return;
774 }
775 if (message.type !== "checkpoint") return;
776 const completedTruncate =
777 truncate !== undefined && "truncateId" in message && message.truncateId === truncate.id;
778 if (completedTruncate) truncate = undefined;
779 // An acknowledged truncate excludes new writer mutations, so its result is current.
780 if (
781 (completedTruncate || ("generation" in message && message.generation === generation)) &&
782 "log" in message &&
783 typeof message.log === "number" &&
784 "checkpointed" in message &&
785 typeof message.checkpointed === "number" &&
786 "blocked" in message &&
787 typeof message.blocked === "boolean"
788 ) {
789 log = message.log;
790 checkpointed = message.checkpointed;
791 blocked = message.blocked;
792 unknown = false;
793 }
794 // Even a stale snapshot must retire its request so a newer check can run.
795 if ("id" in message && message.id === inFlight) inFlight = undefined;
796 if (checkAgain && inFlight === undefined) {
797 checkAgain = false;
798 request(true);
799 }
800 wake();
801 });
802 yield* Effect.addFinalizer(() =>
803 Effect.promise(() => {
804 stopped = true;
805 truncate = undefined;
806 wake();
807 return worker.terminate();
808 }),
809 );
810 writer.exec("PRAGMA wal_autocheckpoint = 0;");
811 observe();
812 worker.unref();
813
814 const runWithAdmission = <A, E>(
815 execute: () => Exit.Exit<A, E>,
816 admitted?: Waiter,
817 ): Effect.Effect<A, E> =>
818 Effect.suspend(() => {
819 if (!gated()) {
820 // Release the election after executing; its successor must see the new WAL state.
821 try {
822 return execute();
823 } finally {
824 if (admitted !== undefined) release(admitted);
825 }
826 }
827 if (admitted !== undefined) release(admitted);
828 const waiter: Waiter = { resume: () => {}, cancelled: false };
829 const wait = Effect.uninterruptibleMask((restore) =>
830 Effect.withFiber((fiber) =>
831 (fiber.getRef(TransactionAcquire)?.restore ?? restore)(
832 Effect.callback<void>(function (this: Scheduler.Scheduler, resume) {
833 const dispatcher = this.makeDispatcher();
834 waiter.resume = () => {
835 dispatcher.scheduleTask(() => {
836 if (!waiter.cancelled) resume(Effect.void);
837 }, 0);
838 };
839 waiters.push(waiter);
840 worker.ref();
841 request(true);
842 wake();
843 return Effect.sync(() => release(waiter));
844 }),
845 ),
846 ),
847 );
848 return wait.pipe(
849 Effect.flatMap(() => runWithAdmission(execute, waiter)),
850 Effect.onInterrupt(() => Effect.sync(() => release(waiter))),
851 );
852 });
853 return { check, runWithAdmission };
854});
855
856const make = Effect.fn("makeWithDatabase")(function* (
857 options: SqliteClientConfig,
858): Effect.fn.Return<Client.SqlClient, SqlError, Scope.Scope | Reactivity.Reactivity> {
859 yield* checkNodeSqliteCompat();
860
861 const compiler = Statement.makeCompilerSqlite();
862 const isFileDatabase = options.filename !== ":memory:" && options.filename !== "";
863
864 const makeConnection = Effect.gen(function* () {
865 const scope = yield* Effect.scope;
866 const db = yield* Effect.try({
867 try: () =>
868 new NodeSqlite.DatabaseSync(options.filename, {
869 readOnly: options.readonly ?? false,
870 allowExtension: false,
871 }),
872 catch: (cause) =>
873 new SqlError({
874 reason: classifyError(cause, "Failed to open database", "open"),
875 }),
876 });
877 yield* Scope.addFinalizer(
878 scope,
879 Effect.try({
880 try: () => db.close(),
881 catch: (cause) =>
882 new SqlError({
883 reason: classifyError(cause, "Failed to close database", "close"),
884 }),
885 }).pipe(Effect.orDie),
886 );
887 // Started after the close finalizer, so the worker stops before the writer closes.
888 const checkpoint =
889 options.checkpointWorker && isFileDatabase
890 ? yield* startCheckpointWorker(options.filename, db)
891 : undefined;
892
893 const statementReaderCache = new WeakMap<NodeSqlite.StatementSync, boolean>();
894 interface CheckpointClassification {
895 backpressure: boolean;
896 check: boolean;
897 }
898 const checkpointClassifications = new WeakMap<
899 NodeSqlite.StatementSync,
900 CheckpointClassification
901 >();
902 const mutatingPragmasWithValues = new Set([
903 "application_id",
904 "auto_vacuum",
905 "journal_mode",
906 "schema_version",
907 "user_version",
908 ]);
909 const mutatesPragma = (name: string | null, value: string | null) => {
910 const pragma = name?.toLowerCase();
911 if (pragma === undefined) return false;
912 if (pragma === "incremental_vacuum" || pragma === "optimize") return true;
913 if (pragma === "wal_checkpoint") return value?.toLowerCase() !== "noop";
914 return value !== null && mutatingPragmasWithValues.has(pragma);
915 };
916 let activeClassification: CheckpointClassification | undefined;
917 const withClassification = <A>(
918 classification: CheckpointClassification | undefined,
919 f: () => A,
920 ): A => {
921 if (classification === undefined) return f();
922 const previous = activeClassification;
923 activeClassification = classification;
924 try {
925 return f();
926 } finally {
927 activeClassification = previous;
928 }
929 };
930 if (checkpoint !== undefined) {
931 // SQLite may reprepare an expired statement while it executes. Keep this
932 // callback installed and collect only around that statement's prepare/run.
933 db.setAuthorizer((actionCode, action, argument) => {
934 const classification = activeClassification;
935 if (classification === undefined) return NodeSqlite.constants.SQLITE_OK;
936 const constants = NodeSqlite.constants;
937 if (actionCode === constants.SQLITE_TRANSACTION) {
938 classification.backpressure ||= action === "BEGIN";
939 classification.check ||= action === "COMMIT" || action === "ROLLBACK";
940 } else if (actionCode === constants.SQLITE_SAVEPOINT) {
941 classification.backpressure ||= action === "BEGIN";
942 classification.check = true;
943 } else if (actionCode === constants.SQLITE_PRAGMA && mutatesPragma(action, argument)) {
944 classification.backpressure = true;
945 classification.check = true;
946 } else if (
947 actionCode !== constants.SQLITE_READ &&
948 actionCode !== constants.SQLITE_SELECT &&
949 actionCode !== constants.SQLITE_FUNCTION &&
950 actionCode !== constants.SQLITE_RECURSIVE &&
951 actionCode !== constants.SQLITE_PRAGMA
952 ) {
953 classification.backpressure = true;
954 classification.check = true;
955 }
956 return constants.SQLITE_OK;
957 });
958 }
959 const hasRows = (statement: NodeSqlite.StatementSync): boolean => {
960 const cached = statementReaderCache.get(statement);
961 if (cached !== undefined) {
962 return cached;
963 }
964 const value = statement.columns().length > 0;
965 statementReaderCache.set(statement, value);
966 return value;
967 };
968
969 const prepare = (sql: string) =>
970 Effect.try({
971 try: () => {
972 const classification = { backpressure: false, check: false };
973 const statement = withClassification(classification, () => db.prepare(sql));
974 if (checkpoint !== undefined) checkpointClassifications.set(statement, classification);
975 return statement;
976 },
977 catch: (cause) =>
978 new SqlError({
979 reason: classifyError(cause, "Failed to prepare statement", "prepare"),
980 }),
981 });
982
983 const prepareCache = yield* Cache.makeWith(prepare, {
984 capacity: 200,
985 // A transient prepare failure must not outlive the lock or missing schema.
986 timeToLive: (exit) => (Exit.isSuccess(exit) ? Duration.minutes(10) : Duration.zero),
987 });
988
989 const statementFor = (sql: string, params: ReadonlyArray<unknown>) =>
990 hasLargeBinding(params) ? prepare(sql) : Cache.get(prepareCache, sql);
991
992 const runStatement = (
993 statement: NodeSqlite.StatementSync,
994 params: ReadonlyArray<unknown>,
995 mode: "rows" | "raw" | "values" = "rows",
996 ) =>
997 Effect.withFiber<ReadonlyArray<any>, SqlError>((fiber) => {
998 const classification = checkpointClassifications.get(statement);
999 const execute = () => {
1000 let arrays = false;
1001 let result: Exit.Exit<ReadonlyArray<unknown>, SqlError>;
1002 try {
1003 const value = withClassification(classification, () => {
1004 statement.setReadBigInts(Boolean(Context.get(fiber.context, Client.SafeIntegers)));
1005 if (hasRows(statement)) {
1006 arrays = mode === "values";
1007 if (arrays) statement.setReturnArrays(true);
1008 return statement.all(...(params as any));
1009 }
1010 const result = statement.run(...(params as any));
1011 return mode === "raw" ? (result as unknown as ReadonlyArray<unknown>) : [];
1012 });
1013 result = Exit.succeed(value);
1014 } catch (cause) {
1015 result = Exit.fail(
1016 new SqlError({
1017 reason: classifyError(cause, "Failed to execute statement", "execute"),
1018 }),
1019 );
1020 } finally {
1021 // OR FAIL may have committed earlier rows even when execution throws.
1022 if (classification?.check) checkpoint?.check();
1023 }
1024 if (arrays) {
1025 try {
1026 statement.setReturnArrays(false);
1027 } catch (cause) {
1028 return Exit.die(
1029 new SqlError({
1030 reason: classifyError(
1031 cause,
1032 "Failed to reset statement result mode",
1033 "resetResultMode",
1034 ),
1035 }),
1036 );
1037 }
1038 }
1039 return result;
1040 };
1041 return checkpoint !== undefined && classification?.backpressure
1042 ? checkpoint.runWithAdmission(execute)
1043 : execute();
1044 });
1045
1046 const run = (sql: string, params: ReadonlyArray<unknown>, raw = false) =>
1047 Effect.flatMap(statementFor(sql, params), (statement) =>
1048 runStatement(statement, params, raw ? "raw" : "rows"),
1049 );
1050 const runValues = (sql: string, params: ReadonlyArray<unknown>) =>
1051 Effect.flatMap(statementFor(sql, params), (statement) =>
1052 runStatement(statement, params, "values"),
1053 );
1054
1055 return identity<Connection>({
1056 execute(sql, params, rowTransform) {
1057 return rowTransform ? Effect.map(run(sql, params), rowTransform) : run(sql, params);
1058 },
1059 executeRaw(sql, params) {
1060 return run(sql, params, true);
1061 },
1062 executeValues(sql, params) {
1063 return runValues(sql, params);
1064 },
1065 executeValuesUnprepared(sql, params) {
1066 return Effect.flatMap(prepare(sql), (statement) =>
1067 runStatement(statement, params ?? [], "values"),
1068 );
1069 },
1070 executeUnprepared(sql, params, rowTransform) {
1071 const effect = prepare(sql).pipe(
1072 Effect.flatMap((statement) => runStatement(statement, params ?? [])),
1073 );
1074 return rowTransform ? Effect.map(effect, rowTransform) : effect;
1075 },
1076 executeStream(_sql, _params) {
1077 return Stream.die(new UnsupportedNodeSqliteOperationError());
1078 },
1079 });
1080 });
1081
1082 const semaphore = yield* Semaphore.make(1);
1083 const connection = yield* makeConnection;
1084 // A reader that cannot start turns off the whole pool, so routing never picks a dead slot.
1085 const readerPool = { unavailable: false };
1086 const readers =
1087 options.readerWorker && isFileDatabase
1088 ? yield* Effect.forEach(Array.from({ length: READER_POOL_SIZE }), () =>
1089 Effect.map(
1090 Effect.all([makeReader(options, readerPool), Semaphore.make(1)]),
1091 ([acquire, lock]) => ({
1092 acquire,
1093 lock,
1094 claims: 0,
1095 }),
1096 ),
1097 )
1098 : [];
1099
1100 const writer = { name: "writer" as const, lock: semaphore, connection };
1101 let activeWriter: ConnectionOwner | undefined;
1102 // Callers must run route uninterruptibly and pass `claimed` a finalizer that runs `release` once.
1103 const route = (claimed: (release: Effect.Effect<void>) => Effect.Effect<void>) =>
1104 Effect.withFiber((fiber) => {
1105 if (readers.length === 0 || readerPool.unavailable || !Context.get(fiber.context, ReadOnly)) {
1106 return Effect.succeed(writer);
1107 }
1108 // Least claimed wins and ties go to the lowest slot, so idle slots never start a worker.
1109 let slot = readers[0]!;
1110 for (const candidate of readers) if (candidate.claims < slot.claims) slot = candidate;
1111 slot.claims++;
1112 const reader = slot;
1113 return Effect.andThen(
1114 claimed(
1115 Effect.sync(() => {
1116 reader.claims--;
1117 }),
1118 ),
1119 Effect.map(reader.acquire, (readerConnection) =>
1120 readerConnection === undefined
1121 ? writer
1122 : { name: "reader" as const, lock: reader.lock, connection: readerConnection },
1123 ),
1124 );
1125 });
1126
1127 const acquired = (
1128 target: { readonly name: Acquired["connection"] },
1129 clock: Clock.Clock,
1130 start: bigint,
1131 ): Acquired => ({
1132 connection: target.name,
1133 lockWaitMs: Number((clock.monotonicTimeNanosUnsafe() - start) / 1000n) / 1000,
1134 });
1135
1136 const writerBorrowBatchSize = 16;
1137 let writerBorrowsSinceYield = 0;
1138 const borrower: Borrower = (use) =>
1139 Effect.uninterruptibleMask((restore) => {
1140 let release: Effect.Effect<void> = Effect.void;
1141 const claimed = (next: Effect.Effect<void>) =>
1142 Effect.sync(() => {
1143 release = next;
1144 });
1145 return Effect.flatMap(route(claimed), (target) => restore(borrowTarget(target, use))).pipe(
1146 Effect.ensuring(Effect.suspend(() => release)),
1147 );
1148 });
1149 const borrowTarget = <A, E, R>(
1150 target: {
1151 readonly name: Acquired["connection"];
1152 readonly lock: Semaphore.Semaphore;
1153 readonly connection: Connection;
1154 },
1155 use: (connection: Connection) => Effect.Effect<A, E, R>,
1156 ): Effect.Effect<A, E, R> =>
1157 Effect.withFiber((fiber) => {
1158 const clock = fiber.getRef(Clock.Clock);
1159 const start = clock.monotonicTimeNanosUnsafe();
1160 const ownerAtWaitStart = target.name === "writer" ? activeWriter : undefined;
1161 const owner = target.name === "writer" ? connectionOwner(fiber) : undefined;
1162 const useConnection = Effect.suspend(() => {
1163 if (target.name === "writer") activeWriter = owner;
1164 if (
1165 fiber.getRef(References.TracerEnabled) &&
1166 fiber.getRef(Statement.SpanPropagationEnabled)
1167 ) {
1168 annotateAcquired(fiber, acquired(target, clock, start), ownerAtWaitStart);
1169 }
1170 return use(target.connection);
1171 });
1172 const useOwnedConnection =
1173 target.name === "writer"
1174 ? Effect.onExit(useConnection, () =>
1175 Effect.sync(() => {
1176 if (activeWriter === owner) activeWriter = undefined;
1177 }),
1178 )
1179 : useConnection;
1180 if (!options.checkpointWorker || target.name !== "writer") {
1181 return target.lock.withPermits(1)(useOwnedConnection);
1182 }
1183 const writerUse = Effect.onExit(useOwnedConnection, (exit) =>
1184 Effect.sync(() => {
1185 writerBorrowsSinceYield = Exit.hasInterrupts(exit)
1186 ? 0
1187 : Math.min(writerBorrowsSinceYield + 1, writerBorrowBatchSize);
1188 }),
1189 );
1190 return Effect.flatMap(target.lock.withPermitsIfAvailable(1)(writerUse), (result) =>
1191 Option.isSome(result)
1192 ? Effect.succeed(result.value)
1193 : target.lock.withPermits(1)(
1194 Effect.andThen(
1195 // Failed SQL also consumes the batch; cancellation hands off immediately.
1196 Effect.suspend(() => {
1197 if (writerBorrowsSinceYield < writerBorrowBatchSize) return Effect.void;
1198 writerBorrowsSinceYield = 0;
1199 return Effect.callback<void>((resume) => {
1200 const immediate = setImmediate(() => resume(Effect.void));
1201 return Effect.sync(() => clearImmediate(immediate));
1202 });
1203 }),
1204 writerUse,
1205 ),
1206 ),
1207 );
1208 });
1209 const claimedUntilScopeCloses = (release: Effect.Effect<void>) =>
1210 Effect.withFiber((fiber) =>
1211 Scope.addFinalizer(Context.getUnsafe(fiber.context, Scope.Scope), release),
1212 );
1213 const transactionAcquirer = Effect.uninterruptible(route(claimedUntilScopeCloses)).pipe(
1214 Effect.flatMap((target) =>
1215 Effect.uninterruptibleMask((restore) => {
1216 const fiber = Fiber.getCurrent()!;
1217 const scope = Context.getUnsafe(fiber.context, Scope.Scope);
1218 const clock = fiber.getRef(Clock.Clock);
1219 const start = clock.monotonicTimeNanosUnsafe();
1220 const pending = fiber.getRef(TransactionAcquire);
1221 const ownerAtWaitStart = target.name === "writer" ? activeWriter : undefined;
1222 const owner = target.name === "writer" ? connectionOwner(fiber) : undefined;
1223 const acquire = Effect.tap((pending?.restore ?? restore)(target.lock.take(1)), () => {
1224 if (target.name === "writer") activeWriter = owner;
1225 if (pending !== undefined) {
1226 pending.acquired = acquired(target, clock, start);
1227 pending.ownerAtWaitStart = ownerAtWaitStart;
1228 }
1229 return Scope.addFinalizer(
1230 scope,
1231 Effect.sync(() => {
1232 if (target.name === "writer" && activeWriter === owner) activeWriter = undefined;
1233 }).pipe(Effect.andThen(target.lock.release(1))),
1234 );
1235 });
1236 return Effect.as(
1237 // A scheduler yield after taking the permit must not precede its release finalizer.
1238 Effect.provideService(acquire, Scheduler.PreventSchedulerYield, true),
1239 target.connection,
1240 );
1241 }),
1242 ),
1243 );
1244
1245 const client = yield* Client.make({
1246 driver: "node:sqlite",
1247 acquirer: transactionAcquirer,
1248 borrower,
1249 compiler,
1250 transactionAcquirer,
1251 // BEGIN IMMEDIATE avoids read-then-write SQLITE_BUSY_SNAPSHOT, which busy_timeout cannot resolve.
1252 beginTransaction: options.readonly === true ? "BEGIN" : "BEGIN IMMEDIATE",
1253 spanAttributes: [
1254 ...(options.spanAttributes ? Object.entries(options.spanAttributes) : []),
1255 [ATTR_DB_SYSTEM_NAME, "sqlite"],
1256 ],
1257 });
1258 const withTransaction = client.withTransaction;
1259 return Object.assign(client, {
1260 withTransaction: <A, E, R>(effect: Effect.Effect<A, E, R>) =>
1261 Effect.uninterruptibleMask((restore) => {
1262 // SqlClient masks setup; acquisition must preserve the caller's interruptibility.
1263 const pending: {
1264 acquired?: Acquired;
1265 ownerAtWaitStart: ConnectionOwner | undefined;
1266 restore: typeof Effect.interruptible | undefined;
1267 } = {
1268 ownerAtWaitStart: undefined,
1269 restore,
1270 };
1271 const body = Effect.withFiber<A, E, R>((fiber) => {
1272 annotateAcquired(fiber, pending.acquired, pending.ownerAtWaitStart);
1273 pending.restore = undefined;
1274 return effect;
1275 });
1276 return restore(
1277 withTransaction(body).pipe(Effect.provideService(TransactionAcquire, pending)),
1278 );
1279 }),
1280 });
1281});
1282
1283export const layer = (config: SqliteClientConfig): Layer.Layer<Client.SqlClient, SqlError> =>
1284 Layer.effect(Client.SqlClient, make(config)).pipe(Layer.provide(Reactivity.layer));