.acquire() and .await on a db pool (Axum, Sqlx, Sqlite)

[scott223]

Hi folks

Learning Rust here and having a blast. Ventured for the first time into a more complicated project where i want to create a small server that saves incoming POST REST requests into a SQL database. I choose Axum, SQLx and SQLite as a stack. I got it working, but i feel that i might be doing it wrong and im learning on async and db pools for the first time.

In the main function loop i create a SqlitePool, and pass it to the Axum state so its available in a handler. To create connection to the db to execute a query, i need to .acquire() and .await twice (once to get from a SqlitePool to a Poolconnection and once to a Connection that fulfills the Executor trait for .execute() the query). That feels cumbersom. is this the way to go? or am i missing something? Thanks!

full code here: planepilot/dataserver/src/main.rs at main · scott223/planepilot · GitHub

    // in the program main fn
    let db = SqlitePool::connect(DB_URL)
        .await
        .expect("can't connect to database");

    let app = Router::new()
        .route( //..
        .layer( // ...
        .with_state(db);

//in the handler

async fn add_data(
    State(pool): State<Pool<Sqlite>>,
    Json(payload): Json<AddData>,
) -> (StatusCode, Json<Data>) {
    // insert your application logic here
    let data = Data {
        value: payload.value,
        timestamp: payload.timestamp,
        channel: payload.channel,
    };

    let mut db = pool
        .acquire()
        .await
        .expect("cannot open new poolconnection");

    let _result = sqlx::query(
        "INSERT INTO datapoints (CreationDate, ChannelId, DataPointValue) VALUES (?, ?, ?)",
    )
    .bind(data.timestamp)
    .bind(data.channel)
    .bind(data.value)
    .execute(
        db.acquire()
            .await
            .expect("cannot open new single connection"),
    )
    .await
    .expect("error when executing query");

[SkiFire13]

You should be able to skip both .acquire().awaits and just pass &*pool to execute since &Pool<Sqlite> implements Execute.

[derspiny]

Rather than executing on the pool, I would be inclined to ask the pool for a transaction, and execute on that, instead. Both pools and transactions implement the required trait, but a transaction additionally ensures that all the queries executed against it complete or fail as a unit, subject to sqlite's transaction semantics.

That looks something akin to

let mut tx = pool.begin().await?;
let result = sqlx::query(…).bind(…)
    .execute(&mut tx)
    .await?;
tx.commit().await?; // or tx.rollback().await?;

[scott223]

Great, thank you!