Use SurrealQL BEGIN and COMMIT in queries, or the Rust SDK `begin` / `commit` / `cancel` transaction handle, and check per-statement results before committing

Manual transactions

While every query in SurrealDB is run inside its own transaction, manual transactions made up of multiple statements can be used via the BEGIN and COMMIT keywords.

Getting started

Start a running database using the following command:

surreal start --user root --pass secret

To follow along interactively, connect using Surrealist or the following command to open a connection in the CLI:

surreal sql --user root --pass secret --pretty

Then use the cargo add command to add the surrealdb and tokio crates. The dependencies inside Cargo.toml should look something like this:

[dependencies]
surrealdb = "3.0.5"
tokio = "1.52.1"

Using a client-side transaction

Once this is done, you can use .begin() to get a client-side Transaction. Run your statements with .query() or other methods such as .select(), .create()` and so on, then end the transaction in one of two ways:

commit() — apply the changes. The future resolves to a Surreal client again.
cancel() — roll back. This also returns the Surreal client when it completes.

Note that the outer Result from awaiting a query only shows that the statements have succeeded, but a response can still include per-statement failures (the request succeeded, but one of the SQL statements did not). Collect those with take_errors() on the query response, or use check() to fail on the first error. If the outer Result is Err, the transaction is not usable as intended.

The following example uses an in-memory database, runs every query in its own short transaction, and cancels (or would skip a commit) when a statement error shows up.

use surrealdb::Surreal;
use surrealdb::engine::any::{connect, Any};

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let db = connect("memory").await?;
    db.use_ns("ns").use_db("db").await?;

    let db = run_in_transaction(db, "LET $x: int = 'not a number';").await?;
    let db = run_in_transaction(db, "SELECT SELECT SELECT").await?;
    run_in_transaction(db, "9").await?;

    Ok(())
}

// Runs a single query inside a new transaction.
// `commit` only runs when there
// are no per-statement errors;
// otherwise the transaction is cancelled.
async fn run_in_transaction(
    db: Surreal<Any>,
    surql: &str,
) -> surrealdb::Result<Surreal<Any>> {
    let tx = db.begin().await?;

    match tx.query(surql).await {
        Ok(mut response) => {
            let errors = response.take_errors();
            if !errors.is_empty() {
                eprintln!("Errors from statements: {errors:#?}\n");
                return tx.cancel().await;
            }
            println!("Ok: {response:#?}\n");
            return tx.commit().await;
        }
        Err(e) => {
            eprintln!("Error from query request: {e}\n");
            return tx.cancel().await;
        }
    }
}

Using SurrealQL transaction statements

A manual transaction can also be performed by sending in a BEGIN and other statements into the .query() method manually. This will result in the same behaviour as the previous method, but will not return a Transaction on the SDK side.

use surrealdb::Surreal;
use surrealdb::engine::remote::ws::Ws;
use surrealdb::opt::auth::Root;
use surrealdb_types::{SurrealValue, ToSql, Value};

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let db = connect("memory").await?;

    db.signin(Root {
        username: "root".to_string(),
        password: "secret".to_string(),
    })
    .await?;

    db.use_ns("main").use_db("main").await?;

    let mut response = db
        .query(
            "
        // Start transaction
        BEGIN;
        // Setup accounts
        CREATE account:one SET balance = 135605.16;
        CREATE account:two SET balance = 91031.31;
        // Move money
        UPDATE account:one SET balance += 300.00;
        UPDATE account:two SET balance -= 300.00;
        // Finalise
        COMMIT;
        ",
        )
        .await?;

    for i in 0..response.num_statements() {
        println!(
            "{}",
            response
                .take::<Option<Value>>(i)
                .unwrap()
                .into_value()
                .to_sql()
        );
    }

    Ok(())
}

The output will look like this.

NONE
[{ balance: 135605.16f, id: account:one }]
[{ balance: 91031.31f, id: account:two }]
[{ balance: 135905.16f, id: account:one }]
[{ balance: 90731.31f, id: account:two }]
NONE

To avoid the possibility of typos, the .set() method can be used to set the amount to transfer.

use surrealdb::Surreal;
use surrealdb::engine::remote::ws::Ws;
use surrealdb::opt::auth::Root;

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let db = Surreal::new::<Ws>("localhost:8000").await?;

    db.signin(Root {
        username: "root".to_string(),
        password: "secret".to_string(),
    })
    .await?;

    db.use_ns("main").use_db("main").await?;

    // Set the parameter $amount for later use
    db.set("amount", 300).await?;

    let response = db
        .query(
            "
        // Start transaction
        BEGIN;
        // Setup accounts
        CREATE account:one SET balance = 135605.16;
        CREATE account:two SET balance = 91031.31;
        // Move money
        UPDATE account:one SET balance += $amount;
        UPDATE account:two SET balance -= $amount;
        // Finalise
        COMMIT;
        ",
        )
        .await?;

    println!("{response:#?}");

	// See if any errors were returned
	response.check()?;

	Ok(())
}