How to use it with tokio::spawn?

[wuminzhe]

async fn function1(conn: &Connection) {
}

#[tokio::main]
async fn main() {
  tokio::spawn(async {
      let conn = Connection::open("./data.db").unwrap();
      function1(&conn).await;
  });
}

the code will get the err:

error: future cannot be sent between threads safely
   --> src/main.rs:123:13
    |
123 |             tokio::spawn(async move {
    |             ^^^^^^^^^^^^ future created by async block is not `Send`
    |
   ::: /Users/akiwu/.cargo/registry/src/github.com-1ecc6299db9ec823/tokio-1.9.0/src/task/spawn.rs:127:21
    |
127 |         T: Future + Send + 'static,
    |                     ---- required by this bound in `tokio::spawn`
    |
    = help: within `Connection`, the trait `Sync` is not implemented for `RefCell<rusqlite::inner_connection::InnerConnection>`
note: future is not `Send` as this value is used across an await
   --> src/main.rs:127:17
    |
127 |                 function1(&conn).await;
    |                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ first, await occurs here, with `&conn` maybe used later...
note: `&conn` is later dropped here
   --> src/main.rs:127:47
    |
127 |                 function1(&conn).await;
    |                                   -----       ^
    |                                   |
    |                                   has type `&Connection` which is not `Send`

But if this code is ok:

async fn function1(conn: &Connection) {
}

#[tokio::main]
async fn main() {
    let conn = Connection::open("./data.db").unwrap();
    function1(&conn).await;
}

why?

[thomcc]

In general we don't have a great story around async. The best option is to send messages to a background thread (or set of background threads that manage a connection pool) and have the results be sent back. If you have a good amount of experience in Rust this isn't too difficult to build, but it's also not trivial, and I don't know of an off-the-shelf solution for it.

Currently, if you need async and don't want to do this, you might be better off with https://github.com/launchbadge/sqlx, although note that it may have worse performance and definitely exposes fewer sqlite-specific features.

I've also seen applications architect things such that you have Connection stored in a thread_local! (or even just use Mutex<Connection> of some sort), which is less work, but also not ideal in many cases.

[ekanna]

Async rusqlite built on top of rusqlite.
https://github.com/programatik29/tokio-rusqlite

[wuminzhe]

Async rusqlite built on top of rusqlite. https://github.com/programatik29/tokio-rusqlite

thank you

[adumbidiot]

I would avoid that library for now. It seems new and has a few problems, like using a std sync_channel which potentially blocks in async code. For optimization purposes, it also doesn't use a connection pool which isn't ideal if you want full concurrency in WAL mode.

[ekanna]

@programatik29 Can you share your thoughts on this comment about sync_channel ?

[programatik29]

It's just me being lazy. I'll replace it with crossbeam channel.

[adumbidiot]

That's just one of the issues I can see with that. The others are that it panics at basically every channel send, making it easy to crash the db thread if a future is cancelled:

/// Contrived timeout example
use std::time::Duration;

#[tokio::main]
async fn main() {
    let con = tokio_rusqlite::Connection::open("./test.db")
        .await
        .expect("failed to open");

    {
        let con_fut = con.call(|con| {
            // Long query
            std::thread::sleep(Duration::from_secs(3));
        });

        let timeout_fut = tokio::time::sleep(Duration::from_secs(1));

        tokio::pin!(con_fut);
        tokio::pin!(timeout_fut);

        // Wait for query to complete or cancel interest
        tokio::select! {
           _ = &mut con_fut => {},
           _ = &mut timeout_fut => {},
        }
    }
    
    // Panic # 1 here as the oneshot receiver is dropped and the db tries to send to it.
    tokio::time::sleep(Duration::from_secs(5)).await;
    
    // Panic # 2 here as the db thread has crashed, and the channel will panic if you try to send to it.
    con.call(|con| {
           // This will never run, the db thread has crashed.
           println!("Still alive?");
    }).await;
}

While only somewhat visible in this example, the db thread will also not restart if it panics. That doesn't really matter that much though as any call to the connection after the db thread panics will also panic, including the call that caused the panic.

[programatik29]

@adumbidiot Thanks for feedback. I will make the futures cancel safe.

[adumbidiot]

Crashing the db thread permanently via an explicit panic is also possible. Here's a contrived example:

use futures::FutureExt;
use std::time::Duration;

#[tokio::main]
async fn main() {
    let con = tokio_rusqlite::Connection::open("./test.db")
        .await
        .expect("failed to open");

    {
        con.call::<_, ()>(|con| {
            // Explicit panic, user error
            panic!("something went really wrong");
        })
        .await;

        // Panic while sending, and for all future sends from any sender
        con.call(|con| {
            println!("this will never run");
        })
        .await;
    }
}

[jeromegn]

Even with a cancellable future, since rusqlite is synchronous (and... well sqlite), you can't really cancel sqlite operations.

Anything non-async function that started will finish its course even if the future that wrapped it is canceled.

This is relevant: https://stackoverflow.com/questions/59977693/how-can-i-stop-running-synchronous-code-when-the-future-wrapping-it-is-dropped

[adumbidiot]

True, but cancelling a future, possibly as a part of a larger future, should not crash your database thread.

[gwenn]

Even with a cancellable future, since rusqlite is synchronous (and... well sqlite), you can't really cancel sqlite operations.

In case you receive the cancel event, you can use:
http://sqlite.org/c3ref/progress_handler.html

If the progress callback returns non-zero, the operation is interrupted.

which indirectly calls sqlite3_interrupt.

I have no Rust example but with JDBC.

Or the SQLite shell.