Sqlite3Client.transaction() silently destroys :memory: databases

[khawa-angx]

Version: @libsql/client@0.17.4

Description:

When using an in-memory database (file::memory: or :memory:), calling Sqlite3Client.transaction() internally sets this.#db = null, which causes the next #getDb() call to create a brand new, empty :memory: database — silently discarding all tables and data.

Root cause in sqlite3.js (https://github.com/tursodatabase/libsql-client-ts/blob/main/packages/libsql-client/src/sqlite3.ts):

async transaction(mode = "write") {                                                                                                                                                                                                                                                                                                                         
    const db = this.#getDb();                                                                                                                                                                                                                                                                                                                               
    executeStmt(db, transactionModeToBegin(mode), this.#intMode);                                                                                                                                                                                                                                                                                           
    this.#db = null; // ← clears the handle — fatal for :memory:                                                                                                                                                                                                                                                                                            
    return new Sqlite3Transaction(db, this.#intMode);                                                                                                                                                                                                                                                                                                       
} 

After transaction() commits/rolls back, #db remains null. The next call to execute(), batch(), or another transaction() triggers #getDb() → new Database(":memory:", options), which creates an entirely separate, empty in-memory database.

Why this matters:

In SQLite, each new Database(":memory:") creates an independent in-memory database. Unlike file-backed databases where a new connection still sees the same data, :memory: connections are completely isolated from each other.

This means every call to transaction() effectively resets the database when using :memory:, making :memory: unusable with any code that opens more than one transaction on the same client.

Minimal reproduction:

import Database from "libsql";                                                                                                                                                                                                                                                                                                                              
import { createClient } from "@libsql/client";                                                                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                                                                            
const client = createClient({ url: ":memory:" });                                                                                                                                   
                                                                                                                                                                                                                                                                                                                                                            
// Create a table                                                                                                                                                                                                                                                                                                                                           
await client.execute("CREATE TABLE t (id INTEGER PRIMARY KEY)");                                                                                                                                                                                                                                                                                            
await client.execute("INSERT INTO t VALUES (1)");                                                                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                                                                                                            
// First transaction — works fine                                                                                                                                                                                                                                                                                                                           
const tx1 = await client.transaction();                                                                                                                                                                                                                                                                                                                     
await tx1.execute("SELECT * FROM t"); // returns [{ id: 1 }]                                                                                                                                                                                                                                                                                             
await tx1.commit();                                                                                                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                                                                            
// Second transaction — explodes                                                                                                                                                                                                                                                                                                                            
const tx2 = await client.transaction();                                                                                                                                                                                                                                                                                                                     
await tx2.execute("SELECT * FROM t"); // SQLITE_ERROR: no such table: t

Expected behavior:

All transactions on the same Sqlite3Client should operate on the same in-memory database. The fix would be to not set #db = null for :memory: databases, or to use savepoints instead of managing the connection handle.

Same issue also affects drizzle-orm users on top of @libsql/client, since drizzle's LibSQLSession.transaction() delegates to Sqlite3Client.transaction().

[sanskar-singh-2403]

@barbieri should we fix this?

[barbieri]

I'm not a maintainer anymore, but likely this is in the core not in the bindings.

cc @penberg