@effect/sql-sqlite-bun: withTransaction fails for readonly query_only clients after BEGIN IMMEDIATE default

[Kashkovsky]

Summary

@effect/sql-sqlite-bun documents that explicit transactions on writable connections use BEGIN IMMEDIATE and that readonly: true clients are unaffected, but the implementation passes BEGIN IMMEDIATE unconditionally to SqlClient.make.

A connection deliberately configured as hard read-only with SQLite's PRAGMA query_only = ON therefore cannot use withTransaction, even when the transaction body contains only a SELECT. The failure occurs while opening the transaction, before the body runs.

This appears to be a narrow regression from #7162. The writer-locking change is useful for writable clients; read-only clients need deferred BEGIN.

Versions and environment

• @effect/sql-sqlite-bun@4.0.0-rc.112
• effect@4.0.0-rc.112
• Bun 1.3.14 (0d9b296a)
• SQLite 3.54.0 (bundled with Bun)
• macOS 27.0 arm64

The current Effect main branch also still has the unconditional opener: beginTransaction: "BEGIN IMMEDIATE".

Minimal reproduction

Install the two RC packages:

bun add effect@4.0.0-rc.112 @effect/sql-sqlite-bun@4.0.0-rc.112

Then run:

import * as SqliteClient from "@effect/sql-sqlite-bun/SqliteClient"
import { Database } from "bun:sqlite"
import { Effect } from "effect"
import { Reactivity } from "effect/unstable/reactivity"

const filename = `/tmp/effect-sqlite-readonly-${crypto.randomUUID()}.sqlite`
const seed = new Database(filename)
seed.exec("CREATE TABLE test (id INTEGER PRIMARY KEY); INSERT INTO test DEFAULT VALUES;")
seed.close(false)

try {
  const rows = await Effect.runPromise(
    Effect.scoped(
      Effect.gen(function*() {
        const sql = yield* SqliteClient.make({
          filename,
          readonly: true
        })
        yield* sql.unsafe("PRAGMA query_only = ON")
        return yield* sql.withTransaction(sql.unsafe("SELECT * FROM test"))
      })
    ).pipe(Effect.provide(Reactivity.layer))
  )
  console.log(rows)
} finally {
  await Bun.file(filename).delete()
}

Actual behavior

The withTransaction(SELECT) call fails at BEGIN IMMEDIATE:

SqlError
└─ UnknownError
   └─ SQLiteError: attempt to write a readonly database
      code: SQLITE_READONLY
      errno: 8

Replacing the transaction opener with deferred BEGIN returns:

[{ id: 1 }]

Expected behavior

• A client configured with readonly: true should open an explicit read transaction with deferred BEGIN.
• Writable clients should retain BEGIN IMMEDIATE so they still avoid read-to-write snapshot upgrade failures and acquire or queue for the writer lock at transaction start.

This matches SQLite's transaction semantics: BEGIN IMMEDIATE starts a write transaction immediately, while PRAGMA query_only = ON rejects attempts to change database state with SQLITE_READONLY.

A direct Bun control reaches the same boundary: Bun's default transaction (BEGIN) succeeds for a SELECT after query_only, while its explicit .immediate() mode fails with SQLITE_READONLY. Bun documents deferred/default and immediate transactions as distinct modes, so this is transaction-policy selection in the Effect adapter rather than a Bun driver defect.

Regression boundary and test gap

The behavior was introduced by commit c30386df / #7162 and first published in 4.0.0-beta.107. beta.106 used the shared deferred BEGIN default; beta.107 and RC.108 through RC.112 contain the unconditional BEGIN IMMEDIATE.

The change's documentation says writable connections use BEGIN IMMEDIATE and readonly: true clients are unaffected. Its read-only transaction test opens with readonly: true, but does not enable PRAGMA query_only, so it does not cover this failure.

Downstream evidence from Threadnote

Threadnote has real snapshot readers that:

1. construct an Effect Bun SQLite client with readonly: true and readwrite: false;
2. enforce PRAGMA query_only = ON; and
3. use withTransaction as a read-only linearization point.

Relevant source:

• read-only client construction and connection policy
• transactional snapshot-lease validation
• exact RC.112 dependency pin

Threadnote currently carries the narrow package patch:

beginTransaction: options.readonly === true ? "BEGIN" : "BEGIN IMMEDIATE"

See the package patch and two-sided real-file regression test.

The test proves both sides of the intended contract:

• after the patch, a read-only + query_only transaction succeeds and still rejects writes;
• a writable Effect client attempting a transaction behind a separately held BEGIN IMMEDIATE writer still fails with SQLITE_BUSY / database is locked, proving writable transactions remain immediate.

The same unpatched RC.112 failure also reproduced on Ubuntu in Threadnote's real cross-repository workset smoke: the GitHub Actions job reports @effect/sql-sqlite-bun failing with SQLiteError: attempt to write a readonly database.

Suggested fix

Use the client's declared access mode when selecting the opener:

beginTransaction: options.readonly === true ? "BEGIN" : "BEGIN IMMEDIATE"

and extend the existing read-only test to enable PRAGMA query_only = ON before calling withTransaction.

The Node SQLite adapter has the same unconditional construction; it may merit the corresponding test/fix if the same case reproduces there, but this report and reproduction are specifically for @effect/sql-sqlite-bun.