Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

SurrealDB

SurrealDB

What is SurrealDB

  • SurrealDB is a multi-model database written in Rust.

  • It has SDK for several languages. Here we'll learn about the Query language(s) of SurrealDB and how to use the database in Rust.

  • There are several ways to use the database:

  • We can use it embedded or as a separate server.

  • Embedded can work with in-memory databse (which is not persistent) or with on-disk backend.

  • When using as a separate server we can have one node or multiple nodes.

  • One SurrealDB can have multiple namespaces and each namespace can have multiple databases.

  • Each database has separate tables, indices, etc.

  • First we need to connect to the database.

  • Then we need to authenticate, if necessary.

  • then we select the namespace and the database.

  • SurrealDB for SQL developers

  • SurrealDB for MongoDB developers

SurrealDB in-memory database in Rust

  • kv-mem
  • Mem

Using the in-memory database can be very useful, especially in short-lived examples ike the ones we have here, but in other cases as well. It does not need any additional server component.

In this example we only setup the database connection, the namespace and the database without doing anything.

For the in-memory database we don't need authentication as only our process can access it.

[package]
name = "in-memory-setup"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
serde = { version = "1.0", features = ["derive"] }
surrealdb = { version = "2.0", features = ["kv-mem"] }
tokio = { version = "1.35", features = ["macros", "rt-multi-thread"] }

use surrealdb::engine::local::Mem;
use surrealdb::Surreal;

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

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

    Ok(())
}

SurrealDB with RocksDB backend in Rust embedded client with local database storage

  • SurrealDB

  • kv-rocksdb

  • RocksDB

  • We can also use local database files (just like in sqlite).

  • This version does not need an external database server either.

  • The compilation time is longer as we also compile the database backend, but this can be used as an embedded, but already persistan database.

  • No need for authentication here either.

[package]
name = "embedded-rocksdb"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
serde = { version = "1.0", features = ["derive"] }
surrealdb = { version = "2.0", features = ["kv-rocksdb"] }
tokio = { version = "1.35", features = ["macros", "rt-multi-thread"] }
  • This will create a folder called tempdb in the root of the crate. You could also give a path there to some other folder.
use surrealdb::engine::local::RocksDb;
use surrealdb::Surreal;

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

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

    Ok(())
}

Start SurrealDB in Docker

  • Install Docker
  • Create a volume to store the data
docker volume create my-surreal-db
  • Start the Docker container using a specific version of SurrealDB image:
docker run --detach --restart always --name surrealdb -p 127.0.0.1:8000:8000 --user root -v$(pwd):/external -v my-surreal-db:/database surrealdb/surrealdb:v2.0.4 start --user root --pass root --log trace file://database

For the current list of available docker tags check SurrealDB on the Docker HUB.

  • This one will listen on port 8000. You could tell it to listen on some other port. e.g. port 8001: -p 8001:8000.

  • Stop the container:

docker stop surrealdb
  • Remove the container
docker container rm surrealdb
  • Remove the volume
docker volume remove my-surreal-db

SurrealDB connect to server

  • Ws
  • signin
  • Root
  • use_ns
  • use_db
[package]
name = "connect-to-server"
version = "0.1.0"
edition = "2021"

[dependencies]
surrealdb = "2.0"
tokio = { version = "1.35", features = ["macros", "rt-multi-thread"] }
serde = { version = "1.0", features = ["derive"] }

use surrealdb::engine::remote::ws::Ws;
use surrealdb::opt::auth::Root;
use surrealdb::{Error, Surreal};

#[tokio::main]
async fn main() -> Result<(), Error> {
    println!("Connect to server.");
    let db = Surreal::new::<Ws>("localhost:8000").await?;

    println!("Authenticating as the root user.");
    db.signin(Root {
        username: "root",
        password: "root",
    })
    .await?;

    println!("Select a specific namespace and database.");
    db.use_ns("namespace").use_db("database").await?;

    Ok(())
}

CREATE and SELECT in Memory

  • Mem
  • Db
  • create
  • content
  • query
  • RecordId
  • Thing
use serde::{Deserialize, Serialize};
use surrealdb::engine::local::Db;
use surrealdb::engine::local::Mem;
use surrealdb::RecordId;
use surrealdb::Surreal;

#[derive(Debug, Serialize, Deserialize)]
struct Message {
    text: String,
}

#[derive(Debug, Serialize, Deserialize)]
struct MessageWithId {
    id: RecordId,
    text: String,
}

#[derive(Debug, Deserialize)]
struct Record {
    #[allow(dead_code)]
    id: RecordId,
}

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let dbh = Surreal::new::<Mem>(()).await?;

    dbh.use_ns("demo").use_db("insert-select-in-memory").await?;

    create_a_new_message_with_random_id(&dbh, "Hello World").await?;
    create_a_new_message_with_random_id(&dbh, "Another message").await?;
    println!();

    let messages = select_all_the_messages(&dbh).await?;
    for message in &messages {
        println!("{:?}", message);
    }
    assert_eq!(messages.len(), 2);
    assert_eq!(messages[0].text, "Another message");
    assert_eq!(messages[1].text, "Hello World");
    println!();

    let messages = select_all_the_messages_with_id(&dbh).await?;
    for message in &messages {
        println!("{:?}", message);
    }

    Ok(())
}

async fn select_all_the_messages(dbh: &Surreal<Db>) -> surrealdb::Result<Vec<Message>> {
    let mut response = dbh.query("SELECT * from messages ORDER BY text").await?;
    let messages: Vec<Message> = response.take(0)?;

    Ok(messages)
}

async fn select_all_the_messages_with_id(
    dbh: &Surreal<Db>,
) -> surrealdb::Result<Vec<MessageWithId>> {
    let mut response = dbh.query("SELECT * from messages ORDER BY name").await?;
    let messages: Vec<MessageWithId> = response.take(0)?;

    Ok(messages)
}

async fn create_a_new_message_with_random_id(
    dbh: &Surreal<Db>,
    message: &str,
) -> surrealdb::Result<()> {
    let created: Option<Record> = dbh
        .create("messages")
        .content(Message {
            text: message.to_owned(),
        })
        .await?;
    println!("created: {created:?}");

    Ok(())
}
[Record { id: Thing { tb: "messages", id: String("s727xixdknh1v7fco4l3") } }]
[Record { id: Thing { tb: "messages", id: String("tnj5imy5fx4v8qvurcv7") } }]
Hello World
Another message

Several ways to add a record to the database

  • We would like to add a record with a unique id generated by SurrealDB

  • Use a struct that does not have and id field and let the database assign one. Use a struct with the same fields and the id field to select the data.

  • Use a single struct where the id field is Option. Let the database generate the RecordId.

  • Use a single struct that has an id field and generate the id before calling create.

  • Use a query with a CREATE statement including an Id we generated previously.

  • Use q query with a CREATE statement without and id field.

SurrealDB CREATE SELECT in different ways

  • TODO
[package]
name = "create-select"
version = "0.1.0"
edition = "2021"

[dependencies]
surrealdb = { version = "2.0", features = ["kv-mem"] }
serde = { version = "1.0", features = ["derive"] }
tokio = { version = "1.35", features = ["macros", "rt-multi-thread"] }

use serde::{Deserialize, Serialize};
use surrealdb::engine::local::{Db, Mem};
use surrealdb::opt::Resource;
use surrealdb::sql::Id;
use surrealdb::{RecordId, RecordIdKey, Surreal};

#[derive(Debug, Serialize, Deserialize)]
struct Message {
    text: String,
}

#[derive(Debug, Serialize, Deserialize)]
struct MessageWithId {
    text: String,
    id: RecordId,
}

#[derive(Debug, Serialize, Deserialize)]
struct MessageWithOptionalId {
    text: String,
    id: Option<RecordId>,
}

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

    db.use_ns("demo").use_db("demo-2").await?;

    add_message_using_struct_without_id(&db, "A message").await?;
    add_message_using_struct_without_id_using_resource(&db, "B message").await?;
    add_message_using_struct_with_id(&db, "C message").await?;
    add_message_using_struct_with_optional_id(&db, "D message").await?;
    add_message_using_query_without_id(&db, "E message").await?;

    let messages = get_all(&db).await?;
    for message in &messages {
        println!("{:?}", message);
    }
    assert_eq!(messages.len(), 5);
    assert_eq!(messages[0].text, "A message");
    assert_eq!(messages[1].text, "B message");
    assert_eq!(messages[2].text, "C message");
    assert_eq!(messages[3].text, "D message");
    assert_eq!(messages[4].text, "E message");

    Ok(())
}

async fn get_all(dbh: &Surreal<Db>) -> surrealdb::Result<Vec<MessageWithId>> {
    let mut response = dbh.query("SELECT * from messages ORDER BY text").await?;
    let messages: Vec<MessageWithId> = response.take(0)?;

    Ok(messages)
}

async fn add_message_using_struct_without_id(
    db: &Surreal<Db>,
    text: &str,
) -> surrealdb::Result<()> {
    let message = Message {
        text: text.to_owned(),
    };

    let result: Option<MessageWithId> = db.create("messages").content(message).await?;
    println!("{:?}", result);
    assert!(result.is_some());
    assert_eq!(result.unwrap().text, text);

    Ok(())
}

async fn add_message_using_struct_without_id_using_resource(
    db: &Surreal<Db>,
    text: &str,
) -> surrealdb::Result<()> {
    let message = Message {
        text: text.to_owned(),
    };

    let result: surrealdb::Value = db
        .create(Resource::from("messages"))
        .content(message)
        .await?;
    println!("{:?}", result);
    //assert_eq!(result.unwrap().text, text);

    Ok(())
}

async fn add_message_using_struct_with_id(db: &Surreal<Db>, text: &str) -> surrealdb::Result<()> {
    let id = RecordId::from_table_key("messages", RecordIdKey::from(Id::ulid().to_string()));

    let message = MessageWithId {
        id,
        text: text.to_owned(),
    };

    let result: Option<MessageWithId> = db.create("messages").content(message).await?;
    println!("{:?}", result);
    assert!(result.is_some());
    assert_eq!(result.unwrap().text, text);

    Ok(())
}

async fn add_message_using_struct_with_optional_id(
    db: &Surreal<Db>,
    text: &str,
) -> surrealdb::Result<()> {
    let message = MessageWithOptionalId {
        id: None,
        text: text.to_owned(),
    };

    let result: Option<MessageWithId> = db.create("messages").content(message).await?;
    println!("{:?}", result);
    assert!(result.is_some());
    assert_eq!(result.unwrap().text, text);

    Ok(())
}

async fn add_message_using_query_without_id(db: &Surreal<Db>, text: &str) -> surrealdb::Result<()> {
    let response = db
        .query("CREATE messages SET  text=$text")
        .bind(("text", text.to_owned()))
        .await?;

    println!("{:?}", response);
    // assert!(result.is_some());
    // assert_eq!(result.unwrap().text, text);

    Ok(())
}

SurrealDB in-memory with SQL demo in Rust - CREATE (INSERT), SELECT, UPDATE, DELETE

  • Mem

  • DEFINE

  • CREATE

  • SELECT

  • UPDATE

  • DELETE

  • surrealdb::Result

  • First think would be to authenticate, but we are skipping that in these examples

  • then select the namespace and the database.

  • There can be as many namespaces as you want and each namespace can have multipled databases.

  • A namespace migh correspond to a department in a company and each database is for a product or (micro)service.

use serde::Deserialize;
use surrealdb::engine::local::{Db, Mem};
use surrealdb::Surreal;

#[derive(Debug, Deserialize)]
struct Person {
    name: String,
    phone: String,
}

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

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

    let _response = db
        .query("DEFINE INDEX person_email ON TABLE person COLUMNS name UNIQUE")
        .await?;

    let data = vec![("Joe", "123"), ("Jane", "456"), ("Jil", "789")];

    create(&db, data).await?;
    let all = get_all(&db).await?;
    assert_eq!(all[0].name, "Jane");
    assert_eq!(all[0].phone, "456");
    assert_eq!(all[1].name, "Jil");
    assert_eq!(all[1].phone, "789");
    assert_eq!(all[2].name, "Joe");
    assert_eq!(all[2].phone, "123");
    list_all(all);

    update(&db).await?;
    let all = get_all(&db).await?;
    list_all(all);

    delete(&db).await?;
    let all = get_all(&db).await?;
    list_all(all);

    match create(&db, vec![("Joe", "7777777")]).await {
        Ok(_) => println!("this will not happen as the previous statement returns an Error"),
        Err(err) => println!("The Error: {err}"),
    };
    println!("-------------");

    let all = get_all(&db).await?;
    list_all(all);

    Ok(())
}

async fn create(db: &Surreal<Db>, data: Vec<(&str, &str)>) -> surrealdb::Result<()> {
    for (name, phone) in data {
        let response = db
            .query("CREATE person SET  name=$name, phone=$phone")
            .bind(("name", name.to_owned()))
            .bind(("phone", phone.to_owned()))
            .await?;

        match response.check() {
            Ok(_) => {}
            Err(err) => {
                println!("Could not add person: '{}'", err);
                return Err(err);
            }
        };
    }
    Ok(())
}

async fn get_all(db: &Surreal<Db>) -> surrealdb::Result<Vec<Person>> {
    let mut entries = db
        .query("SELECT name, phone FROM type::table($table) ORDER BY name ASC")
        .bind(("table", "person"))
        .await?;
    let entries: Vec<Person> = entries.take(0)?;
    Ok(entries)
}

fn list_all(entries: Vec<Person>) {
    for entry in entries {
        println!("{}: {}", entry.name, entry.phone);
    }
    println!("-------------");
}

async fn update(db: &Surreal<Db>) -> surrealdb::Result<()> {
    let name = "Jane";
    let phone = "55555555";

    let response = db
        .query("UPDATE entry SET phone=$phone WHERE name=$name")
        .bind(("name", name))
        .bind(("phone", phone))
        .await?;

    match response.check() {
        Ok(_) => Ok(()),
        Err(err) => {
            eprintln!("Could not update entry {}", err);
            Err(err)
        }
    }
}

async fn delete(db: &Surreal<Db>) -> surrealdb::Result<()> {
    let name = "Jane";

    let response = db
        .query("DELETE entry WHERE name=$name")
        .bind(("name", name))
        .await?;

    match response.check() {
        Ok(_) => Ok(()),
        Err(err) => {
            eprintln!("Could not delete entry {}", err);
            Err(err)
        }
    }
}
cargo run -q > out.out 2>&1

Jane: 456
Jil: 789
Joe: 123
-------------
Jane: 456
Jil: 789
Joe: 123
-------------
Jane: 456
Jil: 789
Joe: 123
-------------
Could not add person: 'Database index `person_email` already contains 'Joe', with record `person:kurmttdvdvv9u7x5i83w`'
The Error: Database index `person_email` already contains 'Joe', with record `person:kurmttdvdvv9u7x5i83w`
-------------
Jane: 456
Jil: 789
Joe: 123
-------------

SurrealDB experiments

  • TODO
[package]
name = "create-select"
version = "0.1.0"
edition = "2021"

[dependencies]
surrealdb = { version = "2.0", features = ["kv-mem"] }
serde = { version = "1.0", features = ["derive"] }
tokio = { version = "1.35", features = ["macros", "rt-multi-thread"] }

use serde::{Deserialize, Serialize};
use surrealdb::engine::local::{Db, Mem};
use surrealdb::opt::Resource;
use surrealdb::Surreal;

#[derive(Debug, Serialize, Deserialize)]
struct Group {
    name: String,
}

// #[derive(Debug, Serialize, Deserialize)]
// struct User {
//     name: String,
// }

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

    db.use_ns("demo").use_db("demo-2").await?;

    list(&db).await?;
    add_group(&db, "Mavens").await?;
    //    add_user(&db, "Mavens", "Jane").await?;

    Ok(())
}

async fn list(db: &Surreal<Db>) -> surrealdb::Result<()> {
    println!("list");
    let groups: Vec<Group> = db.select("groups").await?;
    for group in groups {
        println!("{:?}", group);
    }

    //  let mut response = db.query("SELECT * FROM groups").await?;
    // let entries = response.take(0);
    //         println!("{}", entries);
    //     }

    Ok(())
}

async fn add_group(db: &Surreal<Db>, group_name: &str) -> surrealdb::Result<()> {
    println!("Add group '{group_name}'");
    let group = Group {
        name: group_name.to_owned(),
    };

    let result = db.create(Resource::from("groups")).content(group).await?;
    println!("{}", result);

    Ok(())
}

// async fn add_user(db: &Surreal<Db>, user_name: &str, group_name: &str) -> surrealdb::Result<()> {
//     println!("Add user '{user_name}' to group '{group_name}'");
//     // TODO get the ID of the group

//     Ok(())
// }

SurrealDB - REMOVE NAMESPACE

[package]
name = "remove-namespace"
version = "0.1.0"
edition = "2021"

[dependencies]
serde = { version = "1.0", features = ["derive"] }
surrealdb = { version = "2.0", features = ["kv-mem"] }
tokio = { version = "1.35", features = ["macros", "rt-multi-thread"] }

use serde::{Deserialize, Serialize};
use surrealdb::engine::local::{Db, Mem};
use surrealdb::Surreal;

#[derive(Debug, Deserialize, Serialize)]
struct Entry {
    name: String,
}

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let dbh = Surreal::new::<Mem>(()).await?;
    let namespace = "name-space";
    let database = "data-base";

    dbh.use_ns(namespace).use_db(database).await?;

    entries("Before", &dbh).await;

    let response = dbh
        .query("INSERT INTO people (name) VALUES ($name);")
        .bind(("name", "Foo"))
        .await?;
    response.check()?;

    let response = dbh
        .query("INSERT INTO people (name) VALUES ($name);")
        .bind(("name", "Bar"))
        .await?;
    response.check()?;

    entries("Data added", &dbh).await;

    let result = dbh
        .query(format!("REMOVE NAMESPACE `{namespace}`;"))
        .await?;
    result.check()?;
    //println!("{:?}", result);

    entries("After remove", &dbh).await;

    Ok(())
}

async fn entries(text: &str, dbh: &Surreal<Db>) {
    let mut rows = dbh.query("SELECT name FROM people").await.unwrap();
    let people: Vec<Entry> = rows.take(0).unwrap();
    println!("---- {text}: {}", people.len());
    for person in people {
        println!("{}", person.name);
    }
}
---- Before: 0
---- Data added: 2
Foo
Bar
---- After remove: 0

SurrealDB - CREATE, SELECT, DELETE

  • CREATE
  • SELECT
  • DELETE
use serde::{Deserialize, Serialize};
use surrealdb::engine::local::{Db, Mem};
use surrealdb::opt::Resource;
use surrealdb::Surreal;

#[derive(Debug, Serialize, Deserialize)]
struct Fruit {
    name: String,
}

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let dbh = Surreal::new::<Mem>(()).await?;

    dbh.use_ns("demo").use_db("demo-time").await?;

    let list = get_list(&dbh).await?;
    show_list(&list);
    assert!(list.is_empty());

    for name in ["apple", "banana"] {
        let fruit = Fruit {
            name: name.to_owned(),
        };
        let _result = dbh.create(Resource::from("fruits")).content(fruit).await?;
        //println!("{}", result);
    }
    let list = get_list(&dbh).await?;
    show_list(&list);
    assert_eq!(list.len(), 2);

    let _response = dbh
        .query("DELETE fruits WHERE name=$name")
        .bind(("name", "apple"))
        .await?
        .check();
    //println!("{:?}", response);
    let list = get_list(&dbh).await?;
    show_list(&list);
    assert_eq!(list.len(), 1);
    assert_eq!(list[0].name, "banana");

    Ok(())
}

async fn get_list(dbh: &Surreal<Db>) -> surrealdb::Result<Vec<Fruit>> {
    let fruits: Vec<Fruit> = dbh.select("fruits").await?;
    Ok(fruits)
}

fn show_list(list: &Vec<Fruit>) {
    println!("List:");
    for fruit in list {
        println!("   {:?}", fruit);
    }
}
List:
List:
   Fruit { name: "apple" }
   Fruit { name: "banana" }
List:
   Fruit { name: "banana" }

SurrealDB - Datetime with Chrono

use serde::{Deserialize, Serialize};
use surrealdb::engine::local::{Db, Mem};
use surrealdb::opt::Resource;
use surrealdb::Surreal;

use chrono::{DateTime, Utc};

#[derive(Debug, Serialize, Deserialize)]
struct Fruit {
    name: String,
    date: DateTime<Utc>,
}

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let dbh = Surreal::new::<Mem>(()).await?;
    dbh.use_ns("demo").use_db("demo-time").await?;

    let _response = dbh.query("DELETE fruits").await?.check();
    list(&dbh).await?;

    for name in ["apple", "banana"] {
        let fruit = Fruit {
            name: name.to_owned(),
            date: Utc::now(),
        };
        let _result = dbh.create(Resource::from("fruits")).content(fruit).await?;
        //println!("{}", result);
    }
    list(&dbh).await?;

    let _response = dbh
        .query("DELETE fruits WHERE name=$name")
        .bind(("name", "apple"))
        .await?
        .check();
    //println!("{:?}", response);
    list(&dbh).await?;

    Ok(())
}

async fn list(dbh: &Surreal<Db>) -> surrealdb::Result<()> {
    let utc: DateTime<Utc> = Utc::now();
    let fruits: Vec<Fruit> = dbh.select("fruits").await?;
    println!("List:");
    for fruit in fruits {
        println!("   {:?}", fruit);
        let elapsed = utc - fruit.date;
        println!("{} microseconds", elapsed.num_microseconds().unwrap());
    }

    Ok(())
}
List:
List:
   Fruit { name: "apple", date: 2024-08-20T13:39:19.962090693Z }
1134 microseconds
   Fruit { name: "banana", date: 2024-08-20T13:39:19.962687672Z }
537 microseconds
List:
   Fruit { name: "banana", date: 2024-08-20T13:39:19.962687672Z }
1839 microseconds

SurrealDB - CREATE, SELECT, UPDATE, DELETE

  • update
use serde::{Deserialize, Serialize};
use surrealdb::engine::local::{Db, Mem};
use surrealdb::Surreal;

#[derive(Debug, Serialize, Deserialize)]
struct Fruit {
    name: String,
    color: String,
}

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let dbh = Surreal::new::<Mem>(()).await?;
    dbh.use_ns("demo").use_db("demo-time").await?;

    let _response = dbh.query("DELETE fruits").await?.check();
    list(&dbh).await?;
    let mut id = 0;

    let fruits = vec![
        Fruit {
            name: String::from("apple"),
            color: String::from("yellow"),
        },
        Fruit {
            name: String::from("banana"),
            color: String::from("yellow"),
        },
    ];

    for raw_fruit in fruits {
        id += 1;
        let fruit: Fruit = dbh
            .create(("fruits", id))
            .content(raw_fruit)
            .await?
            .unwrap();
        println!("fruit: {:?}", fruit);
    }
    list(&dbh).await?;

    let _response = dbh
        .query("UPDATE fruits SET color=$color WHERE name=$name")
        .bind(("name", "apple"))
        .bind(("color", "red"))
        .await?
        .check();
    //println!("{:?}", response);
    list(&dbh).await?;

    let fruit = Fruit {
        name: String::from("apple"),
        color: String::from("green"),
    };

    //let _result = dbh.update(Resource::from("fruits")).content(fruit).await?;

    let _fruit: Option<Fruit> = dbh.update(("fruits", 1)).content(fruit).await?;

    list(&dbh).await?;
    Ok(())
}

async fn list(dbh: &Surreal<Db>) -> surrealdb::Result<()> {
    let fruits: Vec<Fruit> = dbh.select("fruits").await?;
    println!("List:");
    for fruit in fruits {
        println!("   {:?}", fruit);
    }
    println!("----------");

    Ok(())
}
List:
----------
db: Fruit { name: "apple", color: "yellow" }
db: Fruit { name: "banana", color: "yellow" }
List:
   Fruit { name: "apple", color: "yellow" }
   Fruit { name: "banana", color: "yellow" }
----------
List:
   Fruit { name: "apple", color: "red" }
   Fruit { name: "banana", color: "yellow" }
----------
List:
   Fruit { name: "apple", color: "green" }
   Fruit { name: "banana", color: "yellow" }
----------

SurrealDB - RSVP

  • Set a value to true or false and if it does not exist, create it first with the appropriate starting value
use serde::{Deserialize, Serialize};
use surrealdb::engine::local::{Db, Mem};
use surrealdb::{RecordId, Surreal};

#[allow(clippy::upper_case_acronyms)]
#[derive(Debug, Serialize, Deserialize)]
struct RSVP {
    uid: u32,
    status: bool,
}

#[derive(Debug, Deserialize)]
struct Record {
    #[allow(dead_code)]
    id: RecordId,
}

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let dbh = Surreal::new::<Mem>(()).await?;

    dbh.use_ns("demo").use_db("demo").await?;
    let _response = dbh
        .query("DEFINE INDEX rsvp_id ON TABLE rsvp COLUMNS uid UNIQUE")
        .await?;

    let _response = dbh.query("DELETE rsvp").await?.check();
    list(&dbh).await?;

    set(&dbh, 1, true).await?;
    set(&dbh, 2, true).await?;
    set(&dbh, 3, false).await?;

    list(&dbh).await?;

    set(&dbh, 1, false).await?;
    set(&dbh, 3, true).await?;

    list(&dbh).await?;

    Ok(())
}

async fn set(dbh: &Surreal<Db>, uid: u32, status: bool) -> surrealdb::Result<()> {
    //println!("set {uid} to status: {status}");

    let mut response = dbh
        .query("SELECT * FROM rsvp where uid=$uid")
        .bind(("uid", uid))
        .await?;
    let rows: Vec<RSVP> = response.take(0)?;
    if let Some(_rsvp) = rows.first() {
        //println!("Update: {rsvp:?} with {status}");
        dbh.query("UPDATE rsvp SET status=$status WHERE uid=$uid")
            .bind(("status", status))
            .bind(("uid", uid))
            .await?;
    } else {
        let _created: Option<Record> = dbh.create("rsvp").content(RSVP { uid, status }).await?;
        //println!("created: {created:?}");
    }

    Ok(())
}

async fn list(db: &Surreal<Db>) -> surrealdb::Result<()> {
    let statuses: Vec<RSVP> = db.select("rsvp").await?;
    println!("List:");
    for status in statuses {
        println!("   {:?}", status);
    }

    Ok(())
}
List:
List:
   RSVP { uid: 3, status: false }
   RSVP { uid: 1, status: true }
   RSVP { uid: 2, status: true }
List:
   RSVP { uid: 3, status: true }
   RSVP { uid: 1, status: false }
   RSVP { uid: 2, status: true }

SurrealDB - toggle

  • TODO: The error handling should be improved
use serde::{Deserialize, Serialize};
use surrealdb::engine::local::{Db, Mem};
use surrealdb::{RecordId, Surreal};

#[derive(Debug, Serialize, Deserialize)]
struct Toggle {
    uid: u32,
    status: bool,
}

#[derive(Debug, Deserialize)]
struct Record {
    #[allow(dead_code)]
    id: RecordId,
}

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let dbh = Surreal::new::<Mem>(()).await?;

    dbh.use_ns("demo").use_db("demo").await?;

    let _response = dbh
        .query("DEFINE INDEX toggle_id ON TABLE toggle COLUMNS uid UNIQUE")
        .await?;

    list(&dbh).await?;

    set(&dbh, 1, true).await?;
    set(&dbh, 2, true).await?;
    set(&dbh, 3, false).await?;

    list(&dbh).await?;

    toggle_status(&dbh, 1).await?;
    toggle_status(&dbh, 3).await?;

    //toggle_status(&dbh, 4).await?;
    list(&dbh).await?;

    Ok(())
}

async fn set(dbh: &Surreal<Db>, uid: u32, status: bool) -> surrealdb::Result<()> {
    //println!("set {uid} to status: {status}");

    let _created: Option<Record> = dbh.create("toggle").content(Toggle { uid, status }).await?;

    Ok(())
}

async fn toggle_status(dbh: &Surreal<Db>, uid: u32) -> surrealdb::Result<()> {
    let mut response = dbh
        .query("SELECT * FROM toggle where uid=$uid")
        .bind(("uid", uid))
        .await?;
    let rows: Vec<Toggle> = response.take(0)?;
    let Some(toggle) = rows.first() else {
        return Ok(()); // Should return Err()
    };

    dbh.query("UPDATE toggle SET status=$status WHERE uid=$uid")
        .bind(("status", !toggle.status))
        .bind(("uid", uid))
        .await?;

    Ok(())
}

async fn list(db: &Surreal<Db>) -> surrealdb::Result<()> {
    let statuses: Vec<Toggle> = db.select("toggle").await?;
    println!("List:");
    for status in statuses {
        println!("   {:?}", status);
    }

    Ok(())
}
List:
List:
   RSVP { uid: 3, status: false }
   RSVP { uid: 1, status: true }
   RSVP { uid: 2, status: true }
List:
   RSVP { uid: 3, status: true }
   RSVP { uid: 1, status: false }
   RSVP { uid: 2, status: true }

Multi-counter with embedded SurrealDB database

[package]
name = "cli-multi-counter"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
serde = { version = "1.0", features = ["derive"] }
surrealdb = { version = "2.0", features = ["kv-rocksdb"] }
tempdir = "0.3"
tokio = { version = "1.35", features = ["macros", "rt-multi-thread"] }

use serde::{Deserialize, Serialize};
use surrealdb::engine::local::{Db, RocksDb};
use surrealdb::Surreal;

#[derive(Debug, Deserialize, Serialize)]
struct Entry {
    name: String,
    count: u32,
}

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let args = std::env::args().collect::<Vec<String>>();

    let database_folder = match std::env::var("DATABASE_PATH") {
        Ok(val) => std::path::PathBuf::from(val),
        Err(_) => {
            let current_dir = std::env::current_dir().unwrap();
            current_dir.join("db")
        }
    };

    let db = Surreal::new::<RocksDb>(database_folder).await?;

    db.use_ns("counter_ns").use_db("counter_db").await?;

    // Maybe do this only when we create the database
    let _response = db
        .query("DEFINE INDEX counter_name ON TABLE counter COLUMNS name UNIQUE")
        .await?;

    if 2 < args.len() {
        eprintln!("Usage: {} NAME     to count NAME", args[0]);
        eprintln!("       {}          to list all the counters", args[0]);
        std::process::exit(1);
    }

    if 2 == args.len() {
        increment(&db, &args[1]).await?;
        return Ok(());
    }

    println!("Listing counters");
    println!("----------------");
    let mut entries = db
        .query("SELECT name, count FROM counter ORDER BY count DESC")
        .await?;
    let entries: Vec<Entry> = entries.take(0)?;
    for entry in entries {
        println!("{}: {}", entry.name, entry.count);
    }

    Ok(())
}

async fn increment(db: &Surreal<Db>, name: &str) -> surrealdb::Result<()> {
    let response = db
        .query("INSERT INTO counter (name, count) VALUES ($name, $count) ON DUPLICATE KEY UPDATE count += 1;")
        .bind(("name", name.to_owned()))
        .bind(("count", 1))
        .await?;

    match response.check() {
        Ok(mut entries) => {
            let entries: Vec<Entry> = entries.take(0)?;
            // fetching the first (and hopefully only) entry
            if let Some(entry) = entries.into_iter().next() {
                println!("{}", entry.count);
            }

            Ok(())
        }
        Err(err) => {
            eprintln!("Could not add entry {}", err);
            std::process::exit(2);
        }
    }
}
#![allow(unused)]
fn main() {
use std::{
    os::unix::process::ExitStatusExt,
    process::{Command, ExitStatus},
};
use tempdir::TempDir;

#[test]
fn test_counter() {
    let tmp_dir = TempDir::new("counter").unwrap();
    println!("tmp_dir: {:?}", tmp_dir);

    std::env::set_var("DATABASE_PATH", tmp_dir.path());

    check("foo", "1\n");
    check("foo", "2\n");
    check("foo", "3\n");
    check("bar", "1\n");
    check("bar", "2\n");
    check("foo", "4\n");

    let result = Command::new("cargo")
        .args(["run", "-q"])
        .output()
        .expect("command failed to start");

    assert_eq!(
        std::str::from_utf8(&result.stdout).unwrap(),
        "Listing counters\n----------------\nfoo: 4\nbar: 2\n"
    );
    assert_eq!(std::str::from_utf8(&result.stderr).unwrap(), "");
    assert_eq!(result.status, ExitStatus::from_raw(0));

    drop(tmp_dir);
}

fn check(name: &str, expected_stdout: &str) {
    let result = Command::new("cargo")
        .args(["run", "-q", name])
        .output()
        .expect("command failed to start");

    assert_eq!(
        std::str::from_utf8(&result.stdout).unwrap(),
        expected_stdout
    );
    assert_eq!(std::str::from_utf8(&result.stderr).unwrap(), "");
    assert_eq!(result.status, ExitStatus::from_raw(0));
}
}

Get version of SurrealDB

//use surrealdb::engine::remote::ws::Ws;
use surrealdb::engine::local::Mem;
use surrealdb::Surreal;

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

    let version = db.version().await?;

    println!("{version:?}");
    println!("{version}");

    Ok(())
}
Version { major: 1, minor: 5, patch: 3 }
1.5.3

Generate ID in SurrealDB

use surrealdb::sql::Id;

fn main() {
    let id = Id::rand(); // Generate a new random ID
    println!("id {id:?}");
    println!("id {id}");
    println!("id {}", id.to_raw());
    println!();

    let ulid = Id::ulid(); // Generate a new random ULID = Universally Unique Lexicographically Sortable Identifier https://github.com/ulid/spec
    println!("ulid {ulid:?}");
    println!("ulid {ulid}");
    println!("ulid {}", ulid.to_raw());
    println!();

    let uuid = Id::uuid(); // Generate a new random UUID
    println!("uuid {uuid:?}");
    println!("uuid {uuid}");
    println!("uuid {}", uuid.to_raw());
}
id String("jcgh05b5adyoh2jv15qq")
id jcgh05b5adyoh2jv15qq
id jcgh05b5adyoh2jv15qq

ulid String("01J7JN8XSV1FJRT86XQDMEGVE3")
ulid 01J7JN8XSV1FJRT86XQDMEGVE3
ulid 01J7JN8XSV1FJRT86XQDMEGVE3

uuid String("0191e554-773b-77d2-9a97-875a81b3c0b3")
uuid ⟨0191e554-773b-77d2-9a97-875a81b3c0b3⟩
uuid 0191e554-773b-77d2-9a97-875a81b3c0b3

Generate Thing in SurrealDB

use surrealdb::sql::{Id, Thing};

fn main() {
    let id = Thing::from(("person", Id::rand()));
    println!("id {id:?}");
    println!("id {id}");
    println!("id {}", id.to_raw());
    println!();

    // Generate a new random ULID = Universally Unique Lexicographically Sortable Identifier https://github.com/ulid/spec
    let ulid = Thing::from(("person", Id::ulid()));
    println!("ulid {ulid:?}");
    println!("ulid {ulid}");
    println!("ulid {}", ulid.to_raw());
    println!();

    let uuid = Thing::from(("person", Id::uuid()));
    println!("uuid {uuid:?}");
    println!("uuid {uuid}");
    println!("uuid {}", uuid.to_raw());
}
id Thing { tb: "person", id: String("qyi311f3aol1vj5lj9jm") }
id person:qyi311f3aol1vj5lj9jm
id person:qyi311f3aol1vj5lj9jm

ulid Thing { tb: "person", id: String("01J7JWR2N1M9DBX7S0PS29VEZ3") }
ulid person:01J7JWR2N1M9DBX7S0PS29VEZ3
ulid person:01J7JWR2N1M9DBX7S0PS29VEZ3

uuid Thing { tb: "person", id: String("0191e5cc-0aa2-7213-91e7-9e6270853975") }
uuid person:⟨0191e5cc-0aa2-7213-91e7-9e6270853975⟩
uuid person:⟨0191e5cc-0aa2-7213-91e7-9e6270853975⟩

Map field to id of other table (FOREIGN KEY)

use serde::{Deserialize, Serialize};
use surrealdb::engine::local::{Db, Mem};
use surrealdb::sql::{Id, Thing};
use surrealdb::Surreal;

const DANCE: &str = "dance";
const STUDENT: &str = "student";

#[derive(Debug, Serialize, Deserialize)]
struct DanceClass {
    id: Thing,
    name: String,
}

#[derive(Debug, Serialize, Deserialize)]
struct Student {
    id: Thing,
    name: String,
    classes: Vec<Thing>,
}

#[derive(Debug, Deserialize)]
#[allow(dead_code)]
struct StudentClasses {
    id: Thing,
    name: String,
    classes: Vec<DanceClass>,
}

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

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

    add_classes(&db).await?;
    let classes = get_classes(&db).await?;

    // the next lines are valid but then will lead to an error when fetching the data
    // classes.push(DanceClass {
    //     id: Thing::from((DANCE, Id::rand())),
    //     name: String::from("Belly dance"),
    // });

    add_students(&db, classes).await?;

    show_students_in_classes(&db).await?;

    Ok(())
}

async fn add_classes(db: &Surreal<Db>) -> surrealdb::Result<()> {
    for name in ["Introduction to Dancing", "Flamenco"] {
        let classes: Vec<DanceClass> = db
            .create(DANCE)
            .content(DanceClass {
                id: Thing::from((DANCE, Id::rand())),
                name: name.to_owned(),
            })
            .await?;
        println!("class added: {classes:?}");
    }

    Ok(())
}

async fn get_classes(db: &Surreal<Db>) -> surrealdb::Result<Vec<DanceClass>> {
    let sql = "SELECT * FROM dance";
    let mut results = db.query(sql).await?;
    let classes: Vec<DanceClass> = results.take(0)?;

    for class in &classes {
        println!("get_classes: {class:?}");
    }

    Ok(classes)
}

async fn add_students(db: &Surreal<Db>, classes: Vec<DanceClass>) -> surrealdb::Result<()> {
    let students: Vec<Student> = db
        .create(STUDENT)
        .content(Student {
            id: Thing::from((STUDENT, Id::rand())),
            name: "Jane Doe".to_owned(),
            classes: classes.into_iter().map(|class| class.id).collect(),
        })
        .await?;
    println!("student added: {students:#?}");

    Ok(())
}

async fn show_students_in_classes(db: &Surreal<Db>) -> surrealdb::Result<()> {
    let sql = format!("SELECT * FROM {STUDENT} FETCH classes");
    let mut results = db.query(sql).await?;
    let students: Vec<StudentClasses> = results.take(0)?;
    println!("Students: {students:#?}");

    Ok(())
}
class added: [DanceClass { id: Thing { tb: "dance", id: String("gjjhx7vr62vaiqr49ivr") }, name: "Introduction to Dancing" }]
class added: [DanceClass { id: Thing { tb: "dance", id: String("c4v2dpfzsreg1u5nleeq") }, name: "Flamenco" }]
get_classes: DanceClass { id: Thing { tb: "dance", id: String("c4v2dpfzsreg1u5nleeq") }, name: "Flamenco" }
get_classes: DanceClass { id: Thing { tb: "dance", id: String("gjjhx7vr62vaiqr49ivr") }, name: "Introduction to Dancing" }
student added: [
    Student {
        id: Thing {
            tb: "student",
            id: String(
                "h8c5rpmyhb3p3l0yu7al",
            ),
        },
        name: "Jane Doe",
        classes: [
            Thing {
                tb: "dance",
                id: String(
                    "c4v2dpfzsreg1u5nleeq",
                ),
            },
            Thing {
                tb: "dance",
                id: String(
                    "gjjhx7vr62vaiqr49ivr",
                ),
            },
        ],
    },
]
Students: [
    StudentClasses {
        id: Thing {
            tb: "student",
            id: String(
                "h8c5rpmyhb3p3l0yu7al",
            ),
        },
        name: "Jane Doe",
        classes: [
            DanceClass {
                id: Thing {
                    tb: "dance",
                    id: String(
                        "c4v2dpfzsreg1u5nleeq",
                    ),
                },
                name: "Flamenco",
            },
            DanceClass {
                id: Thing {
                    tb: "dance",
                    id: String(
                        "gjjhx7vr62vaiqr49ivr",
                    ),
                },
                name: "Introduction to Dancing",
            },
        ],
    },
]

SurrealDB Datetime

  • Datetime
use serde::{Deserialize, Serialize};
use surrealdb::engine::local::Mem;
use surrealdb::sql::{Datetime, Id, Thing};
use surrealdb::Surreal;

const EVENTS: &str = "events";

#[derive(Debug, Serialize, Deserialize)]
struct Event {
    id: Thing,
    title: String,
    date: Datetime,
}

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let db = Surreal::new::<Mem>(()).await?;
    db.use_ns("namespace").use_db("database").await?;

    let _events: Vec<Event> = db
        .create(EVENTS)
        .content(Event {
            id: Thing::from((EVENTS, Id::rand())),
            title: String::from("Introduction to Rust"),
            date: Datetime::default(),
        })
        .await?;

    let events: Vec<Event> = db.select(EVENTS).await?;
    for event in events {
        println!("{event:#?}");
    }

    Ok(())
}
Event {
    id: Thing {
        tb: "events",
        id: String(
            "cxgerehhr27m9gsx5vwg",
        ),
    },
    title: "Introduction to Rust",
    date: Datetime(
        2024-09-14T08:33:03.723138054Z,
    ),
}

Add column to table without a schema

  • In this example we don't have a schema
  • First we have a table with a single column. We add data. List the data.
  • Then we would like to add a second column. We have two options. In the Second example we marked the new field to be Option. That way we can use the data with and without a value in the new column.
  • In the Third example we require the new column. This means we cannot use the SELECT until we add values for the new column in every row in the table.
[package]
name = "add-columns-without-schema"
version = "0.1.0"
edition = "2021"

[dependencies]
serde = { version = "1.0", features = ["derive"] }
surrealdb = { version = "2.0", features = ["kv-mem"] }
tokio = { version = "1.35", features = ["macros", "rt-multi-thread"] }

use serde::{Deserialize, Serialize};
use surrealdb::engine::local::{Db, Mem};
use surrealdb::sql::{Id, Thing};
use surrealdb::Surreal;

#[derive(Debug, Deserialize, Serialize)]
struct First {
    id: Thing,
    name: String,
}

#[derive(Debug, Deserialize, Serialize)]
struct Second {
    id: Thing,
    name: String,
    rgb: Option<String>,
}

#[derive(Debug, Deserialize, Serialize)]
struct Third {
    id: Thing,
    name: String,
    rgb: String,
}

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let dbh = Surreal::new::<Mem>(()).await?;

    dbh.use_ns("demo").use_db("demo").await?;

    add_color(&dbh, "Red").await?;
    add_color(&dbh, "Green").await?;

    let entries = get_first(&dbh).await?;
    assert_eq!(entries.len(), 2);
    assert_eq!(entries[0].name, "Red");
    assert_eq!(entries[1].name, "Green");
    list_first(entries);

    let entries = get_second(&dbh).await?;
    assert_eq!(entries.len(), 2);
    assert_eq!(entries[0].name, "Red");
    assert_eq!(entries[1].name, "Green");
    assert_eq!(entries[0].rgb, None);
    list_second(entries);

    add_color_with_rgb(&dbh, "Blue", "0000FF").await?;

    let entries = get_second(&dbh).await?;
    assert_eq!(entries.len(), 3);
    assert_eq!(entries[0].name, "Red");
    assert_eq!(entries[1].name, "Green");
    assert_eq!(entries[2].name, "Blue");
    assert_eq!(entries[0].rgb, None);
    assert_eq!(entries[1].rgb, None);
    assert_eq!(entries[2].rgb, Some(String::from("0000FF")));
    list_second(entries);

    // We can't do this because the table doesn't have the rgb column
    // let entries = get_third(&dbh).await?;
    // Error: Db(Serialization("failed to deserialize; expected a string, found None"))

    add_missing_rgb(&dbh, "Red", "FF0000").await?;
    add_missing_rgb(&dbh, "Green", "00FF00").await?;

    let entries = get_third(&dbh).await?;
    assert_eq!(entries.len(), 3);
    assert_eq!(entries[0].name, "Red");
    assert_eq!(entries[1].name, "Green");
    assert_eq!(entries[2].name, "Blue");
    assert_eq!(entries[0].rgb, "FF0000");
    assert_eq!(entries[1].rgb, "00FF00");
    assert_eq!(entries[2].rgb, "0000FF");
    list_third(entries);

    Ok(())
}

async fn add_color(db: &Surreal<Db>, name: &str) -> surrealdb::Result<()> {
    let _response: Option<First> = db
        .create("colors")
        .content(First {
            id: Thing::from(("colors", Id::ulid())),
            name: name.to_owned(),
        })
        .await?;

    Ok(())
}

async fn get_first(db: &Surreal<Db>) -> surrealdb::Result<Vec<First>> {
    let mut entries = db.query("SELECT id, name FROM colors").await?;
    let entries: Vec<First> = entries.take(0)?;

    Ok(entries)
}

fn list_first(entries: Vec<First>) {
    for entry in entries {
        println!("{} {}", entry.id, entry.name);
    }
    println!("-------------");
}

async fn add_color_with_rgb(db: &Surreal<Db>, name: &str, rgb: &str) -> surrealdb::Result<()> {
    let _response: Option<Second> = db
        .create("colors")
        .content(Second {
            id: Thing::from(("colors", Id::ulid())),
            name: name.to_owned(),
            rgb: Some(rgb.to_owned()),
        })
        .await?;

    Ok(())
}

async fn get_second(db: &Surreal<Db>) -> surrealdb::Result<Vec<Second>> {
    let mut entries = db.query("SELECT id, name, rgb FROM colors").await?;
    entries.take(0)
}

fn list_second(entries: Vec<Second>) {
    for entry in entries {
        println!(
            "{} {:6} {}",
            entry.id,
            entry.name,
            entry.rgb.unwrap_or("no color".to_owned())
        );
    }
    println!("-------------");
}

async fn get_third(db: &Surreal<Db>) -> surrealdb::Result<Vec<Third>> {
    let mut entries = db.query("SELECT id, name, rgb FROM colors").await?;
    let entries: Vec<Third> = entries.take(0)?;
    Ok(entries)
}

fn list_third(entries: Vec<Third>) {
    for entry in entries {
        println!("{} {:6} {}", entry.id, entry.name, entry.rgb);
    }
    println!("-------------");
}

async fn add_missing_rgb(dbh: &Surreal<Db>, name: &str, rgb: &str) -> surrealdb::Result<()> {
    let _response = dbh
        .query("UPDATE colors SET rgb=$rgb WHERE name=$name")
        .bind(("name", name.to_owned()))
        .bind(("rgb", rgb.to_owned()))
        .await?;

    Ok(())
}
colors:01J8WSQY5VB2SDKSFDSC99KQ4X Red
colors:01J8WSQY5XJ64Z0CRSSXWT7MV5 Green
-------------
colors:01J8WSQY5VB2SDKSFDSC99KQ4X Red    no color
colors:01J8WSQY5XJ64Z0CRSSXWT7MV5 Green  no color
-------------
colors:01J8WSQY5VB2SDKSFDSC99KQ4X Red    no color
colors:01J8WSQY5XJ64Z0CRSSXWT7MV5 Green  no color
colors:01J8WSQY5ZG3KSK9S7NYVSY40H Blue   0000FF
-------------
colors:01J8WSQY5VB2SDKSFDSC99KQ4X Red    FF0000
colors:01J8WSQY5XJ64Z0CRSSXWT7MV5 Green  00FF00
colors:01J8WSQY5ZG3KSK9S7NYVSY40H Blue   0000FF
-------------

SurrealDB - Schema

SurrealDB - define field type - try to create entry with incorrect type (int, string)

  • SCHEMAFULL

  • TABLE

  • FIELD

  • DEFINE

  • Using the DEFINE keyword we can define a table to have schema, then we can define the column and their type.

  • Then if we try to insert (CREATE) an entry that does not match the type the query will fail. We need to use check to verify success.

use serde::{Deserialize, Serialize};
use surrealdb::engine::local::Mem;
use surrealdb::sql::Thing;
use surrealdb::Surreal;

#[derive(Debug, Deserialize, Serialize)]
struct Entry {
    id: Thing,
    number: u32,
}

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let dbh = Surreal::new::<Mem>(()).await?;

    dbh.use_ns("demo").use_db("demo").await?;

    dbh.query("DEFINE TABLE entry SCHEMAFULL").await?;
    dbh.query("DEFINE FIELD number ON TABLE entry TYPE int")
        .await?;

    let res = dbh
        .query(
            "CREATE entry CONTENT {
        number: 42,
    };",
        )
        .await?;
    match res.check() {
        Ok(val) => println!("Success: {val:?}"),
        Err(err) => println!("Error: {err}"),
    }
    println!("---------");

    let res = dbh
        .query(
            "CREATE entry CONTENT {
        number: 'fortytwo',
    };",
        )
        .await?;
    match res.check() {
        Ok(val) => println!("Success: {val:?}"),
        Err(err) => println!("Error: {err}"),
    }

    println!("---------");
    let mut entries = dbh.query("SELECT * FROM entry").await?;
    let entries: Vec<Entry> = entries.take(0)?;
    for entry in entries {
        println!("{} {}", entry.id, entry.number);
    }
    println!("---------");

    Ok(())
}
Success: Response { client: Surreal { router: OnceLock(Router { sender: Sender { .. }, last_id: 4, features: {Backup, LiveQueries} }), engine: PhantomData<surrealdb::api::engine::any::Any> }, results: {0: (Stats { execution_time: Some(639.419µs) }, Ok(Array(Array([Object(Object({"id": Thing(Thing { tb: "entry", id: String("y93feg7h3xxu6ww51spz") }), "number": Number(Int(42))}))]))))}, live_queries: {} }
---------
Error: Found 'fortytwo' for field `number`, with record `entry:o31e2ou83s5buvxc55yq`, but expected a int
---------
entry:y93feg7h3xxu6ww51spz 42
---------

SurrealDB - extra fields are ignored in SCHEMAFULL

  • We DEFINE a SCHEMAFULL table with several field.
  • If we try to create an entry using an extra field that field will be silently ignored.
  • see feature request
use serde::{Deserialize, Serialize};
use surrealdb::engine::local::Mem;
use surrealdb::sql::Thing;
use surrealdb::Surreal;

#[derive(Debug, Deserialize, Serialize)]
struct Entry {
    id: Thing,
    number: u32,
}

#[derive(Debug, Deserialize, Serialize)]
struct EntryWithName {
    id: Thing,
    number: u32,
    name: Option<String>,
}

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let dbh = Surreal::new::<Mem>(()).await?;

    dbh.use_ns("demo").use_db("demo").await?;

    dbh.query("DEFINE TABLE entry SCHEMAFULL").await?;
    dbh.query("DEFINE FIELD number ON TABLE entry TYPE int")
        .await?;

    let res = dbh.query("CREATE entry CONTENT { number: 42 };").await?;
    match res.check() {
        Ok(val) => println!("Success: {val:?}"),
        Err(err) => println!("Error: {err}"),
    }
    println!("---------");

    let res = dbh
        .query("CREATE entry CONTENT { number: 19, name: 'NineTeen' };")
        .await?;
    match res.check() {
        Ok(val) => println!("Success: {val:?}"),
        Err(err) => println!("Error: {err}"),
    }
    println!("---------");

    let mut entries = dbh.query("SELECT * FROM entry").await?;
    let entries: Vec<Entry> = entries.take(0)?;
    for entry in entries {
        println!("{} {}", entry.id, entry.number);
    }
    println!("---------");

    let mut entries = dbh.query("SELECT * FROM entry").await?;
    let entries: Vec<EntryWithName> = entries.take(0)?;
    for entry in entries {
        println!(
            "{} {}  {}",
            entry.id,
            entry.number,
            entry.name.unwrap_or(String::from("NO NAME"))
        );
    }
    println!("---------");

    Ok(())
}
Success: Response { client: Surreal { router: OnceLock(Router { sender: Sender { .. }, last_id: 4, features: {LiveQueries, Backup} }), engine: PhantomData<surrealdb::api::engine::any::Any> }, results: {0: (Stats { execution_time: Some(1.279388ms) }, Ok(Array(Array([Object(Object({"id": Thing(Thing { tb: "entry", id: String("5rw5ezjzvm6rf5nrc9xw") }), "number": Number(Int(42))}))]))))}, live_queries: {} }
---------
Success: Response { client: Surreal { router: OnceLock(Router { sender: Sender { .. }, last_id: 5, features: {LiveQueries, Backup} }), engine: PhantomData<surrealdb::api::engine::any::Any> }, results: {0: (Stats { execution_time: Some(1.046842ms) }, Ok(Array(Array([Object(Object({"id": Thing(Thing { tb: "entry", id: String("wz9sxmpax344kr8u3c8s") }), "number": Number(Int(19))}))]))))}, live_queries: {} }
---------
entry:5rw5ezjzvm6rf5nrc9xw 42
entry:wz9sxmpax344kr8u3c8s 19
---------
entry:5rw5ezjzvm6rf5nrc9xw 42  NO NAME
entry:wz9sxmpax344kr8u3c8s 19  NO NAME
---------

SurrealDB - missing field

  • We have a SCHEMAFULL table but not all the fields are supplied.
  • The CREATE will fail on missing fields
use serde::{Deserialize, Serialize};
use surrealdb::engine::local::Mem;
use surrealdb::sql::Thing;
use surrealdb::Surreal;

#[derive(Debug, Deserialize, Serialize)]
struct Entry {
    id: Thing,
    number: u32,
    name: String,
    email: String,
}

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let dbh = Surreal::new::<Mem>(()).await?;

    dbh.use_ns("demo").use_db("demo").await?;

    dbh.query("DEFINE TABLE entry SCHEMAFULL").await?;
    dbh.query("DEFINE FIELD number ON TABLE entry TYPE int")
        .await?;
    dbh.query("DEFINE FIELD name ON TABLE entry TYPE string")
        .await?;
    dbh.query("DEFINE FIELD email ON TABLE entry TYPE string ASSERT string::is::email($value);")
        .await?;

    let res = dbh
        .query(
            "CREATE entry CONTENT {
        number: 42,
        name: 'Answer',
        email: 'foo@bar.com',

    };",
        )
        .await?;
    match res.check() {
        Ok(val) => println!("Success: {val:?}"),
        Err(err) => println!("Error: {err}"),
    }
    println!("---------");

    let res = dbh
        .query(
            "CREATE entry CONTENT {
        number: 19,
        name: 'NineTeen',
        email: 'not_an_email',
    };",
        )
        .await?;
    match res.check() {
        Ok(val) => println!("Success: {val:?}"),
        Err(err) => println!("Error: {err}"),
    }
    println!("---------");

    let res = dbh
        .query(
            "CREATE entry CONTENT {
        number: 23,
        email: 'missing@bar.com',
    };",
        )
        .await?;
    match res.check() {
        Ok(val) => println!("Success: {val:?}"),
        Err(err) => println!("Error: {err}"),
    }
    println!("---------");

    let mut entries = dbh.query("SELECT * FROM entry").await?;
    let entries: Vec<Entry> = entries.take(0)?;
    for entry in entries {
        println!(
            "{} {} {} {}",
            entry.id, entry.number, entry.name, entry.email
        );
    }
    println!("---------");

    Ok(())
}
Success: Response { client: Surreal { router: OnceLock(Router { sender: Sender { .. }, last_id: 6, features: {LiveQueries, Backup} }), engine: PhantomData<surrealdb::api::engine::any::Any> }, results: {0: (Stats { execution_time: Some(1.370849ms) }, Ok(Array(Array([Object(Object({"email": Strand(Strand("foo@bar.com")), "id": Thing(Thing { tb: "entry", id: String("l2y3ynljpdka6x6xxjpv") }), "name": Strand(Strand("Answer")), "number": Number(Int(42))}))]))))}, live_queries: {} }
---------
Error: Found 'not_an_email' for field `email`, with record `entry:bqtpzweyb8g8k0xjf4s0`, but field must conform to: string::is::email($value)
---------
Error: Found NONE for field `name`, with record `entry:ae621vvz8gc1r5clcpw5`, but expected a string
---------
entry:l2y3ynljpdka6x6xxjpv 42 Answer foo@bar.com
---------

SurrealDB - add field to schema

  • We have a schema and some entries in the databas.
  • We change the schema adding another field, but that field does not exist in the data that is already in the database.
  • For new data we have to supply the new field as well.
  • For old data we don't have to make changes but when we SELECT the data we need to have a struct where this field is Option.
use serde::{Deserialize, Serialize};
use surrealdb::engine::local::Mem;
use surrealdb::{RecordId, Surreal};

#[derive(Debug, Deserialize, Serialize)]
struct EntryNummer {
    id: RecordId,
    number: u32,
}

#[derive(Debug, Deserialize, Serialize)]
struct EntryWithName {
    id: RecordId,
    number: u32,
    name: Option<String>,
}

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let dbh = Surreal::new::<Mem>(()).await?;

    dbh.use_ns("demo").use_db("demo").await?;

    dbh.query("DEFINE TABLE entry SCHEMAFULL").await?;
    dbh.query("DEFINE FIELD number ON TABLE entry TYPE int")
        .await?;

    let res = dbh
        .query(
            "CREATE entry CONTENT {
        number: 42,

    };",
        )
        .await?;
    match res.check() {
        Ok(val) => println!("Success: {val:?}"),
        Err(err) => println!("Error: {err}"),
    }
    println!("---------");

    let mut entries = dbh.query("SELECT * FROM entry").await?;
    let entries: Vec<EntryNummer> = entries.take(0)?;
    assert_eq!(entries.len(), 1);
    assert_eq!(entries[0].number, 42);
    for entry in entries {
        println!("{} {}", entry.id, entry.number);
    }
    println!("---------");

    let res = dbh
        .query("DEFINE FIELD name ON TABLE entry TYPE string")
        .await?;
    match res.check() {
        Ok(val) => println!("Success: {val:?}"),
        Err(err) => println!("Error: {err}"),
    }
    println!("---------");

    let res = dbh
        .query(
            "CREATE entry CONTENT {
        number: 23,
        name: 'twenty three',
    };",
        )
        .await?;
    match res.check() {
        Ok(val) => println!("Success: {val:?}"),
        Err(err) => println!("Error: {err}"),
    }
    println!("---------");

    let mut entries = dbh
        .query("SELECT * FROM entry ORDER BY number DESC")
        .await?;
    let entries: Vec<EntryWithName> = entries.take(0)?;
    assert_eq!(entries.len(), 2);
    assert_eq!(entries[0].number, 42);
    assert_eq!(entries[0].name, None);
    assert_eq!(entries[1].number, 23);
    assert_eq!(entries[1].name, Some(String::from("twenty three")));
    for entry in entries {
        println!(
            "{} {} {}",
            entry.id,
            entry.number,
            entry.name.unwrap_or("missing".to_string())
        );
    }
    println!("---------");

    Ok(())
}

#[cfg(test)]
mod test {
    use super::main;

    #[test]
    fn check() {
        main().unwrap();
    }
}
Success: Response { client: Surreal { router: OnceLock(Router { sender: Sender { .. }, last_id: 2, features: {Backup, LiveQueries} }), engine: PhantomData<surrealdb::api::engine::any::Any> }, results: {0: (Stats { execution_time: Some(1.434877ms) }, Ok(Array(Array([Object(Object({"id": Thing(Thing { tb: "entry", id: String("1iqktq46inqa3ekw677r") }), "number": Number(Int(42))}))]))))}, live_queries: {} }

SurrealDB in Docker using the CLI

  • TODO: types of data?

  • TODO: schema?

  • TODO: index

  • We would like to try SurrealDB, but we rather use Docker instead of installing SurrealDB on our computer.

  • At first we'll use the SurrealDB command line with an in-memory (not persistent) database.

We could use the latest tag, but to make sure total reproducability we prefer to pick the latest tag in the 2.* series.

$ docker run -it --rm  --user root surrealdb/surrealdb:v2.0.4 sql --endpoint memory --ns ns --db db --pretty

  • Using ns as our namespace and db as our database so they won't take much real estate in the prompt. Feel free to use anything else.

  • Create the first entry in the new planet table with an automatically generated ID.

ns/db> CREATE planet SET name = 'Earth';
-- Query 1 (execution time: 906.201µs)
[
	{
		id: planet:l0tpjh7uykux7sr5johy,
		name: 'Earth'
	}
]
  • Create another entry and set the ID manually.
ns/db> CREATE planet:4 SET name = 'Mars';
-- Query 1 (execution time: 315.322µs)
[
	{
		id: planet:4,
		name: 'Mars'
	}
]


ns/db> CREATE planet SET name = 'Mercury', distance = 0.7;
-- Query 1 (execution time: 544.853µs)
[
	{
		distance: 0.7f,
		id: planet:hxkrucoezhwmtayok509,
		name: 'Mercury'
	}
]

ns/db> select * from planet;
-- Query 1 (execution time: 176.03µs)
[
	{
		id: planet:4,
		name: 'Mars'
	},
	{
		id: planet:6xg3xbsnzbkyolssmtb6,
		name: 'Earth'
	},
	{
		distance: 0.7f,
		id: planet:hxkrucoezhwmtayok509,
		name: 'Mercury'
	}
]

ns/db> INFO for db;
-- Query 1 (execution time: 183.976µs)
{
	accesses: {},
	analyzers: {},
	configs: {},
	functions: {},
	models: {},
	params: {},
	tables: {
		person: 'DEFINE TABLE person TYPE ANY SCHEMALESS PERMISSIONS NONE',
		planet: 'DEFINE TABLE planet TYPE ANY SCHEMALESS PERMISSIONS NONE'
	},
	users: {}
}
  • Select all the items from more than one table
ns/db> SELECT * from person, planet
-- Query 1 (execution time: 254.849µs)
[
	{
		home: planet:4,
		id: person:ea6qfpbi8mu9prnn8nfx,
		name: 'Elon Musk'
	},
	{
		home: planet:6xg3xbsnzbkyolssmtb6,
		id: person:f2c3w5699hx64q1guta6,
		name: 'Gabor'
	},
	{
		id: planet:4,
		name: 'Mars'
	},
	{
		id: planet:6xg3xbsnzbkyolssmtb6,
		name: 'Earth'
	},
	{
		distance: 0.7f,
		id: planet:hxkrucoezhwmtayok509,
		name: 'Mercury'
	}
]

SurrealDB - references and SELECT

  • TODO
[package]
name = "demo"
version = "0.1.0"
edition = "2021"

[dependencies]
surrealdb = { version = "2.0", features = ["kv-mem"] }
serde = { version = "1.0", features = ["derive"] }
tokio = { version = "1.35", features = ["macros", "rt-multi-thread"] }


use serde::{Deserialize, Serialize};
use surrealdb::engine::local::{Db, Mem};
use surrealdb::sql::{Id, Thing};
use surrealdb::Surreal;

const COURSE: &str = "course";
const STUDENT: &str = "student";

#[derive(Debug, Serialize, Deserialize)]
struct Course {
    id: Thing,
    name: String,
}

#[derive(Debug, Serialize, Deserialize)]
struct Student {
    id: Thing,
    name: String,
    courses: Vec<Thing>,
}

#[derive(Debug, Deserialize)]
#[allow(dead_code)]
struct StudentWithCourses {
    id: Thing,
    name: String,
    courses: Vec<Course>,
}

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

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

    add_courses(&db).await?;
    let courses = get_courses(&db).await?;
    //println!("courses: {courses:#?}",);
    assert_eq!(courses.len(), 3);
    assert_eq!(courses[0].name, "Biology");
    assert_eq!(courses[1].name, "Chemistry");
    assert_eq!(courses[2].name, "Physics");

    add_students(&db, courses).await?;

    show_students_in_classes(&db).await?;

    Ok(())
}

async fn add_courses(db: &Surreal<Db>) -> surrealdb::Result<()> {
    for name in ["Chemistry", "Biology", "Physics"] {
        let course: Option<Course> = db
            .create(COURSE)
            .content(Course {
                id: Thing::from((COURSE, Id::rand())),
                name: name.to_owned(),
            })
            .await?;
        println!("course added: {course:?}");
    }

    Ok(())
}

async fn get_courses(db: &Surreal<Db>) -> surrealdb::Result<Vec<Course>> {
    let sql = "SELECT * FROM type::table($table) ORDER BY name";
    let mut results = db.query(sql).bind(("table", COURSE)).await?;
    let courses: Vec<Course> = results.take(0)?;

    for class in &courses {
        println!("get_courses: {class:?}");
    }

    Ok(courses)
}

async fn add_students(db: &Surreal<Db>, courses: Vec<Course>) -> surrealdb::Result<()> {
    let student: Option<Student> = db
        .create(STUDENT)
        .content(Student {
            id: Thing::from((STUDENT, Id::rand())),
            name: "Jane Doe".to_owned(),
            courses: courses.into_iter().map(|class| class.id).collect(),
        })
        .await?;
    println!("student added: {student:#?}");

    Ok(())
}

async fn show_students_in_classes(db: &Surreal<Db>) -> surrealdb::Result<()> {
    let sql = "SELECT * FROM type::table($table) FETCH courses";
    let mut results = db.query(sql).bind(("table", STUDENT)).await?;
    let students: Vec<StudentWithCourses> = results.take(0)?;
    println!("Students: {students:#?}");

    Ok(())
}
class added: [DanceClass { id: Thing { tb: "dance", id: String("gjjhx7vr62vaiqr49ivr") }, name: "Introduction to Dancing" }]
class added: [DanceClass { id: Thing { tb: "dance", id: String("c4v2dpfzsreg1u5nleeq") }, name: "Flamenco" }]
get_classes: DanceClass { id: Thing { tb: "dance", id: String("c4v2dpfzsreg1u5nleeq") }, name: "Flamenco" }
get_classes: DanceClass { id: Thing { tb: "dance", id: String("gjjhx7vr62vaiqr49ivr") }, name: "Introduction to Dancing" }
student added: [
    Student {
        id: Thing {
            tb: "student",
            id: String(
                "h8c5rpmyhb3p3l0yu7al",
            ),
        },
        name: "Jane Doe",
        classes: [
            Thing {
                tb: "dance",
                id: String(
                    "c4v2dpfzsreg1u5nleeq",
                ),
            },
            Thing {
                tb: "dance",
                id: String(
                    "gjjhx7vr62vaiqr49ivr",
                ),
            },
        ],
    },
]
Students: [
    StudentClasses {
        id: Thing {
            tb: "student",
            id: String(
                "h8c5rpmyhb3p3l0yu7al",
            ),
        },
        name: "Jane Doe",
        classes: [
            DanceClass {
                id: Thing {
                    tb: "dance",
                    id: String(
                        "c4v2dpfzsreg1u5nleeq",
                    ),
                },
                name: "Flamenco",
            },
            DanceClass {
                id: Thing {
                    tb: "dance",
                    id: String(
                        "gjjhx7vr62vaiqr49ivr",
                    ),
                },
                name: "Introduction to Dancing",
            },
        ],
    },
]

SurrealDB Demo

  • TODO
[package]
name = "demo"
version = "0.1.0"
edition = "2021"

[dependencies]
surrealdb = { version = "2.0", features = ["kv-mem"] }
serde = { version = "1.0", features = ["derive"] }
tokio = { version = "1.35", features = ["macros", "rt-multi-thread"] }


use serde::{Deserialize, Serialize};
use surrealdb::engine::local::Mem;
use surrealdb::opt::Resource;
use surrealdb::sql::{Datetime, Id, Thing};
use surrealdb::Surreal;

// Dance classes table name
const DANCE: &str = "dance";
// Students table name
const STUDENT: &str = "student";

// Dance class table schema
#[derive(Debug, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
struct DanceClass {
    id: Thing,
    name: String,
    created_at: Datetime,
}

// Student table schema
#[derive(Debug, Serialize)]
#[serde(rename_all = "camelCase")]
struct Student {
    id: Thing,
    name: String,
    classes: Vec<Thing>,
    created_at: Datetime,
}

// Student model with full class details
#[derive(Debug, Deserialize)]
#[serde(rename_all = "camelCase")]
#[allow(dead_code)]
struct StudentClasses {
    id: Thing,
    name: String,
    classes: Vec<DanceClass>,
    created_at: Datetime,
}

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

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

    // Create a dance class and store the result
    let classes: Option<DanceClass> = db
        .create(DANCE)
        .content(DanceClass {
            id: Thing::from((DANCE, Id::rand())),
            name: "Introduction to Dancing".to_owned(),
            created_at: Datetime::default(),
        })
        .await?;

    // Create a student and assign them to the previous dance class
    // We don't care about the result here so we don't need to
    // type-hint and store it. We use `Resource::from` to return
    // a `sql::Value` instead and ignore it.
    db.create(Resource::from(STUDENT))
        .content(Student {
            id: Thing::from((STUDENT, Id::rand())),
            name: "Jane Doe".to_owned(),
            classes: classes.into_iter().map(|class| class.id).collect(),
            created_at: Datetime::default(),
        })
        .await?;

    // Prepare the SQL query to retrieve students and full class info
    let sql = format!("SELECT * FROM {STUDENT} FETCH classes");

    // Run the query
    let mut results = db.query(sql).await?;

    // Extract the first query statement result and deserialise it as a vector of students
    let students: Vec<StudentClasses> = results.take(0)?;

    // Use the result as you see fit. In this case we are simply pretty printing it.
    println!("Students = {:?}", students);

    Ok(())
}

SurrealDB columns with schema

  • TODO
[package]
name = "add-columns-without-schema"
version = "0.1.0"
edition = "2021"

[dependencies]
serde = { version = "1.0", features = ["derive"] }
surrealdb = { version = "2.0", features = ["kv-mem"] }
tokio = { version = "1.35", features = ["macros", "rt-multi-thread"] }

use serde::{Deserialize, Serialize};
use surrealdb::engine::local::Mem;
use surrealdb::sql::Thing;
use surrealdb::Surreal;

#[derive(Debug, Deserialize, Serialize)]
struct EntryNummer {
    id: Thing,
    number: u32,
}

#[derive(Debug, Deserialize, Serialize)]
struct EntryWithName {
    id: Thing,
    number: u32,
    name: Option<String>,
}

// #[derive(Debug, Deserialize, Serialize)]
// struct NameOnly {
//     id: Thing,
//     name: String,
// }

// #[derive(Debug, Deserialize, Serialize)]
// struct NameEmail {
//     id: Thing,
//     name: String,
//     email: Option<String>,
// }

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    let dbh = Surreal::new::<Mem>(()).await?;

    dbh.use_ns("demo").use_db("demo").await?;

    dbh.query("DEFINE TABLE entry SCHEMAFULL").await?;
    dbh.query("DEFINE FIELD number ON TABLE entry TYPE int")
        .await?;

    let res = dbh
        .query(
            "CREATE entry CONTENT {
        number: 42,

    };",
        )
        .await?;
    match res.check() {
        Ok(val) => println!("Success: {val:?}"),
        Err(err) => println!("Error: {err}"),
    }
    println!("---------");

    let mut entries = dbh.query("SELECT * FROM entry").await?;
    let entries: Vec<EntryNummer> = entries.take(0)?;
    for entry in entries {
        println!("{} {}", entry.id, entry.number);
    }
    println!("---------");

    let res = dbh
        .query("DEFINE FIELD name ON TABLE entry TYPE string")
        .await?;
    match res.check() {
        Ok(val) => println!("Success: {val:?}"),
        Err(err) => println!("Error: {err}"),
    }
    println!("---------");

    let res = dbh
        .query(
            "CREATE entry CONTENT {
        number: 23,
        name: 'twenty three',
    };",
        )
        .await?;
    match res.check() {
        Ok(val) => println!("Success: {val:?}"),
        Err(err) => println!("Error: {err}"),
    }
    println!("---------");

    let mut entries = dbh.query("SELECT * FROM entry").await?;
    let entries: Vec<EntryWithName> = entries.take(0)?;
    for entry in entries {
        println!(
            "{} {} {}",
            entry.id,
            entry.number,
            entry.name.unwrap_or("missing".to_string())
        );
    }
    println!("---------");

    // let mut entries = dbh.query("SELECT * FROM entry").await?;
    // let entries: Vec<EntryWithName> = entries.take(0)?;
    // for entry in entries {
    //     println!("{} {}  {}", entry.id, entry.number, entry.name.unwrap_or(String::from("NO NAME")));
    // }
    // println!("---------");

    // dbh.query("DEFINE FIELD name ON TABLE entry TYPE string").await?;
    // dbh.query("DEFINE FIELD email ON TABLE user TYPE string ASSERT string::is::email($value);").await?;

    // dbh.query("CREATE entry CONTENT {
    //     name: 'First entry',
    // };").await?;

    // let mut res = dbh.query("CREATE entry CONTENT {
    //     name: 'Second entry',
    //     email: 'foobar.com',
    // };").await?;
    // let x = res.check().unwrap();
    // //let errors = res.take_errors();
    // //println!("{:?}", errors);

    // list_name(&dbh).await?;
    // list_name_email(&dbh).await?;

    Ok(())
}

// async fn list_name(dbh: &Surreal<Db>) -> surrealdb::Result<()> {
//     let mut entries = dbh.query("SELECT id, name FROM entry").await?;
//     let entries: Vec<NameOnly> = entries.take(0)?;
//     for entry in entries {
//         println!("{} {}", entry.id, entry.name);
//     }
//     println!("---------");
//     Ok(())
// }

// async fn list_name_email(dbh: &Surreal<Db>) -> surrealdb::Result<()> {
//     let mut entries = dbh.query("SELECT id, name, email FROM entry").await?;
//     let entries: Vec<NameEmail> = entries.take(0)?;
//     for entry in entries {
//         println!("{} {:15} {}", entry.id, entry.name, entry.email.unwrap_or("NO EMAIL".to_string()));
//     }
//     println!("---------");

//     Ok(())
// }

SurrealDB TODO

  • Example where we can set the database connection externally (either hostname and port number or in-memory database)
  • Example for SELECT without using a struct
  • Example select into struct with id and without id in the struct