upsert()

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

Example usage

use serde::{Deserialize, Serialize};
use surrealdb::engine::remote::ws::Ws;
use surrealdb::opt::auth::Root;
use surrealdb::RecordId;
use surrealdb::Surreal;

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

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

#[derive(Debug, Serialize)]
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",
        password: "root",
    })
    .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;

.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

Example usage

use serde::{Deserialize, Serialize};
use surrealdb::engine::remote::ws::Ws;
use surrealdb::opt::auth::Root;
use surrealdb::Surreal;

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

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

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

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

    db.use_ns("ns").use_db("db").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

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 serde::{Deserialize, Serialize};
use surrealdb::engine::remote::ws::Ws;
use surrealdb::opt::auth::Root;
use surrealdb::opt::PatchOp;
use surrealdb::sql::Datetime;
use surrealdb::Surreal;

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

#[derive(Debug, Serialize, Deserialize)]
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",
        password: "root",
    })
    .await?;

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

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

Translated query

This function will run the following query in the database:

UPSERT $resource PATCH $data;