1/**
2 * Connects Effect SQL to SQLite on Node.js using `node: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 through one connection,
6 * caches prepared statements, enables WAL mode unless disabled, and waits up
7 * to five seconds for busy databases by default. Explicit transactions on
8 * writable connections use `BEGIN IMMEDIATE` to avoid read-to-write lock
9 * upgrades, which serializes them behind other writers even when they only
10 * read. Clients opened with `readonly: true` are unaffected. Busy waits block
11 * the Node.js event loop because `node:sqlite` is synchronous. Database backup
12 * and extension loading are supported; streaming queries and `updateValues`
13 * are not.
14 *
15 * @since 4.0.0
16 */
17import * as Cache from "effect/Cache"
18import * as Clock from "effect/Clock"
19import * as Config from "effect/Config"
20import * as Context from "effect/Context"
21import * as Duration from "effect/Duration"
22import * as Effect from "effect/Effect"
23import * as Exit from "effect/Exit"
24import { identity } from "effect/Function"
25import * as Layer from "effect/Layer"
26import * as Reactivity from "effect/reactivity/Reactivity"
27import * as Schedule from "effect/Schedule"
28import type * as Scope from "effect/Scope"
29import * as Semaphore from "effect/Semaphore"
30import * as Client from "effect/sql/SqlClient"
31import type { Connection } from "effect/sql/SqlConnection"
32import { classifySqliteError, SqlError } from "effect/sql/SqlError"
33import * as Statement from "effect/sql/Statement"
34import * as Stream from "effect/Stream"
35import { backup as backupDatabase, DatabaseSync } from "node:sqlite"
36import type { StatementSync } from "node:sqlite"
37
38const ATTR_DB_SYSTEM_NAME = "db.system.name"
39const MAX_BUSY_TIMEOUT = 2_147_483_647
40const NANOS_PER_MILLI = BigInt(1_000_000)
41
42/**
43 * Runtime type identifier used to mark Node `SqliteClient` values.
44 *
45 * @category type IDs
46 * @since 4.0.0
47 */
48export const TypeId: TypeId = "~@effect/sql-sqlite-node/SqliteClient"
49
50/**
51 * Type-level identifier used to mark Node `SqliteClient` values.
52 *
53 * @category type IDs
54 * @since 4.0.0
55 */
56export type TypeId = "~@effect/sql-sqlite-node/SqliteClient"
57
58/**
59 * Node SQLite client service, extending `SqlClient` with database export, backup, and extension loading helpers. `updateValues` is not supported.
60 *
61 * @category services
62 * @since 4.0.0
63 */
64export interface SqliteClient extends Client.SqlClient {
65 readonly [TypeId]: TypeId
66 readonly config: SqliteClientConfig
67 readonly backup: (destination: string) => Effect.Effect<BackupMetadata, SqlError>
68 readonly loadExtension: (path: string) => Effect.Effect<void, SqlError>
69
70 /** Not supported in sqlite */
71 readonly updateValues: never
72}
73
74/**
75 * Metadata returned from a Node SQLite backup operation, reporting total and remaining page counts.
76 *
77 * @category models
78 * @since 4.0.0
79 */
80export interface BackupMetadata {
81 readonly totalPages: number
82 readonly remainingPages: number
83}
84
85/**
86 * Service tag for the node SQLite client implementation.
87 *
88 * @category services
89 * @since 4.0.0
90 */
91export const SqliteClient = Context.Service<SqliteClient>("@effect/sql-sqlite-node/SqliteClient")
92
93/**
94 * Configuration for a node SQLite client backed by `node:sqlite`, including the database filename, read-only mode, statement cache settings, WAL and busy timeout behavior, span attributes, and query/result name transforms.
95 *
96 * @category models
97 * @since 4.0.0
98 */
99export interface SqliteClientConfig {
100 readonly filename: string
101 readonly readonly?: boolean | undefined
102 readonly prepareCacheSize?: number | undefined
103 readonly prepareCacheTTL?: Duration.Input | undefined
104 readonly disableWAL?: boolean | undefined
105 /**
106 * How long SQLite waits when the database is busy. Defaults to 5 seconds.
107 * `Duration.infinity` is clamped to SQLite's maximum timeout.
108 * Waiting blocks the Node.js event loop because `node:sqlite` is synchronous.
109 */
110 readonly busyTimeout?: Duration.Input | undefined
111 readonly spanAttributes?: Record<string, unknown> | undefined
112
113 readonly transformResultNames?: ((str: string) => string) | undefined
114 readonly transformQueryNames?: ((str: string) => string) | undefined
115}
116
117interface SqliteConnection extends Connection {
118 readonly isTransaction: () => boolean
119 readonly backup: (destination: string) => Effect.Effect<BackupMetadata, SqlError>
120 readonly loadExtension: (path: string) => Effect.Effect<void, SqlError>
121}
122
123/**
124 * Creates a scoped node SQLite client from the supplied configuration, using a single serialized connection with WAL and a 5-second busy timeout enabled 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.
125 *
126 * @category constructors
127 * @since 4.0.0
128 */
129export const make = (
130 options: SqliteClientConfig
131): Effect.Effect<SqliteClient, SqlError, Scope.Scope | Reactivity.Reactivity> =>
132 Effect.gen(function*() {
133 const compiler = Statement.makeCompilerSqlite(options.transformQueryNames)
134 const transformRows = options.transformResultNames ?
135 Statement.defaultTransforms(
136 options.transformResultNames
137 ).array :
138 undefined
139
140 const makeConnection = Effect.gen(function*() {
141 const db = yield* Effect.acquireRelease(
142 Effect.try({
143 try: () =>
144 new DatabaseSync(options.filename, {
145 readOnly: options.readonly ?? false,
146 allowExtension: true
147 }),
148 catch: (cause) => new SqlError({ reason: classifyError(cause, "Failed to open database", "connect") })
149 }),
150 (db) => Effect.sync(() => db.close())
151 )
152 db.enableLoadExtension(false)
153 const busyTimeout = Math.min(
154 MAX_BUSY_TIMEOUT,
155 Math.max(0, Math.round(Duration.toMillis(options.busyTimeout ?? Duration.seconds(5))))
156 )
157 const exec = (sql: string) =>
158 Effect.try({
159 try: () => db.exec(sql),
160 catch: (cause) => new SqlError({ reason: classifyError(cause, "Failed to configure database", "connect") })
161 })
162 yield* exec(`PRAGMA busy_timeout = ${busyTimeout}`)
163
164 if (options.disableWAL !== true && !options.readonly) {
165 // Switching to WAL can fail with SQLITE_BUSY without invoking the busy
166 // handler, so retry it until busyTimeout has elapsed. Each attempt's
167 // native busy wait is limited to the remaining time.
168 const deadline = (yield* Clock.monotonicTimeNanos) + BigInt(busyTimeout) * NANOS_PER_MILLI
169 const remainingMillis = Effect.map(
170 Clock.monotonicTimeNanos,
171 (now) => Math.max(0, Number((deadline - now) / NANOS_PER_MILLI))
172 )
173 yield* remainingMillis.pipe(
174 Effect.flatMap((remaining) => exec(`PRAGMA busy_timeout = ${remaining}`)),
175 Effect.andThen(exec("PRAGMA journal_mode = WAL")),
176 Effect.retry({
177 while: (error) =>
178 error.reason._tag === "LockTimeoutError" && Effect.map(remainingMillis, (remaining) => remaining > 0),
179 schedule: Schedule.spaced("10 millis")
180 })
181 )
182 yield* exec(`PRAGMA busy_timeout = ${busyTimeout}`)
183 }
184
185 const prepare = (sql: string) =>
186 Effect.try({
187 try: () => db.prepare(sql),
188 catch: (cause) => new SqlError({ reason: classifyError(cause, "Failed to prepare statement", "prepare") })
189 })
190
191 const prepareCacheTTL = options.prepareCacheTTL ?? Duration.minutes(10)
192 const prepareCache = yield* Cache.makeWith(prepare, {
193 capacity: options.prepareCacheSize ?? 200,
194 timeToLive: (exit) => Exit.isSuccess(exit) ? prepareCacheTTL : Duration.zero
195 })
196
197 const runStatement = (
198 statement: StatementSync,
199 params: ReadonlyArray<unknown>,
200 raw: boolean
201 ) =>
202 Effect.withFiber<ReadonlyArray<any>, SqlError>((fiber) => {
203 const useSafeIntegers = Context.get(fiber.context, Client.SafeIntegers)
204 return Effect.try({
205 try: () => {
206 statement.setReadBigInts(useSafeIntegers)
207 if (!raw || statement.columns().length > 0) {
208 return statement.all(...(params as Array<any>)) as ReadonlyArray<any>
209 }
210 const result = statement.run(...(params as Array<any>))
211 return { changes: result.changes, lastInsertRowid: result.lastInsertRowid } as any
212 },
213 catch: (cause) => new SqlError({ reason: classifyError(cause, "Failed to execute statement", "execute") })
214 })
215 })
216
217 const runStatementValues = (
218 statement: StatementSync,
219 params: ReadonlyArray<unknown>
220 ) =>
221 Effect.withFiber<ReadonlyArray<ReadonlyArray<unknown>>, SqlError>((fiber) => {
222 const useSafeIntegers = Context.get(fiber.context, Client.SafeIntegers)
223 return Effect.try({
224 try: () => {
225 statement.setReadBigInts(useSafeIntegers)
226 return statement.all(...(params as Array<any>)) as unknown as ReadonlyArray<ReadonlyArray<unknown>>
227 },
228 catch: (cause) => new SqlError({ reason: classifyError(cause, "Failed to execute statement", "execute") })
229 })
230 })
231
232 const runStatementValuesUnprepared = (
233 statement: StatementSync,
234 params: ReadonlyArray<unknown>
235 ) =>
236 Effect.withFiber<ReadonlyArray<ReadonlyArray<unknown>>, SqlError>((fiber) => {
237 const useSafeIntegers = Context.get(fiber.context, Client.SafeIntegers)
238 return Effect.try({
239 try: () => {
240 statement.setReadBigInts(useSafeIntegers)
241 statement.setReturnArrays(true)
242 return statement.all(...(params as Array<any>)) as unknown as ReadonlyArray<ReadonlyArray<unknown>>
243 },
244 catch: (cause) => new SqlError({ reason: classifyError(cause, "Failed to execute statement", "execute") })
245 })
246 })
247
248 const run = (
249 sql: string,
250 params: ReadonlyArray<unknown>,
251 raw = false
252 ) =>
253 Effect.flatMap(
254 Cache.get(prepareCache, sql),
255 (s) => runStatement(s, params, raw)
256 )
257
258 const runValues = (
259 sql: string,
260 params: ReadonlyArray<unknown>
261 ) =>
262 Effect.acquireUseRelease(
263 Cache.get(prepareCache, sql),
264 (statement) => {
265 statement.setReturnArrays(true)
266 return runStatementValues(statement, params)
267 },
268 (statement) => Effect.sync(() => statement.setReturnArrays(false))
269 )
270
271 const runValuesUnprepared = (
272 sql: string,
273 params: ReadonlyArray<unknown>
274 ) => Effect.flatMap(prepare(sql), (statement) => runStatementValuesUnprepared(statement, params))
275
276 return identity<SqliteConnection>({
277 isTransaction: () => db.isTransaction,
278 execute(sql, params, transformRows) {
279 return transformRows
280 ? Effect.map(run(sql, params), transformRows)
281 : run(sql, params)
282 },
283 executeRaw(sql, params) {
284 return run(sql, params, true)
285 },
286 executeValues(sql, params) {
287 return runValues(sql, params)
288 },
289 executeValuesUnprepared(sql, params) {
290 return runValuesUnprepared(sql, params)
291 },
292 executeUnprepared(sql, params, transformRows) {
293 const effect = Effect.flatMap(prepare(sql), (statement) => runStatement(statement, params ?? [], false))
294 return transformRows ? Effect.map(effect, transformRows) : effect
295 },
296 executeStream(_sql, _params) {
297 return Stream.die("executeStream not implemented")
298 },
299 backup(destination) {
300 return Effect.suspend(() => {
301 let totalPages = 0
302 return Effect.tryPromise({
303 try: () =>
304 backupDatabase(db, destination, {
305 progress: (progress) => {
306 totalPages = progress.totalPages
307 }
308 }).then((pages): BackupMetadata => ({ totalPages: totalPages || pages, remainingPages: 0 })),
309 catch: (cause) => new SqlError({ reason: classifyError(cause, "Failed to backup database", "backup") })
310 })
311 })
312 },
313 loadExtension(path) {
314 return Effect.acquireUseRelease(
315 Effect.sync(() => db.enableLoadExtension(true)),
316 () =>
317 Effect.try({
318 try: () => db.loadExtension(path),
319 catch: (cause) =>
320 new SqlError({ reason: classifyError(cause, "Failed to load extension", "loadExtension") })
321 }),
322 () => Effect.sync(() => db.enableLoadExtension(false))
323 )
324 }
325 })
326 })
327
328 const connection = yield* makeConnection
329 const { acquirer, onCommitFailure, transactionAcquirer } = Client.makeSqliteAcquirers({
330 connection: Effect.succeed(connection),
331 semaphore: yield* Semaphore.make(1),
332 isTransaction: (conn) => conn.isTransaction()
333 })
334
335 return Object.assign(
336 (yield* Client.make({
337 acquirer,
338 compiler,
339 transactionAcquirer,
340 onCommitFailure,
341 releaseSavepoint: (name) => `RELEASE SAVEPOINT ${name}`,
342 beginTransaction: options.readonly === true ? "BEGIN" : "BEGIN IMMEDIATE",
343 spanAttributes: [
344 ...(options.spanAttributes ? Object.entries(options.spanAttributes) : []),
345 [ATTR_DB_SYSTEM_NAME, "sqlite"]
346 ],
347 transformRows
348 })) as SqliteClient,
349 {
350 [TypeId]: TypeId as TypeId,
351 config: options,
352 backup: (destination: string) => Effect.flatMap(acquirer, (_) => _.backup(destination)),
353 loadExtension: (path: string) => Effect.flatMap(acquirer, (_) => _.loadExtension(path))
354 }
355 )
356 })
357
358/**
359 * Builds a layer from an Effect `Config` value, providing both the node `SqliteClient` service and the generic `SqlClient` service.
360 *
361 * @category layers
362 * @since 4.0.0
363 */
364export const layerConfig = (
365 config: Config.Wrap<SqliteClientConfig>
366): Layer.Layer<SqliteClient | Client.SqlClient, Config.ConfigError | SqlError> =>
367 Layer.effectContext(
368 Config.unwrap(config).pipe(
369 Effect.flatMap(make),
370 Effect.map((client) =>
371 Context.make(SqliteClient, client).pipe(
372 Context.add(Client.SqlClient, client)
373 )
374 )
375 )
376 ).pipe(Layer.provide(Reactivity.layer))
377
378/**
379 * Builds a layer from a node SQLite client configuration, providing both `SqliteClient` and the generic `SqlClient` service.
380 *
381 * @category layers
382 * @since 4.0.0
383 */
384export const layer = (
385 config: SqliteClientConfig
386): Layer.Layer<SqliteClient | Client.SqlClient, SqlError> =>
387 Layer.effectContext(
388 Effect.map(make(config), (client) =>
389 Context.make(SqliteClient, client).pipe(
390 Context.add(Client.SqlClient, client)
391 ))
392 ).pipe(Layer.provide(Reactivity.layer))
393
394// internal
395
396const classifyError = (cause: unknown, message: string, operation: string) =>
397 classifySqliteError(sqliteCauseWithErrno(cause), { message, operation })
398
399const sqliteCauseWithErrno = (cause: unknown): unknown => {
400 if (typeof cause !== "object" || cause === null || !("errcode" in cause) || "errno" in cause) {
401 return cause
402 }
403 const errcode = (cause as { readonly errcode: unknown }).errcode
404 if (typeof errcode !== "number") {
405 return cause
406 }
407 return Object.assign(cause, { errno: errcode })
408}