Create and reuse database connections between routes

Question

A bit about my app, I have multiple databases (SQLite, PostgreSQL, ...) and I want to create an interactive UI (a simple text box) for users to run raw SQL queries only if they select and start querying a database (create/ reuse a DB connection)

My idea is I want a route to initiate a database connection and another route to check if the connection exists and use it.

The ro_db connection was created successfully but still is None in async fn get_db_connection. Is there any way to solve it or any better approach?

use anyhow::Context;
use axum::extract::{Path, State};
use sqlx::sqlite::SqlitePool;

#[derive(Clone)]
pub(crate) struct ApiContext {
    ro_db: Option<SqlitePool>, // Read-only connection use for UI interaction
}

pub(crate) fn router() -> Router<ApiContext> {
    Router::new()
        .route(
            "/api/db_conns/:conn_id",
            get(get_db_connection),
        )
        .route(
            "/api/db_conns",
            post(create_db_connection),
        )
}

async fn create_db_connection(
    mut ctx: State<ApiContext>,
    Path(download_id): Path<i64>,
) -> Result<()> {
    let ro_db = SqlitePool::connect("sqlite:data.db?mode=ro")
        .await
        .context("could not connect to database_url")?;

    ctx.ro_db = Some(ro_db);

    Ok(())
}

async fn get_db_connection(ctx: State<ApiContext>, Path(conn_id): Path<i64>) -> Result<()> {
    match &ctx.ro_db {
        Some(ro_db) => {
            let _ = sqlx::query!("SELECT * FROM table").execute(ro_db).await?;
        }
        None => {
            // ro_db still is None here
        }
    }

    Ok(())
}

Answer 1

My idea is I want a route to initiate a database connection and another route to check if the connection exists and use it.

That's not what your code is doing, it's a bad idea to have endpoints implicitly depend on one another that way, and it's completely unnecessary anyway:

• per the documentation State is a per-router global, your handler gets a Clone of the state, in order to modify the global state you need some sort of inner mutation e.g. a Mutex
• but that's useless here because you're creating a connection pool not a connection, the entire purpose of connection pools is to internally store and reuse connections, you don't need to create connections by hand, just ask the pool for a connection and you'll get one
• you're supposed to configure the pool to specify how connections are managed e.g. if the pool should "warm up" (min connections), how high the cap is (max connections), and how long connections can remain open

So just initialise the state as a properly configured SqlitePool in the first place.