upsert

The .upsert() method for the SurrealDB Rust SDK upserts all or specific records in a table.

Upserts all records in a table, or a specific record.

Method Syntax

db.upsert(resource)

The .upsert() method is followed by second method that refers to the type of upsert to use: an upsert with .content(), .merge(), or .patch().

`.upsert().content()`

Upserts all records in a table, or a specific record, in the database.

Method Syntax

db.upsert(resource).content(data)

Note

This function replaces the current document / record data with the specified data.

Arguments

Argument	Type	Description
`resource`	The table name or the specific record ID to create.
`data`	The document / record data to insert.

Example usage

use surrealdb::Surreal;
use surrealdb::engine::remote::ws::Ws;
use surrealdb::opt::auth::Root;
use surrealdb::types::{RecordId, SurrealValue};

#[derive(Debug, SurrealValue)]
struct Person {
    id: RecordId,
    name: Option<String>,
    company: Option<String>,
    settings: Option<Settings>,
}

#[derive(Debug, SurrealValue)]
struct Settings {
    active: bool,
    marketing: bool,
}

#[derive(Debug, SurrealValue)]
struct Company {
    company: String,
}

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

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

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

    // upsert one record in a table
    let person: Option<Person> = db
        .upsert(("person", "jaime"))
        .content(Company {
            company: "SurrealDB".into(),
        })
        .await?;
    dbg!(person);
    Ok(())
}

Translated query

This function will run the following query in the database:

upsert $resource CONTENT $data;

Restrict records with `.range()`

For upserts against a whole table, use .range(...) together with .content, .merge, or .patch if only keys inside the range should be considered:

use surrealdb::{
    engine::any::connect,
    opt::Resource,
    types::{SurrealValue, ToSql, Value},
};

#[derive(SurrealValue)]
struct Content {
    second_half_of_alphabet: bool,
}

#[tokio::main]
async fn main() {
    let db = connect("memory").await.unwrap();
    db.use_ns("ns").use_db("db").await.unwrap();

    db.query("CREATE person:alucard, person:plato, person:vlad")
        .await
        .unwrap();

    let res = db
        .upsert::<Value>(Resource::from("person"))
        .range("n"..="z")
        .content(Content {
            second_half_of_alphabet: true,
        })
        .await
        .unwrap()
        .to_sql();
    println!("{res:?}");
}

`.upsert().merge()`

Modifies all records in a table, or a specific record, in the database.

Method Syntax

db.upsert(resource).merge(data)

Note

This function merges the current document / record data with the specified data.

Arguments

Argument	Description
`resource`	The table name or the specific record ID to create.
`data`	`resource`	The document / record data to insert.

Example usage

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

#[derive(Debug, SurrealValue, Default)]
struct Person {
    name: String,
    company: Option<String>,
    active: Option<bool>,
    marketing: Option<bool>,
}

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

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

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

    // upsert a single record
    let person: Option<Person> = db
        .upsert(("person", "tobie"))
        .merge(Person {
            name: "Tobie".into(),
            ..Default::default()
        })
        .await?;

    dbg!(person);
    Ok(())
}

Translated query

This function will run the following query in the database:

upsert $resource MERGE $data;

`.upsert().patch()`

Applies JSON Patch changes to all records, or a specific record, in the database.

Method Syntax

db.upsert(resource).patch(patch_op)

Note

This function patches the current document / record data with the specified JSON Patch data.

Arguments

Argument	Description
`resource`	The table name or the specific record ID to modify.
`data`	The JSON Patch data with which to modify the records.

Example usage

The .patch() method uses a struct called a PatchOp that contains the four methods add(), change(), remove(), and replace(). Each of these methods takes different arguments depending on the operation. For example, PathOp::remove() only takes a single argument (a path), while PathOp::replace() takes a second value for the replacement value.

use surrealdb::Surreal;
use surrealdb::engine::remote::ws::Ws;
use surrealdb::opt::PatchOp;
use surrealdb::opt::auth::Root;
use surrealdb::types::{Datetime, SurrealValue};

#[derive(Debug, SurrealValue, Default)]
struct Person {
    name: String,
    company: Option<String>,
    settings: Option<Settings>,
    created_at: Option<Datetime>,
    tags: Option<Vec<String>>,
}

#[derive(Debug, SurrealValue)]
struct Settings {
    active: bool,
}

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

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

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

    // upsert a record with a specific ID
    let person: Option<Person> = db
        .upsert(("person", "tobie"))
        .patch(PatchOp::replace("/name", "Tobie"))
        .patch(PatchOp::replace("/settings/active", false))
        .patch(PatchOp::add("/tags", ["developer", "engineer"]))
        .patch(PatchOp::remove("/company"))
        .await?;
    dbg!(person);
    Ok(())
}

Multiple operations with `PatchOps`

Chaining .patch() once per operation works, but each call adds a level of nesting to the builder. PatchOps collects the operations into a single value instead, and carries the same four methods so that they can be chained on one another.

use surrealdb::engine::any::connect;
use surrealdb::opt::PatchOps;
use surrealdb::types::{Datetime, SurrealValue};

#[derive(Debug, SurrealValue, Default)]
struct Person {
    name: String,
    company: Option<String>,
    settings: Option<Settings>,
    created_at: Option<Datetime>,
    tags: Option<Vec<String>>,
}

#[derive(Debug, SurrealValue)]
struct Settings {
    active: bool,
}

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

    db.query(
        "CREATE person:tobie SET name = 'Tobie', company = 'SurrealDB', settings = { active: true }",
    )
    .await?
    .check()?;

    let person: Option<Person> = db
        .upsert(("person", "tobie"))
        .patch(
            PatchOps::new()
                .replace("/settings/active", false)
                .add("/tags", ["developer", "engineer"])
                .remove("/company"),
        )
        .await?;
    dbg!(person);
    Ok(())
}

PatchOps is built up in order and applied in order, so an operation can depend on one before it. A single PatchOp still works wherever PatchOps is expected, as .patch() takes anything that converts into PatchOps, including a Vec<PatchOp>.

Note

Removing a field the target struct declares as non-optional will make the response fail to deserialise. Take the result as a Value, or declare the field as an Option, when a patch removes it.

Translated query

This function will run the following query in the database:

UPSERT $resource PATCH $data;

.upsert().content()

Arguments

Example usage

Translated query

Restrict records with .range()

.upsert().merge()

Arguments

Example usage

Translated query

.upsert().patch()

Arguments

Example usage

Multiple operations with PatchOps

Translated query

See also

`.upsert().content()`

Restrict records with `.range()`

`.upsert().merge()`

`.upsert().patch()`

Multiple operations with `PatchOps`