How to build and commit multi-query transaction in sqlx?

Question

In sqlx there's a Transaction type which lets you run multiple queries in one transaction.

I'm trying to figure out how to do this, which sadly is not documented, although there is the automatically generated API docs.

My first attempt:

async fn insert_user() {
    let pool: sqlx::Pool<sqlx::MySql> =
        futures::executor::block_on(crate::db::open_mariadb_pool()).unwrap();
    use sqlx::Acquire;
    let mut conn = pool.acquire().await.unwrap();
    let tx = conn.begin().await.unwrap();
    let insert_query = sqlx::query("INSERT INTO user (email, email_verification_secret, email_verified, password_hash, hourly_rate)
VALUES (?, ?, ?, ?, ?);"
    )
        .bind("text@example.com")
        .bind(false)
        .bind(123)
        .bind("pwhash")
        .bind(20);
    let get_row_query = sqlx::query::<sqlx::MySql>("SELECT * FROM user WHERE id = LAST_INSERT_ID();");
    insert_query.execute(tx);
    get_row_query.execute(tx);
    tx.commit();
}

Produces the following error:

error[E0277]: the trait bound `Transaction<'_, MySql>: Executor<'_>` is not satisfied
  --> src/controller_user.rs:86:26
   |
86 |     insert_query.execute(tx);
   |                          ^^ the trait `Executor<'_>` is not implemented for `Transaction<'_, MySql>`
   |
   = help: the following implementations were found:
             <&'t mut Transaction<'c, MySql> as Executor<'t>>

error[E0277]: the trait bound `Transaction<'_, MySql>: Executor<'_>` is not satisfied
  --> src/controller_user.rs:87:27
   |
87 |     get_row_query.execute(tx);
   |                           ^^ the trait `Executor<'_>` is not implemented for `Transaction<'_, MySql>`
   |
   = help: the following implementations were found:
             <&'t mut Transaction<'c, MySql> as Executor<'t>>

I don't really know where to start thinking about this - but am failing to find out from the automatically generated API docs.

score 10 · Answer 1 · edited Dec 23 '20 at 16:47

There is an example usage in the test tests/mssql/mssql.rs in the function it_can_work_with_transactions.

The usage appears to be:

let mut tx = conn.begin().await?;

sqlx::query("INSERT INTO _sqlx_users_1922 (id) VALUES (@p1)")
    .bind(10_i32)
    .execute(&mut tx)
    .await?;

tx.commit().await?;

The only obvious difference between what you are doing and this code is passing a mutable reference, rather than the value as the argument to execute.

With that in mind, if we look carefully at your error message, that is exactly what it says. After replacing some bits with ... to leave only the essentials it reads

... the trait `Executor<...>` is not implemented for `Transaction<...>`
   |
... the following implementations were found:
             &mut Transaction<...> as Executor<...>

How to build and commit multi-query transaction in sqlx?

1 Answers1