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

TL;DR

Workaround: use file::memory:?cache=shared as the URL (shared cache mode keeps one DB; introduced ~0.8/0.9 via PR #(220).

The error

Issue tursodatabase/libsql-client-ts#229 (closed, 11 comments): #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.

Fix

1. Workaround: use file::memory:?cache=shared as the URL (shared cache mode keeps one DB; introduced ~0.8/0.9 via PR #(220).

Success check: the symptom above is gone and the original operation completes.

When to use

• You hit this exact symptom in libsql-client-ts.
• You want the verified fix without digging through the thread.

When not to use

• A different error in libsql-client-ts, even a similar-looking one.
• General libsql-client-ts setup or credential questions.

Compatibility

• Libsql Client Ts (tursodatabase/libsql-client-ts)

Variant phrasings

• How to fix "usage of transactions in :memory: database was broken with #105" in libsql-client-ts
• libsql-client-ts: usage of transactions in :memory: database was broken with #105

Root cause

Root cause: PR #105 made transactions take ownership of a lazily-created connection, so on :memory: DBs the in-memory database is effectively discarded for subsequent operations after a transaction.

Edge cases

• Caveat: closing connections behaves oddly in shared-cache mode.

Source

• https://github.com/tursodatabase/libsql-client-ts/issues/229

Asked in gh:tursodatabase/libsql-client-ts#229 (https://github.com/tursodatabase/libsql-client-ts/issues/229): Usage of transactions in :memory: database was broken with #105. The issue report: Issue tursodatabase/libsql-client-ts#229 (closed, 11 comments): #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](https://github.com/tursodatabase/libsql-client-ts/blob/4258668bd9659e8faaf2aba9098c20ce549bd112 The verified answer: Honest status: STILL BROKEN on main / @libsql/client 0.17.2 per a maintainer (BartWaardenburg, CONTRIBUTOR), who re-confirmed with a 17-line repro. Root cause: PR #105 made transactions take ownership of a lazily-created connection, so on :memory: DBs the in-memory database is effectively discarded for subsequent operations after a transaction. Workaround: use file::memory:?cache=shared as the URL (shared cache mode keeps one DB; introduced ~0.8/0.9 via PR #220). Caveat: closing connections behaves oddly in shared-cache mode.