How to use DB connections in Actix several times per function?

Question

At https://actix.rs/docs/databases/ there is an example:

async fn index(pool: web::Data<DbPool>, name: web::Path<(String)>) -> impl Responder {
    let name = name.into_inner();

    let conn = pool.get().expect("couldn't get db connection from pool");

    let user = web::block(move || actions::insert_new_user(&conn, &user))
        .await
        .map_err(|e| {
            eprintln!("{}", e);
            HttpResponse::InternalServerError().finish()
        })?;
    
    Ok(HttpResponse::Ok().json(user))
}

What to do if I need several SQL queries in one function? The following obviously won't work due to moving conn:

async fn index(pool: web::Data<DbPool>, name: web::Path<(String)>) -> impl Responder {
    let name = name.into_inner();

    let conn = pool.get().expect("couldn't get db connection from pool");

    let user1 = web::block(move || actions::insert_new_user(&conn, &user))
        .await
        .map_err(|e| {
            eprintln!("{}", e);
            HttpResponse::InternalServerError().finish()
        })?;

    // ... asynchronous code here

    let user2 = web::block(move || actions::insert_new_user(&conn, &user))
        .await
        .map_err(|e| {
            eprintln!("{}", e);
            HttpResponse::InternalServerError().finish()
        })?;
    
    Ok(HttpResponse::Ok().json(user))
}

I can receive several connections by repeated calls of pool.get(), but that inefficient to open a new SQL connection when one connection is enough.

So, how to deal with this?

Simply remove `move`? Why then in the official example it uses `move`? — porton, Jan 23 '22 at 15:45
Removal of `move` leads to `NonNull cannot be shared between threads safely within PooledConnection>, the trait Sync is not implemented for NonNull`. So, it seems the only solution is to obtain a new connection every time :-( — porton, Jan 23 '22 at 15:53

Netwave · Answer 1 · 2022-01-23T16:31:51.990

You just need to move the reference, not the connection itself:

async fn index(pool: web::Data<DbPool>, name: web::Path<(String)>) -> impl Responder {
    let name = name.into_inner();

    let conn_ = pool.get().expect("couldn't get db connection from pool");
    let conn = &conn_;
    let user1 = web::block(move || actions::insert_new_user(&conn, &user))
        .await
        .map_err(|e| {
            eprintln!("{}", e);
            HttpResponse::InternalServerError().finish()
        })?;

    // ... asynchronous code here

    let user2 = web::block(move || actions::insert_new_user(&conn, &user))
        .await
        .map_err(|e| {
            eprintln!("{}", e);
            HttpResponse::InternalServerError().finish()
        })?;
    
    Ok(HttpResponse::Ok().json(user))
}

Your problem can be reduced to (not working version):


struct Conn {}

fn foo(_: &Conn) {}


fn main() {

    let conn_ = Conn {};
    
    (move || foo(&conn_))();
    (move || foo(&conn_))();
    
}

Playground

Working version:

struct Conn {}

fn foo(_: &Conn) {}


fn main() {

    let conn_ = Conn {};
    let conn = &conn_;
    (move || foo(conn))();
    (move || foo(conn))();
    
}

Playground

See my answer with explanation that your answer is wrong (please don't delete, just comment). — porton, Jan 23 '22 at 17:09
Following your solution, I nevertheless get an error. See my new question https://stackoverflow.com/q/70825457/856090 — porton, Jan 23 '22 at 18:51

How to use DB connections in Actix several times per function?

1 Answers1