Handle a function that can be called within a transaction or not

[frederikhors]

If a function can be called with a transaction or a direct connection (and so it should start a new transaction from there because it needs one) what do you suggest to use?

Example code:

async fn find_player_id_and_assign_goal(
    &self,
    db: Option<&mut sqlx::PgConnection>,
    id: &str,
    team_id: &str,
) -> Result<String, Error> {
    let mut tx = None;

    let db = match db {
        Some(db) => {
            // I need to detect if `db` here is already a transaction or not, in which case I should start a new one:
            db
        },
        None => &mut *tx.insert(self.pool.begin().await?),
    };

    let _ = self.team_service.find_team_id(Some(db), team_id).await?;

    let player_id = self.repo.find_player_id(Some(db), id).await?;

    self.goal_service.assign_to_player(Some(db), id).await?;

    if let Some(tx) = tx {
        tx.commit().await?;
    }

    Ok(player_id)
}

This can work, but I only have one issue:

If Some(db) => db I need to detect there if we're already in a transaction (if db is one). If not I should start a new one.

Do you think is there a better way to accomplish this?

[CommanderStorm]

(you forgot to ask a concrete question. assuming you want feedback on the code provided)

Why do you have two sources for your pgconnection? (pool vs db)
=> I would suggest you simplify this to one source

Also lets drop the find_team_id/team_id as the result is unused.

async fn find_player_id_and_assign_goal(id: &str) -> anyhowResult<String> {
    let mut tx = self.pool.begin().await?;
    
    let player_id = self.repo.find_player_id(tx, id).await?;
    self.goal_service.assign_to_player(tx, id).await?;
    
    tx.commit().await?;
    Ok(player_id)
}

Then, consider if you can do this in one sql query (for this one, you don't need a transaction, repository or service.. the code provied sounds like OOP-overengineering)

async fn assign_goal_to_player(&self, different_id: &str) -> anyhow::Result<String> {
    Ok(sqlx::query_scalar!(
        r#"INSERT INTO goals(player_id)
        SELECT id FROM players
        WHERE different_id=$1
        RETURNING player_id"#,
        different_id
    )
    .fetch_one(&self.pool)
    .await?)
}