How do I actually start and finish a transaction?

In SQLx, you don't manually send "BEGIN" and "COMMIT" queries. Instead, you use the begin() method on your pool. This gives you a Transaction object that wraps a connection. The most important thing to remember is that if this object is dropped without commit() being called, SQLx will automatically roll back the changes for you. I've seen plenty of bugs where developers forgot to commit, and they spent an hour wondering why their data wasn't saving—it's almost always because the transaction was dropped at the end of the scope.

async fn place_order(pool: &PgPool, user_id: i32, product_id: i32) -> anyhow::Result<()> {
    // Start the transaction
    let mut tx = pool.begin().await?;

    // Use 'tx' instead of 'pool' for your queries
    sqlx::query!("UPDATE inventory SET stock = stock - 1 WHERE id = $1", product_id)
        .execute(&mut *tx)
        .await?;

    sqlx::query!("INSERT INTO orders (user_id, product_id) VALUES ($1, $2)", user_id, product_id)
        .execute(&mut *tx)
        .await?;

    // If we reach here, everything succeeded. Now we commit.
    tx.commit().await?;
    
    Ok(())
}

How do I pass a transaction into other functions?

This is where things get a bit clunky in Rust. You'll likely want to break your logic into smaller functions, but you can't just pass the pool because that would start a new connection. You need to pass a reference to the transaction. The trick is using the &mut *tx syntax or, more flexibly, using the Executor trait. For most of your projects, simply passing a mutable reference to the transaction is the most straightforward path.

Notice in the example below how I use &mut PgConnection as the argument type. Since Transaction derefs to the connection, this works perfectly and keeps your helper functions clean.

async fn deduct_stock(executor: &mut PgConnection, product_id: i32) -> anyhow::Result<()> {
    sqlx::query!("UPDATE inventory SET stock = stock - 1 WHERE id = $1", product_id)
        .execute(executor)
        .await?;
    Ok(())
}

async fn create_order_record(executor: &mut PgConnection, user_id: i32, product_id: i32) -> anyhow::Result<()> {
    sqlx::query!("INSERT INTO orders (user_id, product_id) VALUES ($1, $2)", user_id, product_id)
        .execute(executor)
        .await?;
    Ok(())
}

// Then in your main logic:
let mut tx = pool.begin().await?;
deduct_stock(&mut *tx, product_id).await?;
create_order_record(&mut *tx, user_id, product_id).await?;
tx.commit().await?;

Do I need to call rollback manually if an error occurs?

Short answer: No. One of the nicest things about the way SQLx handles transactions is that it leverages Rust's ownership and drop semantics. If your function returns early via the ? operator, the tx variable goes out of scope and is dropped. When a Transaction is dropped without commit() being called, SQLx sends a rollback command to the database.

I usually tell people to think of tx.commit() as the "Save" button. Everything you do up until that line is just a draft. If the code crashes, returns an error, or panics, the draft is simply thrown away. This makes your error handling significantly leaner because you don't have to wrap every single query in a massive try-catch block just to trigger a rollback.

📋 Practical Task

Exercise: Implement an Atomic Fund Transfer between User Accounts

You are building a simple banking module. You need to create a function transfer_funds(pool: &PgPool, from_id: i32, to_id: i32, amount: i64) that ensures money is never "lost" during a transfer.

Requirements:

• Start a transaction using pool.begin().
• Subtract the amount from the account matching from_id.
• Add the amount to the account matching to_id.
• Ensure that if the second update fails (e.g., the to_id doesn't exist), the money is not deducted from the first account.
• Commit the transaction only after both operations succeed.

Database Schema for context:

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    balance BIGINT NOT NULL
);