Sqlx: Add or document ability to run SQL agnostic of whether I'm using a Connection or a Transaction

[jsdw]

I'd like to be able to write a function that executes some SQL, but have it be agnostic over whether the thing it's using to execute said SQL is a single Connection or a Transaction, so that logic higher up the stack can decide whether it wants to wrap such calls in a transaction or not.

The code I have written to achieve this looks like the following:

pub struct User {
    name: String
}

impl User {
    pub async fn from_db_by_email<E: sqlx::Executor<Database = sqlx::Postgres>>(email_address: &str, conn: &E) -> Option<User>
    {
        let user = sqlx::query_as!(
            User,
            "select name from (select 'Foo' as name, '[email protected]' as email) lark where email = $1",
            email_address
        ).fetch_one(conn).await.ok()?;

        Some(user)
    }
}

async fn foo() -> Result<(), sqlx::Error> {

    let client = statscloud_db::ClientBuilder::new().connect_with_password("foo").await?;
    let conn = client.acquire().await?;

    // Here I can decide whether to pass a Transaction or not (that's the hope, anyway):
    User::from_db_by_email("[email protected]", &conn);

    Ok(())
}

However, this hits an overflow error:

error[E0275]: overflow evaluating the requirement `&mut _: sqlx_core::executor::RefExecutor<'_>`
  |
  = help: consider adding a `#![recursion_limit="256"]` attribute to your crate
  = note: required because of the requirements on the impl of `sqlx_core::executor::RefExecutor<'_>` for `&mut sqlx_core::pool::connection::PoolConnection<_>`
  = note: required because of the requirements on the impl of `sqlx_core::executor::RefExecutor<'_>` for `&mut sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<_>>`
  = note: required because of the requirements on the impl of `sqlx_core::executor::RefExecutor<'_>` for `&mut sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<_>>>`
  = note: required because of the requirements on the impl of `sqlx_core::executor::RefExecutor<'_>` for `&mut sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<_>>>>`
  = note: required because of the requirements on the impl of `sqlx_core::executor::RefExecutor<'_>` for `&mut sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<_>>>>>`
  = note: required because of the requirements on the impl of `sqlx_core::executor::RefExecutor<'_>` for `&mut sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<_>>>>>>`
  = note: required because of the requirements on the impl of `sqlx_core::executor::RefExecutor<'_>` for `&mut sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<_>>>>>>>`
  = note: required because of the requirements on the impl of `sqlx_core::executor::RefExecutor<'_>` for `&mut sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<sqlx_core::pool::connection::PoolConnection<_>>>>>>>>`

Trimming the sqlx::query_as! line so that it looks like the following seems to avoid the overflow issue:

sqlx::query_as!(
    User,
    "select name from (select 'Foo' as name, '[email protected]' as email) lark where email = $1",
    email_address
);

Adding back the fetch_one so that it becomes the following causes the overflow to occur again:

sqlx::query_as!(
    User,
    "select name from (select 'Foo' as name, '[email protected]' as email) lark where email = $1",
    email_address
).fetch_one(conn);

So I guess the issue is around the type being handed to fetch_one.

Is this a bug, or am I holding it wrong?

[jsdw]

Digging into the code more, I believe that fetch_one needs a RefExecutor, not an executor.

The problem with using RefExecutor to try an abstract over Connection vs Transaction is that it is implemented for refs (often mutable refs) of the various Executor types, but inside the function I have an opaque object that I can only use by moving into the fetch_one call, preventing me from doing more than one query inside a function.

In other words, this works:

pub async fn from_db_by_email2<'e, E: sqlx::executor::RefExecutor<'e, Database = sqlx::Postgres>>(email_address: &str, conn: E) -> Option<User>{

    let user = sqlx::query_as!(
        User,
        "select * from sc_users.users where email = $1",
        email_address
    ).fetch_one(conn).await.ok()?;

    Some(user)
}

async fn run_some_queries() -> Result<(), sqlx::Error> {

    use sqlx::prelude::*;

    // Either of these should be passable to the function below ideally:
    let mut client = sqlx::PgConnection::connect("some/url").await?;
    // let mut tr = client.begin().await?;

    from_db_by_email2("[email protected]", &mut client).await;

    Ok(())
}

But I can't find a way to be able to run more than one query inside the from_db_by_email function, and so this (and various combinations involving &mut etc) does not work:

pub async fn from_db_by_email2<'e, E: sqlx::executor::RefExecutor<'e, Database = sqlx::Postgres>>(email_address: &str, conn: E) -> Option<User>{

    let user = sqlx::query_as!(
        User,
        "select * from sc_users.users where email = $1",
        email_address
    ).fetch_one(conn).await.ok()?;

    let user2 = sqlx::query_as!(
        User,
        "select * from sc_users.users where email = $1",
        email_address
    ).fetch_one(conn).await.ok()?; // <-- whoops, using moved 'conn'.. but how to avoid??

    Some(user)
}

I wonder whether implementing RefExecutor on all types that are &mut RefExecutor might help, as then I could take a mutable reference to conn in the above function and pass that to each fetch_one query?

Is there another, more practical way to abstract over Connections and Transactions in this way?

[mehcode]

Busy weekend so I can't give a longer answer right now but you can write methods that accept &mut PgConnection and then if you want to pass a transaction you can do &mut *tx when invoking the method as Transaction dereferences to the connection.

Hopefully that helps. It's not possible to sanely abstract over a generic executor without weird type bounds due to some limitations in Rusts type resolution right now (waiting on lazy normalization).

[jsdw]

Oooh, thankyou, that works great! In fact, I just pass &mut Transaction to the function and it seems to deref automatically to &mut PgConnection as well :)