Axum pgconnection through shared state

Question

I want to share the PgConnection through the app level state to all the routes and services trying to achieve dependency injection.

The problem I am facing is, sqlx requires mut reference to the connection. While axum uses Arc to provide immutable reference of the state across all the processes that it spins.

This forces me to use Arc<Mutex<PgConnection>> just so that I have mutable reference to satisfy sqlx

pub struct AppState {
    pub db_connection: Arc<Mutex<PgConnection>>,
    pub jwt: JWTSettings,
}

And this is how I use it in the route

pub async fn sign_up(
        State(state): State<Arc<AppState>>,
        Json(signup_payload): Json<SignupPayload>,
    ) -> Result<Json<LoginResponse>, StatusCode> {
 let mut connection = state.db_connection.lock().unwrap();
    sqlx::query!("select * from users where username=$1", signup_payload.username)
        .fetch_one(&mut*connection)
        .await
        .map_err(|err| {
            println!("ERROR: Unable to execute the query {:?}", err);
            return StatusCode::INTERNAL_SERVER_ERROR;
        });

Issue I don't want the route/thread to hold the connection lock otherwise there is no point of worker processes to run parallely.

Answer 1

You should not use a single connection, but a connection pool. This will allow an efficient reuse of connections.

sqlx provides support for pools:

pub struct AppState {
    pub db_pool: sqlx::PgPool,
    pub jwt: JWTSettings,
}

pub async fn sign_up(
    State(state): State<Arc<AppState>>,
    Json(signup_payload): Json<SignupPayload>,
) -> Result<Json<LoginResponse>, StatusCode> {
    let mut connection = state.db_pool.acquire().await.unwrap();
    sqlx::query!(
        "select * from users where username=$1",
        signup_payload.username
    )
    .fetch_one(&mut *connection)
    .await
    .map_err(|err| {
        println!("ERROR: Unable to execute the query {:?}", err);
        return StatusCode::INTERNAL_SERVER_ERROR;
    });
}

Answer 2

Generally speaking, whether in Rust or in another programming language, you should never keep a long-lived connection. This can break your application if, for instance, there is maintenance on the database.

Rather, you should keep a connection pool. This will reuse a connection if one is available, otherwise it will connect you.

If I understand correctly, you are using sqlx. It supports pools out of the box.