1/**
2 * Connects Effect SQL to SQLite when running on Bun, using `bun:sqlite`.
3 *
4 * This module opens a SQLite database and exposes it as both `SqliteClient` and
5 * the generic Effect SQL client. It serializes access to the database, enables
6 * WAL mode unless disabled, and waits up to five seconds for busy databases by
7 * default. Explicit transactions on writable connections use `BEGIN IMMEDIATE`
8 * to avoid read-to-write lock upgrades, which serializes them behind other
9 * writers even when they only read. Clients opened with `readonly: true` are
10 * unaffected. Busy waits block the event loop because `bun:sqlite` is
11 * synchronous. Database export and extension loading are supported; streaming
12 * queries and `updateValues` are not.
13 *
14 * @since 4.0.0
15 */
16import { constants, Database } from "bun:sqlite"
17import * as Clock from "effect/Clock"
18import * as Config from "effect/Config"
19import * as Context from "effect/Context"
20import * as Duration from "effect/Duration"
21import * as Effect from "effect/Effect"
22import { identity } from "effect/Function"
23import * as Layer from "effect/Layer"
24import * as Reactivity from "effect/reactivity/Reactivity"
25import * as Schedule from "effect/Schedule"
26import type * as Scope from "effect/Scope"
27import * as Semaphore from "effect/Semaphore"
28import * as Client from "effect/sql/SqlClient"
29import type { Connection } from "effect/sql/SqlConnection"
30import { classifySqliteError, SqlError } from "effect/sql/SqlError"
31import * as Statement from "effect/sql/Statement"
32import * as Stream from "effect/Stream"
33
34const ATTR_DB_SYSTEM_NAME = "db.system.name"
35const MAX_BUSY_TIMEOUT = 2_147_483_647
36const NANOS_PER_MILLI = BigInt(1_000_000)
37
38const classifyError = (cause: unknown, message: string, operation: string) =>
39 classifySqliteError(cause, { message, operation })
40
41/**
42 * Runtime type identifier used to mark Bun `SqliteClient` values.
43 *
44 * @category type IDs
45 * @since 4.0.0
46 */
47export const TypeId: TypeId = "~@effect/sql-sqlite-bun/SqliteClient"
48
49/**
50 * Type-level identifier used to mark Bun `SqliteClient` values.
51 *
52 * @category type IDs
53 * @since 4.0.0
54 */
55export type TypeId = "~@effect/sql-sqlite-bun/SqliteClient"
56
57/**
58 * Bun SQLite client service, extending `SqlClient` with database export and extension loading helpers. `updateValues` is not supported.
59 *
60 * @category services
61 * @since 4.0.0
62 */
63export interface SqliteClient extends Client.SqlClient {
64 readonly [TypeId]: TypeId
65 readonly config: SqliteClientConfig
66 readonly export: Effect.Effect<Uint8Array, SqlError>
67 readonly loadExtension: (path: string) => Effect.Effect<void, SqlError>
68
69 /** Not supported in sqlite */
70 readonly updateValues: never
71}
72
73/**
74 * Service tag for the Bun SQLite client service.
75 *
76 * **When to use**
77 *
78 * Use to access or provide a Bun SQLite client through the Effect context.
79 *
80 * @category services
81 * @since 4.0.0
82 */
83export const SqliteClient = Context.Service<SqliteClient>("@effect/sql-sqlite-bun/Client")
84
85/**
86 * Configuration for a Bun SQLite client, including filename, open mode flags, WAL and busy timeout behavior, span attributes, and query/result name transforms.
87 *
88 * @category models
89 * @since 4.0.0
90 */
91export interface SqliteClientConfig {
92 readonly filename: string
93 readonly readonly?: boolean | undefined
94 readonly create?: boolean | undefined
95 readonly readwrite?: boolean | undefined
96 readonly disableWAL?: boolean | undefined
97 /**
98 * How long SQLite waits when the database is busy. Defaults to 5 seconds.
99 * `Duration.infinity` is clamped to SQLite's maximum timeout.
100 * Waiting blocks the event loop because `bun:sqlite` is synchronous.
101 */
102 readonly busyTimeout?: Duration.Input | undefined
103
104 readonly spanAttributes?: Record<string, unknown> | undefined
105
106 readonly transformResultNames?: ((str: string) => string) | undefined
107 readonly transformQueryNames?: ((str: string) => string) | undefined
108}
109
110interface SqliteConnection extends Connection {
111 readonly isTransaction: () => boolean
112 readonly export: Effect.Effect<Uint8Array, SqlError>
113 readonly loadExtension: (path: string) => Effect.Effect<void, SqlError>
114}
115
116/**
117 * Creates a scoped Bun SQLite client for a database file, enabling WAL and a 5-second busy timeout by default. Explicit transactions on writable connections take the write lock for their duration, even when they only read; clients opened with `readonly: true` are unaffected. Streaming queries are not implemented.
118 *
119 * @category constructors
120 * @since 4.0.0
121 */
122export const make = (
123 options: SqliteClientConfig
124): Effect.Effect<SqliteClient, SqlError, Scope.Scope | Reactivity.Reactivity> =>
125 Effect.gen(function*() {
126 const compiler = Statement.makeCompilerSqlite(options.transformQueryNames)
127 const transformRows = options.transformResultNames ?
128 Statement.defaultTransforms(
129 options.transformResultNames
130 ).array :
131 undefined
132
133 const makeConnection = Effect.gen(function*() {
134 const readonly = options.readonly === true
135 const openOptions = {
136 readonly,
137 readwrite: readonly ? false : options.readwrite ?? true,
138 create: readonly ? false : options.create ?? true
139 }
140 const db = yield* Effect.acquireRelease(
141 Effect.try({
142 try: () =>
143 new Database(
144 options.filename,
145 options.filename.startsWith("file:")
146 ? constants.SQLITE_OPEN_URI |
147 (openOptions.readonly ? constants.SQLITE_OPEN_READONLY : 0) |
148 (openOptions.readwrite || openOptions.create ? constants.SQLITE_OPEN_READWRITE : 0) |
149 (openOptions.create ? constants.SQLITE_OPEN_CREATE : 0)
150 : openOptions
151 ),
152 catch: (cause) => new SqlError({ reason: classifyError(cause, "Failed to open database", "connect") })
153 }),
154 (db) => Effect.sync(() => db.close())
155 )
156 const busyTimeout = Math.min(
157 MAX_BUSY_TIMEOUT,
158 Math.max(0, Math.round(Duration.toMillis(options.busyTimeout ?? Duration.seconds(5))))
159 )
160 const exec = (sql: string) =>
161 Effect.try({
162 try: () => db.run(sql),
163 catch: (cause) => new SqlError({ reason: classifyError(cause, "Failed to configure database", "connect") })
164 })
165 yield* exec(`PRAGMA busy_timeout = ${busyTimeout};`)
166
167 if (options.disableWAL !== true && !readonly) {
168 // Switching to WAL can fail with SQLITE_BUSY without invoking the busy
169 // handler, so retry it until busyTimeout has elapsed. Each attempt's
170 // native busy wait is limited to the remaining time.
171 const deadline = (yield* Clock.monotonicTimeNanos) + BigInt(busyTimeout) * NANOS_PER_MILLI
172 const remainingMillis = Effect.map(
173 Clock.monotonicTimeNanos,
174 (now) => Math.max(0, Number((deadline - now) / NANOS_PER_MILLI))
175 )
176 yield* remainingMillis.pipe(
177 Effect.flatMap((remaining) => exec(`PRAGMA busy_timeout = ${remaining}`)),
178 Effect.andThen(exec("PRAGMA journal_mode = WAL;")),
179 Effect.retry({
180 while: (error) =>
181 error.reason._tag === "LockTimeoutError" && Effect.map(remainingMillis, (remaining) => remaining > 0),
182 schedule: Schedule.spaced("10 millis")
183 })
184 )
185 yield* exec(`PRAGMA busy_timeout = ${busyTimeout}`)
186 }
187
188 const prepare = (sql: string, useSafeIntegers: boolean) => {
189 const statement = db.query(sql)
190 // @ts-ignore bun-types missing safeIntegers method, fixed in https://github.com/oven-sh/bun/pull/26627
191 statement.safeIntegers(useSafeIntegers)
192 return statement
193 }
194
195 const run = (
196 sql: string,
197 params: ReadonlyArray<unknown> = []
198 ) =>
199 Effect.withFiber<Array<any>, SqlError>((fiber) => {
200 const useSafeIntegers = Context.get(fiber.context, Client.SafeIntegers)
201 try {
202 return Effect.succeed((prepare(sql, useSafeIntegers).all(...(params as any)) ?? []) as Array<any>)
203 } catch (cause) {
204 return Effect.fail(new SqlError({ reason: classifyError(cause, "Failed to execute statement", "execute") }))
205 }
206 })
207
208 const runValues = (
209 sql: string,
210 params: ReadonlyArray<unknown> = []
211 ) =>
212 Effect.withFiber<Array<any>, SqlError>((fiber) => {
213 const useSafeIntegers = Context.get(fiber.context, Client.SafeIntegers)
214 try {
215 return Effect.succeed((prepare(sql, useSafeIntegers).values(...(params as any)) ?? []) as Array<any>)
216 } catch (cause) {
217 return Effect.fail(
218 new SqlError({ reason: classifyError(cause, "Failed to execute statement", "executeValues") })
219 )
220 }
221 })
222
223 return identity<SqliteConnection>({
224 isTransaction: () => db.inTransaction,
225 execute(sql, params, transformRows) {
226 return transformRows
227 ? Effect.map(run(sql, params), transformRows)
228 : run(sql, params)
229 },
230 executeRaw(sql, params) {
231 return run(sql, params)
232 },
233 executeValues(sql, params) {
234 return runValues(sql, params)
235 },
236 executeValuesUnprepared(sql, params) {
237 return runValues(sql, params)
238 },
239 executeUnprepared(sql, params, transformRows) {
240 return this.execute(sql, params, transformRows)
241 },
242 executeStream(_sql, _params) {
243 return Stream.die("executeStream not implemented")
244 },
245 export: Effect.try({
246 try: () => db.serialize(),
247 catch: (cause) => new SqlError({ reason: classifyError(cause, "Failed to export database", "export") })
248 }),
249 loadExtension: (path) =>
250 Effect.try({
251 try: () => db.loadExtension(path),
252 catch: (cause) =>
253 new SqlError({ reason: classifyError(cause, "Failed to load extension", "loadExtension") })
254 })
255 })
256 })
257
258 const connection = yield* makeConnection
259 const { acquirer, onCommitFailure, transactionAcquirer } = Client.makeSqliteAcquirers({
260 connection: Effect.succeed(connection),
261 semaphore: yield* Semaphore.make(1),
262 isTransaction: (conn) => conn.isTransaction()
263 })
264
265 const client: SqliteClient = Object.assign(
266 (yield* Client.make({
267 acquirer,
268 compiler,
269 transactionAcquirer,
270 onCommitFailure,
271 releaseSavepoint: (name) => `RELEASE SAVEPOINT ${name}`,
272 beginTransaction: options.readonly === true ? "BEGIN" : "BEGIN IMMEDIATE",
273 spanAttributes: [
274 ...(options.spanAttributes ? Object.entries(options.spanAttributes) : []),
275 [ATTR_DB_SYSTEM_NAME, "sqlite"]
276 ],
277 transformRows
278 })) as SqliteClient,
279 {
280 [TypeId]: TypeId as TypeId,
281 config: options,
282 export: Effect.withFiber((fiber) =>
283 Context.getOption(fiber.context, client.transactionService)._tag === "Some"
284 ? connection.export
285 : Effect.flatMap(acquirer, (_) => _.export)
286 ),
287 loadExtension: (path: string) => Effect.flatMap(acquirer, (_) => _.loadExtension(path))
288 }
289 )
290 return client
291 })
292
293/**
294 * Creates a layer from a `Config`-wrapped Bun SQLite client configuration, providing both `SqliteClient` and `SqlClient`.
295 *
296 * @category layers
297 * @since 4.0.0
298 */
299export const layerConfig = (
300 config: Config.Wrap<SqliteClientConfig>
301): Layer.Layer<SqliteClient | Client.SqlClient, Config.ConfigError | SqlError> =>
302 Layer.effectContext(
303 Config.unwrap(config).pipe(
304 Effect.flatMap(make),
305 Effect.map((client) =>
306 Context.make(SqliteClient, client).pipe(
307 Context.add(Client.SqlClient, client)
308 )
309 )
310 )
311 ).pipe(Layer.provide(Reactivity.layer))
312
313/**
314 * Creates a layer from a concrete Bun SQLite client configuration, providing both `SqliteClient` and `SqlClient`.
315 *
316 * @category layers
317 * @since 4.0.0
318 */
319export const layer = (
320 config: SqliteClientConfig
321): Layer.Layer<SqliteClient | Client.SqlClient, SqlError> =>
322 Layer.effectContext(
323 Effect.map(make(config), (client) =>
324 Context.make(SqliteClient, client).pipe(
325 Context.add(Client.SqlClient, client)
326 ))
327 ).pipe(Layer.provide(Reactivity.layer))