Recipe

Group statements into a sqlx transaction

Use when: two or more statements must all take effect or none of them may, such as moving credits between rows or recording a loan and charging for it

Goal: either every change is committed together, or the database is left exactly as it was

Taught in Transactions

1. 1

   let mut tx = pool.begin().await?; at the start of the function.

   Example

   let mut tx = db.begin().await?; in gift.

   Why: every statement of the group must run on one connection, inside one BEGIN.

2. 2

   Pass &mut *tx as the executor, and finish each with ?. A statement run on &pool instead commits on its own, outside the group.

   Example

   sqlx::query("UPDATE members SET credits = credits + ? WHERE id = ?").bind(amount).bind(to).execute(&mut *tx).await?;

   Why: ? returns early on any failure, and the dropped transaction rolls everything back.

3. 3

   tx.commit().await?; just before returning Ok. An early return because a check failed needs nothing special: the drop rolls back.

   Why: nothing is permanent until commit, and a transaction dropped without it is rolled back.