SurrealDB - relation between tables - foreign key

Launching the database in Docker

In this example we are going to use SurrealDB running in a Docker container. All you need to do to launch is is to install Docer and then to launch the database with the following command:

docker run --name surrealdb --rm -p 8000:8000 --user root \
   -v my-surreal-db:/database surrealdb/surrealdb:latest   \
   start --log trace file://database

For detailed explanation check out the article Getting started with SurrealDB using Docker and a Rust client.

examples/surrealdb/foreign-key/Cargo.toml

[package]
name = "foreign-key"
version = "0.1.0"
edition = "2021"

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

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

examples/surrealdb/foreign-key/src/main.rs

use std::collections::HashMap;

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

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

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

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

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

    let places: HashMap<&str, Vec<&str>> = HashMap::from([
        ("Israel", vec!["Tel Aviv", "Haifa", "Jerusalem"]),
        ("Hungary", vec!["Debrecen", "Budapest"]),
    ]);
    //dbg!(places);

    db.query("DELETE country").await?;

    for name in places.keys() {
        println!("{name}");
        //let country = Country { name: name};
        //db.create(Resource::from("country")).content(country).await?;

        db.query("CREATE country SET name=$name")
            .bind(("name", name))
            .await?;
    }

    // for name in [
    //     String::from("Mildendo"),
    //     String::from("Plips"),
    //     String::from("Wiggywack"),
    // ] {
    //     // let city = City { name: name, country: Country { name : String::from("Lilliput")}};
    //     // db.create(Resource::from("city")).content(city).await?;

    //     db
    //         .query("CREATE city SET name=$name, country=(SELECT VALUE id FROM country WHERE name=$country LIMIT 1)[0]")
    //         .bind(("name", &name))
    //         .bind(("country", "Lilliput"))
    //         .await?;
    // }

    // for name in [String::from("Blefuscu-city")] {
    //     //let city = City { name: name, country: Country { name : String::from("Blefuscu")}};
    //     //db.create(Resource::from("city")).content(city).await?;

    //     db
    //     .query("CREATE city SET name=$name, country=(SELECT VALUE id FROM country WHERE name=$country LIMIT 1)[0]")
    //         .bind(("name", name))
    //         .bind(("country", "Blefuscu"))
    //         .await?;
    // }

    // // I was expecting this to fail or to create "Spain" in the country table
    // for name in [String::from("Madrid"), String::from("Barcelona")] {
    //     let city = City { name: name, country: Country { name : String::from("Spain")}};
    //     db.create(Resource::from("city")).content(city).await?;
    // }

    // for name in [String::from("Madrid")] {
    //     //let city = City { name: name, country: Country { name : String::from("Blefuscu")}};
    //     //db.create(Resource::from("city")).content(city).await?;

    //     db
    //     .query("CREATE city SET name=$name, country=(SELECT VALUE id FROM country WHERE name=$country LIMIT 1)[0]")
    //         .bind(("name", name))
    //         .bind(("country", "Spain"))
    //         .await?;
    // }

    // let mut backup = db.export(()).await?;
    // while let Some(result) = backup.next().await {
    //     match result {
    //         Ok(bytes) => {
    //             // Do something with the bytes received...
    //         }
    //         Err(error) => {
    //             // Handle the export error
    //         }
    //     }
    // }
    // //let info = db.info();

    // //println!("{}", info);

    println!("------ Countries -------");
    let mut response = db.query("SELECT * FROM country").await?;
    let countries: Vec<Country> = response.take(0)?;
    for country in &countries {
        println!("country: {}", country.name);
    }

    // println!("------ Cities -------");
    // let mut response = db.query("SELECT * FROM city FETCH country").await?;
    // let cities: Vec<City> = response.take(0)?;
    // for city in &cities {
    //     //println!("city: {}", city.name);
    //     println!("city: {:15} in {}", city.name, city.country.name);
    // }


    
    
    Ok(())
}

examples/surrealdb/foreign-key/src/example.rs

use std::borrow::Cow;
use serde::{Serialize, Deserialize};
//use serde_json::json;
use surrealdb::{Error, Surreal};
use surrealdb::engine::remote::ws::Ws;

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

// Pro tip: Replace String with Cow<'static, str> to
// avoid unnecessary heap allocations when inserting

#[derive(Serialize, Deserialize, Debug)]
struct Name {
    first: Cow<'static, str>,
    last: Cow<'static, str>,
}

// Install at https://surrealdb.com/install
// and use `surreal start --user root --pass root`
// to start a working database to take the following queries
// See the results via `surreal sql --ns namespace --db database --pretty`
// or https://surrealist.app/
// followed by the query `SELECT * FROM person;`
#[tokio::main]
async fn main() -> Result<(), Error> {
    let db = Surreal::new::<Ws>("localhost:8000").await?;

    // Select a specific namespace / database
    db.use_ns("namespace").use_db("database").await?;

    db.query("DELETE person").await?;

    // Create a new person with a random ID
    let created: Vec<Person> = db.create("person")
        .content(Person {
            title: "Founder & CEO".into(),
            name: Name {
                first: "Tobie".into(),
                last: "Morgan Hitchcock".into(),
            },
        })
        .await?;

    // Select all people records
    let people: Vec<Person> = db.select("person").await?;
    dbg!(people);


    Ok(())
}