raw_sql api doesn't seem to work within axum handler

[manswami23]

Bug Description

Hello,

I am setting up an axum web server with a simple route handler. The handler acquires a connection from the connection pool, and then runs some arbitrary statement. Originally, I was using the sqlx::query_as api, and everything was fine. However, out of curiosity, I tried using sqlx::raw_sql, and my code fails to compile with the following error message:

implementation of `sqlx::Executor` is not general enough
`sqlx::Executor<'0>` would have to be implemented for the type `&mut sqlx::PgConnection`, for any lifetime `'0`...
...but `sqlx::Executor<'1>` is actually implemented for the type `&'1 mut sqlx::PgConnection`, for some specific lifetime `'1`

To me, it looks like both the query_as and raw_sql apis use the Executor trait under the hood. So I'm curious as to why the former can successfully resolve the trait implementation for the PgConnection reference while the latter fails.

Minimal Reproduction

use axum::extract::{Path, Query, State};
use axum::response::{Html, IntoResponse};
use axum::routing::{delete, get, get_service, post, put};
use axum::{handler, Extension, Json, Router};
use core::num;
use serde::Deserialize;
use serde_json::json;
use sqlx::pool::PoolConnection;
use sqlx::postgres::PgPoolOptions;
use sqlx::{Executor, Pool, Postgres};
use std::collections::HashMap;
use std::net::SocketAddr;
use std::sync::Arc;

pub struct AppState {
    pub db: Pool<Postgres>,
}

#[tokio::main]
async fn main() {
    // 1) Create a connection pool
    let poolResult = PgPoolOptions::new()
        .max_connections(5)
        .connect("postgres://postgres:postgres@db:5432/mydb")
        .await;

    if (poolResult.is_ok()) {
        let pool = poolResult.unwrap();
        let routes_all = Router::new()
            .merge(routes_hello())
            .with_state(Arc::new(AppState { db: pool }));

        let addr = SocketAddr::from(([127, 0, 0, 1], 9080));

        println!("listening on {:?}", addr);
        axum::Server::bind(&addr)
            .serve(routes_all.into_make_service())
            .await
            .unwrap();
    } else {
        println!("Failed to connect to db: {:?}", poolResult.err());
    }
}

fn routes_hello() -> Router<Arc<AppState>> {
    Router::new().route("/hello", get(handler_test))
}

async fn handler_test(State(state): State<Arc<AppState>>) -> impl IntoResponse {
    testConnection(state).await;
    Html("text response")
}

async fn testConnection(state: Arc<AppState>) -> () {
    let conn_result = state.db.acquire().await;
    if conn_result.is_ok() {
        let res1 = {
            let mut conn = conn_result.unwrap();
            let raw_query = "SELECT * FROM my_table WHERE id = 1"; // Ensure your table and field exist
            let res: Result<sqlx::postgres::PgQueryResult, sqlx::Error> = sqlx::raw_sql(raw_query)
                .execute(&mut *conn) // Dereference the connection to get a mutable reference
                .await;
        };
    }
}

Interestingly, when I invoke raw_sql outside of the handler (e.g. I run it right after creating the connection pool in the main method), there are no issues. So I'm wondering if the reference through the Arc is somehow messing up the lifetime of the PgConnection?

Info

[dependencies]
tokio = { version = "1", features = ["full"] }
async-trait = "0.1"
axum = "0.6"
# -- Serde
serde = { version = "1", features = ["derive"] }
serde_json = "1"
serde_with = "3"

tower-http = { version = "0.4", features = ["fs"] }

sqlx = { version = "0.7", features = ["postgres", "runtime-tokio-native-tls"] }

Database: Using Postgres, v17.
OS: Windows 10
Rust version: rustc 1.81.0 (eeb90cda1 2024-09-04)

[manswami23]

Comparing the signature of the sqlx::raw_sql vs sqlx::query execute methods (where the latter compiles) I did notice one thing.

sqlx::raw_sql execute

 pub async fn execute<'e, E>(
        self,
        executor: E,
    ) -> crate::Result<<E::Database as Database>::QueryResult>
    where
        'q: 'e,
        E: Executor<'e>,
    {
        executor.execute(self).await
    }

sqlx::query execute

 #[inline]
    pub async fn execute<'e, 'c: 'e, E>(self, executor: E) -> Result<DB::QueryResult, Error>
    where
        'q: 'e,
        A: 'e,
        E: Executor<'c, Database = DB>,
    {
        executor.execute(self).await
    }

I'm still new to Rust, but it seems like the Executor's lifetime in query ('c) is bounded by 'e (what is 'e referring to?). But raw_sql has no such bound. I wonder if the extra bound on the query method somehow makes the lifetime "resolvable" by the compiler, because it seems like the issue with raw_sql is that the compiler for whatever reason doesn't know the lifetime of the connection reference, and so is asking for an Executor trait impl that can apply to any lifetime rather than a specific lifetime.

[joeydewaal]

You can change this line

let res: Result<sqlx::postgres::PgQueryResult, sqlx::Error> = sqlx::raw_sql(raw_query)
    .execute(&mut *conn) // Dereference the connection to get a mutable reference
    .await;

to

 let res: Result<sqlx::postgres::PgQueryResult, sqlx::Error> =
    conn.execute(sqlx::raw_sql(raw_query)).await;

then it should compile

[manswami23]

Thanks @joeydewaal , that worked. I am curious as to why that worked, since it looks both routes end up invoking the Executor trait's execute function.

Why was the latter able to resolve the Executor trait impl on the PgConnection, but the former approach was unable to?

[manswami23]

And along the same lines, why is the following able to compile?

let res: Result<sqlx::postgres::PgQueryResult, sqlx::Error> = sqlx::query(raw_query)
    .execute(&mut *conn) // Dereference the connection to get a mutable reference
    .await;