rusqlite: Transactions don't work with async/await

I’m migrating a pretty big codebase to use async/await and ran into problem with making rusqlite work. I understand why Connection is not Sync and normally i would use a Mutex to hold a connection in a multi threaded environment, but in this case i’m holding a reference to a Connection and &T: Send is not Send.

playground link: https://play.rust-lang.org/?version=stable&mode=debug&edition=2018&gist=c25f81c48a319e5fbaf88b79ac1989eb

use rusqlite;
use tokio; // 0.2.13

fn main() {
    let rt = tokio::runtime::Runtime::new().unwrap();

    let handle = rt.spawn(async {
        let conn = rusqlite::Connection::open_in_memory().unwrap();
        let tx = conn.transaction().unwrap();
        async {
            // do something async
        }
        .await;
        tx.execute_batch("SELECT 1").unwrap();
        tx.commit();
    });

    rt.block_on(handle).unwrap();
}

About this issue

Original URL
State: closed
Created 4 years ago
Reactions: 3
Comments: 18 (11 by maintainers)

Most upvoted comments

Worth noting that sqlx might be a better choice if you need to use sqlite and async. It’s somewhat deliberate that rusqlite is awkward to use in this context, as blocking IO is bad here.

sqlx is extremely slow with SQLite, up to 500% slower than rusqlite in my tests.

spawn_blocking is also not an option in cases where you need to call async functions during transaction as async is not possible within spawn_blocking.

Currently there doesn’t seem to be any good way to use SQLite in async Rust.

malaire on Jan 30, 2021

I’m attempting to create async-wrapper for rusqlite based on actor which runs inside std::thread::spawn and keeps Connection and Transaction there. Actor can receive closure via mpsc. Closure takes Connection or Transaction as parameter and is run within the thread, returning result via oneshot channel.

This would fix the main problem of sqlx which is really high async overhead, as each query is separate async task: The closure can run any number of queries with fixed async overhead per closure.

This would also allow running async operations during transaction:

start transaction
run closure against transaction
run other async code
run other closure against transaction
commit

malaire on Jan 30, 2021

rusqlite doesn’t provide an async API, please use spawn_blocking.

Not using spawn_blocking is just asking for trouble, and with spawn_blocking things should just work.

KamilaBorowska on Jun 6, 2020

(This also roughly relates the question raised at https://github.com/rusqlite/rusqlite/issues/188#issuecomment-631169811 the other day).

From some discussion, I’ve heard that doing blocking operations on async worker threads is a big problem, and that it’s probably good that rusqlite behave as it does currently, as a way to signal to users that they’re doing something very wrong.

In the mean time, you should either follow @xfix’s advice (use spawn_blocking), or if that is is not-viable, perhaps you could use the sqlx crate, which seems to make SQLite async by performing IO on a background thread.

Eventually there’s a reasonable chance that similar async support will become desirable for rusqlite (I suspect Firefox may eventually need it…), but there are no short term plans for this. (And if this happens it wouldn’t be the default though, it would be behind in a feature; or possibly even a separate rusqlite-async crate).

Because of all that I’m going to close this as works-as-intended for now, even if it’s a bit surprising.

thomcc on Jun 7, 2020

Are you able to use Statement across await? I’m guessing not… I know of a solution for Transaction (make transaction hold &mut Connection. Sadly this requires killing the new unchecked_transaction API, which has valid use cases… That said it’s new so not that big of a deal I guess…)

It would be nice to fix the general problem though – I suspect fixing that would mean many other APIs don’t work still.

thomcc on Apr 12, 2020