What do you think about this code for functions that take both pool and transaction?

[frederikhors]

I'm trying sqlx for the first time these days, sorry if this is a dumb question.

I'm writing a function that I want to take both PgPool and Transaction<Postgres>.

I'm using the below (SIMPLIFIED) code:

#[derive(Debug, Default, sqlx::FromRow)]
pub struct PgPlayer {
    pub id: String,
    pub created_at: time::OffsetDateTime,
    pub updated_at: Option<time::OffsetDateTime>,
    pub name: String,
    pub team: Option<String>,
}

async fn player_delete(&self, id: &str) -> Result<()> {
  let mut tx = self.pool.begin().await?;

  let player_in_db = query_by_id(&mut tx, id).await?;

  // Do something with player_in_db here...

  delete(&mut tx, id).await?;

  tx.commit().await?;

  Ok(())
}

pub fn query_by_id<'a>(
  db: impl sqlx::PgExecutor<'a> + 'a,
  id: &'a str,
) -> Pin<Box<dyn Future<Output = Result<PgPlayer>> + Send + 'a>> {
  Box::pin(async move {
      let player = sqlx::query_as::<_, PgPlayer>(
          r#"SELECT * FROM players WHERE id = $1"#,
      )
      .bind(id)
      .fetch_one(db)
      .await?;

      Ok(player)
  })
}

pub async fn delete<'a>(
  db: impl sqlx::PgExecutor<'a>,
  id: &str,
) -> Result<()> {
  sqlx::query(r#"DELETE FROM players WHERE id = $1"#)
      .bind(id)
      .execute(db)
      .await?;

  Ok(())
}

Is this good enough or is there a better way to write it (maybe avoiding the lifetimes in impl sqlx::PgExecutor<'a>)?

[mrjackwills]

I was looking to achieve a similar result, and found this discussion, which, as long as you are using 0.6+, says that you can do something like this;

   pub async fn insert(postgres: impl PgExecutor<'_>, email: &str) -> Result<Self, ApiError> {
       // actual query here etc
}

which will enable you to use this insert function with either PgPool or a transaction, at least so far that's what I have been able to achieve

[frederikhors]

And so? The same one I'm using? What do you mean?

[mrjackwills]

Sorry yeah, I completely misread this