The best way to control Claude Code, Codex and more from any computer or phone

main
ts 1,147 lines 42.4 KB
Raw
1 import * as NodeSqlite from "node:sqlite";
2 import * as NodeUtil from "node:util";
3 import * as NodeWorkerThreads from "node:worker_threads";
4
5 import * as Cache from "effect/Cache";
6 import * as Clock from "effect/Clock";
7 import * as Duration from "effect/Duration";
8 import * as Effect from "effect/Effect";
9 import * as Result from "effect/Result";
10 import * as Exit from "effect/Exit";
11 import * as Fiber from "effect/Fiber";
12 import { identity } from "effect/Function";
13 import * as Layer from "effect/Layer";
14 import * as Option from "effect/Option";
15 import * as Predicate from "effect/Predicate";
16 import * as References from "effect/References";
17 import * as Schema from "effect/Schema";
18 import * as Scheduler from "effect/Scheduler";
19 import * as Scope from "effect/Scope";
20 import * as Semaphore from "effect/Semaphore";
21 import * as Context from "effect/Context";
22 import * as Stream from "effect/Stream";
23 import * as Tracer from "effect/Tracer";
24 import * as Reactivity from "effect/reactivity/Reactivity";
25 import * as Client from "effect/sql/SqlClient";
26 import type { Borrower, Connection } from "effect/sql/SqlConnection";
27 import { SqlError, classifySqliteError } from "effect/sql/SqlError";
28 import * as Statement from "effect/sql/Statement";
29
30 const ATTR_DB_SYSTEM_NAME = "db.system.name";
31
32 export 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 /** Runs the writer and WAL checkpoints off the event loop. Ignored for in-memory databases. */
39 readonly writerWorker?: boolean | undefined;
40 }
41
42 // SqlClient masks transaction acquisition, so readOnly carries the caller's interruption policy.
43 const 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. */
49 export 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
54 interface Acquired {
55 readonly connection: "writer" | "reader";
56 readonly lockWaitMs: number;
57 }
58
59 interface ConnectionOwner {
60 readonly fiberId: number;
61 readonly name: string;
62 readonly traceId?: string;
63 readonly spanId?: string;
64 }
65
66 const 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
78 const 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
99 const 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
108 export 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
120 export 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
129 const 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.
147 const 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.
159 const LARGE_BINDING_SIZE = 64 * 1024;
160
161 const isBlob = (value: unknown): value is ArrayBufferView | ArrayBufferLike =>
162 ArrayBuffer.isView(value) || NodeUtil.types.isAnyArrayBuffer(value);
163
164 const bindingSize = (value: unknown): number => {
165 if (typeof value === "string") {
166 return value.length;
167 }
168 return isBlob(value) ? value.byteLength : 0;
169 };
170
171 const 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 // A reader refuses a database outside WAL mode with this code; retrying cannot change that.
197 const NOT_WAL = "ERR_SQLITE_NOT_WAL";
198
199 // Inline worker source also runs in bundles and single-executable builds without module files.
200 const workerSource = `
201 const { parentPort, workerData } = require("node:worker_threads");
202 const { DatabaseSync } = require("node:sqlite");
203 const describe = (error) => ({
204 message: String(error && error.message ? error.message : error),
205 code: error && error.code,
206 errcode: error && error.errcode,
207 errstr: error && error.errstr,
208 });
209 let db;
210 try {
211 // A read-only open cannot be undone by SQL, unlike PRAGMA query_only.
212 const next = new DatabaseSync(workerData.filename, {
213 readOnly: workerData.readOnly,
214 allowExtension: workerData.allowExtension,
215 });
216 next.exec("PRAGMA busy_timeout = 5000;");
217 // Reads the header, so a file that is not a database fails here rather than on first use.
218 const mode = String(next.prepare("PRAGMA journal_mode").get().journal_mode).toLowerCase();
219 // Without WAL a reader's shared lock blocks the writer's commits, so refuse.
220 if (workerData.requireWal && mode !== "wal") {
221 throw Object.assign(new Error("database uses journal_mode " + mode + ", not wal"), { code: ${JSON.stringify(NOT_WAL)} });
222 }
223 db = next;
224 parentPort.postMessage({ type: "ready" });
225 } catch (error) {
226 parentPort.postMessage({ type: "unavailable", ...describe(error) });
227 }
228 const statements = new Map();
229 const prepare = (sql, cached) => {
230 if (!cached) return db.prepare(sql);
231 let statement = statements.get(sql);
232 if (statement === undefined) {
233 statement = db.prepare(sql);
234 if (statements.size >= workerData.prepareCacheSize) statements.delete(statements.keys().next().value);
235 } else {
236 statements.delete(sql);
237 }
238 statements.set(sql, statement);
239 return statement;
240 };
241 parentPort.on("message", (request) => {
242 if (request.type === "close") {
243 // Closing lets SQLite checkpoint and remove the WAL, which terminating would skip.
244 try {
245 if (db !== undefined) db.close();
246 } catch {}
247 parentPort.close();
248 return;
249 }
250 const { id, sql, params, cached, values, raw, safeIntegers, received, savepoints } = request;
251 let operation = "open";
252 let iterator;
253 try {
254 if (db === undefined) throw new Error("database connection is unavailable");
255 operation = "execute";
256 for (const savepoint of savepoints) db.exec(savepoint);
257 operation = "prepare";
258 const statement = prepare(sql, cached);
259 operation = "execute";
260 statement.setReadBigInts(safeIntegers);
261 if (statement.columns().length > 0) {
262 statement.setReturnArrays(values);
263 let rows = [];
264 let bytes = 0;
265 let pendingBatch = false;
266 iterator = statement.iterate(...params);
267 for (const row of iterator) {
268 let rowBytes = 0;
269 for (const key in row) {
270 const value = row[key];
271 rowBytes += typeof value === "string" ? value.length * 2
272 : ArrayBuffer.isView(value) ? value.byteLength : 8;
273 }
274 if (rows.length > 0 && (rows.length >= 16384 || bytes + rowBytes > 8 * 1024 * 1024)) {
275 // Build the next batch during receipt, but allow only one unacknowledged message.
276 if (pendingBatch) Atomics.wait(received, 0, 0);
277 Atomics.store(received, 0, 0);
278 parentPort.postMessage({ id, rows, more: true });
279 rows = [];
280 bytes = 0;
281 pendingBatch = true;
282 }
283 rows.push(row);
284 bytes += rowBytes;
285 }
286 if (pendingBatch) Atomics.wait(received, 0, 0);
287 parentPort.postMessage({ id, rows });
288 } else {
289 const result = statement.run(...params);
290 parentPort.postMessage({ id, rows: raw ? result : [] });
291 }
292 } catch (error) {
293 // A step that throws leaves its statement active; an active write blocks every later COMMIT.
294 try {
295 iterator?.return();
296 } catch {}
297 // A failed iterator can report its previous SQLite error when the statement is reused.
298 statements.delete(sql);
299 parentPort.postMessage({ id, error: { operation, ...describe(error) } });
300 }
301 });
302 `;
303
304 interface WorkerRequest {
305 readonly sql: string;
306 readonly params: ReadonlyArray<unknown>;
307 readonly cached: boolean;
308 readonly values: boolean;
309 readonly raw: boolean;
310 }
311
312 interface WorkerFailure {
313 readonly message: string;
314 readonly code?: unknown;
315 readonly errcode?: unknown;
316 readonly errstr?: unknown;
317 }
318
319 type WorkerStartup =
320 | { readonly type: "ready" }
321 | ({ readonly type: "unavailable" } & WorkerFailure);
322
323 type WorkerReply =
324 | { readonly id: number; readonly rows: ReadonlyArray<any>; readonly more?: boolean }
325 | {
326 readonly id: number;
327 readonly error: WorkerFailure & { readonly operation: "open" | "prepare" | "execute" };
328 };
329
330 const workerFailureMessages = {
331 open: "Failed to open database",
332 prepare: "Failed to prepare statement",
333 execute: "Failed to execute statement",
334 } as const;
335
336 const failed = (cause: unknown, message: string, operation: string) =>
337 new SqlError({ reason: classifyError(cause, message, operation) });
338
339 const failedWith = (
340 { message, code, errcode, errstr }: WorkerFailure,
341 operation: keyof typeof workerFailureMessages,
342 ) =>
343 failed(
344 Object.assign(new Error(message), { code, errcode, errstr }),
345 workerFailureMessages[operation],
346 operation,
347 );
348
349 // Read-transaction cleanup succeeds after worker exit because it can no longer change anything.
350 const endsReadTransaction = (sql: string) => /^\s*ROLLBACK\b|^\s*COMMIT\b/i.test(sql);
351
352 // WAL readers run concurrently; one worker serialized every read behind the slowest transaction.
353 const READER_POOL_SIZE = 4;
354 const READER_STARTUP_TIMEOUT = Duration.seconds(10);
355 const READER_RETRY_DELAY = Duration.seconds(1);
356 const READER_RETRY_MAX_DELAY = Duration.minutes(1);
357
358 // Bind transactions to one worker; fail after its exit instead of resuming outside the transaction on a replacement.
359 const makeWorkerConnection = Effect.fnUntraced(function* (options: {
360 readonly filename: string;
361 readonly role: "reader" | "writer";
362 readonly readOnly: boolean;
363 readonly onReply?: () => void;
364 }) {
365 const label = options.role === "reader" ? "Database reader" : "Database writer";
366 const readOnly = options.readOnly;
367 let current:
368 | {
369 readonly worker: NodeWorkerThreads.Worker;
370 readonly connection: Connection;
371 readonly started: Promise<WorkerStartup>;
372 }
373 | undefined;
374 let nextRequestId = 0;
375 // Materialize nested savepoints with the next statement; empty jobs need no worker round trip.
376 const pendingSavepoints: string[] = [];
377 // A replacement writer would lose the pragmas callers set on it, such as foreign_keys.
378 let writerStopped: string | undefined;
379
380 const start = () => {
381 const worker = new NodeWorkerThreads.Worker(workerSource, {
382 eval: true,
383 workerData: {
384 filename: options.filename,
385 readOnly,
386 requireWal: options.role === "reader",
387 allowExtension: false,
388 prepareCacheSize: 200,
389 },
390 });
391 let announce: (startup: WorkerStartup) => void = () => {};
392 const started = new Promise<WorkerStartup>((resolve) => {
393 announce = resolve;
394 });
395 let stoppedMessage: string | undefined;
396 const pending = new Map<
397 number,
398 {
399 readonly endsTransaction: boolean;
400 readonly received: Int32Array;
401 readonly rows: Array<unknown>;
402 readonly resume: (reply: WorkerReply) => void;
403 }
404 >();
405 const stop = (message: string) => {
406 stoppedMessage = message;
407 if (options.role === "writer") writerStopped = message;
408 announce({ type: "unavailable", message });
409 if (current?.worker === worker) current = undefined;
410 for (const [id, request] of pending) {
411 request.resume(
412 request.endsTransaction
413 ? { id, rows: [] }
414 : { id, error: { operation: "execute", message } },
415 );
416 }
417 pending.clear();
418 };
419 worker.on("message", (reply: WorkerReply | WorkerStartup) => {
420 if ("type" in reply) {
421 announce(reply);
422 if (pending.size === 0) worker.unref();
423 return;
424 }
425 const request = pending.get(reply.id);
426 if (request === undefined) return;
427 if ("rows" in reply && reply.more) {
428 for (const row of reply.rows) request.rows.push(row);
429 Atomics.store(request.received, 0, 1);
430 Atomics.notify(request.received, 0);
431 return;
432 }
433 pending.delete(reply.id);
434 if (pending.size === 0) worker.unref();
435 options.onReply?.();
436 if ("rows" in reply && request.rows.length > 0) {
437 for (const row of reply.rows) request.rows.push(row);
438 request.resume({ id: reply.id, rows: request.rows });
439 } else {
440 request.resume(reply);
441 }
442 });
443 worker.on("error", (cause) => stop(`${label} failed: ${cause.message}`));
444 worker.on("exit", (code) => stop(`${label} exited with code ${code}`));
445
446 const request = (input: WorkerRequest) =>
447 Effect.withFiber<ReadonlyArray<any>, SqlError>((fiber) => {
448 // A COMMIT that never reached a writer must fail; only its ROLLBACK is moot.
449 const endsTransaction = readOnly
450 ? endsReadTransaction(input.sql)
451 : /^\s*ROLLBACK\b/i.test(input.sql);
452 if (stoppedMessage !== undefined) {
453 return endsTransaction
454 ? Effect.succeed([])
455 : Effect.fail(failedWith({ message: stoppedMessage }, "execute"));
456 }
457 const safeIntegers = Boolean(Context.get(fiber.context, Client.SafeIntegers));
458 const reply = Effect.callback<ReadonlyArray<any>, SqlError>((resume) => {
459 const id = nextRequestId++;
460 const received = new Int32Array(new SharedArrayBuffer(Int32Array.BYTES_PER_ELEMENT));
461 pending.set(id, {
462 endsTransaction,
463 received,
464 rows: [],
465 resume: (reply) =>
466 resume(
467 "rows" in reply
468 ? Effect.succeed(reply.rows)
469 : Effect.fail(failedWith(reply.error, reply.error.operation)),
470 ),
471 });
472 if (pending.size === 1) worker.ref();
473 const cached = input.cached && !hasLargeBinding(input.params);
474 const message = {
475 id,
476 safeIntegers,
477 ...input,
478 cached,
479 received,
480 savepoints: [...pendingSavepoints],
481 };
482 // oxlint-disable-next-line unicorn/require-post-message-target-origin -- Node workers do not accept a target origin.
483 worker.postMessage(message);
484 // An unsent savepoint must stay queued, or its rollback would target an older sibling's.
485 pendingSavepoints.length = 0;
486 });
487 // A posted write runs to the end, as it did on the event loop; the caller and its lock
488 // must not move on while the worker is still applying it. Reads stay cancellable.
489 return options.role === "writer" ? Effect.uninterruptible(reply) : reply;
490 });
491
492 const connection = identity<Connection>({
493 execute(sql, params, rowTransform) {
494 const effect = request({ sql, params, cached: true, values: false, raw: false });
495 return rowTransform ? Effect.map(effect, rowTransform) : effect;
496 },
497 executeRaw(sql, params) {
498 return request({ sql, params, cached: true, values: false, raw: true });
499 },
500 executeValues(sql, params) {
501 return request({ sql, params, cached: true, values: true, raw: false });
502 },
503 executeValuesUnprepared(sql, params) {
504 return request({ sql, params: params ?? [], cached: false, values: true, raw: false });
505 },
506 executeUnprepared(sql, params, rowTransform) {
507 // Read snapshots need deferred BEGIN; readonly connections reject IMMEDIATE and EXCLUSIVE.
508 const statement = readOnly && /^\s*BEGIN\b/i.test(sql) ? "BEGIN" : sql;
509 const effect = Effect.suspend(() => {
510 if (!readOnly && /^SAVEPOINT effect_sql_\d+$/.test(statement)) {
511 pendingSavepoints.push(statement);
512 return Effect.succeed([]);
513 }
514 const rollback = /^ROLLBACK TO SAVEPOINT (effect_sql_\d+)$/.exec(statement);
515 if (rollback !== null) {
516 const pending = pendingSavepoints.lastIndexOf(`SAVEPOINT ${rollback[1]}`);
517 if (pending !== -1) {
518 pendingSavepoints.splice(pending);
519 return Effect.succeed([]);
520 }
521 }
522 if (statement === "COMMIT" || statement === "ROLLBACK") pendingSavepoints.length = 0;
523 return request({
524 sql: statement,
525 params: params ?? [],
526 cached: false,
527 values: false,
528 raw: false,
529 });
530 });
531 return rowTransform ? Effect.map(effect, rowTransform) : effect;
532 },
533 executeStream(_sql, _params) {
534 return Stream.die(new UnsupportedNodeSqliteOperationError());
535 },
536 });
537 current = { worker, connection, started };
538 return current;
539 };
540
541 yield* Effect.addFinalizer(() =>
542 Effect.promise(async () => {
543 const open = current;
544 if (open === undefined) return;
545 if (options.role === "writer") {
546 await new Promise<void>((resolve) => {
547 open.worker.once("exit", () => resolve());
548 open.worker.ref();
549 // oxlint-disable-next-line unicorn/require-post-message-target-origin -- Node workers do not accept a target origin.
550 open.worker.postMessage({ type: "close" });
551 });
552 }
553 await open.worker.terminate();
554 }),
555 );
556
557 const retire = (worker: NodeWorkerThreads.Worker) => {
558 if (current?.worker === worker) current = undefined;
559 void worker.terminate();
560 };
561
562 return Effect.suspend((): Effect.Effect<Connection, SqlError> => {
563 if (current === undefined && writerStopped !== undefined) {
564 return Effect.fail(failedWith({ message: writerStopped }, "execute"));
565 }
566 let entry: NonNullable<typeof current>;
567 try {
568 entry = current ?? start();
569 } catch (cause) {
570 return Effect.fail(failed(cause, workerFailureMessages.open, "open"));
571 }
572 const started = Effect.promise(() => entry.started);
573 return Effect.flatMap(
574 Effect.withFiber((fiber) => {
575 const restore = Context.get(fiber.context, ReadOnly) ?? identity;
576 // Readers fall back to the writer; the writer has no fallback, so a slow disk only delays it.
577 return restore(
578 options.role === "reader"
579 ? Effect.timeoutOption(started, READER_STARTUP_TIMEOUT)
580 : Effect.asSome(started),
581 );
582 }),
583 (startup): Effect.Effect<Connection, SqlError> => {
584 const outcome = Option.getOrUndefined(startup);
585 if (outcome?.type === "ready") {
586 // A late ready event must not hand out a worker retired by another acquire timeout.
587 return current === entry
588 ? Effect.succeed(entry.connection)
589 : Effect.fail(failedWith({ message: `${label} stopped while starting` }, "open"));
590 }
591 retire(entry.worker);
592 return Effect.fail(
593 failedWith(
594 outcome ?? {
595 message: `${label} did not open the database within ${Duration.toMillis(READER_STARTUP_TIMEOUT)} ms`,
596 },
597 "open",
598 ),
599 );
600 },
601 );
602 });
603 });
604
605 const startFailure = (error: SqlError) => {
606 const cause = error.reason.cause;
607 return {
608 reason: cause instanceof Error ? cause.message : error.message,
609 permanent: Predicate.hasProperty(cause, "code") && cause.code === NOT_WAL,
610 };
611 };
612
613 // A reader that cannot start turns off the whole pool, so routing never picks a dead slot, until
614 // one background retry starts it again.
615 const makeReader = Effect.fnUntraced(function* (filename: string, pool: { unavailable: boolean }) {
616 const acquire = yield* makeWorkerConnection({ filename, role: "reader", readOnly: true });
617 const scope = yield* Effect.scope;
618 const context = yield* Effect.context<never>();
619
620 const recover = (delay: Duration.Duration): Effect.Effect<void> =>
621 Effect.sleep(delay).pipe(
622 Effect.andThen(acquire),
623 Effect.matchEffect({
624 onSuccess: () =>
625 Effect.suspend(() => {
626 pool.unavailable = false;
627 return Effect.logInfo(
628 "SQLite reader worker available again; reads use the reader workers",
629 );
630 }),
631 onFailure: (error) => {
632 const { reason, permanent } = startFailure(error);
633 return permanent
634 ? Effect.logInfo(
635 "SQLite reader worker retries stopped permanently; reads use the writer",
636 { reason },
637 )
638 : recover(Duration.min(Duration.times(delay, 2), READER_RETRY_MAX_DELAY));
639 },
640 }),
641 );
642
643 return Effect.suspend((): Effect.Effect<Connection | undefined> =>
644 pool.unavailable
645 ? Effect.undefined
646 : acquire.pipe(
647 Effect.map((connection) => (pool.unavailable ? undefined : connection)),
648 Effect.catch((error) => {
649 if (pool.unavailable) return Effect.undefined;
650 pool.unavailable = true;
651 const { reason, permanent } = startFailure(error);
652 // Pool state belongs to the client: never log or retry with the failed read's services.
653 return Effect.logWarning("SQLite reader worker unavailable; reads use the writer", {
654 reason,
655 }).pipe(
656 Effect.andThen(
657 permanent ? Effect.void : Effect.forkIn(recover(READER_RETRY_DELAY), scope),
658 ),
659 Effect.updateContext((_: Context.Context<never>) => context),
660 Effect.as(undefined),
661 );
662 }),
663 ),
664 );
665 });
666
667 // A checkpoint copies WAL pages into the database and syncs it. On the writer's thread every
668 // write would wait for that sync, so a second connection checkpoints beside the writes.
669 const CHECKPOINT_FRAMES = 1000;
670 const CHECKPOINT_IDLE_MS = 100;
671 // If checkpoints fall behind constant writes, the writer checkpoints itself, delaying writes only.
672 const WRITER_BACKSTOP_FRAMES = 10_000;
673
674 const checkpointWorkerSource = `
675 const { parentPort, workerData } = require("node:worker_threads");
676 const { DatabaseSync } = require("node:sqlite");
677 let db;
678 let frames;
679 let passive;
680 let idle;
681 const check = (force) => {
682 if (db === undefined) {
683 db = new DatabaseSync(workerData.filename);
684 db.exec("PRAGMA busy_timeout = 5000;");
685 // macOS requires F_FULLFSYNC to flush the drive cache.
686 db.exec("PRAGMA checkpoint_fullfsync = ON;");
687 frames = db.prepare("PRAGMA wal_checkpoint(NOOP)");
688 passive = db.prepare("PRAGMA wal_checkpoint(PASSIVE)");
689 }
690 const before = frames.get();
691 let state = before;
692 if (state.log - state.checkpointed >= workerData.frames || (force && state.log > state.checkpointed)) {
693 passive.get();
694 state = frames.get();
695 }
696 // The WAL restarts only once every frame is checkpointed, so catch up when writes pause.
697 // A reader pinning the WAL stops progress; the next write retries instead of a timer loop.
698 const progressed = !force || state.checkpointed > before.checkpointed;
699 if (state.log > state.checkpointed && progressed) {
700 idle = setTimeout(() => run(true), workerData.idleMs);
701 }
702 parentPort.postMessage({ type: "checked", log: state.log, checkpointed: state.checkpointed });
703 };
704 const run = (force) => {
705 clearTimeout(idle);
706 try {
707 check(force);
708 } catch (error) {
709 parentPort.postMessage({ type: "failed", message: String(error && error.message ? error.message : error) });
710 }
711 };
712 parentPort.on("message", () => run(false));
713 `;
714
715 const sqliteVersion = () => {
716 const db = new NodeSqlite.DatabaseSync(":memory:");
717 try {
718 return String(db.prepare("SELECT sqlite_version() AS version").get()?.version);
719 } finally {
720 db.close();
721 }
722 };
723
724 // Returns the writer's per-statement notification, or undefined when the writer checkpoints alone.
725 const startCheckpointWorker = Effect.fnUntraced(function* (filename: string) {
726 const warn = (reason: string) =>
727 Effect.logWarning("SQLite checkpoint worker unavailable; the writer checkpoints its own WAL", {
728 reason,
729 });
730 const version = sqliteVersion();
731 const [major = 0, minor = 0] = version.split(".").map(Number);
732 // Older SQLite treats NOOP as PASSIVE, so counting frames would checkpoint on every write.
733 if (major < 3 || (major === 3 && minor < 51)) {
734 yield* warn(`SQLite ${version} has no NOOP checkpoint`);
735 return undefined;
736 }
737 const started = Result.try(
738 () =>
739 new NodeWorkerThreads.Worker(checkpointWorkerSource, {
740 eval: true,
741 workerData: { filename, frames: CHECKPOINT_FRAMES, idleMs: CHECKPOINT_IDLE_MS },
742 }),
743 );
744 if (Result.isFailure(started)) {
745 yield* warn(String(started.failure));
746 return undefined;
747 }
748 const worker = started.success;
749 worker.unref();
750 const runFork = Effect.runForkWith(yield* Effect.context<never>());
751 let stopped = false;
752 const stop = (reason: string) => {
753 if (stopped) return;
754 stopped = true;
755 void worker.terminate();
756 runFork(warn(reason));
757 };
758 worker.on("message", (message: { readonly type?: unknown; readonly message?: unknown }) => {
759 if (message?.type === "failed") stop(String(message.message));
760 });
761 worker.on("error", (cause) => stop(cause.message));
762 worker.on("exit", (code) => stop(`exited with code ${code}`));
763 yield* Effect.addFinalizer(() =>
764 Effect.promise(() => {
765 stopped = true;
766 return worker.terminate();
767 }),
768 );
769 let scheduled = false;
770 return () => {
771 if (stopped || scheduled) return;
772 scheduled = true;
773 // One check per event-loop turn is enough, however many statements finished in it.
774 setImmediate(() => {
775 scheduled = false;
776 // oxlint-disable-next-line unicorn/require-post-message-target-origin -- Node workers do not accept a target origin.
777 if (!stopped) worker.postMessage(0);
778 });
779 };
780 });
781
782 const make = Effect.fn("makeWithDatabase")(function* (
783 options: SqliteClientConfig,
784 ): Effect.fn.Return<Client.SqlClient, SqlError, Scope.Scope | Reactivity.Reactivity> {
785 yield* checkNodeSqliteCompat();
786
787 const compiler = Statement.makeCompilerSqlite();
788 const isFileDatabase = options.filename !== ":memory:" && options.filename !== "";
789
790 const makeConnection = Effect.gen(function* () {
791 const scope = yield* Effect.scope;
792 const db = yield* Effect.try({
793 try: () =>
794 new NodeSqlite.DatabaseSync(options.filename, {
795 readOnly: options.readonly ?? false,
796 allowExtension: false,
797 }),
798 catch: (cause) =>
799 new SqlError({
800 reason: classifyError(cause, "Failed to open database", "open"),
801 }),
802 });
803 yield* Scope.addFinalizer(
804 scope,
805 Effect.try({
806 try: () => db.close(),
807 catch: (cause) =>
808 new SqlError({
809 reason: classifyError(cause, "Failed to close database", "close"),
810 }),
811 }).pipe(Effect.orDie),
812 );
813 const statementReaderCache = new WeakMap<NodeSqlite.StatementSync, boolean>();
814 const hasRows = (statement: NodeSqlite.StatementSync): boolean => {
815 const cached = statementReaderCache.get(statement);
816 if (cached !== undefined) {
817 return cached;
818 }
819 const value = statement.columns().length > 0;
820 statementReaderCache.set(statement, value);
821 return value;
822 };
823
824 const prepare = (sql: string) =>
825 Effect.try({
826 try: () => db.prepare(sql),
827 catch: (cause) =>
828 new SqlError({
829 reason: classifyError(cause, "Failed to prepare statement", "prepare"),
830 }),
831 });
832
833 const prepareCache = yield* Cache.makeWith(prepare, {
834 capacity: 200,
835 // A transient prepare failure must not outlive the lock or missing schema.
836 timeToLive: (exit) => (Exit.isSuccess(exit) ? Duration.minutes(10) : Duration.zero),
837 });
838
839 const statementFor = (sql: string, params: ReadonlyArray<unknown>) =>
840 hasLargeBinding(params) ? prepare(sql) : Cache.get(prepareCache, sql);
841
842 const runStatement = (
843 statement: NodeSqlite.StatementSync,
844 params: ReadonlyArray<unknown>,
845 mode: "rows" | "raw" | "values" = "rows",
846 ) =>
847 Effect.withFiber<ReadonlyArray<any>, SqlError>((fiber) => {
848 let arrays = false;
849 let result: Exit.Exit<ReadonlyArray<unknown>, SqlError>;
850 try {
851 statement.setReadBigInts(Boolean(Context.get(fiber.context, Client.SafeIntegers)));
852 if (hasRows(statement)) {
853 arrays = mode === "values";
854 if (arrays) statement.setReturnArrays(true);
855 result = Exit.succeed(statement.all(...(params as any)));
856 } else {
857 const value = statement.run(...(params as any));
858 result = Exit.succeed(
859 mode === "raw" ? (value as unknown as ReadonlyArray<unknown>) : [],
860 );
861 }
862 } catch (cause) {
863 result = Exit.fail(
864 new SqlError({
865 reason: classifyError(cause, "Failed to execute statement", "execute"),
866 }),
867 );
868 }
869 if (arrays) {
870 try {
871 statement.setReturnArrays(false);
872 } catch (cause) {
873 return Exit.die(
874 new SqlError({
875 reason: classifyError(
876 cause,
877 "Failed to reset statement result mode",
878 "resetResultMode",
879 ),
880 }),
881 );
882 }
883 }
884 return result;
885 });
886
887 const run = (sql: string, params: ReadonlyArray<unknown>, raw = false) =>
888 Effect.flatMap(statementFor(sql, params), (statement) =>
889 runStatement(statement, params, raw ? "raw" : "rows"),
890 );
891 const runValues = (sql: string, params: ReadonlyArray<unknown>) =>
892 Effect.flatMap(statementFor(sql, params), (statement) =>
893 runStatement(statement, params, "values"),
894 );
895
896 return identity<Connection>({
897 execute(sql, params, rowTransform) {
898 return rowTransform ? Effect.map(run(sql, params), rowTransform) : run(sql, params);
899 },
900 executeRaw(sql, params) {
901 return run(sql, params, true);
902 },
903 executeValues(sql, params) {
904 return runValues(sql, params);
905 },
906 executeValuesUnprepared(sql, params) {
907 return Effect.flatMap(prepare(sql), (statement) =>
908 runStatement(statement, params ?? [], "values"),
909 );
910 },
911 executeUnprepared(sql, params, rowTransform) {
912 const effect = prepare(sql).pipe(
913 Effect.flatMap((statement) => runStatement(statement, params ?? [])),
914 );
915 return rowTransform ? Effect.map(effect, rowTransform) : effect;
916 },
917 executeStream(_sql, _params) {
918 return Stream.die(new UnsupportedNodeSqliteOperationError());
919 },
920 });
921 });
922
923 const semaphore = yield* Semaphore.make(1);
924 const writerWorker = options.writerWorker === true && isFileDatabase;
925 let checkpointAfterWrite: (() => void) | undefined;
926 const writerConnection: Effect.Effect<Connection, SqlError> = writerWorker
927 ? yield* makeWorkerConnection({
928 filename: options.filename,
929 role: "writer",
930 readOnly: options.readonly ?? false,
931 onReply: () => checkpointAfterWrite?.(),
932 })
933 : Effect.succeed(yield* makeConnection);
934 // Opening now fails the layer for a file that is not a database, not its first query.
935 const opened = yield* writerConnection;
936 // Started after the writer, so it stops first and the writer's close can checkpoint.
937 if (writerWorker && options.readonly !== true) {
938 checkpointAfterWrite = yield* startCheckpointWorker(options.filename);
939 if (checkpointAfterWrite !== undefined) {
940 yield* opened.executeUnprepared(
941 `PRAGMA wal_autocheckpoint = ${WRITER_BACKSTOP_FRAMES}`,
942 [],
943 undefined,
944 );
945 }
946 }
947 const readerPool = { unavailable: false };
948 const readers =
949 options.readerWorker && isFileDatabase
950 ? yield* Effect.forEach(Array.from({ length: READER_POOL_SIZE }), () =>
951 Effect.map(
952 Effect.all([makeReader(options.filename, readerPool), Semaphore.make(1)]),
953 ([acquire, lock]) => ({
954 acquire,
955 lock,
956 claimed: false,
957 }),
958 ),
959 )
960 : [];
961 // One permit per slot, so a queued read waits for any reader, not one it picked while all were busy.
962 const readerAdmission = yield* Semaphore.make(readers.length);
963
964 interface Target {
965 readonly name: Acquired["connection"];
966 readonly lock: Semaphore.Semaphore;
967 readonly connection: Connection;
968 }
969 const writer = Effect.map(writerConnection, (connection): Target => ({
970 name: "writer",
971 lock: semaphore,
972 connection,
973 }));
974 let activeWriter: ConnectionOwner | undefined;
975 // Callers must run route uninterruptibly and pass `claimed` a finalizer that runs `release` once.
976 // Waits use the caller's `restore`, not readOnly's, which may have been captured outside a mask.
977 const route = (
978 claimed: (release: Effect.Effect<void>) => Effect.Effect<void>,
979 restore: typeof Effect.interruptible,
980 ) =>
981 Effect.withFiber((fiber): Effect.Effect<Target, SqlError> => {
982 if (readers.length === 0 || readerPool.unavailable || !Context.get(fiber.context, ReadOnly)) {
983 return writer;
984 }
985 const claim = Effect.andThen(
986 restore(readerAdmission.take(1)),
987 Effect.suspend(() => {
988 // Holding a permit leaves a slot idle; the lowest wins, so idle slots never start a worker.
989 const reader = readers.find((slot) => !slot.claimed)!;
990 reader.claimed = true;
991 const release = Effect.andThen(
992 Effect.sync(() => {
993 reader.claimed = false;
994 }),
995 readerAdmission.release(1),
996 );
997 return Effect.as(claimed(release), reader);
998 }),
999 );
1000 return Effect.flatMap(
1001 // A scheduler yield after taking the permit must not precede its release finalizer.
1002 Effect.provideService(claim, Scheduler.PreventSchedulerYield, true),
1003 (reader) =>
1004 Effect.flatMap(
1005 Effect.provideService(reader.acquire, ReadOnly, restore),
1006 (readerConnection) =>
1007 readerConnection === undefined
1008 ? writer
1009 : Effect.succeed<Target>({
1010 name: "reader",
1011 lock: reader.lock,
1012 connection: readerConnection,
1013 }),
1014 ),
1015 );
1016 });
1017
1018 const acquired = (
1019 target: { readonly name: Acquired["connection"] },
1020 clock: Clock.Clock,
1021 start: bigint,
1022 ): Acquired => ({
1023 connection: target.name,
1024 lockWaitMs: Number((clock.monotonicTimeNanosUnsafe() - start) / 1000n) / 1000,
1025 });
1026
1027 const borrower: Borrower = (use) =>
1028 Effect.uninterruptibleMask((restore) => {
1029 let release: Effect.Effect<void> = Effect.void;
1030 const claimed = (next: Effect.Effect<void>) =>
1031 Effect.sync(() => {
1032 release = next;
1033 });
1034 // Started before routing, so lock wait includes waiting for reader admission.
1035 return Effect.withFiber((fiber) => {
1036 const start = fiber.getRef(Clock.Clock).monotonicTimeNanosUnsafe();
1037 return Effect.flatMap(route(claimed, restore), (target) =>
1038 restore(borrowTarget(target, use, start)),
1039 );
1040 }).pipe(Effect.ensuring(Effect.suspend(() => release)));
1041 });
1042 const borrowTarget = <A, E, R>(
1043 target: Target,
1044 use: (connection: Connection) => Effect.Effect<A, E, R>,
1045 start: bigint,
1046 ): Effect.Effect<A, E, R> =>
1047 Effect.withFiber((fiber) => {
1048 const clock = fiber.getRef(Clock.Clock);
1049 const ownerAtWaitStart = target.name === "writer" ? activeWriter : undefined;
1050 const owner = target.name === "writer" ? connectionOwner(fiber) : undefined;
1051 const useConnection = Effect.suspend(() => {
1052 if (target.name === "writer") activeWriter = owner;
1053 if (
1054 fiber.getRef(References.TracerEnabled) &&
1055 fiber.getRef(Statement.SpanPropagationEnabled)
1056 ) {
1057 annotateAcquired(fiber, acquired(target, clock, start), ownerAtWaitStart);
1058 }
1059 return use(target.connection);
1060 });
1061 const useOwnedConnection =
1062 target.name === "writer"
1063 ? Effect.onExit(useConnection, () =>
1064 Effect.sync(() => {
1065 if (activeWriter === owner) activeWriter = undefined;
1066 }),
1067 )
1068 : useConnection;
1069 return target.lock.withPermits(1)(useOwnedConnection);
1070 });
1071 const claimedUntilScopeCloses = (release: Effect.Effect<void>) =>
1072 Effect.withFiber((fiber) =>
1073 Scope.addFinalizer(Context.getUnsafe(fiber.context, Scope.Scope), release),
1074 );
1075 // Started before routing, so lock wait includes waiting for reader admission.
1076 const transactionAcquirer = Effect.uninterruptibleMask((maskRestore) =>
1077 Effect.withFiber((fiber) => {
1078 const start = fiber.getRef(Clock.Clock).monotonicTimeNanosUnsafe();
1079 const pending = fiber.getRef(TransactionAcquire);
1080 const restore = pending?.restore ?? maskRestore;
1081 return Effect.flatMap(route(claimedUntilScopeCloses, restore), (target) => {
1082 const scope = Context.getUnsafe(fiber.context, Scope.Scope);
1083 const clock = fiber.getRef(Clock.Clock);
1084 const ownerAtWaitStart = target.name === "writer" ? activeWriter : undefined;
1085 const owner = target.name === "writer" ? connectionOwner(fiber) : undefined;
1086 const acquire = Effect.tap(restore(target.lock.take(1)), () => {
1087 if (target.name === "writer") activeWriter = owner;
1088 if (pending !== undefined) {
1089 pending.acquired = acquired(target, clock, start);
1090 pending.ownerAtWaitStart = ownerAtWaitStart;
1091 }
1092 return Scope.addFinalizer(
1093 scope,
1094 Effect.sync(() => {
1095 if (target.name === "writer" && activeWriter === owner) activeWriter = undefined;
1096 }).pipe(Effect.andThen(target.lock.release(1))),
1097 );
1098 });
1099 return Effect.as(
1100 // A scheduler yield after taking the permit must not precede its release finalizer.
1101 Effect.provideService(acquire, Scheduler.PreventSchedulerYield, true),
1102 target.connection,
1103 );
1104 });
1105 }),
1106 );
1107
1108 const client = yield* Client.make({
1109 driver: "node:sqlite",
1110 acquirer: transactionAcquirer,
1111 borrower,
1112 compiler,
1113 transactionAcquirer,
1114 // BEGIN IMMEDIATE avoids read-then-write SQLITE_BUSY_SNAPSHOT, which busy_timeout cannot resolve.
1115 beginTransaction: options.readonly === true ? "BEGIN" : "BEGIN IMMEDIATE",
1116 spanAttributes: [
1117 ...(options.spanAttributes ? Object.entries(options.spanAttributes) : []),
1118 [ATTR_DB_SYSTEM_NAME, "sqlite"],
1119 ],
1120 });
1121 const withTransaction = client.withTransaction;
1122 return Object.assign(client, {
1123 withTransaction: <A, E, R>(effect: Effect.Effect<A, E, R>) =>
1124 Effect.uninterruptibleMask((restore) => {
1125 // SqlClient masks setup; acquisition must preserve the caller's interruptibility.
1126 const pending: {
1127 acquired?: Acquired;
1128 ownerAtWaitStart: ConnectionOwner | undefined;
1129 restore: typeof Effect.interruptible | undefined;
1130 } = {
1131 ownerAtWaitStart: undefined,
1132 restore,
1133 };
1134 const body = Effect.withFiber<A, E, R>((fiber) => {
1135 annotateAcquired(fiber, pending.acquired, pending.ownerAtWaitStart);
1136 pending.restore = undefined;
1137 return effect;
1138 });
1139 return restore(
1140 withTransaction(body).pipe(Effect.provideService(TransactionAcquire, pending)),
1141 );
1142 }),
1143 });
1144 });
1145
1146 export const layer = (config: SqliteClientConfig): Layer.Layer<Client.SqlClient, SqlError> =>
1147 Layer.effect(Client.SqlClient, make(config)).pipe(Layer.provide(Reactivity.layer));