Transactions with SQLx

23 min read

Database transactions let you group several statements into one atomic unit: either all of them take effect, or none of them do. SQLx models a transaction as a Rust value — a guard — whose lifetime is the transaction, so the borrow checker and Drop enforce correctness that you have to remember by hand in TypeScript.

Quick Overview

A transaction wraps a set of SQL statements so they commit together or roll back together — the database stays consistent even if your program panics, the connection drops, or a constraint is violated halfway through. In SQLx you call pool.begin().await to get a Transaction value, run queries through &mut *tx, and finish with tx.commit() or tx.rollback(). The crucial difference from a Node.js client: if you simply drop the Transaction without committing, SQLx rolls it back for you. There is no way to leak an open transaction by forgetting a finally block.

Note: This file assumes you have read SQLx Intro (setup, connecting, the pool) and Writing Queries (query, query_as, parameter binding). Here we focus only on transactions: begin/commit/rollback, the Transaction guard, atomicity, and savepoints.

TypeScript/JavaScript Example

With node-postgres (pg), a transaction means manually pulling a client out of the pool, issuing BEGIN/COMMIT/ROLLBACK as raw statements, and — critically — remembering to release() the client and to roll back on every error path:

1
import { Pool } from "pg";
2

3
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
4

5
async function transfer(from: number, to: number, amount: number): Promise<void> {
6
  // You must check out a single client; the whole transaction lives on it.
7
  const client = await pool.connect();
8
  try {
9
    await client.query("BEGIN");
10

11
    await client.query(
12
      "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
13
      [amount, from],
14
    );
15
    await client.query(
16
      "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
17
      [amount, to],
18
    );
19

20
    await client.query("COMMIT");
21
  } catch (err) {
22
    // If you forget this, the transaction stays open and holds a connection.
23
    await client.query("ROLLBACK");
24
    throw err;
25
  } finally {
26
    // If you forget this, the client never returns to the pool — a leak.
27
    client.release();
28
  }
29
}

The footguns are well known to anyone who has run Postgres in production:

Forget ROLLBACK in the catch and the connection sits in an aborted-transaction state.
Forget client.release() in the finally and you slowly exhaust the pool until every request hangs.
Accidentally run a query on pool instead of client and it executes outside the transaction — silently, with no error.

Higher-level tools paper over this. Prisma’s $transaction([...]) takes an array of operations; TypeORM has dataSource.transaction(async (manager) => { ... }). Both rely on you routing every call through the right object and on a callback that, if it throws, triggers a rollback. Nothing in the type system stops you from using the wrong handle.

Rust Equivalent

SQLx turns the transaction itself into a value. pool.begin() hands you a Transaction<'_, DB>; you run queries through &mut *tx; you finish with commit() or rollback(). There is no separate “check out the client / release the client” dance — the Transaction owns its connection and returns it to the pool when dropped.

1
use sqlx::sqlite::SqlitePoolOptions;
2
use sqlx::{Pool, Sqlite};
3

4
async fn transfer(
5
    pool: &Pool<Sqlite>,
6
    from: i64,
7
    to: i64,
8
    amount: i64,
9
) -> Result<(), sqlx::Error> {
10
    // `begin` checks out a connection and issues BEGIN. `tx` is the guard.
11
    let mut tx = pool.begin().await?;
12

13
    sqlx::query("UPDATE accounts SET balance = balance - ? WHERE id = ?")
14
        .bind(amount)
15
        .bind(from)
16
        .execute(&mut *tx) // run *through the transaction*, not the pool
17
        .await?;
18

19
    sqlx::query("UPDATE accounts SET balance = balance + ? WHERE id = ?")
20
        .bind(amount)
21
        .bind(to)
22
        .execute(&mut *tx)
23
        .await?;
24

25
    tx.commit().await?; // both UPDATEs become permanent together
26
    Ok(())
27
}
28

29
#[tokio::main]
30
async fn main() -> Result<(), sqlx::Error> {
31
    let pool = SqlitePoolOptions::new()
32
        .connect("sqlite::memory:")
33
        .await?;
34

35
    // (table setup omitted — see the Real-World Example for the full program)
36
    transfer(&pool, 1, 2, 30).await?;
37
    Ok(())
38
}

The dependencies for the examples in this file (SQLite keeps them runnable with no external server; the same code works against Postgres or MySQL by swapping the pool type and connection string):

1
[dependencies]
2
sqlx = { version = "0.8", features = ["runtime-tokio", "sqlite"] }
3
tokio = { version = "1", features = ["rt-multi-thread", "macros"] }

1
cargo add sqlx --features runtime-tokio,sqlite
2
cargo add tokio --features rt-multi-thread,macros

Note: The current stable toolchain is Rust 1.96.0 on the 2024 edition; cargo new selects it automatically. The examples were compiled and run with SQLx 0.8 and Tokio 1.

The two missing error paths from the TypeScript version — “forgot to roll back” and “forgot to release” — simply cannot happen here. We will see why next.

Detailed Explanation

`begin()` returns a guard, not a string command

In pg you send the word BEGIN. In SQLx you receive a value:

1
let mut tx = pool.begin().await?;

tx has type sqlx::Transaction<'_, Sqlite>. Three things happened in that one line:

A connection was checked out of the pool (or a new one opened if the pool had spare capacity).
BEGIN was sent on that connection.
Ownership of the connection-plus-open-transaction was moved into tx.

Because tx owns the connection, there is no separate handle to release. The connection’s fate is tied to tx’s fate.

`&mut *tx` — running a query “through” the transaction

Every executor method (execute, fetch_one, fetch_all, fetch_optional) accepts something that implements the Executor trait. A &Pool implements it (each call grabs a connection from the pool). A &mut Transaction does not directly, but a &mut <Connection> does — and *tx derefs the transaction to its underlying connection, so &mut *tx is “a mutable reference to this transaction’s connection”:

1
sqlx::query("UPDATE accounts SET balance = balance - ? WHERE id = ?")
2
    .bind(amount)
3
    .bind(from)
4
    .execute(&mut *tx) // <- the transaction's connection
5
    .await?;

This is the single most important habit to build: inside a transaction, pass &mut *tx, never &pool. If you pass &pool you check out a different connection and run the statement outside your transaction. (See Common Pitfalls.) The &mut is what forces statements to run one at a time, in order — the borrow checker won’t let two queries borrow the same transaction concurrently, which mirrors how SQL transactions are inherently sequential on a single connection.

`commit` and `rollback` consume the guard

1
tx.commit().await?;

commit takes self by value (it consumes tx), sends COMMIT, and returns the connection to the pool. After this line tx no longer exists — you cannot accidentally run another statement on a committed transaction, because the value is gone. rollback works the same way but sends ROLLBACK. Both are async and return Result, because the round-trip to the database can itself fail.

The guard: implicit rollback on drop

Here is the behavior that has no TypeScript equivalent. If a Transaction is dropped — because you returned early, an error propagated via ?, or a panic unwound the stack — SQLx rolls it back automatically:

1
async fn guard_rollback(pool: &Pool<Sqlite>) -> Result<(), sqlx::Error> {
2
    {
3
        let mut tx = pool.begin().await?;
4
        sqlx::query("UPDATE accounts SET balance = 12345 WHERE id = 2")
5
            .execute(&mut *tx)
6
            .await?;
7
        // No commit, no rollback: `tx` is dropped here -> rolled back.
8
    }
9
    Ok(())
10
}

Running this against a fresh database, then reading account 2 back, prints:

1
bob after guard rollback: balance=80

The balance is unchanged — the UPDATE to 12345 was discarded. This is the RAII (Resource Acquisition Is Initialization) pattern: the resource (the open transaction) is released by Drop, deterministically, the instant the value goes out of scope. It is the same mechanism that closes a Rust File or releases a MutexGuard. The TypeScript finally block is a runtime convention you must write; the Rust rollback-on-drop is a language guarantee you cannot forget.

Note: Because SQLx is async, the drop-rollback is “best effort” in one narrow sense: the rollback statement is queued on the connection and sent when the connection is next used (or cleaned up by the pool). The transaction’s effects are never visible to other connections, so atomicity holds; you just shouldn’t rely on the ROLLBACK having flushed at the exact instant of the drop. When you care, call rollback().await explicitly.

Explicit rollback when there is no error

Sometimes you want to abandon a transaction even though nothing failed — for example, a “dry run” or a business rule that says “don’t proceed”. Call rollback() explicitly:

1
async fn explicit_rollback(pool: &Pool<Sqlite>) -> Result<(), sqlx::Error> {
2
    let mut tx = pool.begin().await?;
3
    sqlx::query("UPDATE accounts SET balance = 999 WHERE id = 1")
4
        .execute(&mut *tx)
5
        .await?;
6
    // Changed our mind: undo everything.
7
    tx.rollback().await?;
8
    Ok(())
9
}

Atomicity in action

Atomicity is the “A” in ACID: the transaction is all-or-nothing. Consider a transaction whose second statement violates a CHECK (balance >= 0) constraint:

1
async fn overdraw(pool: &Pool<Sqlite>) -> Result<(), sqlx::Error> {
2
    let mut tx = pool.begin().await?;
3

4
    // This one would succeed on its own.
5
    sqlx::query("UPDATE accounts SET balance = balance + 1000 WHERE id = 2")
6
        .execute(&mut *tx)
7
        .await?;
8

9
    // This one fails the CHECK constraint.
10
    sqlx::query("UPDATE accounts SET balance = balance - 1000 WHERE id = 1")
11
        .execute(&mut *tx)
12
        .await?; // <-- returns Err here; we never reach commit
13

14
    tx.commit().await?;
15
    Ok(())
16
}

The second execute().await? returns Err, so the function returns early via ?, tx is dropped, and the transaction rolls back — including the first UPDATE, which had already executed successfully. The real output, starting from alice=100, bob=50:

1
overdraw failed: error returned from database: (code: 275) CHECK constraint failed: balance >= 0
2
balances after failed tx: [(1, 100), (2, 50)]

Both balances are exactly as they started. The +1000 to bob never persisted, because it was part of the same atomic unit as the failing statement. That is the entire point of a transaction, and SQLx gave it to you for free: you wrote no rollback handler.

Key Differences

Concern	TypeScript (`pg` / Prisma / TypeORM)	Rust (SQLx)
What a transaction is	Raw `BEGIN`/`COMMIT` strings on a checked-out client, or a callback	A `Transaction<'_, DB>` value (a guard)
Getting a transaction	`const c = await pool.connect(); await c.query("BEGIN")`	`let mut tx = pool.begin().await?;`
Running a statement in it	`await client.query(...)` (must use `client`, not `pool`)	`.execute(&mut *tx).await?` (must use `tx`, not `pool`)
Committing	`await client.query("COMMIT")`	`tx.commit().await?` (consumes `tx`)
Rollback on error	Manual `catch { ROLLBACK }` you must write	Automatic on drop; or explicit `tx.rollback().await?`
Returning the connection	Manual `client.release()` in `finally`	Automatic on drop — there is no `release`
Use-after-commit	Allowed; runtime error or silent no-op	Compile error — `commit` moved the value
Wrong-handle bug	Easy: `pool.query` instead of `client.query` runs outside the tx	Easy to write `&pool`, but it is a clearly different argument; linters and review catch it, and it never leaks the tx
Nested transactions	Library-specific; often unsupported	`tx.begin()` opens a real SQL `SAVEPOINT`

The mental shift: in TypeScript a transaction is a protocol you follow (begin, then remember to commit-or-rollback, then remember to release). In Rust a transaction is a type you hold, and the compiler plus Drop enforce the protocol. Forgetting cleanup is not a bug you can write.

Savepoints (nested transactions)

Calling begin() on a Transaction (rather than on a Pool) opens a savepoint — a nested, partially-rollback-able sub-transaction. Rolling back the inner one undoes only its work; the outer transaction continues:

1
use sqlx::Acquire; // brings the nesting `begin` (a SAVEPOINT) into scope on Transaction
2

3
let mut tx = pool.begin().await?;
4
sqlx::query("UPDATE t SET n = 10 WHERE id = 1")
5
    .execute(&mut *tx)
6
    .await?;
7

8
// Nested transaction == SAVEPOINT.
9
let mut sp = tx.begin().await?;
10
sqlx::query("UPDATE t SET n = 999 WHERE id = 1")
11
    .execute(&mut *sp)
12
    .await?;
13
sp.rollback().await?; // undo just the savepoint
14

15
tx.commit().await?; // keeps n = 10

Final value after running this:

1
final n after savepoint rollback + outer commit: 10

The inner n = 999 was discarded; the outer n = 10 committed. This is how you implement “try this sub-step, and if it fails, undo only it and carry on” — something most JavaScript ORMs handle poorly or not at all.

Common Pitfalls

Pitfall 1: Running queries on the pool instead of the transaction

This is the direct analog of calling pool.query instead of client.query in pg. It compiles, runs, and silently executes outside your transaction:

1
// Here `pool` is an OWNED `Pool<Sqlite>`, so `&pool` is `&Pool` — a valid `Executor`.
2
let mut tx = pool.begin().await?;
3

4
sqlx::query("UPDATE accounts SET balance = balance - 10 WHERE id = 1")
5
    .execute(&pool) // logic bug: runs on a DIFFERENT connection, outside `tx`
6
    .await?;
7

8
tx.commit().await?; // commits an EMPTY transaction; the UPDATE already happened

There is no compiler error here because &pool (where pool is an owned Pool<Sqlite>) is a perfectly valid Executor — it just isn’t your transaction. The fix is mechanical: inside a transaction, always pass &mut *tx. A good habit is to write a helper that takes &mut Transaction so the type system stops you from passing a pool by mistake (see Best Practices).

Note: This silent bug only slips past the compiler when pool is owned. Most functions in this file take pool: &Pool<Sqlite> by reference — there, &pool is &&Pool<Sqlite>, which does not implement Executor, so the same typo becomes a compile error (error[E0277]: the trait bound &&Pool: Executor<’_> is not satisfied). Either way, pass &mut *tx and the ambiguity disappears.

Pitfall 2: Using a transaction after committing it

commit (and rollback) take ownership of the transaction. Touch it afterward and you get a compile error, not a runtime surprise:

1
let mut tx = pool.begin().await?;
2
sqlx::query("INSERT INTO t (id) VALUES (1)")
3
    .execute(&mut *tx)
4
    .await?;
5
tx.commit().await?;
6

7
// does not compile (error[E0382]: borrow of moved value: `tx`)
8
sqlx::query("INSERT INTO t (id) VALUES (2)")
9
    .execute(&mut *tx)
10
    .await?;

The real error from cargo build:

1
error[E0382]: borrow of moved value: `tx`
2
   --> src/main.rs:18:24
3
    |
4
 10 |     let mut tx = pool.begin().await?;
5
    |         ------ move occurs because `tx` has type `Transaction<'_, Sqlite>`, which does not implement the `Copy` trait
6
...
7
 14 |     tx.commit().await?;
8
    |        -------- `tx` moved due to this method call
9
...
10
 18 |         .execute(&mut *tx)
11
    |                        ^^ value borrowed here after move
12
    |
13
note: `Transaction::<'c, DB>::commit` takes ownership of the receiver `self`, which moves `tx`
14
   --> .../sqlx-core-0.8.6/src/transaction.rs:116:29
15
    |
16
116 |     pub async fn commit(mut self) -> Result<(), Error> {
17
    |                             ^^^^

In TypeScript, calling client.query("INSERT ...") after COMMIT would either start a brand-new implicit transaction or error at runtime depending on the driver. Rust catches the mistake before the program ever runs. If you genuinely need a second transaction, call pool.begin() again to get a fresh one.

Pitfall 3: Forgetting `commit` and expecting the writes to stick

Because of the rollback-on-drop guard, the opposite of the TypeScript leak happens: if you forget to commit, your writes silently vanish rather than leaking a connection.

1
async fn save(pool: &Pool<Sqlite>) -> Result<(), sqlx::Error> {
2
    let mut tx = pool.begin().await?;
3
    sqlx::query("INSERT INTO logs (msg) VALUES ('hello')")
4
        .execute(&mut *tx)
5
        .await?;
6
    Ok(()) // logic bug: no commit -> tx dropped -> INSERT rolled back
7
}

This compiles and the function returns Ok(()), but nothing was saved. The cure is to make commit the last meaningful line before the success path returns, and to lean on ? so any earlier failure rolls back as intended.

Pitfall 4: Holding a transaction open across slow work

A Transaction holds a connection out of the pool and (on most databases) holds row locks. If you begin(), then .await a slow HTTP call, then run your SQL, you are starving the pool and blocking other writers for the whole network round-trip. Do the slow work before begin(), keep the transaction as short as possible, and commit promptly. This is the same advice as “keep BEGIN…COMMIT short” in any database, but it matters more in an async server where a single stuck transaction can back up an entire request queue. See also Connection Pooling.

Best Practices

Write helpers that borrow the transaction

Make functions take &mut Transaction<'_, DB> so callers can compose multi-step work, and so the type system prevents anyone from accidentally passing a pool. Note the double-deref &mut **tx: *tx derefs the &mut Transaction reference, and **tx derefs the Transaction to its connection.

1
use sqlx::{Sqlite, Transaction};
2

3
async fn reserve_stock(
4
    tx: &mut Transaction<'_, Sqlite>,
5
    product_id: i64,
6
    qty: i64,
7
) -> Result<(), sqlx::Error> {
8
    sqlx::query("UPDATE products SET stock = stock - ? WHERE id = ?")
9
        .bind(qty)
10
        .bind(product_id)
11
        .execute(&mut **tx) // &mut Transaction -> &mut Connection
12
        .await?;
13
    Ok(())
14
}

Or be generic over `Acquire` so a helper works with a pool or a transaction

If a helper should be usable both standalone (against the pool) and inside a transaction, bound it on Acquire:

1
use sqlx::{Acquire, Sqlite};
2

3
async fn debit<'c, A>(conn: A, id: i64, amount: i64) -> Result<(), sqlx::Error>
4
where
5
    A: Acquire<'c, Database = Sqlite>,
6
{
7
    let mut conn = conn.acquire().await?;
8
    sqlx::query("UPDATE accounts SET balance = balance - ? WHERE id = ?")
9
        .bind(amount)
10
        .bind(id)
11
        .execute(&mut *conn)
12
        .await?;
13
    Ok(())
14
}
15

16
// Call it on a transaction:  debit(&mut *tx, 1, 10).await?;
17
// or directly on the pool:   debit(&pool, 1, 10).await?;

Let `?` drive rollback

Don’t write match arms that call rollback() on every error — that is the manual TypeScript style. Use ? and let the guard roll back on the early return. Only call rollback() explicitly when you are abandoning the transaction without an error (a business-rule “no”, a dry run).

Commit exactly once, at the end of the happy path

Structure functions so every fallible step uses ? and the single tx.commit().await? is the last thing before returning success. This makes the all-or-nothing boundary obvious to readers.

Prefer compile-checked macros for the queries inside

Transactions compose with the query! / query_as! macros exactly like query / query_as do — pass &mut *tx as the executor. The macros verify your SQL against the live schema at compile time. That is the subject of Writing Queries; everything in this file works identically with the macro forms.

Real-World Example

A production-flavored “place an order” service: insert an order, decrement stock for each line item, and record the line items — all atomically. If any product is out of stock (a CHECK (stock >= 0) constraint), the entire order is rolled back, including the order row itself. This is the canonical use case for transactions.

1
use sqlx::sqlite::SqlitePoolOptions;
2
use sqlx::{Pool, Sqlite, Transaction};
3

4
#[derive(Debug)]
5
struct OrderLine {
6
    product_id: i64,
7
    quantity: i64,
8
}
9

10
async fn setup(pool: &Pool<Sqlite>) -> Result<(), sqlx::Error> {
11
    sqlx::query(
12
        "CREATE TABLE products (
13
            id INTEGER PRIMARY KEY,
14
            name TEXT NOT NULL,
15
            stock INTEGER NOT NULL CHECK (stock >= 0)
16
        )",
17
    )
18
    .execute(pool)
19
    .await?;
20
    sqlx::query("CREATE TABLE orders (id INTEGER PRIMARY KEY, total INTEGER NOT NULL)")
21
        .execute(pool)
22
        .await?;
23
    sqlx::query(
24
        "CREATE TABLE order_items (
25
            order_id INTEGER NOT NULL,
26
            product_id INTEGER NOT NULL,
27
            quantity INTEGER NOT NULL
28
        )",
29
    )
30
    .execute(pool)
31
    .await?;
32
    sqlx::query("INSERT INTO products (id, name, stock) VALUES (1, 'Keyboard', 5), (2, 'Mouse', 3)")
33
        .execute(pool)
34
        .await?;
35
    Ok(())
36
}
37

38
// Reusable helper that operates on a borrowed transaction.
39
async fn reserve_stock(
40
    tx: &mut Transaction<'_, Sqlite>,
41
    product_id: i64,
42
    qty: i64,
43
) -> Result<(), sqlx::Error> {
44
    sqlx::query("UPDATE products SET stock = stock - ? WHERE id = ?")
45
        .bind(qty)
46
        .bind(product_id)
47
        .execute(&mut **tx)
48
        .await?;
49
    Ok(())
50
}
51

52
async fn place_order(pool: &Pool<Sqlite>, lines: &[OrderLine]) -> Result<i64, sqlx::Error> {
53
    let mut tx = pool.begin().await?;
54

55
    let order_id: i64 = sqlx::query_scalar("INSERT INTO orders (total) VALUES (0) RETURNING id")
56
        .fetch_one(&mut *tx)
57
        .await?;
58

59
    for line in lines {
60
        reserve_stock(&mut tx, line.product_id, line.quantity).await?;
61
        sqlx::query("INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)")
62
            .bind(order_id)
63
            .bind(line.product_id)
64
            .bind(line.quantity)
65
            .execute(&mut *tx)
66
            .await?;
67
    }
68

69
    tx.commit().await?;
70
    Ok(order_id)
71
}
72

73
#[tokio::main]
74
async fn main() -> Result<(), sqlx::Error> {
75
    let pool = SqlitePoolOptions::new().connect("sqlite::memory:").await?;
76
    setup(&pool).await?;
77

78
    // Succeeds: enough stock.
79
    let id = place_order(
80
        &pool,
81
        &[
82
            OrderLine { product_id: 1, quantity: 2 },
83
            OrderLine { product_id: 2, quantity: 1 },
84
        ],
85
    )
86
    .await?;
87
    println!("placed order {id}");
88

89
    // Fails: not enough Mouse stock. The WHOLE order rolls back.
90
    let result = place_order(
91
        &pool,
92
        &[
93
            OrderLine { product_id: 1, quantity: 1 },
94
            OrderLine { product_id: 2, quantity: 99 },
95
        ],
96
    )
97
    .await;
98
    println!("second order: {result:?}");
99

100
    // Atomicity check: the Keyboard from the failed order was NOT decremented,
101
    // and no second order row was persisted.
102
    let stocks: Vec<(String, i64)> =
103
        sqlx::query_as("SELECT name, stock FROM products ORDER BY id")
104
            .fetch_all(&pool)
105
            .await?;
106
    println!("final stock: {stocks:?}");
107

108
    let order_count: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM orders")
109
        .fetch_one(&pool)
110
        .await?;
111
    println!("orders persisted: {}", order_count.0);
112

113
    Ok(())
114
}

Real output:

1
placed order 1
2
second order: Err(Database(SqliteError { code: 275, message: "CHECK constraint failed: stock >= 0" }))
3
final stock: [("Keyboard", 3), ("Mouse", 2)]
4
orders persisted: 1

Read that output carefully — it is the whole lesson:

The first order placed (id 1) and decremented stock to Keyboard 3, Mouse 2.
The second order tried Keyboard 1 (which would have succeeded) and Mouse 99 (which violated the constraint). The Mouse statement failed, ? propagated the error, tx dropped, and everything in that transaction rolled back.
Final stock is Keyboard 3 — not 2. The Keyboard decrement from the failed order was undone, even though that statement had already executed successfully.
orders persisted: 1 — the INSERT INTO orders row from the failed attempt was rolled back too, so no orphan order exists.

You wrote zero rollback handling. The ? operator and the Drop guard did it.

Exercises

Exercise 1: Guard the balance yourself

Difficulty: Beginner

Objective: Practice begin, an explicit rollback, and conditional commit without relying on a database CHECK constraint.

Instructions: Write async fn safe_transfer(pool, from, to, amount) -> Result<bool, sqlx::Error> that begins a transaction, reads the sender’s balance, and if it is less than amount, rolls back and returns Ok(false). Otherwise it performs both UPDATEs, commits, and returns Ok(true). Use a table accounts(id INTEGER PRIMARY KEY, balance INTEGER NOT NULL) seeded with (1, 100), (2, 50).

Solution

1
use sqlx::sqlite::SqlitePoolOptions;
2
use sqlx::{Pool, Sqlite};
3

4
async fn safe_transfer(
5
    pool: &Pool<Sqlite>,
6
    from: i64,
7
    to: i64,
8
    amount: i64,
9
) -> Result<bool, sqlx::Error> {
10
    let mut tx = pool.begin().await?;
11

12
    let from_balance: i64 = sqlx::query_scalar("SELECT balance FROM accounts WHERE id = ?")
13
        .bind(from)
14
        .fetch_one(&mut *tx)
15
        .await?;
16

17
    if from_balance < amount {
18
        tx.rollback().await?; // explicit: no error, we just decline
19
        return Ok(false);
20
    }
21

22
    sqlx::query("UPDATE accounts SET balance = balance - ? WHERE id = ?")
23
        .bind(amount)
24
        .bind(from)
25
        .execute(&mut *tx)
26
        .await?;
27
    sqlx::query("UPDATE accounts SET balance = balance + ? WHERE id = ?")
28
        .bind(amount)
29
        .bind(to)
30
        .execute(&mut *tx)
31
        .await?;
32

33
    tx.commit().await?;
34
    Ok(true)
35
}
36

37
#[tokio::main]
38
async fn main() -> Result<(), sqlx::Error> {
39
    let pool = SqlitePoolOptions::new().connect("sqlite::memory:").await?;
40
    sqlx::query("CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL)")
41
        .execute(&pool)
42
        .await?;
43
    sqlx::query("INSERT INTO accounts (id, balance) VALUES (1, 100), (2, 50)")
44
        .execute(&pool)
45
        .await?;
46

47
    println!("transfer 30:   {}", safe_transfer(&pool, 1, 2, 30).await?);
48
    println!("transfer 1000: {}", safe_transfer(&pool, 1, 2, 1000).await?);
49

50
    let balances: Vec<(i64, i64)> =
51
        sqlx::query_as("SELECT id, balance FROM accounts ORDER BY id")
52
            .fetch_all(&pool)
53
            .await?;
54
    println!("balances: {balances:?}");
55
    Ok(())
56
}

Output:

1
transfer 30:   true
2
transfer 1000: false
3
balances: [(1, 70), (2, 80)]

The 1000 transfer was declined and left balances untouched; only the 30 transfer applied.

Exercise 2: Prove rollback-on-drop yourself

Difficulty: Intermediate

Objective: Observe the Transaction guard’s automatic rollback by deliberately dropping a transaction without committing, then confirming the write did not persist.

Instructions: Create a table events(id INTEGER PRIMARY KEY, name TEXT). Write a function that begins a transaction, inserts a row, and returns without committing (let tx drop). After calling it, query SELECT COUNT(*) FROM events and assert it is 0. Then do the same insert in a transaction you do commit, and confirm the count is 1.

Solution

1
use sqlx::sqlite::SqlitePoolOptions;
2
use sqlx::{Pool, Sqlite};
3

4
async fn insert_then_drop(pool: &Pool<Sqlite>) -> Result<(), sqlx::Error> {
5
    let mut tx = pool.begin().await?;
6
    sqlx::query("INSERT INTO events (name) VALUES ('dropped')")
7
        .execute(&mut *tx)
8
        .await?;
9
    // No commit. `tx` drops here -> ROLLBACK.
10
    Ok(())
11
}
12

13
async fn insert_then_commit(pool: &Pool<Sqlite>) -> Result<(), sqlx::Error> {
14
    let mut tx = pool.begin().await?;
15
    sqlx::query("INSERT INTO events (name) VALUES ('kept')")
16
        .execute(&mut *tx)
17
        .await?;
18
    tx.commit().await?;
19
    Ok(())
20
}
21

22
#[tokio::main]
23
async fn main() -> Result<(), sqlx::Error> {
24
    let pool = SqlitePoolOptions::new().connect("sqlite::memory:").await?;
25
    sqlx::query("CREATE TABLE events (id INTEGER PRIMARY KEY, name TEXT)")
26
        .execute(&pool)
27
        .await?;
28

29
    insert_then_drop(&pool).await?;
30
    let after_drop: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM events")
31
        .fetch_one(&pool)
32
        .await?;
33
    assert_eq!(after_drop.0, 0);
34
    println!("after drop, count = {}", after_drop.0);
35

36
    insert_then_commit(&pool).await?;
37
    let after_commit: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM events")
38
        .fetch_one(&pool)
39
        .await?;
40
    assert_eq!(after_commit.0, 1);
41
    println!("after commit, count = {}", after_commit.0);
42
    Ok(())
43
}

Output:

1
after drop, count = 0
2
after commit, count = 1

The dropped transaction left nothing behind; only the committed one persisted.

Exercise 3: Savepoints for partial rollback

Difficulty: Advanced

Objective: Use a nested transaction (savepoint) to undo one sub-step while keeping the rest of the outer transaction.

Instructions: Create audit(id INTEGER PRIMARY KEY, action TEXT NOT NULL). In one outer transaction: (1) insert "start"; (2) open a savepoint with tx.begin(), insert "risky" through it, then rollback() the savepoint; (3) insert "end" through the outer transaction; (4) commit the outer transaction. Afterward, the table should contain exactly start and end — never risky. (Remember to bring the nesting begin into scope with use sqlx::Acquire;.)

Solution

1
use sqlx::sqlite::SqlitePoolOptions;
2
use sqlx::Acquire; // enables the nesting `begin()` on a Transaction (savepoint)
3

4
#[tokio::main]
5
async fn main() -> Result<(), sqlx::Error> {
6
    let pool = SqlitePoolOptions::new().connect("sqlite::memory:").await?;
7
    sqlx::query("CREATE TABLE audit (id INTEGER PRIMARY KEY, action TEXT NOT NULL)")
8
        .execute(&pool)
9
        .await?;
10

11
    let mut tx = pool.begin().await?;
12

13
    sqlx::query("INSERT INTO audit (action) VALUES ('start')")
14
        .execute(&mut *tx)
15
        .await?;
16

17
    // Savepoint: try a risky step, then undo only it.
18
    let mut sp = tx.begin().await?;
19
    sqlx::query("INSERT INTO audit (action) VALUES ('risky')")
20
        .execute(&mut *sp)
21
        .await?;
22
    sp.rollback().await?; // discards 'risky' only
23

24
    sqlx::query("INSERT INTO audit (action) VALUES ('end')")
25
        .execute(&mut *tx)
26
        .await?;
27

28
    tx.commit().await?;
29

30
    let actions: Vec<(String,)> = sqlx::query_as("SELECT action FROM audit ORDER BY id")
31
        .fetch_all(&pool)
32
        .await?;
33
    println!("{:?}", actions.iter().map(|r| r.0.as_str()).collect::<Vec<_>>());
34
    Ok(())
35
}

Output:

1
["start", "end"]

The risky insert was rolled back at the savepoint, while start and end survived to the outer commit.

Transactions with SQLx

Quick Overview

TypeScript/JavaScript Example

Rust Equivalent

Detailed Explanation

begin() returns a guard, not a string command

&mut *tx — running a query “through” the transaction

commit and rollback consume the guard

The guard: implicit rollback on drop

Explicit rollback when there is no error

Atomicity in action

Key Differences

Savepoints (nested transactions)

Common Pitfalls

Pitfall 1: Running queries on the pool instead of the transaction

Pitfall 2: Using a transaction after committing it

Pitfall 3: Forgetting commit and expecting the writes to stick

Pitfall 4: Holding a transaction open across slow work

Best Practices

Write helpers that borrow the transaction

Or be generic over Acquire so a helper works with a pool or a transaction

Let ? drive rollback

Commit exactly once, at the end of the happy path

Prefer compile-checked macros for the queries inside

Real-World Example

Further Reading

Exercises

Exercise 1: Guard the balance yourself

Exercise 2: Prove rollback-on-drop yourself

Exercise 3: Savepoints for partial rollback

`begin()` returns a guard, not a string command

`&mut *tx` — running a query “through” the transaction

`commit` and `rollback` consume the guard

Pitfall 3: Forgetting `commit` and expecting the writes to stick

Or be generic over `Acquire` so a helper works with a pool or a transaction

Let `?` drive rollback