Unable to abstract over Pool and Transaction

[Wopple]

I've been fighting with this for some time now, and I've only been able to manage partial solutions. I have not reached a full solution for what should logically be possible.

I understand there is a fundamental difference between a pool and a transaction. A pool can spawn multiple connections which would be reasonable to schedule as separate async blocks running in parallel. A transaction is a single connection which cannot support parallel execution. However, given the common pattern of async and then immediately await before the next async, it should be able possible to use a transaction the same as a pool in that scenario.

What I've found that works is:

pub async fn foo(conn: &mut PgConnection) -> Result<i32, sqlx::Error> {
    sqlx::query("SELECT 1 AS v")
        .map(|row: PgRow| row.get("v"))
        .fetch_one(conn)
        .await
}

let pool: PgPool;
let pool_conn = &mut *pool.acquire().await?;
let mut tx = pool.begin().await?;

foo(pool_conn).await();
foo(pool_conn).await();

foo(&mut tx).await();
foo(&mut tx).await();

Despite being able to call the foo function multiple times, the limitation here is the &mut PgConnection inside foo can only be used once. When you call fetch_one, it gets derefed and moved internally. There's an idea where you can abstract over Acquire<Connection = Connection<Postgres>> so you can pass in something that can acquire a connection, but pools implement Acquire for PoolConnection which is different from Connection. I have not found a way to smooth over that discrepancy.

[abonander]

Firstly, you can avoid the consumption of the &mut PgConnection if you reborrow:

pub async fn foo(conn: &mut PgConnection) -> Result<i32, sqlx::Error> {
    sqlx::query("<do something else>")
        .execute(&mut *conn)
        .await?;

    sqlx::query("SELECT 1 AS v")
        .map(|row: PgRow| row.get("v"))
        .fetch_one(&mut *conn)
        .await?
}

Secondly, if you use impl Acquire<Database = Postgres> (so don't constrict the Connection associated type) then you should be able to use either:

pub async fn foo(conn: impl Acquire<Database = Postgres>) -> Result<i32, sqlx::Error> {
    // no-op for `PgConnection`, `Transaction`
    let mut conn = conn.acquire().await?;

    sqlx::query("SELECT 1 AS v")
        .map(|row: PgRow| row.get("v"))
        .fetch_one(conn)
        .await
}

foo(&pool).await?;
foo(&mut tx).await?;

[johannescpk]

Seems like this no longer works? At least if fails for me with the error

error[E0277]: the trait bound <impl Acquire<'a, Database = Postgres> as sqlx::Acquire<'a>>::Connection: Executor<'_> is not satisfied

Workaround I used: #1635, but hit a different issue

[kvzn]

Any news in v0.6.0?

[Wopple]

I just marked @abonander 's reply as the answer. In particular, the re-borrowing with &mut *conn is a solution for the limitation I was experiencing.