1import { NodeFileSystem } from "@effect/platform-node"
2import { SqliteClient } from "@effect/sql-sqlite-node"
3import { assert, describe, it } from "@effect/vitest"
4import { Cause, Duration, Effect, Exit, Fiber, FileSystem, Option } from "effect"
5import { Reactivity } from "effect/reactivity"
6import { ConnectionError, SqlError } from "effect/sql/SqlError"
7import { TestClock } from "effect/testing"
8import { DatabaseSync } from "node:sqlite"
9
10const makeClient = Effect.gen(function*() {
11 const fs = yield* FileSystem.FileSystem
12 const dir = yield* fs.makeTempDirectoryScoped()
13 return yield* SqliteClient.make({
14 filename: dir + "/test.db"
15 })
16}).pipe(Effect.provide([NodeFileSystem.layer, Reactivity.layer]))
17
18const makeClients = Effect.gen(function*() {
19 const fs = yield* FileSystem.FileSystem
20 const dir = yield* fs.makeTempDirectoryScoped()
21 const filename = dir + "/test.db"
22 return {
23 client: yield* SqliteClient.make({ filename }),
24 contender: yield* SqliteClient.make({ filename })
25 }
26}).pipe(Effect.provide([NodeFileSystem.layer, Reactivity.layer]))
27
28const makeLockedDatabase = Effect.gen(function*() {
29 const fs = yield* FileSystem.FileSystem
30 const dir = yield* fs.makeTempDirectoryScoped()
31 const filename = dir + "/test.db"
32 const lock = yield* Effect.acquireRelease(
33 Effect.sync(() => new DatabaseSync(filename)),
34 (db) => Effect.sync(() => db.close())
35 )
36 lock.exec("BEGIN IMMEDIATE")
37 return { filename, unlock: () => lock.exec("ROLLBACK") }
38}).pipe(Effect.provide(NodeFileSystem.layer))
39
40describe("Client", () => {
41 it.effect("releases completed nested savepoints", () =>
42 Effect.gen(function*() {
43 const sql = yield* makeClient
44 yield* sql`CREATE TABLE savepoint_release (value INTEGER)`
45 yield* sql.withTransaction(Effect.gen(function*() {
46 for (const rollback of [false, true]) {
47 yield* sql.withTransaction(
48 sql`INSERT INTO savepoint_release VALUES (1)`.pipe(
49 Effect.andThen(rollback ? Effect.fail("rollback") : Effect.void)
50 )
51 ).pipe(Effect.ignore)
52 const error = yield* sql`RELEASE SAVEPOINT effect_sql_1`.unprepared.pipe(Effect.flip)
53 assert.strictEqual(error._tag, "SqlError")
54 }
55 }))
56 assert.deepStrictEqual(yield* sql`SELECT value FROM savepoint_release`, [{ value: 1 }])
57 const error = yield* sql.withTransaction(
58 sql.withTransaction(sql`INSERT INTO savepoint_release VALUES (2)`).pipe(
59 Effect.andThen(Effect.fail("outer rollback"))
60 )
61 ).pipe(Effect.flip)
62 assert.strictEqual(error, "outer rollback")
63 assert.deepStrictEqual(yield* sql`SELECT value FROM savepoint_release`, [{ value: 1 }])
64 }))
65
66 it.effect("should work", () =>
67 Effect.gen(function*() {
68 const sql = yield* makeClient
69 let response
70 response = yield* sql`CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)`
71 assert.deepStrictEqual(response, [])
72 response = yield* sql`INSERT INTO test (name) VALUES ('hello')`
73 assert.deepStrictEqual(response, [])
74 response = yield* sql`SELECT * FROM test`
75 assert.deepStrictEqual(response, [{ id: 1, name: "hello" }])
76 response = yield* sql`SELECT * FROM test`.valuesUnprepared
77 assert.deepStrictEqual(response, [[1, "hello"]])
78 response = yield* sql`INSERT INTO test (name) VALUES ('world')`.pipe(sql.withTransaction)
79 assert.deepStrictEqual(response, [])
80 response = yield* sql`SELECT * FROM test`
81 assert.deepStrictEqual(response, [
82 { id: 1, name: "hello" },
83 { id: 2, name: "world" }
84 ])
85 response = yield* sql`INSERT INTO test (name) VALUES ('unprepared')`.valuesUnprepared
86 assert.deepStrictEqual(response, [])
87 assert.deepStrictEqual(yield* sql`SELECT * FROM test WHERE id = 3`, [{ id: 3, name: "unprepared" }])
88 }))
89
90 it.effect.each(["rows", "values"] as const)(
91 "returns cached INSERT %s across count_changes OFF → ON → OFF",
92 (mode) =>
93 Effect.gen(function*() {
94 const sql = yield* makeClient
95 yield* sql`CREATE TABLE count_changes (value INTEGER)`
96 yield* sql`PRAGMA count_changes = OFF`
97 const insert = sql`INSERT INTO count_changes VALUES (1)`
98 const execute = mode === "values" ? insert.values : insert
99
100 assert.deepStrictEqual(yield* execute, [])
101 yield* sql`PRAGMA count_changes = ON`
102 // Node may omit fields after recompilation; check only the row count.
103 assert.lengthOf(yield* execute, 1)
104 yield* sql`PRAGMA count_changes = OFF`
105 assert.deepStrictEqual(yield* execute, [])
106 assert.deepStrictEqual(yield* sql`SELECT COUNT(*) AS count FROM count_changes`, [{ count: 3 }])
107 })
108 )
109
110 it.effect.each(["rows", "values"] as const)(
111 "retries %s queries after a missing table is created",
112 (mode) =>
113 Effect.gen(function*() {
114 const sql = yield* makeClient
115 const select = sql`SELECT value FROM created_later`
116 const execute: Effect.Effect<ReadonlyArray<unknown>, SqlError> = mode === "values" ? select.values : select
117
118 const error = yield* Effect.flip(execute)
119 assert.strictEqual(error._tag, "SqlError")
120 yield* sql`CREATE TABLE created_later (value INTEGER)`
121 yield* sql`INSERT INTO created_later VALUES (1)`
122
123 assert.deepStrictEqual(yield* execute, mode === "values" ? [[1]] : [{ value: 1 }])
124 })
125 )
126
127 it.effect("should work with raw", () =>
128 Effect.gen(function*() {
129 const sql = yield* makeClient
130 let response
131 response = yield* sql`CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)`.raw
132 assert.deepStrictEqual(response, { changes: 0, lastInsertRowid: 0 })
133 response = yield* sql`INSERT INTO test (name) VALUES ('hello')`.raw
134 assert.deepStrictEqual(response, { changes: 1, lastInsertRowid: 1 })
135 response = yield* sql`SELECT * FROM test`.raw
136 assert.deepStrictEqual(response, [{ id: 1, name: "hello" }])
137 response = yield* sql`INSERT INTO test (name) VALUES ('world')`.raw.pipe(sql.withTransaction)
138 assert.deepStrictEqual(response, { changes: 1, lastInsertRowid: 2 })
139 response = yield* sql`SELECT * FROM test`
140 assert.deepStrictEqual(response, [
141 { id: 1, name: "hello" },
142 { id: 2, name: "world" }
143 ])
144 }))
145
146 for (const mode of ["unprepared", "valuesUnprepared"] as const) {
147 it.effect(`captures ${mode} preparation errors`, () =>
148 Effect.gen(function*() {
149 const sql = yield* makeClient
150 yield* sql`CREATE TABLE test (id INTEGER PRIMARY KEY)`
151
152 const statement = sql`CREATE TABLE test (id INTEGER PRIMARY KEY)`
153 const execution = mode === "unprepared"
154 ? Effect.as(statement.unprepared, false)
155 : Effect.as(statement.valuesUnprepared, false)
156 const recovered = yield* execution.pipe(Effect.catchTag("SqlError", () => Effect.succeed(true)))
157
158 assert.isTrue(recovered)
159 }))
160 }
161
162 it.effect("withTransaction", () =>
163 Effect.gen(function*() {
164 const sql = yield* makeClient
165 yield* sql`CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)`
166 yield* sql.withTransaction(sql`INSERT INTO test (name) VALUES ('hello')`)
167 const rows = yield* sql`SELECT * FROM test`
168 assert.deepStrictEqual(rows, [{ id: 1, name: "hello" }])
169 }))
170
171 it.effect("recovers a failed deferred commit without losing an in-memory database", () =>
172 Effect.gen(function*() {
173 const sql = yield* SqliteClient.make({ filename: ":memory:" }).pipe(Effect.provide(Reactivity.layer))
174 yield* sql`PRAGMA foreign_keys = ON`
175 yield* sql`CREATE TABLE parent (id INTEGER PRIMARY KEY)`
176 yield* sql`CREATE TABLE child (parent_id INTEGER REFERENCES parent(id) DEFERRABLE INITIALLY DEFERRED)`
177 yield* sql`INSERT INTO parent VALUES (1)`
178
179 const failedCommit = yield* Effect.exit(sql.withTransaction(sql`INSERT INTO child VALUES (999)`))
180 assert.isTrue(Exit.isFailure(failedCommit))
181 if (!Exit.isFailure(failedCommit)) return
182 assert.match(Cause.pretty(failedCommit.cause), /foreign key constraint failed/i)
183
184 assert.deepStrictEqual(yield* sql`SELECT * FROM parent`, [{ id: 1 }])
185 assert.deepStrictEqual(yield* sql`SELECT * FROM child`, [])
186 yield* sql.withTransaction(sql`INSERT INTO child VALUES (1)`)
187 assert.deepStrictEqual(yield* sql`SELECT * FROM child`, [{ parent_id: 1 }])
188 }))
189
190 it.effect("poisons the connection when failed-commit cleanup fails", () =>
191 Effect.gen(function*() {
192 const sql = yield* makeClient
193 yield* sql`PRAGMA foreign_keys = ON`
194 yield* sql`CREATE TABLE parent (id INTEGER PRIMARY KEY)`
195 yield* sql`CREATE TABLE child (parent_id INTEGER REFERENCES parent(id) DEFERRABLE INITIALLY DEFERRED)`
196 yield* sql`INSERT INTO parent VALUES (1)`
197
198 // Reserve lends the same SQLite connection used by transactions. Fail only
199 // the cleanup ROLLBACK, leaving the deferred constraint failure real.
200 const conn = yield* Effect.scoped(sql.reserve)
201 const executeUnprepared = conn.executeUnprepared
202 let failRollback = true
203 Object.defineProperty(conn, "executeUnprepared", {
204 configurable: true,
205 value: (...args: Parameters<typeof executeUnprepared>) => {
206 if (args[0] === "ROLLBACK" && failRollback) {
207 return Effect.fail(
208 new SqlError({
209 reason: new ConnectionError({
210 message: "injected rollback failure",
211 operation: "rollback",
212 cause: new Error("injected rollback failure")
213 })
214 })
215 )
216 }
217 return executeUnprepared(...args)
218 }
219 })
220
221 const failedCommit = yield* Effect.exit(sql.withTransaction(sql`INSERT INTO child VALUES (999)`))
222 assert.isTrue(Exit.isFailure(failedCommit))
223 if (!Exit.isFailure(failedCommit)) return
224 const cause = Cause.pretty(failedCommit.cause)
225 assert.match(cause, /foreign key constraint failed/i)
226 assert.match(cause, /injected rollback failure/i)
227
228 const query = yield* Effect.exit(sql`SELECT * FROM parent`)
229 assert.isTrue(Exit.isFailure(query))
230 if (Exit.isFailure(query)) {
231 assert.match(Cause.pretty(query.cause), /cannot be reused after failed COMMIT cleanup/i)
232 }
233 const transaction = yield* Effect.exit(sql.withTransaction(Effect.die("transaction body ran")))
234 assert.isTrue(Exit.isFailure(transaction))
235 const queryAfterTransaction = yield* Effect.exit(sql`SELECT * FROM parent`)
236 assert.isTrue(Exit.isFailure(queryAfterTransaction))
237 // Retry cleanup after the transient failure clears, without replacing the database.
238 failRollback = false
239 assert.deepStrictEqual(yield* sql`SELECT * FROM parent`, [{ id: 1 }])
240 assert.deepStrictEqual(yield* sql`SELECT * FROM child`, [])
241 }))
242
243 it.effect("does not poison a connection already rolled back before failed commit cleanup", () =>
244 Effect.gen(function*() {
245 const sql = yield* makeClient
246 yield* sql`CREATE TABLE items (id INTEGER PRIMARY KEY)`
247
248 const failedCommit = yield* Effect.exit(sql.withTransaction(Effect.gen(function*() {
249 yield* sql`INSERT INTO items VALUES (1)`
250 const insert = yield* Effect.exit(sql`INSERT OR ROLLBACK INTO items VALUES (1)`)
251 assert.isTrue(Exit.isFailure(insert))
252 })))
253 assert.isTrue(Exit.isFailure(failedCommit))
254 if (Exit.isFailure(failedCommit)) {
255 assert.match(Cause.pretty(failedCommit.cause), /no transaction is active/i)
256 }
257
258 assert.deepStrictEqual(yield* sql`SELECT * FROM items`, [])
259 }))
260
261 it.effect("withTransaction rollback", () =>
262 Effect.gen(function*() {
263 const sql = yield* makeClient
264 yield* sql`CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)`
265 yield* sql`INSERT INTO test (name) VALUES ('hello')`.pipe(
266 Effect.andThen(Effect.fail("boom")),
267 sql.withTransaction,
268 Effect.ignore
269 )
270 const rows = yield* sql`SELECT * FROM test`
271 assert.deepStrictEqual(rows, [])
272 }))
273
274 it.effect("uses a 5 second busy timeout", () =>
275 Effect.gen(function*() {
276 const sql = yield* makeClient
277 assert.deepStrictEqual(yield* sql`PRAGMA busy_timeout`, [{ timeout: 5000 }])
278
279 const custom = yield* SqliteClient.make({ filename: ":memory:", busyTimeout: "1 second" }).pipe(
280 Effect.provide(Reactivity.layer)
281 )
282 assert.deepStrictEqual(yield* custom`PRAGMA busy_timeout`, [{ timeout: 1000 }])
283
284 const infinite = yield* SqliteClient.make({ filename: ":memory:", busyTimeout: Duration.infinity }).pipe(
285 Effect.provide(Reactivity.layer)
286 )
287 assert.deepStrictEqual(yield* infinite`PRAGMA busy_timeout`, [{ timeout: 2_147_483_647 }])
288 }))
289
290 it.effect("starts transactions immediately", () =>
291 Effect.gen(function*() {
292 const { client, contender } = yield* makeClients
293 yield* contender`PRAGMA busy_timeout = 1`
294
295 yield* client.withTransaction(
296 Effect.gen(function*() {
297 const error = yield* Effect.flip(contender`BEGIN IMMEDIATE`)
298 assert.strictEqual(error._tag, "SqlError")
299 assert(error.reason.cause instanceof Error)
300 assert.match(error.reason.cause.message, /database is locked/i)
301 })
302 )
303 }))
304
305 it.effect("fails a contended transaction with a typed error", () =>
306 Effect.gen(function*() {
307 const { client, contender } = yield* makeClients
308 yield* contender`PRAGMA busy_timeout = 1`
309
310 const exit = yield* client.withTransaction(
311 Effect.exit(contender.withTransaction(Effect.void))
312 )
313
314 assert.isTrue(Exit.isFailure(exit))
315 if (!Exit.isFailure(exit)) {
316 return
317 }
318 // `BEGIN IMMEDIATE` cannot take the write lock, so it fails before a
319 // transaction exists. The failure has to stay a typed, retryable
320 // `SqlError` instead of being replaced by a rollback defect.
321 assert.isFalse(
322 Cause.hasDies(exit.cause),
323 `expected a typed failure but the cause contains a defect:\n${Cause.pretty(exit.cause)}`
324 )
325 const error = Option.getOrThrow(Cause.findErrorOption(exit.cause))
326 assert.strictEqual(error._tag, "SqlError")
327 assert(error.reason.cause instanceof Error)
328 assert.match(error.reason.cause.message, /database is locked/i)
329 }))
330
331 it.effect("supports transactions on readonly clients", () =>
332 Effect.gen(function*() {
333 const fs = yield* FileSystem.FileSystem
334 const dir = yield* fs.makeTempDirectoryScoped()
335 const filename = dir + "/test.db"
336
337 yield* Effect.scoped(
338 Effect.gen(function*() {
339 const sql = yield* SqliteClient.make({ filename })
340 yield* sql`CREATE TABLE test (id INTEGER PRIMARY KEY)`
341 })
342 )
343
344 const sql = yield* SqliteClient.make({ filename, readonly: true })
345 yield* sql`PRAGMA query_only = ON`
346 assert.deepStrictEqual(yield* sql.withTransaction(sql`SELECT * FROM test`), [])
347 }).pipe(Effect.provide([NodeFileSystem.layer, Reactivity.layer])))
348
349 it.effect("supports backup and export", () =>
350 Effect.gen(function*() {
351 const sql = yield* makeClient
352 yield* sql`CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)`
353 yield* sql`INSERT INTO test (name) VALUES ('hello')`
354
355 const metadata = yield* sql.backup(sql.config.filename + ".backup")
356 assert(metadata.totalPages > 0)
357 assert.strictEqual(metadata.remainingPages, 0)
358 }))
359
360 it.effect("retries enabling WAL while the database is locked", () =>
361 Effect.gen(function*() {
362 const { filename, unlock } = yield* makeLockedDatabase
363 const fiber = yield* SqliteClient.make({ filename }).pipe(Effect.forkChild({ startImmediately: true }))
364 unlock()
365 yield* TestClock.adjust("10 millis")
366 const sql = yield* Fiber.join(fiber)
367 assert.deepStrictEqual(yield* sql`PRAGMA journal_mode`, [{ journal_mode: "wal" }])
368 }).pipe(Effect.provide(Reactivity.layer)))
369
370 it.effect("fails to enable WAL with a typed error after busyTimeout", () =>
371 Effect.gen(function*() {
372 const { filename } = yield* makeLockedDatabase
373 const fiber = yield* SqliteClient.make({ filename, busyTimeout: "1 second" }).pipe(
374 Effect.forkChild({ startImmediately: true })
375 )
376 yield* TestClock.adjust("2 seconds")
377 const error = yield* Effect.flip(Fiber.join(fiber))
378 assert.strictEqual(error.reason._tag, "LockTimeoutError")
379 }).pipe(Effect.provide(Reactivity.layer)))
380
381 it.effect("does not enable WAL on readonly clients", () =>
382 Effect.gen(function*() {
383 const fs = yield* FileSystem.FileSystem
384 const dir = yield* fs.makeTempDirectoryScoped()
385 const filename = dir + "/test.db"
386
387 yield* Effect.scoped(
388 Effect.gen(function*() {
389 const sql = yield* SqliteClient.make({ filename, disableWAL: true })
390 yield* sql`CREATE TABLE test (id INTEGER PRIMARY KEY)`
391 })
392 )
393
394 const sql = yield* SqliteClient.make({ filename, readonly: true })
395 assert.deepStrictEqual(yield* sql`PRAGMA journal_mode`, [{ journal_mode: "delete" }])
396 }).pipe(Effect.provide([NodeFileSystem.layer, Reactivity.layer])))
397})