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.

The .query() method can take any number of statements, returning a Response that contains the results of each of them. In addition, the same method before being called returns a struct that also allows the same .query() method to be called on it, chaining the new query onto the existing query. This can help greatly with readability, as the example code below shows.

Getting started

Start a running database using the following command:

surreal start --user root --pass root

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

surrealdb % surreal sql --user root --pass root --ns namespace --db database --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 = "2.0.4"
tokio = "1.41.0"

Once this is done, copy and paste the following code to run a manual transaction that creates two account records and then transfers 300 units from one account to the other.

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

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

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

	db.use_ns("namespace").use_db("database").await?;

    let response = db

        // Start transaction
        .query("BEGIN")

        // Setup accounts
        .query("
            CREATE account:one SET balance = 135605.16;
            CREATE account:two SET balance = 91031.31;
        ")

        // Move money
        .query("
            UPDATE account:one SET balance += 300.00;
            UPDATE account:two SET balance -= 300.00;
        ")

        // Finalise
        .query("COMMIT")
        .await?;

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

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

	Ok(())
}

To avoid the possibility of typos, the .set()[/docs/sdk/rust/methods/set] method can be used to set the amount to transfer.

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

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

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

	db.use_ns("namespace").use_db("database").await?;

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

    let response = db

        // Start transaction
        .query("BEGIN")

        // Setup accounts
        .query("
            CREATE account:one SET balance = 135605.16;
            CREATE account:two SET balance = 91031.31;
        ")

        // Move money
        .query("
            UPDATE account:one SET balance += $transfer_amount;
            UPDATE account:two SET balance -= $transfer_amount;
        ")

        // Finalise
        .query("COMMIT")
        .await?;

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

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

	Ok(())
}