Using a connection retrieved from `Acquire` more than once not possible because of lifetimes

[johannescpk]

As per #1441

pub async fn query<'a, A>(conn: A) -> Result<(), sqlx::Error>
where
	A: Acquire<'a>,
	A::Connection: 'a,
	A::Connection: sqlx::Executor<'a, Database = Postgres>,
{
	let conn = conn.acquire().await?;

	sqlx::query!("SELECT 1 as v",).fetch_one(conn).await?;

	Ok(())
}

works fine.

However, if one would want to do more than one query with the acquired connection, it would require the conn to be &mut, which looks like this

pub async fn save<'a, A>(conn: A) -> Result<(), sqlx::Error>
where
	A: Acquire<'a>,
	A::Connection: 'a,
	&'a mut A::Connection: sqlx::Executor<'a, Database = Postgres>,
{
	let mut conn = conn.acquire().await?;

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

	Ok(())
}

But that fails with

error[E0597]: `conn` does not live long enough
    |
122 |     pub async fn save<'a, A>(conn: A) -> Result<(), sqlx::Error>
    |                       -- lifetime `'a` defined here
...
130 |         sqlx::query!("SELECT 1 as v",).fetch_one(&mut conn).await?;
    |         -----------------------------------------^^^^^^^^^-
    |         |                                        |
    |         |                                        borrowed value does not live long enough
    |         argument requires that `conn` is borrowed for `'a`
...
133 |     }
    |     - `conn` dropped here while still borrowed

Reason for that seems to be the lifetime on the executing methods, like fetch_one here. Any way around this or am I missing something?

[OskarPersson]

@johannescpk Were you able to solve this?

[mehcode]

https://github.com/launchbadge/sqlx/blob/master/sqlx-core/src/migrate/migrator.rs#L90

[OskarPersson]

@mehcode Not sure how I would apply this in the example given by @johannescpk.

Also, would this work with PgPool, PgConnection and transactions?

[OskarPersson]

With the following

use sqlx::{self, pool::PoolConnection, Executor, Pool, Postgres, Transaction};

pub type DB = Pool<Postgres>;
pub trait Queryer<'c>: Executor<'c, Database = sqlx::Postgres> {}

impl<'c> Queryer<'c> for &Pool<Postgres> {}
impl<'c> Queryer<'c> for &'c mut Transaction<'_, Postgres> {}
impl<'c> Queryer<'c> for &'c mut PoolConnection<Postgres> {}

I'm able to create a function that accepts &PgPool, &mut PoolConnection and &mut Transaction

pub async fn foo<'c, C: Queryer<'c>>(db: C, val: i32) -> Result<i32, sqlx::Error> {
    let foo = sqlx::query!("SELECT $1::int4 as foo FROM items", val)
        .fetch_one(db)
        .await
        .unwrap();
    Ok(foo.foo.unwrap())
}

However, like @johannescpk, I also am unable to execute multiple queries in the same function:

error[E0382]: use of moved value: `db`
  --> src/foo.rs:41:20
   |
34 | pub async fn foo<'c, C: Queryer<'c>>(db: C, val: i32) -> Result<i32, sqlx::Error> {
   |                                      -- move occurs because `db` has type `C`, which does not implement the `Copy` trait
35 |     let foo = sqlx::query!("SELECT $1::int4 as foo FROM items", val)
36 |         .fetch_one(db)
   |                    -- value moved here
...
41 |         .fetch_one(db)
   |                    ^^ value used here after move
   |
help: consider further restricting this bound
   |
34 | pub async fn foo<'c, C: Queryer<'c> + Copy>(db: C, val: i32) -> Result<i32, sqlx::Error> {

@mehcode How do you suggest I proceed here?

[johannescpk]

@johannescpk Were you able to solve this?

Unfortunately not, had to resort to only accept Transactions as method argument.

@mehcode Sadly that doesn't help with multiple query!s, as @OskarPersson also pointed out.

[jplatte]

This is far from from obvious, but it is possible:

pub async fn save<'a, A>(conn: A) -> Result<(), sqlx::Error>
where
    A: Acquire<'a, Database = Postgres>,
{
    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(())
}

conn has to be dereferenced because Acquire::Connection is only guaranteed to implement Deref to a "usable" connection, not "be one" (implement Executor) itself.

[johannescpk]

Thanks @jplatte, that actually solves it.

[kvzn]

This is far from from obvious, but it is possible:

pub async fn save<'a, A>(conn: A) -> Result<(), sqlx::Error>
where
    A: Acquire<'a, Database = Postgres>,
{
    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(())
}

conn has to be dereferenced because Acquire::Connection is only guaranteed to implement Deref to a "usable" connection, not "be one" (implement Executor) itself.

With this method, can I just pass in Pool when transaction is not needed, and pass in Transaction when transaction is needed?

[yuyayang02]

Is there a solution to this problem now?