insert

insert adds one record or many to a table, and inserts graph edges through .relation().

Method Syntax

db.insert(resource).content(data);
db.insert(resource).relation(data);

Arguments

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

Example usage

Inserting a record with a specific ID:

use surrealdb::engine::any::connect;
use surrealdb::opt::auth::Root;
use surrealdb::types::SurrealValue;

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

#[derive(SurrealValue)]
struct Data {
    name: String,
    settings: Settings,
}

#[derive(Debug, SurrealValue)]
struct Person {
    name: String,
    settings: Settings,
}

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

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

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

    let person: Option<Person> = db
        .insert(("person", "tobie"))
        .content(Data {
            name: "Tobie".to_string(),
            settings: Settings {
                active: true,
                marketing: true,
            },
        })
        .await?;
    dbg!(person);
    Ok(())
}

Inserting multiple records into a table:

use surrealdb::engine::any::connect;
use surrealdb::opt::auth::Root;
use surrealdb::types::SurrealValue;

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

#[derive(SurrealValue)]
struct Data {
    name: String,
    settings: Settings,
}

#[derive(Debug, SurrealValue)]
struct Person {
    name: String,
    settings: Settings,
}

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

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

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

    let people: Vec<Person> = db
        .insert("person")
        .content(vec![
            Data {
                name: "Tobie".to_string(),
                settings: Settings {
                    active: true,
                    marketing: false,
                },
            },
            Data {
                name: "Jaime".to_string(),
                settings: Settings {
                    active: true,
                    marketing: true,
                },
            },
        ])
        .await?;
    dbg!(people);
    Ok(())
}

An example of two person records and one company record, followed by .insert().relation() to create a relation between them. Note the usage of the #[surreal(rename)] attribute to interface between the Rust struct Founded and the original relation table, which must have an in and an out field.

use surrealdb::engine::any::connect;
use surrealdb::opt::auth::Root;
use surrealdb::types::{RecordId, SurrealValue};

#[derive(SurrealValue)]
struct Data {
    id: RecordId,
    name: String,
}

#[derive(Debug, SurrealValue)]
struct Record {
    name: String,
    id: RecordId,
}

#[derive(Debug, SurrealValue)]
struct Founded {
    #[surreal(rename = "in")]
    founder: RecordId,
    #[surreal(rename = "out")]
    company: RecordId,
}

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

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

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

    let records: Vec<Record> = db
        .insert("person")
        .content(vec![
            Data {
                id: RecordId::new("person", "tobie"),
                name: "Tobie".to_string(),
            },
            Data {
                id: RecordId::new("person", "jaime"),
                name: "Jaime".to_string(),
            },
            Data {
                id: RecordId::new("company", "surrealdb"),
                name: "SurrealDB".to_string(),
            },
        ])
        .await?;
    dbg!(records);

    let founded: Vec<Founded> = db
        .insert("founded")
        .relation(vec![
            Founded {
                founder: RecordId::new("person", "tobie"),
                company: RecordId::new("company", "surrealdb"),
            },
            Founded {
                founder: RecordId::new("person", "jaime"),
                company: RecordId::new("company", "surrealdb"),
            },
        ])
        .await?;
    dbg!(founded);
    Ok(())
}

The equivalent SurrealQL statements to create and query the relations are:

RELATE [person:jaime, person:tobie]->founded->company:surrealdb;
SELECT ->founded->company FROM person;

Inserting many records with `ON DUPLICATE KEY UPDATE`

Given a Vec, .insert() sends one INSERT INTO <table> $data statement, so all the records in the example above are written in a single request. Writing the same statement with .query() does the same work, and is only needed for a clause that .insert() cannot express.

.insert() fails on a record that already exists, and has no way to express what should change instead. For that, write the INSERT statement with .query() and bind the whole Vec as one parameter. $input in the ON DUPLICATE KEY UPDATE clause is the new value for the record being updated.

use surrealdb::engine::any::connect;
use surrealdb::opt::auth::Root;
use surrealdb::types::{RecordId, SurrealValue};

#[derive(Debug, SurrealValue)]
struct Page {
    id: RecordId,
    crawled: bool,
}

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

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

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

    let pages = vec![
        Page { id: RecordId::new("page", "home"), crawled: false },
        Page { id: RecordId::new("page", "about"), crawled: false },
    ];

    let ids: Vec<RecordId> = db
        .query(
            "INSERT INTO page $pages
                ON DUPLICATE KEY UPDATE
                    crawled = crawled OR $input.crawled,
                    visits += 1
                RETURN VALUE id",
        )
        .bind(("pages", pages))
        .await?
        .take(0)?;
    dbg!(ids);
    Ok(())
}

A record in the array that does not exist yet is created from its value, and the ON DUPLICATE KEY UPDATE clause runs only for a record that already exists. The parameter takes the place of the whole value. This means that INSERT INTO page $pages is correct, while INSERT INTO page (id, crawled) VALUES $pages is a parse error, because VALUES expects literal tuples in parentheses.

Arguments

Example usage

Inserting many records with ON DUPLICATE KEY UPDATE

See also

Inserting many records with `ON DUPLICATE KEY UPDATE`