import * as NodeSqlite from "node:sqlite";
import type * as NodeWorkerThreads from "node:worker_threads";
import * as NodeServices from "@effect/platform-node/NodeServices";
import { assert, it } from "@effect/vitest";
import * as Deferred from "effect/Deferred";
import * as Effect from "effect/Effect";
import * as Fiber from "effect/Fiber";
import * as FileSystem from "effect/FileSystem";
import * as Path from "effect/Path";
import * as SqlClient from "effect/sql/SqlClient";
import * as Statement from "effect/sql/Statement";
import { ChildProcess, ChildProcessSpawner } from "effect/process";
import * as Stream from "effect/Stream";
import type * as Tracer from "effect/Tracer";
import { vi } from "vite-plus/test";
import * as SqliteClient from "./nodeSqliteClient.ts";
const startedWorkers = vi.hoisted((): Array<NodeWorkerThreads.Worker> => []);
const silentWorkers = vi.hoisted(() => ({ on: false, readyOnTerminate: false }));
const failingWorkers = vi.hoisted(() => ({ message: undefined as string | undefined }));
vi.mock("node:worker_threads", async (importOriginal) => {
const actual = await importOriginal<typeof NodeWorkerThreads>();
class RecordedWorker extends actual.Worker {
constructor(...args: ConstructorParameters<typeof actual.Worker>) {
super(
...((silentWorkers.on
? ["setInterval(() => {}, 1 << 30);", { eval: true }]
: failingWorkers.message !== undefined
? [`throw new Error(${JSON.stringify(failingWorkers.message)});`, { eval: true }]
: args) as ConstructorParameters<typeof actual.Worker>),
);
startedWorkers.push(this);
}
override terminate() {
// A "ready" already queued when termination starts reaches the parent before "exit".
if (silentWorkers.readyOnTerminate) this.emit("message", { type: "ready" });
return super.terminate();
}
}
return { ...actual, Worker: RecordedWorker };
});
const withReaderClient = <A, E>(
use: (sql: SqlClient.SqlClient) => Effect.Effect<A, E, SqlClient.SqlClient>,
) =>
Effect.gen(function* () {
const fs = yield* FileSystem.FileSystem;
const path = yield* Path.Path;
const directory = yield* fs.makeTempDirectoryScoped({ prefix: "t3-sqlite-reader-" });
const filename = path.join(directory, "state.sqlite");
return yield* Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient;
yield* sql`PRAGMA journal_mode = WAL`;
yield* sql`CREATE TABLE entries(name TEXT NOT NULL)`;
yield* sql`INSERT INTO entries VALUES ('first')`;
return yield* use(sql);
}).pipe(Effect.provide(SqliteClient.layer({ filename, readerWorker: true })));
}).pipe(Effect.scoped, Effect.provide(NodeServices.layer));
const countEntries = (sql: SqlClient.SqlClient) =>
Effect.map(
sql<{ count: number }>`SELECT COUNT(*) AS count FROM entries`,
(rows) => rows[0]?.count,
);
const layer = it.layer(SqliteClient.layer({ filename: ":memory:" }));
layer("NodeSqliteClient", (it) => {
it.effect("records the writer connection owner on a waiting query", () =>
Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient;
const held = yield* Deferred.make<void>();
const release = yield* Deferred.make<void>();
const holder = yield* sql
.withTransaction(
sql`SELECT 1`.pipe(
Effect.andThen(Deferred.succeed(held, undefined)),
Effect.andThen(Deferred.await(release)),
),
)
.pipe(Effect.withSpan("test.writerHolder"), Effect.forkChild);
yield* Deferred.await(held);
const waitingSpan = yield* Deferred.make<Tracer.Span>();
const observeWaiter: Statement.Transformer = (statement, _sql, _fiber, span) =>
Deferred.succeed(waitingSpan, span).pipe(Effect.as(statement));
const waiter = yield* sql`SELECT 1`.pipe(
Effect.provideService(Statement.CurrentTransformer, observeWaiter),
Effect.provideService(Statement.SpanPropagationEnabled, true),
(effect) => Effect.forkChild(effect, { startImmediately: true }),
);
const span = yield* Deferred.await(waitingSpan);
yield* Deferred.succeed(release, undefined);
yield* Fiber.join(holder);
yield* Fiber.join(waiter);
assert.equal(span.attributes.get("db.lock_owner_at_start.name"), "test.writerHolder");
assert.typeOf(span.attributes.get("db.lock_owner_at_start.fiber_id"), "number");
assert.typeOf(span.attributes.get("db.lock_owner_at_start.trace_id"), "string");
assert.typeOf(span.attributes.get("db.lock_owner_at_start.span_id"), "string");
}),
);
it.effect("retries preparing a query after the missing schema becomes available", () =>
Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient;
const select = sql<{ name: string }>`SELECT name FROM created_after_prepare_failure`;
const error = yield* select.pipe(Effect.flip);
assert.equal(error._tag, "SqlError");
assert.equal(error.reason.operation, "prepare");
yield* sql`CREATE TABLE created_after_prepare_failure(name TEXT NOT NULL)`;
yield* sql`INSERT INTO created_after_prepare_failure VALUES ('recovered')`;
assert.deepEqual(yield* select, [{ name: "recovered" }]);
assert.deepEqual(yield* select.values, [["recovered"]]);
}),
);
it.effect("classifies constraint failures by their SQLite result code", () =>
Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient;
yield* sql`CREATE TABLE constrained(name TEXT NOT NULL UNIQUE)`;
yield* sql`INSERT INTO constrained VALUES ('taken')`;
const duplicate = yield* sql`INSERT INTO constrained VALUES ('taken')`.pipe(Effect.flip);
assert(duplicate.reason._tag === "UniqueViolation");
assert.equal(duplicate.reason.constraint, "constrained.name");
const missing = yield* sql`INSERT INTO constrained VALUES (NULL)`.pipe(Effect.flip);
assert.equal(missing.reason._tag, "ConstraintError");
}),
);
});
const largeText = "x".repeat(64 * 1024 + 1);
it.effect("reads integers on the values path as the caller asked, cached or not", () =>
Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient;
for (const value of ["small", largeText]) {
const values = sql`SELECT 1 AS id, length(${value}) AS size`.values;
assert.deepEqual(yield* values.pipe(Effect.provideService(SqlClient.SafeIntegers, true)), [
[1n, BigInt(value.length)],
]);
assert.deepEqual(yield* values, [[1, value.length]]);
}
}).pipe(Effect.provide(SqliteClient.layer({ filename: ":memory:" }))),
);
const makeTempDatabase = Effect.gen(function* () {
const fs = yield* FileSystem.FileSystem;
const path = yield* Path.Path;
const directory = yield* fs.makeTempDirectoryScoped({ prefix: "t3-sqlite-transaction-" });
const filename = path.join(directory, "state.sqlite");
const other = yield* Effect.acquireRelease(
Effect.sync(() => new NodeSqlite.DatabaseSync(filename)),
(database) => Effect.sync(() => database.close()),
);
yield* Effect.sync(() => {
other.exec(`
PRAGMA journal_mode = WAL;
CREATE TABLE counters(id INTEGER PRIMARY KEY, value INTEGER NOT NULL);
INSERT INTO counters VALUES (1, 0);
`);
});
return { filename, other };
});
it.effect("keeps another connection from committing between a transaction's read and write", () =>
Effect.gen(function* () {
const { filename, other } = yield* makeTempDatabase;
yield* Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient;
yield* sql.withTransaction(
Effect.gen(function* () {
const [row] = yield* sql<{ readonly value: number }>`
SELECT value FROM counters WHERE id = 1
`;
// Deferred BEGIN permits a competing commit, causing SQLITE_BUSY_SNAPSHOT that no timeout can resolve.
yield* Effect.sync(() =>
assert.throws(
() => other.exec("UPDATE counters SET value = value + 1 WHERE id = 1"),
/database is locked/,
),
);
yield* sql`UPDATE counters SET value = ${(row?.value ?? 0) + 10} WHERE id = 1`;
}),
);
yield* Effect.sync(() => other.exec("UPDATE counters SET value = value + 1 WHERE id = 1"));
assert.deepEqual(yield* sql`SELECT value FROM counters WHERE id = 1`.values, [[11]]);
}).pipe(Effect.provide(SqliteClient.layer({ filename })));
}).pipe(Effect.provide(NodeServices.layer)),
);
it.effect("waits for another process's lock while the database opens", () =>
Effect.gen(function* () {
const fs = yield* FileSystem.FileSystem;
const path = yield* Path.Path;
const directory = yield* fs.makeTempDirectoryScoped({ prefix: "t3-sqlite-open-" });
const filename = path.join(directory, "state.sqlite");
const seed = new NodeSqlite.DatabaseSync(filename);
seed.exec("PRAGMA journal_mode = WAL; CREATE TABLE entries(name TEXT NOT NULL)");
seed.close();
const spawner = yield* ChildProcessSpawner.ChildProcessSpawner;
const holder = yield* spawner.spawn(
ChildProcess.make(process.execPath, [
"-e",
`const db = new (require("node:sqlite").DatabaseSync)(process.argv[1]);
db.exec("PRAGMA locking_mode = EXCLUSIVE; BEGIN EXCLUSIVE");
console.log("held");
setTimeout(() => db.close(), 300);`,
filename,
]),
);
yield* Stream.runHead(holder.stdout);
yield* Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient;
assert.deepEqual(yield* sql`SELECT COUNT(*) FROM entries`.values, [[0]]);
}).pipe(Effect.provide(SqliteClient.layer({ filename, checkpointWorker: true })));
}).pipe(Effect.scoped, Effect.provide(NodeServices.layer)),
);
it.effect("reports a transaction blocked by another writer as a lock timeout", () =>
Effect.gen(function* () {
const { filename, other } = yield* makeTempDatabase;
yield* Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient;
yield* sql`PRAGMA busy_timeout = 0`;
const read = sql.withTransaction(sql`SELECT value FROM counters WHERE id = 1`.values);
yield* Effect.sync(() => other.exec("BEGIN IMMEDIATE"));
const error = yield* read.pipe(Effect.flip);
assert.equal(error.reason._tag, "LockTimeoutError");
yield* Effect.sync(() => other.exec("COMMIT"));
assert.deepEqual(yield* read, [[0]]);
}).pipe(Effect.provide(SqliteClient.layer({ filename })));
}).pipe(Effect.provide(NodeServices.layer)),
);
it.effect("lets the writer commit while a read-only transaction is open", () =>
withReaderClient((sql) =>
Effect.gen(function* () {
const snapshotTaken = yield* Deferred.make<void>();
const writer = yield* Effect.forkChild(
Deferred.await(snapshotTaken).pipe(
Effect.andThen(sql`INSERT INTO entries VALUES ('second')`),
),
);
const counts = yield* sql
.withTransaction(
Effect.gen(function* () {
const before = yield* countEntries(sql);
yield* Deferred.succeed(snapshotTaken, undefined);
// Holding the writer lock here would deadlock this join.
yield* Fiber.join(writer);
const after = yield* countEntries(sql);
return { before, after };
}),
)
.pipe(SqliteClient.readOnly);
assert.deepEqual(counts, { before: 1, after: 1 });
assert.equal(yield* countEntries(sql).pipe(SqliteClient.readOnly), 2);
}),
),
);
it.effect("serves another read while a read-only transaction is open", () =>
withReaderClient((sql) =>
Effect.gen(function* () {
const opened = yield* Deferred.make<void>();
const other = yield* Effect.forkChild(
Deferred.await(opened).pipe(Effect.andThen(countEntries(sql).pipe(SqliteClient.readOnly))),
);
yield* sql
.withTransaction(
Effect.gen(function* () {
yield* countEntries(sql);
yield* Deferred.succeed(opened, undefined);
// One shared reader would make this join wait on the transaction forever.
assert.equal(yield* Fiber.join(other), 1);
}),
)
.pipe(SqliteClient.readOnly);
}),
),
);
// regression: #19226 read-only transactions run on the separate reader connection
it.effect("rejects a write in a read-only transaction", () =>
withReaderClient((sql) =>
Effect.gen(function* () {
const error = yield* sql
.withTransaction(sql`INSERT INTO entries VALUES ('blocked')`)
.pipe(SqliteClient.readOnly, Effect.flip);
assert.equal(error._tag, "SqlError");
assert.include(String(error.reason.cause), "readonly database");
assert.equal(yield* countEntries(sql), 1);
}),
),
);
it.effect("keeps the reader read-only even if a statement turns query_only off", () =>
withReaderClient((sql) =>
Effect.gen(function* () {
const error = yield* sql
.withTransaction(
Effect.gen(function* () {
yield* sql`PRAGMA query_only = OFF`;
yield* sql`INSERT INTO entries VALUES ('blocked')`;
}),
)
.pipe(SqliteClient.readOnly, Effect.flip);
assert.equal(error._tag, "SqlError");
assert.include(String(error.reason.cause), "readonly database");
assert.equal(yield* countEntries(sql), 1);
}),
),
);
it.effect("keeps read-only work inside a write transaction on the writer", () =>
withReaderClient((sql) =>
sql.withTransaction(
Effect.gen(function* () {
yield* sql`INSERT INTO entries VALUES ('uncommitted')`;
const count = yield* sql.withTransaction(countEntries(sql)).pipe(SqliteClient.readOnly);
assert.equal(count, 2);
}),
),
),
);
it.effect("fails a read-only transaction whose reader stops, then starts a new reader", () =>
withReaderClient((sql) =>
Effect.gen(function* () {
const startedBefore = startedWorkers.length;
const error = yield* sql
.withTransaction(
Effect.gen(function* () {
yield* countEntries(sql);
yield* Effect.promise(() => startedWorkers.at(-1)!.terminate());
return yield* countEntries(sql);
}),
)
.pipe(SqliteClient.readOnly, Effect.flip);
assert.equal(error._tag, "SqlError");
assert.include(String(error.reason.cause), "Database reader exited");
assert.equal(yield* countEntries(sql).pipe(SqliteClient.readOnly), 1);
assert.equal(startedWorkers.length, startedBefore + 2);
}),
),
);
const withFileClient = <A, E>(
setup: (
sql: SqlClient.SqlClient,
filename: string,
) => Effect.Effect<A, E, SqlClient.SqlClient | FileSystem.FileSystem>,
) =>
Effect.gen(function* () {
const fs = yield* FileSystem.FileSystem;
const path = yield* Path.Path;
const directory = yield* fs.makeTempDirectoryScoped({ prefix: "t3-sqlite-reader-off-" });
const filename = path.join(directory, "state.sqlite");
return yield* Effect.gen(function* () {
return yield* setup(yield* SqlClient.SqlClient, filename);
}).pipe(Effect.provide(SqliteClient.layer({ filename, readerWorker: true })));
}).pipe(Effect.scoped, Effect.provide(NodeServices.layer));
it.effect("uses the writer when the reader cannot open the database", () =>
withFileClient((sql, filename) =>
Effect.gen(function* () {
yield* sql`PRAGMA journal_mode = WAL`;
yield* sql`CREATE TABLE entries(name TEXT NOT NULL)`;
yield* sql`INSERT INTO entries VALUES ('kept')`;
yield* (yield* FileSystem.FileSystem).remove(filename);
const startedBefore = startedWorkers.length;
assert.equal(yield* sql.withTransaction(countEntries(sql)).pipe(SqliteClient.readOnly), 1);
assert.equal(yield* sql.withTransaction(countEntries(sql)).pipe(SqliteClient.readOnly), 1);
// A read beside an open read transaction must not start a worker on the next slot.
const held = yield* Deferred.make<void>();
const release = yield* Deferred.make<void>();
const holder = yield* sql
.withTransaction(
countEntries(sql).pipe(
Effect.andThen(Deferred.succeed(held, undefined)),
Effect.andThen(Deferred.await(release)),
),
)
.pipe(SqliteClient.readOnly, Effect.forkChild);
yield* Deferred.await(held);
const other = yield* countEntries(sql).pipe(SqliteClient.readOnly, (effect) =>
Effect.forkChild(effect, { startImmediately: true }),
);
yield* Deferred.succeed(release, undefined);
yield* Fiber.join(holder);
assert.equal(yield* Fiber.join(other), 1);
assert.equal(startedWorkers.length, startedBefore + 1);
}),
),
);
it.effect("finishes masked database cleanup after its caller is interrupted", () =>
Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient;
yield* sql`CREATE TABLE cleanup_writes(value INTEGER PRIMARY KEY)`;
const entered = yield* Deferred.make<void>();
const work = yield* Effect.forkChild(
Deferred.succeed(entered, undefined).pipe(
Effect.andThen(Effect.never),
Effect.onInterrupt(() =>
Effect.gen(function* () {
yield* sql`INSERT INTO cleanup_writes VALUES (1)`;
yield* sql.withTransaction(sql`INSERT INTO cleanup_writes VALUES (2)`);
}),
),
),
{ startImmediately: true },
);
yield* Deferred.await(entered);
yield* Fiber.interrupt(work);
assert.deepEqual(yield* sql`SELECT value FROM cleanup_writes ORDER BY value`.values, [
[1],
[2],
]);
}).pipe(Effect.provide(SqliteClient.layer({ filename: ":memory:" }))),
);
// oxlint-disable-next-line t3code/no-real-time-wait-in-tests -- a zero-delay macrotask boundary that lets the worker's queued messages land, not a wait for time
const eventLoopTurn = Effect.promise(() => new Promise<void>((resolve) => setImmediate(resolve)));
it.effect.each([
{ reservation: false, masked: false },
{ reservation: false, masked: true },
{ reservation: true, masked: false },
{ reservation: true, masked: true },
])(
"preserves caller cancellation while waiting (reservation=$reservation, masked=$masked)",
({ reservation, masked }) =>
Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient;
yield* sql`CREATE TABLE entries(value INTEGER)`;
const held = yield* Deferred.make<void>();
const release = yield* Deferred.make<void>();
const holder = yield* Effect.forkChild(
sql.withTransaction(
Deferred.succeed(held, undefined).pipe(Effect.andThen(Deferred.await(release))),
),
{ startImmediately: true },
);
yield* Deferred.await(held);
const entered = yield* Deferred.make<void>();
const body = sql`INSERT INTO entries VALUES (1)`.pipe(
Effect.andThen(Deferred.succeed(entered, undefined)),
);
const acquire = reservation
? Effect.scoped(
Effect.gen(function* () {
const connection = yield* sql.reserve;
yield* connection.executeUnprepared("INSERT INTO entries VALUES (1)", [], undefined);
yield* Deferred.succeed(entered, undefined);
}),
)
: sql.withTransaction(body);
const waiter = yield* Effect.forkChild(masked ? Effect.uninterruptible(acquire) : acquire, {
startImmediately: true,
});
const cancelled = yield* Effect.forkChild(Fiber.interrupt(waiter), {
startImmediately: true,
});
yield* Effect.gen(function* () {
yield* eventLoopTurn;
assert.equal(cancelled.pollUnsafe() !== undefined, !masked);
assert.isFalse(yield* Deferred.isDone(entered));
}).pipe(Effect.ensuring(Deferred.succeed(release, undefined)));
yield* Fiber.join(holder);
yield* Fiber.join(cancelled);
assert.equal(yield* Deferred.isDone(entered), masked);
yield* sql.withTransaction(sql`INSERT INTO entries VALUES (2)`);
assert.deepEqual(
yield* sql`SELECT value FROM entries ORDER BY value`.values,
masked ? [[1], [2]] : [[2]],
);
}).pipe(Effect.provide(SqliteClient.layer({ filename: ":memory:" }))),
);