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