Generic function that accepts a SQLx PgPool or MySqlPool

Question

I'd like to setup a generic function that accepts a SQLx PgPool or MySqlPool.

use dotenv::dotenv;
use sqlx::postgres::PgPool;
use sqlx::{Pool, Database};
use std::env;

#[derive(Debug)]
struct Todo {
    id: i64,
    description: String,
    done: bool,
}

#[actix_web::main]
async fn main() -> anyhow::Result<()> {
    dotenv().ok();
    let pool = PgPool::connect(&env::var("DATABASE_URL")?).await?;
    list_todos(&pool).await?;
    Ok(())
}



async fn list_todos<D: Database>(pool: &Pool<D>) -> anyhow::Result<Vec<Todo>> {
    let todos = sqlx::query_as!(
        Todo,
        r#"
        SELECT id, description, done
        FROM todos
        ORDER BY id
        "#
    )
    .fetch_all(pool)
    .await?;

    Ok(todos)
}

The error I see is:

32 |     .fetch_all(pool)
   |      ^^^^^^^^^ expected type parameter `D`, found struct `Postgres`
   |
   = note: expected type parameter `D`
                      found struct `Postgres`

error[E0277]: the trait bound `for<'c> &'c mut <D as sqlx::Database>::Connection: Executor<'c>` is not satisfied

Any hints on how to setup the function to accept a PgPool or MySqlPool param? Thanks

Answer 1

The query macro docs state that:

The QueryAs instance will be bound to the same database type as query!() was compiled against (e.g. you cannot build against a Postgres database and then run the query against a MySQL database).

However, the function does not have this limitation. Knowing this we can build some functions that are generic over any executor type of any database type.

---

When you're trying to be generic over any backend in sqlx you need to be generic over the database (sqlite, mysql, postgresql, etc), the executor (PgPool, SqlitePool, a raw Connection, a Transaction, etc), any encoded (parameters/bind variables), and decoded (query_as return types) values.

For example, creating a table:

pub async fn create_products_table<'a, DB, E>(e: E) -> Result<(), Error>
where
    DB: Database,
    <DB as HasArguments<'a>>::Arguments: IntoArguments<'a, DB>,
    E: Executor<'a, Database = DB>,
{
    sqlx::query(include_str!(
        "../sql/create_products_table.sql"
    ))
    .execute(e)
    .await?;
    Ok(())
}

Selecting items from a table:

pub struct Product {
    name: String,
    other: String,
}

impl<'r, R> FromRow<'r, R> for Product
where
    R: Row,
    //Here we have bounds for index by str
    for<'c> &'c str: ColumnIndex<R>,
    //We need this bound because `Product` contains `String`
    for<'c> String: Decode<'c, R::Database> + Type<R::Database>,
{
    fn from_row(row: &'r R) -> Result<Self, Error> {
        //Here we index by str
        let name = row.try_get("name")?;
        //Here we index by str
        let other = row.try_get("other")?;
        Ok(Self { name, other })
    }
}

pub async fn select_products<'a, 'b, DB, E>(
    e: E,
    name: &'a str,
) -> impl Stream<Item = Result<Product, Error>> + 'b
where
    'a: 'b,
    DB: Database,
    <DB as HasArguments<'a>>::Arguments: IntoArguments<'a, DB>,
    for<'c> E: 'a + Executor<'c, Database = DB>,

    //We need this bound because `Product` contains `String`
    for<'c> String: Decode<'c, DB> + Type<DB>,

    //We need this bound because 'name' function argument is of type `&str`
    for<'c> &'c str: Encode<'c, DB> + Type<DB>,

    //We need this bound for `FromRow` implementation or if we intend on indexing rows by str.
    //You will probably need to write your own `FromRow` implementation.
    for<'c> &'c str: ColumnIndex<<DB as Database>::Row>,
{
    query_as(include_str!("../sql/select_products.sql"))
        .bind(name)
        .fetch(e)
}

I would love for this to be more streamlined or documented as it seems ripe for macros. Perhaps I will draft some docs for a PR to sqlx. Let me know if you need more examples.

---

I am also aware of the Any database driver, however it has the distinct disadvantage of requiring an opaque type with no trivial way of converting from concrete to opaque types. This means you have to use AnyPool everywhere you'd be generic over a database and can never use a concrete PgPool or SqlitePool. You also must solely rely on connection strings to differentiate database implementations (yuck)

Answer 2

Looking at the proc macro expansion for the query! macro, it looks like they fill in the specific type for the underlying DB based on the connection string in the .env file. Thus, the macro is generating specialized code inside the function for the Postgres implementation but your function is generic over different Database implementors, i.e. expecting Pool<Db> where Db: Database.

cargo expand output with some reformatting:

    use ::sqlx::Arguments as _;
    let query_args =
        <sqlx::postgres::Postgres as ::sqlx::database::HasArguments>::Arguments::default();
    let todos = ::sqlx::query_with::<sqlx::postgres::Postgres, _>(
        "\n        SELECT id, description, done\n        FROM todos\n        ORDER BY id\n        ",
        query_args,
    )
    .try_map(|row: sqlx::postgres::PgRow| {
        use ::sqlx::Row as _;
        let sqlx_query_as_id = row.try_get_unchecked::<i32, _>(0usize)?;
        let sqlx_query_as_description = row.try_get_unchecked::<String, _>(1usize)?;
        let sqlx_query_as_done = row.try_get_unchecked::<bool, _>(2usize)?;
        Ok(Todo {
            id: sqlx_query_as_id,
            description: sqlx_query_as_description,
            done: sqlx_query_as_done,
        })
    })
    .fetch_all(pool)
    .await?;
    Ok(todos)

Even if you follow @Netwave's suggestion to use a generic Executor, the macro will still produce the same non-generic code inside the function and refuse to compile. Although, there is the difference that @Netwave's answer uses the query function instead of the compile-time checked query! macro.

Using the Executor trait for dispatching the queries and dropping support for compile time checks allows to write something like the following:

async fn list_todos_unchecked<'e, Exe>(pool: Exe) -> anyhow::Result<()>
where
    Exe: Executor<'e>,
    <Exe::Database as HasArguments<'e>>::Arguments:
        IntoArguments<'e, <Exe as Executor<'e>>::Database>,
{
    let todos = sqlx::query(
        "\n        SELECT id, description, done\n        FROM todos\n        ORDER BY id\n        ",
    )
    .fetch_all(pool)
    .await?;
    Ok(())
}

sqlx also has support for the Any driver that supports proxying to any enabled DB driver at runtime. sqlx doesn't support macros based on the Any driver yet but it's tracked in this issue.

Answer 3

Couldn't test it properly, but you could leverage Executor directly as generics. From the documentation:

A type that contains or can provide a database connection to use for executing queries against the database.

No guarantees are provided that successive queries run on the same physical database connection.

A Connection is an Executor that guarantees that successive queries are ran on the same physical database connection.

Implemented for the following:

&Pool &mut PoolConnection &mut Connection

async fn list_todos<'e, Exe: Executor<'e>>(executor: Exe) -> anyhow::Result<()>
where
    <Exe::Database as HasArguments<'e>>::Arguments:
        IntoArguments<'e, <Exe as Executor<'e>>::Database>,
{
    let todos = sqlx::query(
        r#"
        SELECT id, description, done
        FROM todos
        ORDER BY id
        "#,
    )
    .execute(executor)
    .await?;

    Ok(())
}

Disclaimer: return types and other things changed a bit since I don't have an environment for testing it properly. But it compiles without problems for the Executor bindings. You would have to adapt whats left.