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

[epatey]

#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.

1. A single connection is created lazily whenever user executes a statement
2. When a transaction starts, the Transaction object takes ownership of the current transaction.
3. 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'.

[epatey]

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;
}

[erkannt]

Related? tursodatabase/libsql#1411

[khuezy]

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

[winghouchan]

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.

[khuezy]

@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.

[winghouchan]

@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))?

[winghouchan]

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

[winghouchan]

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

[winghouchan]

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

[khuezy]

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.

[BartWaardenburg]

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.