Usage of transactions in :memory: database was broken with #105

#105 regressed in memory databases when transactions are used. Once a transaction is created, the entire in memory database will be effectively discarded for subsequent db operations that the client makes.

From the regressing PR.

A single connection is created lazily whenever user executes a statement

When a transaction starts, the Transaction object takes ownership of the current transaction.

Subsequent queries will create a new connection.

In particular, this line of code from within transaction, null's out this.#db.

Subsequent calls to #getDb will create a new database here. Because it's purely in memory, this essentially creates a new empty database.

I wonder if the fix would be as simple as skipping the dropping/recreating of the Database if path === ':memory'.

async transaction(mode: TransactionMode = "write"): Promise<Transaction> {
    const db = this.#getDb();
    executeStmt(db, transactionModeToBegin(mode), this.#intMode);
    if (this.#path !== ':memory:') {
        this.#db = null; // A new connection will be lazily created on next use
    }
    return new Sqlite3Transaction(db, this.#intMode);
}

// Lazily creates the database connection and returns it
#getDb(): Database.Database {
    if (this.#db === null && this.$path !== ':memory:') {
        this.#db = new Database(this.#path, this.#options);
    }
    return this.#db;
}

Related? tursodatabase/libsql#1411

This is fixed in 0.9.0 file::memory:?cache=shared

Mitigate problems using an in-memory database

@khuezy

This is fixed in 0.9.0

@khuezy, how does 0.9.0 fix this? Could you point out the relevant part in the diff?

file::memory:?cache=shared

For me, this revealed another problem: closing the database connection did not appear to be working. New connections were still using the old database, even after closing. Has anyone else encountered this too? @thewilkybarkid is this what you're referring to in the commit you've linked?

Based on @epatey's comment, I used the following patch to resolve:

diff --git a/node_modules/@libsql/client/lib-cjs/sqlite3.js b/node_modules/@libsql/client/lib-cjs/sqlite3.js
index f479612..5bb7f4d 100644
--- a/node_modules/@libsql/client/lib-cjs/sqlite3.js
+++ b/node_modules/@libsql/client/lib-cjs/sqlite3.js
@@ -145,7 +145,11 @@ class Sqlite3Client {
     async transaction(mode = "write") {
         const db = this.#getDb();
         executeStmt(db, (0, util_1.transactionModeToBegin)(mode), this.#intMode);
-        this.#db = null; // A new connection will be lazily created on next use
+
+        if (!this.#path.includes(":memory:")) {
+            this.#db = null; // A new connection will be lazily created on next use
+        }
+
         return new Sqlite3Transaction(db, this.#intMode);
     }
     async executeMultiple(sql) {
diff --git a/node_modules/@libsql/client/lib-esm/sqlite3.js b/node_modules/@libsql/client/lib-esm/sqlite3.js
index 8aa7047..22b6781 100644
--- a/node_modules/@libsql/client/lib-esm/sqlite3.js
+++ b/node_modules/@libsql/client/lib-esm/sqlite3.js
@@ -123,7 +123,11 @@ export class Sqlite3Client {
     async transaction(mode = "write") {
         const db = this.#getDb();
         executeStmt(db, transactionModeToBegin(mode), this.#intMode);
-        this.#db = null; // A new connection will be lazily created on next use
+
+        if (!this.#path.includes(":memory:")) {
+            this.#db = null; // A new connection will be lazily created on next use
+        }
+
         return new Sqlite3Transaction(db, this.#intMode);
     }
     async executeMultiple(sql) {

this.#path.includes(':memory:') handles the existence of the file scheme and potential query parameters:

✅ :memory:
✅ file::memory:
✅ file::memory:?cache=shared

The modification in getDb did not seem necessary.

@winghouchan

@winghouchan Hi, my comment wasn't clear, the OP was in June so the diff was on an older version:
v0.7.0...v0.9.0

I'm not sure of the issue you're having, perhaps messaging the team on the discord will get their attention if there's an issue.

@khuezy

@khuezy, so it wasn't 0.9.0 then?

Taking a look through PRs, are you referring to #220 (released in 0.8.0 (commit))?

Ah, I see #220 intended to fix tursodatabase/libsql#1411 which is a duplicate of this issue.

I don't think it actually fixes the issue but introduces a workaround (cache mode of shared) which itself has weird behaviours. I've created an MCVE here: https://github.com/winghouchan/libsql-in-memory-transactions-broken-mcve

@sivukhin

@sivukhin, what are your thoughts as the author of #220?

Yea it's more of a workaround. Was probably fixed around 0.8, my original comment was meant to say "upgrade to the latest 0.9.0 where the workaround was introduced after 0.7.0". I only use :memory: in my tests so the work around was fine for me.

refactor(database): migrate from expo sqlite to op sqlite

fix(data): patch @libsql/client to replay PRAGMAs after transaction()

fix(sqlite3): keep connection handle for in-memory databases across t…

@epatey

Confirming this is still broken on the current main branch and @libsql/client@0.17.2, with the canonical :memory: URL (no cache=shared workaround).

Minimal repro (17 lines):

import { createClient } from "@libsql/client";

const client = createClient({ url: ":memory:" });

await client.execute("CREATE TABLE things (id INTEGER PRIMARY KEY, name TEXT)");
await client.execute("INSERT INTO things VALUES (1, 'first')");

const before = await client.execute("SELECT * FROM things");
console.log("before tx :", before.rows.length, "row(s)"); // 1

const tx = await client.transaction("write");
await tx.execute("INSERT INTO things VALUES (2, 'second')");
await tx.commit();

const after = await client.execute("SELECT * FROM things");
console.log("after tx  :", after.rows.length, "row(s)");
// Throws: LibsqlError: SQLITE_ERROR: no such table: things

I hit this while porting a test harness to Drizzle + @libsql/client. A typical createTestDb() helper using :memory: silently loses its schema mid-test the first time any code path goes through a Drizzle db.transaction(...) — a nasty footgun for anyone using a Drizzle-style in-memory test database.

I've opened #342 with a fix that takes @epatey's original suggestion (this.#db = null guarded on in-memory URLs) and uses the existing isInMemoryConfig helper from @libsql/core/config instead of a path-string check, so all three URL forms (:memory:, file::memory:, file::memory:?cache=private) are handled uniformly.

Also folds in #140, which is the same bug.

The PR updates the three existing tests under describe("execute()") that were asserting rejects.toThrow() — those were documenting the broken behaviour rather than guarding against it — and adds two positive withInMemoryClient tests for commit-preserves-data and rollback-preserves-original-state. Full in-memory suite (14 tests) and full file-backed suite (171 tests) both pass with no regressions.

fix(backend): keep local libsql databases intact across transactions …

feat: provenance retraction on a composable, atomic write pipeline (#188

Version Packages (#193)

fix(sqlite3): keep connection handle for in-memory databases across t…

fix(sqlite3): give a client one connection, for file and memory alike

fix(sqlite3): give a client a connection pool, like the hrana clients

sqlite3: keep connection handle for in-memory databases across transa…

Navigation Menu

Repository navigation

Usage of transactions in `:memory:` database was broken with #105 #229

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Usage of transactions in :memory: database was broken with #105 #229

Description

Activity

epatey commented on Jun 24, 2024

erkannt commented on Jun 28, 2024

khuezy commented on Aug 15, 2024

winghouchan commented on Dec 21, 2024

khuezy commented on Dec 23, 2024

winghouchan commented on Dec 23, 2024

winghouchan commented on Dec 23, 2024

winghouchan commented on Dec 23, 2024

winghouchan commented on Dec 23, 2024

khuezy commented on Dec 23, 2024

BartWaardenburg commented on Apr 10, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Usage of transactions in `:memory:` database was broken with #105 #229