Sqlx Acquire causes issue with axum handler when using transactions.

[ArbazIrshad]

Whenever I try to call a function using Acquire in my axum handler and I am try to use a transaction, the axum handler starts giving me error. But if I pass a PgPool to it my handler works fine. Is there anyway to fix this? The axum handler error is not helpful and using the debug_handler macro also doesn't help in anyway.
Should I not use Acquire and Executor and only use &mut PgConnection?
Does anyone knows on how to fix this issue?

pub async fn create_docs<'c, A>(
executor: A,
) -> Result<> 
where
    A: sqlx::Acquire<'c, Database = sqlx::Postgres> + Send {
}

[sueun-dev]

The trait error comes from Acquire not being an Executor. A &PgPool happens to impl both, so the pool version compiles, but a generic A: Acquire can't be handed straight to .execute(), which is exactly why it falls apart the moment you pass a transaction.

Grab a connection inside the fn and run against that:

async fn create_docs<'c, A>(conn: A) -> Result<(), sqlx::Error>
where
    A: Acquire<'c, Database = Postgres> + Send,
{
    let mut conn = conn.acquire().await?;
    sqlx::query("...").execute(&mut *conn).await?;
    Ok(())
}

Then call create_docs(&pool) or create_docs(&mut tx). I checked this on sqlx 0.8.6 and it compiles for pool, PoolConnection, and Transaction, and the future stays Send so axum is happy. If you never need a nested tx, taking impl Executor and passing &mut *tx at the call site works too.

[ArbazIrshad]

@sueun-dev I think you may have misunderstood my issue. I understand the difference between an executor and acquire.

The issue that i am facing is that an axum handler is calling the above function, when I pass a transaction to create_doc in the axum handler, it starts giving me compiler error. But the error is removed if I pass a pool to the function.

Currently, I have decided to switch to &mut PgConnection because my task was getting delayed and I didn't want to spend more time on this issues. But I would like to find a solution to this problem

[ArbazIrshad]

fn run_query<'a, 'c, A>(conn: A) -> impl Future<Output = Result<(), BoxDynError>> + Send + 'a
where
    A: Acquire<'c, Database = Postgres> + Send + 'a,
{
    async move {
        let mut conn = conn.acquire().await?;

        sqlx::query!("SELECT 1 as v").fetch_one(&mut *conn).await?;
        sqlx::query!("SELECT 2 as v").fetch_one(&mut *conn).await?;

        Ok(())
    }
}

This was the solution. So, I have just decided to continue with &mut PgConnection. Hopefully, the bug is fixed with the diagnostics soon. So, we don't have to use this solution.

[NuclEnergy]

You can do this:

async fn do_something_in_db(conn: &mut PgConnection) -> Result<(), sqlx::Error> {
    query.execute(&mut *conn).await?;
    query2.execute(&mut *conn).await?;

    Ok(())
}

// From pool
let conn = &mut pool.acquire().await?;
do_something_in_db(conn).await?;

// From transaction
let mut tx = pool.begin().await?;
do_something_in_db(&mut *tx).await?;
tx.commit().await?;