1import { assert, describe, it } from "@effect/vitest"
2import { Cause, Duration, Effect, Exit, Fiber } from "effect"
3import { Reactivity } from "effect/reactivity"
4import { TestClock } from "effect/testing"
5import { rejects } from "node:assert/strict"
6import { mkdtemp, rm, stat } from "node:fs/promises"
7import { tmpdir } from "node:os"
8import { join } from "node:path"
9import { pathToFileURL } from "node:url"
10
11const isBun = "bun" in process.versions
12
13const makeLockedDatabase = Effect.gen(function*() {
14 const { Database } = yield* Effect.promise(() => import("bun:sqlite"))
15 const dir = yield* Effect.acquireRelease(
16 Effect.promise(() => mkdtemp(join(tmpdir(), "effect-sqlite-bun-"))),
17 (dir) => Effect.promise(() => rm(dir, { recursive: true, force: true }))
18 )
19 const filename = join(dir, "test.db")
20 const lock = yield* Effect.acquireRelease(
21 Effect.sync(() => new Database(filename)),
22 (db) => Effect.sync(() => db.close())
23 )
24 lock.run("BEGIN IMMEDIATE")
25 return { filename, unlock: () => lock.run("ROLLBACK") }
26})
27
28describe("Client", () => {
29 it.effect("should work", () => Effect.void)
30
31 it.effect.skipIf(!isBun)("uses a 5 second busy timeout", () =>
32 Effect.gen(function*() {
33 const { SqliteClient } = yield* Effect.promise(() => import("@effect/sql-sqlite-bun"))
34 const sql = yield* SqliteClient.make({ filename: ":memory:" })
35 assert.deepStrictEqual(yield* sql`PRAGMA busy_timeout`, [{ timeout: 5000 }])
36
37 const custom = yield* SqliteClient.make({ filename: ":memory:", busyTimeout: "1 second" })
38 assert.deepStrictEqual(yield* custom`PRAGMA busy_timeout`, [{ timeout: 1000 }])
39
40 const infinite = yield* SqliteClient.make({ filename: ":memory:", busyTimeout: Duration.infinity })
41 assert.deepStrictEqual(yield* infinite`PRAGMA busy_timeout`, [{ timeout: 2_147_483_647 }])
42 }).pipe(Effect.provide(Reactivity.layer)))
43
44 it.effect.skipIf(!isBun)("starts transactions immediately", () =>
45 Effect.gen(function*() {
46 const { SqliteClient } = yield* Effect.promise(() => import("@effect/sql-sqlite-bun"))
47 const filename = `/tmp/effect-sqlite-bun-transaction-${crypto.randomUUID()}.db`
48 yield* Effect.acquireRelease(
49 Effect.void,
50 () => Effect.promise(() => rm(filename, { force: true }))
51 )
52
53 const client = yield* SqliteClient.make({ filename })
54 const contender = yield* SqliteClient.make({ filename })
55 yield* contender`PRAGMA busy_timeout = 1`
56
57 yield* client.withTransaction(
58 Effect.gen(function*() {
59 const error = yield* Effect.flip(contender`BEGIN IMMEDIATE`)
60 assert.strictEqual(error._tag, "SqlError")
61 assert(error.reason.cause instanceof Error)
62 assert.match(error.reason.cause.message, /database is locked/i)
63 })
64 )
65 }).pipe(Effect.provide(Reactivity.layer)))
66
67 it.effect.skipIf(!isBun)("exports inside transactions", () =>
68 Effect.gen(function*() {
69 const { SqliteClient } = yield* Effect.promise(() => import("@effect/sql-sqlite-bun"))
70 const { Database } = yield* Effect.promise(() => import("bun:sqlite"))
71 const sql = yield* SqliteClient.make({ filename: ":memory:" })
72 yield* sql`CREATE TABLE test (id INTEGER PRIMARY KEY)`
73
74 const bytes = yield* sql.withTransaction(
75 sql`INSERT INTO test DEFAULT VALUES`.pipe(Effect.andThen(sql.export))
76 )
77 const snapshot = Database.deserialize(bytes)
78 try {
79 assert.deepStrictEqual(snapshot.query("SELECT * FROM test").all(), [{ id: 1 }])
80 } finally {
81 snapshot.close()
82 }
83 }).pipe(Effect.provide(Reactivity.layer)))
84
85 it.effect.skipIf(!isBun)(
86 "recovers a failed deferred commit without losing an in-memory database",
87 () =>
88 Effect.gen(function*() {
89 const { SqliteClient } = yield* Effect.promise(() => import("@effect/sql-sqlite-bun"))
90 const sql = yield* SqliteClient.make({ filename: ":memory:" })
91 yield* sql`PRAGMA foreign_keys = ON`
92 yield* sql`CREATE TABLE parent (id INTEGER PRIMARY KEY)`
93 yield* sql`CREATE TABLE child (parent_id INTEGER REFERENCES parent(id) DEFERRABLE INITIALLY DEFERRED)`
94 yield* sql`INSERT INTO parent VALUES (1)`
95
96 const failedCommit = yield* Effect.exit(sql.withTransaction(sql`INSERT INTO child VALUES (999)`))
97 assert.isTrue(Exit.isFailure(failedCommit))
98 if (!Exit.isFailure(failedCommit)) return
99 assert.match(Cause.pretty(failedCommit.cause), /foreign key constraint failed/i)
100
101 assert.deepStrictEqual(yield* sql`SELECT * FROM parent`, [{ id: 1 }])
102 assert.deepStrictEqual(yield* sql`SELECT * FROM child`, [])
103 yield* sql.withTransaction(sql`INSERT INTO child VALUES (1)`)
104 assert.deepStrictEqual(yield* sql`SELECT * FROM child`, [{ parent_id: 1 }])
105 }).pipe(Effect.provide(Reactivity.layer))
106 )
107
108 it.effect.skipIf(!isBun)("readonly clients reject writes", () =>
109 Effect.gen(function*() {
110 const { SqliteClient } = yield* Effect.promise(() => import("@effect/sql-sqlite-bun"))
111 const filename = `/tmp/effect-sqlite-bun-readonly-${crypto.randomUUID()}.db`
112 yield* Effect.acquireRelease(
113 Effect.void,
114 () => Effect.promise(() => rm(filename, { force: true }))
115 )
116
117 yield* Effect.scoped(
118 Effect.gen(function*() {
119 const sql = yield* SqliteClient.make({ filename })
120 yield* sql`CREATE TABLE test (id INTEGER PRIMARY KEY)`
121 })
122 )
123
124 const sql = yield* SqliteClient.make({ filename, readonly: true })
125 assert.deepStrictEqual(yield* sql`SELECT * FROM test`, [])
126
127 const error = yield* Effect.flip(sql`INSERT INTO test DEFAULT VALUES`)
128 assert.strictEqual(error._tag, "SqlError")
129 assert(error.reason.cause instanceof Error)
130 assert.match(error.reason.cause.message, /attempt to write a readonly database/i)
131
132 yield* sql`PRAGMA query_only = ON`
133 assert.deepStrictEqual(yield* sql.withTransaction(sql`SELECT * FROM test`), [])
134 }).pipe(Effect.provide(Reactivity.layer)))
135
136 it.effect.skipIf(!isBun)("opens file: URIs in readonly mode", () =>
137 Effect.gen(function*() {
138 const { SqliteClient } = yield* Effect.promise(() => import("@effect/sql-sqlite-bun"))
139 const filename = `/tmp/effect-sqlite-bun-uri-${crypto.randomUUID()}.db`
140 yield* Effect.acquireRelease(
141 Effect.void,
142 () => Effect.promise(() => rm(filename, { force: true }))
143 )
144
145 yield* Effect.scoped(
146 Effect.gen(function*() {
147 const sql = yield* SqliteClient.make({ filename })
148 yield* sql`CREATE TABLE test (id INTEGER PRIMARY KEY)`
149 yield* sql`INSERT INTO test (id) VALUES (1)`
150 })
151 )
152
153 const uri = `${pathToFileURL(filename).href}?immutable=1`
154 const sql = yield* SqliteClient.make({ filename: uri, readonly: true })
155 assert.deepStrictEqual(yield* sql`SELECT * FROM test`, [{ id: 1 }])
156 }).pipe(Effect.provide(Reactivity.layer)))
157
158 it.effect.skipIf(!isBun)("rejects writes to a plain readonly file: URI", () =>
159 Effect.gen(function*() {
160 const { SqliteClient } = yield* Effect.promise(() => import("@effect/sql-sqlite-bun"))
161 const filename = `/tmp/effect-sqlite-bun-uri-readonly-${crypto.randomUUID()}.db`
162 yield* Effect.acquireRelease(Effect.void, () => Effect.promise(() => rm(filename, { force: true })))
163
164 yield* Effect.scoped(Effect.gen(function*() {
165 const sql = yield* SqliteClient.make({ filename })
166 yield* sql`CREATE TABLE test (id INTEGER PRIMARY KEY)`
167 }))
168
169 const sql = yield* SqliteClient.make({ filename: pathToFileURL(filename).href, readonly: true })
170 assert.deepStrictEqual(yield* sql`SELECT * FROM test`, [])
171 const error = yield* Effect.flip(sql`INSERT INTO test (id) VALUES (1)`)
172 assert.strictEqual(error._tag, "SqlError")
173 assert(error.reason.cause instanceof Error)
174 assert.match(error.reason.cause.message, /attempt to write a readonly database/i)
175 }).pipe(Effect.provide(Reactivity.layer)))
176
177 it.effect.skipIf(!isBun)("does not create a missing file: URI with create: false", () =>
178 Effect.gen(function*() {
179 const { SqliteClient } = yield* Effect.promise(() => import("@effect/sql-sqlite-bun"))
180 const filename = `/tmp/effect-sqlite-bun-uri-missing-${crypto.randomUUID()}.db`
181 yield* Effect.acquireRelease(Effect.void, () => Effect.promise(() => rm(filename, { force: true })))
182
183 const error = yield* Effect.flip(
184 Effect.scoped(SqliteClient.make({ filename: pathToFileURL(filename).href, create: false }))
185 )
186 assert.strictEqual(error.reason._tag, "ConnectionError")
187 yield* Effect.promise(() => rejects(stat(filename), { code: "ENOENT" }))
188 }).pipe(Effect.provide(Reactivity.layer)))
189
190 it.effect.skipIf(!isBun)("create implies readwrite for file: URIs", () =>
191 Effect.gen(function*() {
192 const { SqliteClient } = yield* Effect.promise(() => import("@effect/sql-sqlite-bun"))
193 const filename = `/tmp/effect-sqlite-bun-uri-create-${crypto.randomUUID()}.db`
194 yield* Effect.acquireRelease(Effect.void, () => Effect.promise(() => rm(filename, { force: true })))
195 const sql = yield* SqliteClient.make({ filename: pathToFileURL(filename).href, readwrite: false })
196 yield* sql`CREATE TABLE test (id INTEGER PRIMARY KEY)`
197 yield* sql`INSERT INTO test (id) VALUES (1)`
198 assert.deepStrictEqual(yield* sql`SELECT * FROM test`, [{ id: 1 }])
199 }).pipe(Effect.provide(Reactivity.layer)))
200
201 it.effect.skipIf(!isBun)("retries enabling WAL while the database is locked", () =>
202 Effect.gen(function*() {
203 const { SqliteClient } = yield* Effect.promise(() => import("@effect/sql-sqlite-bun"))
204 const { filename, unlock } = yield* makeLockedDatabase
205 const fiber = yield* SqliteClient.make({ filename }).pipe(Effect.forkChild({ startImmediately: true }))
206 unlock()
207 yield* TestClock.adjust("10 millis")
208 const sql = yield* Fiber.join(fiber)
209 assert.deepStrictEqual(yield* sql`PRAGMA journal_mode`, [{ journal_mode: "wal" }])
210 }).pipe(Effect.provide(Reactivity.layer)))
211
212 it.effect.skipIf(!isBun)("fails to enable WAL with a typed error after busyTimeout", () =>
213 Effect.gen(function*() {
214 const { SqliteClient } = yield* Effect.promise(() => import("@effect/sql-sqlite-bun"))
215 const { filename } = yield* makeLockedDatabase
216 const fiber = yield* SqliteClient.make({ filename, busyTimeout: "1 second" }).pipe(
217 Effect.forkChild({ startImmediately: true })
218 )
219 yield* TestClock.adjust("2 seconds")
220 const error = yield* Effect.flip(Fiber.join(fiber))
221 assert.strictEqual(error.reason._tag, "LockTimeoutError")
222 }).pipe(Effect.provide(Reactivity.layer)))
223})