Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/launchbadge/sqlx/llms.txt

Use this file to discover all available pages before exploring further.

SQLx wraps database transactions in a Transaction struct that tracks whether the transaction is open and ensures it is cleanly closed. You can begin a transaction from a pool or an existing connection, execute queries through it using the Executor trait, and commit or roll back when done. If a Transaction is dropped without being explicitly committed, SQLx automatically initiates a rollback.

​
Beginning a transaction

Call .begin() on a pool or a connection to start a transaction. From a pool, SQLx acquires a connection and immediately issues a BEGIN: 
// From a pool — acquires a connection and begins a transaction
let mut tx = pool.begin().await?;

// From a connection — begins a transaction on that connection
let mut tx = conn.begin().await?;
The returned Transaction<'_, DB> holds the connection for its lifetime. Successive queries inside the same transaction use this single connection.

​
Executing queries inside a transaction

Transaction implements Deref<Target = DB::Connection>, so you can pass &mut *tx anywhere a mutable connection reference is accepted. Query finalizers accept it as an executor: 
use sqlx::PgPool;

async fn transfer_funds(
    pool: &PgPool,
    from_id: i64,
    to_id: i64,
    amount: i64,
) -> sqlx::Result<()> {
    let mut tx = pool.begin().await?;

    sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2")
        .bind(amount)
        .bind(from_id)
        .execute(&mut *tx)
        .await?;

    sqlx::query("UPDATE accounts SET balance = balance + $1 WHERE id = $2")
        .bind(amount)
        .bind(to_id)
        .execute(&mut *tx)
        .await?;

    tx.commit().await?;
    Ok(())
}

​
Committing and rolling back

1

Commit

Call tx.commit().await? to persist all changes made during the transaction. After committing, the Transaction is consumed.
tx.commit().await?;
2

Rollback

Call tx.rollback().await? to discard all changes. After rolling back, the Transaction is consumed.
tx.rollback().await?;
3

Automatic rollback on drop

If the Transaction is dropped without calling commit() or rollback(), SQLx calls start_rollback() on the underlying connection. The rollback is queued and executed the next time the connection is used (including when it is returned to the pool).This means that returning early from a function with ? on an error will automatically roll back the transaction:
async fn create_user(pool: &PgPool, name: &str) -> sqlx::Result<i64> {
    let mut tx = pool.begin().await?;

    let id: i64 = sqlx::query_scalar(
        "INSERT INTO users (name) VALUES ($1) RETURNING id"
    )
    .bind(name)
    .fetch_one(&mut *tx)
    .await?;  // If this errors, tx is dropped → automatic rollback

    // Some other operation that might fail...
    sqlx::query("INSERT INTO audit_log (user_id, event) VALUES ($1, 'created')")
        .bind(id)
        .execute(&mut *tx)
        .await?;  // If this errors, tx is dropped → automatic rollback

    tx.commit().await?;
    Ok(id)
}

​
The connection.transaction() helper

For simple cases you can use the transaction() method on a connection, which begins, runs a closure, and commits or rolls back based on whether the closure returns Ok or Err: 
use sqlx::postgres::{PgConnection, PgRow};
use sqlx::Connection;

let rows: Vec<PgRow> = conn.transaction(|txn| Box::pin(async move {
    sqlx::query("SELECT * FROM users")
        .fetch_all(&mut **txn)
        .await
})).await?;

​
Nested transactions and savepoints

When .begin() is called on a connection that already has an active transaction, SQLx creates a savepoint instead of issuing a second BEGIN. Savepoints use automatically generated names (_sqlx_savepoint_1, _sqlx_savepoint_2, …). 
let mut tx = pool.begin().await?;  // issues BEGIN

// Nested — issues SAVEPOINT _sqlx_savepoint_1
let mut nested = tx.begin().await?;

sqlx::query("INSERT INTO events (name) VALUES ($1)")
    .bind("nested_event")
    .execute(&mut *nested)
    .await?;

// RELEASE SAVEPOINT _sqlx_savepoint_1
nested.commit().await?;

// COMMIT
tx.commit().await?;
Rolling back a savepoint restores the state of the outer transaction to what it was at the time the savepoint was created, without affecting the outer transaction: 
let mut tx = pool.begin().await?;

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

sqlx::query("DELETE FROM users WHERE active = FALSE")
    .execute(&mut *savepoint)
    .await?;

// ROLLBACK TO SAVEPOINT _sqlx_savepoint_1 — outer tx is unaffected
savepoint.rollback().await?;

// The DELETE above was rolled back; tx can still commit other work
tx.commit().await?;
Transaction depth is tracked per-connection. Depth 0 means no active transaction; depth 1 means a transaction is active; depth 2 or higher means one or more savepoints are open.

​
Transactions with the query!() macros

The compile-time macros work with transactions the same way as the query functions — pass &mut *tx as the executor: 
use sqlx::PgPool;

async fn complete_todo(pool: &PgPool, id: i64) -> anyhow::Result<bool> {
    let mut tx = pool.begin().await?;

    let rows_affected = sqlx::query!(
        r#"
UPDATE todos
SET done = TRUE
WHERE id = $1
        "#,
        id
    )
    .execute(&mut *tx)
    .await?
    .rows_affected();

    tx.commit().await?;
    Ok(rows_affected > 0)
}

​
Custom BEGIN statements

Some databases support non-standard transaction isolation levels or other options on the BEGIN statement. Use begin_with() to supply a custom statement: 
let tx = pool.begin_with("BEGIN ISOLATION LEVEL SERIALIZABLE").await?;
begin_with() requires that the custom statement actually puts the connection into a transaction. If it does not, subsequent operations may behave unexpectedly.

Build docs developers (and LLMs) love

Get started for freeTalk to us