Rusqlite `Connection` and `Statement` lifetimes issue

help
1

I have the following code

let mut stmt = unwrap!(
  conn.prepare_cached("SELECT EXISTS(SELECT 1 FROM users WHERE name = ?1);"),
  |err| {
    return Err((conn, db::Error::Rusqlite(err)));
  },
);

let res = unwrap!(
  stmt.query_row([&name], |row| row.get::<_, bool>(0)),
  |err| {
    return Err((conn, db::Error::Rusqlite(err)));
  },
);

Ok((conn, res))

(unwrap! is just a shortcut for match x { Ok(x) => x, Err($err) => $action })

which basically runs the query and on success returns the connection to be reused and result, and on errors returns the connection and error.

The code does not compile

error[E0505]: cannot move out of `conn` because it is borrowed
   --> src/main.rs:449:33
    |
428 | async fn db_manager(mut cmd_rx: db::CmdRx, mut conn: Connection) {
    |                                            -------- binding `conn` declared here
...
447 |                   conn.prepare_cached("SELECT EXISTS(SELECT 1 FROM users WHERE name = ?1);"),
    |                   --------------------------------------------------------------------------
    |                   |
    |                   borrow of `conn` occurs here
    |                   a temporary with access to the borrow is created here ...
448 |                   |err| {
449 |                     return Err((conn, db::Error::Rusqlite(err)));
    |                                 ^^^^ move out of `conn` occurs here
450 |                   },
451 |                 );
    |                  - ... and the borrow might be used here, when that temporary is dropped and runs the destructor for type `Result<CachedStatement<'_>, rusqlite::Error>`

error[E0505]: cannot move out of `conn` because it is borrowed
   --> src/main.rs:456:33
    |
428 | async fn db_manager(mut cmd_rx: db::CmdRx, mut conn: Connection) {
    |                                            -------- binding `conn` declared here
...
447 |                   conn.prepare_cached("SELECT EXISTS(SELECT 1 FROM users WHERE name = ?1);"),
    |                   ---- borrow of `conn` occurs here
...
456 |                     return Err((conn, db::Error::Rusqlite(err)));
    |                                 ^^^^ move out of `conn` occurs here
...
461 |               })
    |               - borrow might be used here, when `stmt` is dropped and runs the `Drop` code for type `CachedStatement`

error[E0505]: cannot move out of `conn` because it is borrowed
   --> src/main.rs:460:21
    |
428 | async fn db_manager(mut cmd_rx: db::CmdRx, mut conn: Connection) {
    |                                            -------- binding `conn` declared here
...
447 |                   conn.prepare_cached("SELECT EXISTS(SELECT 1 FROM users WHERE name = ?1);"),
    |                   ---- borrow of `conn` occurs here
...
460 |                 Ok((conn, res))
    |                     ^^^^ move out of `conn` occurs here
461 |               })
    |               - borrow might be used here, when `stmt` is dropped and runs the `Drop` code for type `CachedStatement`

For more information about this error, try `rustc --explain E0505`.
error: could not compile `db-server` (bin "db-server") due to 3 previous errors

so I add a few drop()s

let mut stmt = unwrap!(
  conn.prepare_cached("SELECT EXISTS(SELECT 1 FROM users WHERE name = ?1);"),
  |err| {
    return Err((conn, db::Error::Rusqlite(err)));
  },
);

let res = unwrap!(
  stmt.query_row([&name], |row| row.get::<_, bool>(0)),
  |err| {
    drop(stmt);
    return Err((conn, db::Error::Rusqlite(err)));
  },
);

drop(stmt);
Ok((conn, res))

but it still does not compile

error[E0505]: cannot move out of `conn` because it is borrowed
   --> src/main.rs:449:33
    |
428 | async fn db_manager(mut cmd_rx: db::CmdRx, mut conn: Connection) {
    |                                            -------- binding `conn` declared here
...
447 |                   conn.prepare_cached("SELECT EXISTS(SELECT 1 FROM users WHERE name = ?1);"),
    |                   --------------------------------------------------------------------------
    |                   |
    |                   borrow of `conn` occurs here
    |                   a temporary with access to the borrow is created here ...
448 |                   |err| {
449 |                     return Err((conn, db::Error::Rusqlite(err)));
    |                                 ^^^^ move out of `conn` occurs here
450 |                   },
451 |                 );
    |                  - ... and the borrow might be used here, when that temporary is dropped and runs the destructor for type `Result<CachedStatement<'_>, rusqlite::Error>`

For more information about this error, try `rustc --explain E0505`.
error: could not compile `db-server` (bin "db-server") due to 1 previous error

so I think of moving the call to a variable so that I can drop it in the error path

let stmt =
  conn.prepare_cached("SELECT EXISTS(SELECT 1 FROM users WHERE name = ?1);");

let mut stmt = unwrap!(stmt, |err| {
  drop(stmt);
  return Err((conn, db::Error::Rusqlite(err)));
});

let res = unwrap!(
  stmt.query_row([&name], |row| row.get::<_, bool>(0)),
  |err| {
    drop(stmt);
    return Err((conn, db::Error::Rusqlite(err)));
  },
);

drop(stmt);
Ok((conn, res))

different errors than the first attempt

error[E0382]: use of partially moved value: `stmt`
   --> src/main.rs:450:24
    |
449 |                 let mut stmt = unwrap!(stmt, |err| {
    |                                               --- value partially moved here
450 |                   drop(stmt);
    |                        ^^^^ value used here after partial move
    |
    = note: partial move occurs because value has type `rusqlite::Error`, which does not implement the `Copy` trait
help: borrow this binding in the pattern to avoid moving the value
    |
449 |                 let mut stmt = unwrap!(stmt, |ref err| {
    |                                               +++

error[E0505]: cannot move out of `conn` because it is borrowed
   --> src/main.rs:458:33
    |
428 | async fn db_manager(mut cmd_rx: db::CmdRx, mut conn: Connection) {
    |                                            -------- binding `conn` declared here
...
447 |                   conn.prepare_cached("SELECT EXISTS(SELECT 1 FROM users WHERE name = ?1);");
    |                   ---- borrow of `conn` occurs here
...
458 |                     return Err((conn, db::Error::Rusqlite(err)));
    |                                 ^^^^ move out of `conn` occurs here
...
464 |               })
    |               - borrow might be used here, when `stmt` is dropped and runs the destructor for type `Result<CachedStatement<'_>, rusqlite::Error>`

error[E0505]: cannot move out of `conn` because it is borrowed
   --> src/main.rs:463:21
    |
428 | async fn db_manager(mut cmd_rx: db::CmdRx, mut conn: Connection) {
    |                                            -------- binding `conn` declared here
...
447 |                   conn.prepare_cached("SELECT EXISTS(SELECT 1 FROM users WHERE name = ?1);");
    |                   ---- borrow of `conn` occurs here
...
463 |                 Ok((conn, res))
    |                     ^^^^ move out of `conn` occurs here
464 |               })
    |               - borrow might be used here, when `stmt` is dropped and runs the destructor for type `Result<CachedStatement<'_>, rusqlite::Error>`

Some errors have detailed explanations: E0382, E0505.
For more information about an error, try `rustc --explain E0382`.
error: could not compile `db-server` (bin "db-server") due to 3 previous errors

No matter what I tried I could not solve it. I do not understand why I cannot return conn in the error path as in that case nothing is borrowing it

2

That's a really odd way to handling errors, at least to me, and it's what is creating the problem. No matter how you refactor it, unwrap will try to move the conn while it's borrowed.

Try producing the error, matching it (which consumes it and removes the borrow on conn), then returning conn and the error.

3

Using match directly (for both usages) still has the same issue

4

Fair, it was hard to replicate the code on my side. It's a bit more tricky when you want to return conn and the error at the same time.

It can depend what the context is here. You need to return conn ever time... you might be in a multithreaded situation?

You return the conn if something fails, but instead of moving connection in the first place, you could give a &conn or &mut conn. Normally with rusqlite I would setup a queue for a writer with rx and tx. But it normally increases the complexity a bit, and it may not be your situation here.

If you need Sync you can simplify it with a simple Mutex, but it also slows it down a lot.

5

It is in an async task that pulls out commands from a queue channel and then uses tokio::task::spawn_blocking() moving the connection in to run the database queries, awaiting the join handle and then reassigning the conn variable for the next query

6

It looks like you are calling a method of conn, and trying to pass to this method a closure inside of which you are trying to move the connection.

So rustc is telling you that it cannot move the connection to inside the closure, because the connection is also needed outside of the closure at the same time.

7

I am not calling any methods on conn outside of the spawn_blocking calls. The conn is moved into them, and then returned to be reused. If you mean the closure-like syntax of unwrap!, it is just to make match cleaner (hence why return works)

#[macro_export]
macro_rules! unwrap {
  ($expr:expr, |$binding:pat_param| $action:expr $(,)?) => {
    match $expr {
      Ok(x) => x,
      Err($binding) => $action,
    }
  };

  ($expr:expr, $action:expr $(,)?) => {
    $crate::unwrap!($expr, |_| $action)
  };
}
8

You are right, my assumption was incorrect.

Did you try to make a more minimal example by expanding the macro, maybe leaving only one of the two calls to rusqlite, and using directly a statement instead of a closure ?

9

Inside the spawn_blocking, create another closure that borrows conn and returns the error or Ok(()).

    let check = |conn: &Connection| -> rusqlite::Result<()> {
        match conn.prepare_cached("SELECT EXISTS(SELECT 1 FROM users WHERE name = ?1);") {
            Ok(_) => {
                Ok(())
            }
            Err(e) => {
                return Err(e);
            }
        }
    };

Then I think you should be able to check that and return both conn and the error.
It still feels sketchy. A connection pool might be a good idea.

10

But then how do I get access to the statement?

11

You run it like a function right after you declare it let res = check(&conn);

12

Yes but I need the value of the Ok branch of the prepare_cached call so that I can execute the statement

13

It's the best I can come up with, but you won't need to return the stmt if you query in one command:

    let check = |conn: &Connection| -> rusqlite::Result<bool> {
        match conn.query_row(
            "SELECT EXISTS(SELECT 1 FROM users WHERE name = ?1);",
            [&"aaa"],
            |row| row.get::<_, bool>(0),
        ) {
            Ok(res) => Ok(res),
            Err(e) => {
                return Err(e);
            }
        }
    };
14

Ok so I found Share Connection into several threads · Issue #188 · rusqlite/rusqlite · GitHub and after seeing comment and comment got to tokio-rusqlite/src/lib.rs at aa06eb79eafe798971aec44d7d19f3f498228495 · programatik29/tokio-rusqlite · GitHub after which I came up with the following that works

use db::Command::*;
match c {
  UserExists { name, respond_to } => {
    let (conn2, res) = unwrap!(
      tokio::task::spawn_blocking(move || {
        let res = db_user_exists(&conn, &name);
        (conn, res)
      })
      .await,
      |err| {
        return;
      },
    );
    
    conn = conn2;
    
    // @todo
  }
  
  _ => {}
}

fn db_user_exists(conn: &Connection, name: &str) -> Result<bool, db::Error> {
  let mut stmt = conn
    .prepare_cached("SELECT EXISTS(SELECT 1 FROM users WHERE name = ?1);")
    .map_err(db::Error::Rusqlite)?;

  stmt
    .query_row([name], |row| row.get::<_, bool>(0))
    .map_err(db::Error::Rusqlite)
}

I actually am really happy with this because it isolates the logic of each command as well while also making the code much easier to read. This will do for now