[sql] Add support for transactions

[Sahasrara]

Here's the method I use to execute a bunch of queries within a transaction:

    async executeTransaction(transaction: Transaction): Promise<void> {
        let wasError: boolean = false;
        try {
            this._db.execute('BEGIN;');
            await transaction();
        } catch (err) {
            wasError = true;
            this._db.execute('ROLLBACK;');
            throw err;
        } finally {
            if (!wasError) {
                this._db.execute('COMMIT;');
            }
        }
    }

Example:

        await db.executeTransaction(async () => {
            // A bunch of await db.execute() statements
        });

When I do this, and throw an error, rollback is executed, but rollback doesn't take place.

[FabianLars]

Hi, thanks for reaching out. Could you also include the output of the tauri info command and a bit more info about your database? sqlite, mysql, psql, etc? Also, do you think you could come up with a runable minimal reproduction example?

[Sahasrara]

Sure thing!

[✔] Environment
    - OS: Windows 10.0.22631 X64
    ✔ WebView2: 120.0.2210.121
    ✔ MSVC: Visual Studio Build Tools 2022
    ✔ rustc: 1.75.0 (82e1608df 2023-12-21)
    ✔ cargo: 1.75.0 (1d8b05cdd 2023-11-20)
    ✔ rustup: 1.26.0 (5af9b9484 2023-04-05)
    ✔ Rust toolchain: stable-x86_64-pc-windows-msvc (default)
    - node: 20.10.0
    - pnpm: 8.14.1
    - npm: 10.2.3

[-] Packages
    - tauri [RUST]: 2.0.0-alpha.20
    - tauri-build [RUST]: 2.0.0-alpha.13
    - wry [RUST]: 0.35.1
    - tao [RUST]: 0.24.0
    - @tauri-apps/api [NPM]: 2.0.0-alpha.13
    - @tauri-apps/cli [NPM]: 2.0.0-alpha.20

[-] App
    - build-type: bundle
    - CSP: unset
    - distDir: ../dist
    - devPath: http://localhost:1420/
    - framework: Svelte
    - bundler: Vite

[Sahasrara]

I'm using Sqlite. I think the problem is that because I'm issuing multiple commands, they're being executed on different members of the connection pool.

I dug in to this a little, and I think sqlx has a transaction abstraction that's not available through the current tauri-plugin-sql APIs. I think that might be the issue.

I'll try to get a repro to you, but I'm kind of slammed at the moment with a move to a new house.

[madisvain]

@Sahasrara looking at the Rust code and SQLX documentation I dont see implementation of transactions logic: https://docs.rs/sqlx/latest/sqlx/struct.Transaction.html

There is nothing like this implemented in the tauri sql plugin: https://github.com/tauri-apps/tauri-plugin-sql/blob/v1/src/plugin.rs#L202

@FabianLars is this something thats supported at all?

[FabianLars]

@FabianLars is this something thats supported at all?

Probably not, I don't know.

I was once told that it worked for someone without explicit support in the plugin but I doubt it tbh.

[angelxmoreno]

could we change the title of this issue and turn it into a feature request?

[enrique-lozano]

Any news here?

[fmabap]

@FabianLars I read that the plugin is based on sqlx.

I found the following blog. It says that you have to create a transaction with a special method in sqlx to get a transaction object and then you have to do your sql executions with this new transaction object.

https://jmoiron.github.io/sqlx/#transactions

It looks like that this method and the transaction object are not available in the plugin.

Can you please add them?

[cardo-podcast]

That would be a nice feature!

[RunasSudo]

In simple cases, it's possible to perform the transaction in one call, e.g. db.execute("BEGIN; INSERT INTO ...; INSERT INTO ...; COMMIT;"). However, this doesn't work when not all commands are known in advance.

It seems to be relatively simple to expose sqlx::Transaction to JavaScript – I have done a small proof of concept at https://gist.github.com/RunasSudo/8d30798e7cd7bbddaaba0348c8ea8f58 for SQLite.

However, I suspect (though don't know how to verify) that this would lead to resource leaks if a transaction is begun in JavaScript but not committed or rolled back, e.g. due to an error in JavaScript. Currently I am using a FinalizationRegistry in JS to account for this but unsure if that is the best method. Ideally Tauri would be able to take care of all this behind the scenes.