DEV Community: Dave MacLeod

Hybrid vector + text Search in the terminal with SurrealDB and Ratatui

Dave MacLeod — Thu, 21 Aug 2025 06:28:05 +0000

SurrealDB: shiny newcomer, low-level database, or both?

SurrealDB is admittedly a pretty shiny database. It's built in Rust, a programming language that has just barely hit the 10-year mark since it hit version 1.0. A good deal of attention is paid to the website and brand design, and the official Surrealist app is not only slick but even capable of visualizing data as an interconnected net of records.

This blog post goes into further detail on how this graph visualization works, leading to output like the following that lets you see which European countries belong to which intranational groups.

But the other side of being a database written in a low-level language like Rust is that you can compile it to machine code with a single command and do everything you want with it on the command line, and thus on small devices on the edge as well. The main way to work with SurrealDB on the terminal is with the surreal sql command to open up a REPL, but building a TUI (terminal user interface) is another way to make it happen. That's what this post is about.

This post features the same AI-native demo app as in a previous post, except that this time it is built using Ratatui, the main way to build a TUI using Rust.

You can read more about the demo app functionality in the previous post, but here is a quick recap of what it does. It lets you:

Insert documents that are sourced from a Wikipedia API that provides a short summary of an article
Manually link a document to another
Automatically go through the article summary to try to link to other documents (e.g. if it sees the word "Canada" in the summary, it will try to link to a document:Canada)
See the linked articles from a single article recursively down to a depth of 3
Perform full-text search on article content
Perform semantic (vector) search via OpenAI or Mistral embeddings to find the closest articles to a sample of text (requires a key to use as it must call in to OpenAI and/or Mistral to first retrieve the embeddings)
Query the database.

You can see what the final product will look like in the following video.

It also comes with a new feature that is so new that it was merged into SurrealDB just two days ago and is only available in nightly, called hybrid search. We'll look at how that works in the next section.

Designing a TUI

Ratatui has a showcase here that demonstrates the range of design that can be done in a terminal UI. With a good amount of work you can generate graphics with them, such as this Minesweeper game. One core developer on the Ratatui project has even made a guitar tuner and realtime fretboard display out of it! But most of the time a terminal UI will be a relatively simple app made up of a few rectangles and some code to react to the user's keystrokes.

This is the case with this app, with the exception of a bit of some official SurrealDB colours.

Ratatui allows you to generate your own colours if you know their labellings such as RGB or hex. You can see the names of the SurrealDB brand colours and their values on this page. However, not all terminals are able to display True Colour, and since colour is the way this app shows which window is currently selected, we don't want to take a chance by going with Colour::Rgb(u8, u8, u8).

Fortunately, Ratatui allows 8-bit colour values to be specified and these are usually close enough. That gives us the following values, but you can change them to Color::Rgb if your terminal allows it.

const SURREAL_PINK: Color = Color::Indexed(199);
const SURREAL_PURPLE: Color = Color::Indexed(93);
const LAVENDER_MIST: Color = Color::Indexed(235);
const MOONLIT_WHITE: Color = Color::Indexed(15);
// These two are similar to each other so their
// 8-bit values end up being the same
const DEEP_AMETHYST: Color = Color::Indexed(233);
const OBSIDIAN_VIOLET: Color = Color::Indexed(233);

That gives us a selection of colours a bit different than the True Colour values, but close enough for a terminal app.

How does hybrid search work?

The PR that adds hybrid search to SurrealDB does a great job at explaining how it works. While vector search works well for lexical similarity, and full-text (lexical) search is optimized for precise results, sometimes you might want to combine the two to obtain a single similarity score.

The question then arises: how should these be weighted? If you have a certain vector similarity score, how could this be combined with the similarity or distance results from a full-text search?

This was precisely the reason why the previous blog post didn't have hybrid search, as I couldn't think of a way to combine the two that wasn't arbitrary. Fortunately, it turns out that algorithms already exist to combine the two. Let's read the description from the PR on what hybrid search does:

Users frequently need “hybrid” retrieval: lexical search (BM25) gives precise keyword matches,
while vector search captures semantic similarity even when keywords don’t overlap.

Today SurrealDB exposes both capabilities separately, but there is no built‑in way to combine them,
forcing application code to stitch results together.

Adding an RRF‑based fusion primitive lets users get higher recall and better relevance with a
single query, without manual score normalization.

Reciprocal Rank Fusion is simple, robust across heterogeneous scorers, and can be implemented
on top of existing full‑text and vector index mechanisms, minimizing complexity while unlocking
a common production use case.

Hybrid search in SurrealDB is done using three main ways:

search::rrf(): reciprocal rank fusion
search::linear() using 'minmax' normalization
seach::linear() using 'zscore' normalization

The search::rrf() function is the simplest method, because all it looks for is an array with the results of vector search and full-text search to stitch together, followed by the number of results to return, and an optional k constant that is used to smooth the ranking defaults to 60.

search::rrf([$vs, $ft], 4, 60)

The other two ways to use hybrid search have an extra parameter to set a weighting such as [2, 1] (giving vector search twice the weight of FTS) along with the choice between 'minmax' and 'zscore'. We won't use linear weighting in this post, but here is what these functions look like for reference.

-- Fuse with Linear / minmax
search::linear([$vs, $ft], [2, 1], 4, 'minmax');

-- Fuse with Linear / zscore
search::linear([$vs, $ft], [2, 1], 4, 'zscore');

Now, there is a good amount of setup required before using search::rrf(): one query to find the four nearest semantic neighbours, and another to get all of the full-text query matches. For more on these queries, see this section in the previous blog.

let vs = self.surreal.query("SELECT id, extract,
title,
vector::distance::knn() AS distance
    FROM document
    WHERE {field} <|4,COSINE|> $embeds
    ORDER BY distance;
    ").bind(("embeds", embeds))
    .bind(("field", field))
    .await.unwrap().take::<Value>(0).unwrap();

    let ft = self.surreal.query("SELECT id, 
            search::highlight('**', '**', 0) AS title,
            search::highlight('**', '**', 1) AS extract,
(search::score(0) * 3) + search::score(1) AS score
            FROM document
        WHERE title @0@ $input OR extract @1@ $input
        ORDER BY score DESC;")
        .bind(("input", fts_query))
        .await.unwrap().take::<Value>(0).unwrap();

Ok(self.surreal
    .query("SELECT id, rrf_score FROM search::rrf([$vs, $ft], 4, 60) ORDER BY rrf_score DESC;")
    .bind(("vs", vs))
    .bind(("ft", ft)).await.unwrap().take::<Value>(0).unwrap().to_string())

Two more points to note about these functions are:

They require the inputs to have an id field, as this is used to stitch them together. So make sure to include id in both the vector query and full-text query.
They won't return an error if you don't have

Let's give this a try in the app by doing the following:

First add some articles to the database: the article for Canada, its two largest provinces, and their two largest cities.

Canada,Ontario,Quebec,Montreal,Quebec City,Toronto,Ottawa

Then search for Canada,popul city. That will do a hybrid search for documents that are a close neighbour to document:Canada and also have a match for "populous city" on the full-text index. Using edgengram(3,10) in the search index means that popul will match a large number of words such as "populous" and "population".

DEFINE ANALYZER en_analyzer TOKENIZERS class FILTERS lowercase,edgengram(3,10);
DEFINE INDEX en_extract ON document FIELDS extract SEARCH ANALYZER en_analyzer BM25 HIGHLIGHTS;
DEFINE INDEX en_title ON document FIELDS title SEARCH ANALYZER en_analyzer BM25 HIGHLIGHTS;

The interesting thing about the output is that it will usually be the same, whether OpenAI or Mistral embeddings are used. The FTS here in addition to the hybrid search output truly does flatten the results.

[{ id: document:Quebec_City, rrf_score: 0.01639344262295082f },
{ id: document:Toronto, rrf_score: 0.016129032258064516f },
{ id: document:Ottawa, rrf_score: 0.015873015873015872f },
{ id: document:Ontario, rrf_score: 0.015625f }]

In contrast, a vector-only search returns more varying results including not just distance metrics but also the order in which they are returned. OpenAI considers Ottawa to be the second-most important article after Ontario when it comes to the keyword "Canada":

[{ distance: 0.09790307035147372f, extract: "Ontario is the southernmost province of Canada...", title: 'Ontario' },
{ distance: 0.11906508835503704f, extract: 'Ottawa is the capital city of Canada....', title: 'Ottawa' }]

While Mistral has put Quebec in second place.

[{ distance: 0.36376299190450023f, extract: "Ontario is the southernmost province of Canada...", title: 'Ontario' },
{ distance: 0.36566584443638417f, extract: "Quebec is Canada's largest province by area...", title: 'Quebec' }]

As you can see, working with raw embedding output tends to be more dynamic, while a hybrid search tends to be flatter and with less variance.

To see more of a difference when using an RFF query, try using A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,Alphabet,Latin alphabet to insert a bunch of articles about the alphabet. If you do a search for something vague like A,alphabet inside the app then you will finally see some variation in the results.

-- Mistral
[{ id: document:Alphabet, rrf_score: 0.01639344262295082f },
{ id: document:O, rrf_score: 0.014705882352941176f },
{ id: document:W, rrf_score: 0.014084507042253521f },
{ id: document:I, rrf_score: 0.0136986301369863f },
{ id: document:T, rrf_score: 0.013157894736842105f }]

-- OpenAI
[{ id: document:Alphabet, rrf_score: 0.01639344262295082f },
{ id: document:W, rrf_score: 0.014084507042253521f },
{ id: document:P, rrf_score: 0.012987012987012988f },
{ id: document:N, rrf_score: 0.012658227848101266f },
{ id: document:G, rrf_score: 0.012195121951219513f }]

If you firm up the search to something like A,alphabet first letter then the results will begin to converge again.

[{ id: document:A, rrf_score: 0.01639344262295082f },
{ id: document:U, rrf_score: 0.016129032258064516f }]

The document:U part is interesting. Let's try a raw query to see why it's showing up. This is all we need to see the content:

document:U.extract

There it is! U just happens to be the twenty-first letter of the alphabet.

'U, or u, is the twenty-first letter and the fifth vowel letter of the Latin alphabet,
used in the modern English alphabet and the alphabets of other western European languages
and others worldwide. Its name in English is u, plural ues.'

By the way, u's is a perfectly acceptable way to pluralize the letter u. Don't believe everything you read online!

Plurals almost never take an apostrophe. Chicago style uses an apostrophe for the
plural of lowercase single letters (x’s and o’s), but for little else
(for instance, we write “dos and don’ts”).

As you can see, playing around with an app of this sort can take you into some pretty interesting corners of the world.

On that note, let's finish up with the source code for the app and how to run it.

Final notes and app source code

Assuming you have Rust installed, the app can be run with a single command. Without an OPENAI_API_KEY and MISTRAL_API_KEY embedded retrieval and search won't work, but everything else will.

OPENAI_API_KEY=yourkeyhere MISTRAL_API_KEY=yourkeyhere cargo run

To use the search::rrf() function until the next version of SurrealDB is out, you can download the nightly version of SurrealDB, run that and change connect("memory") to connect("ws://localhost:8000"), or just be lazy by cloning the SurrealDB repo and adding it to Cargo.toml as follows.

anyhow = "1.0.97"
async-openai = "0.28.3"
crossterm = { version = "0.29.0", features = ["osc52"] }
mistralai-client = "0.14.0"
ratatui = "0.29.0"
serde = "1.0.219"
serde_json = "1.0.140"
surrealdb = { path = "../surrealdb/crates/sdk", features = ["kv-mem"] }
tokio = "1.44.0"
ureq = "3.0.12"

Then just paste the following content into main.rs and you are good to go!

Questions or comments? Drop by one of the many places where the SurrealDB community gathers to get in touch.

use std::{
    fmt::Display,
    sync::{
        LazyLock,
        mpsc::{Receiver, Sender, channel},
    },
};

use Constraint::{Min, Percentage};
use anyhow::Error;
use async_openai::{
    Client as OpenAiClient, config::OpenAIConfig, types::CreateEmbeddingRequestArgs,
};
use crossterm::{
    event::{self, Event, KeyCode, KeyModifiers},
    execute,
};
use mistralai_client::v1::{client::Client as MistralClient, constants::EmbedModel::MistralEmbed};
use ratatui::{
    layout::{Constraint, Layout},
    style::{Color, Style},
    widgets::{Block, BorderType, Paragraph, Wrap},
};
use serde::Deserialize;
use surrealdb::{
    engine::any::{Any, connect},
    {RecordId, Surreal, Value},
};

const _SURREAL_PINK: Color = Color::Indexed(199);
const SURREAL_PURPLE: Color = Color::Indexed(93);
const LAVENDER_MIST: Color = Color::Indexed(235);
const _DEEP_AMETHYST: Color = Color::Indexed(233);
const _OBSIDIAN_VIOLET: Color = Color::Indexed(233);
const _MOONLIT_WHITE: Color = Color::Indexed(15);

const MESSAGE: &str = "-----COMMANDS-----

Esc: delete text in current square
Enter: Run command, or copy Output content to clipboard
Shift + up/down: Scroll through Output content
Ctrl+C: Close app";

static OPENAI_API_KEY: LazyLock<String> =
    LazyLock::new(|| std::env::var("OPENAI_API_KEY").unwrap_or("NONE".to_string()));

static MISTRAL_API_KEY: LazyLock<String> =
    LazyLock::new(|| std::env::var("MISTRAL_API_KEY").unwrap_or("NONE".to_string()));

static OPENAI_CLIENT: LazyLock<OpenAiClient<OpenAIConfig>> = LazyLock::new(|| {
    let config = OpenAIConfig::new().with_api_key(&*OPENAI_API_KEY);
    OpenAiClient::with_config(config)
});

static MISTRAL_CLIENT: LazyLock<MistralClient> =
    LazyLock::new(|| MistralClient::new(Some(MISTRAL_API_KEY.clone()), None, None, None).unwrap());

trait StringOutput {
    fn output(self) -> String;
}

impl StringOutput for Result<String, String> {
    fn output(self) -> String {
        match self {
            Ok(o) => o,
            Err(e) => e,
        }
    }
}

#[derive(Deserialize, Debug, Clone)]
struct PageContent {
    title: String,
    extract: String,
}

fn page_for(page: &str) -> String {
    format!("http://en.wikipedia.org/api/rest_v1/page/summary/{page}")
}

fn init() -> &'static str {
    r#"
    DEFINE NAMESPACE ns;
    DEFINE DATABASE db;
    USE NS ns;
    USE DB db;
    DEFINE FIELD extract ON document TYPE string;
    DEFINE FIELD title ON document TYPE string;
    DEFINE FIELD mistral_embedding ON document TYPE option<array<float>> DEFAULT [];
    DEFINE FIELD openai_embedding ON document TYPE option<array<float>> DEFAULT [];
    DEFINE ANALYZER en_analyzer TOKENIZERS class FILTERS lowercase,edgengram(3,10);
    DEFINE INDEX en_extract ON document FIELDS extract SEARCH ANALYZER en_analyzer BM25 HIGHLIGHTS;
    DEFINE INDEX en_title ON document FIELDS title SEARCH ANALYZER en_analyzer BM25 HIGHLIGHTS;

    DEFINE TABLE link TYPE RELATION IN document OUT document ENFORCED;

    DEFINE INDEX only_one_link ON link FIELDS in,out UNIQUE;"#
}

#[derive(Debug, Clone)]
enum AppMessage {
    RunQuery(String),
    InsertDocuments(String),
    LinkDocuments(String),
    LinkUnlinkedDocs,
    OpenAiSimilaritySearch(String),
    MistralSimilaritySearch(String),
    FullTextSearch(String),
    SeeAllDocumentTitles,
    SeeLinkedArticles(String),
    AddOpenAiEmbeddings,
    AddMistralEmbeddings,
    MistralRrf(String),
    OpenAiRrf(String),
}

#[derive(Debug, Clone)]
enum DatabaseMessage {
    RunQuery(String),
    InsertDocuments(String),
    LinkDocuments(String),
    LinkUnlinkedDocs(String),
    OpenAiSimilaritySearch(String),
    MistralSimilaritySearch(String),
    FullTextSearch(String),
    SeeAllDocumentTitles(String),
    SeeLinkedArticles(String),
    AddOpenAiEmbeddings(String),
    AddMistralEmbeddings(String),
    MistralRrf(String),
    OpenAiRrf(String),
}

async fn get_openai_embeddings(content: Vec<PageContent>) -> Result<Vec<Vec<f32>>, String> {
    let extracts = content
        .into_iter()
        .map(|v| v.extract)
        .collect::<Vec<String>>();
    // Get the OpenAI embeddings
    let request = CreateEmbeddingRequestArgs::default()
        .model("text-embedding-3-small")
        .input(extracts)
        .dimensions(1536u32)
        .build()
        .map_err(|e| e.to_string())?;
    match OPENAI_CLIENT.embeddings().create(request).await {
        Ok(res) => Ok(res
            .data
            .into_iter()
            .map(|v| v.embedding)
            .collect::<Vec<Vec<f32>>>()),
        Err(e) => Err(e.to_string()),
    }
}

async fn get_mistral_embeddings(content: Vec<PageContent>) -> Result<Vec<Vec<f32>>, String> {
    let extracts = content
        .into_iter()
        .map(|v| v.extract)
        .collect::<Vec<String>>();
    // Get the Mistral embeddings
    match MISTRAL_CLIENT
        .embeddings_async(MistralEmbed, extracts, None)
        .await
    {
        Ok(res) => Ok(res
            .data
            .into_iter()
            .map(|d| d.embedding)
            .collect::<Vec<Vec<f32>>>()),
        Err(e) => Err(e.to_string()),
    }
}

fn get_possible_links(title: &str, content: &str) -> Vec<String> {
    content
        .split_whitespace()
        .filter(|word| matches!(word.chars().next(), Some(c) if c.is_uppercase()))
        .filter_map(|word| {
            let only_alpha = word
                .chars()
                .filter(|c| c.is_alphabetic())
                .collect::<String>();
            // Keep long words
            if only_alpha.chars().count() >= 3 && only_alpha != title {
                Some(only_alpha)
            } else {
                None
            }
        })
        .collect::<Vec<String>>()
}

#[derive(Deserialize, Debug)]
struct LinkOutput {
    r#in: RecordId,
    out: RecordId,
}

impl Display for LinkOutput {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        write!(f, "in: {} out: {}", self.r#in.key(), self.out.key())
    }
}

impl Database {
    fn send(&self, msg: DatabaseMessage) {
        self.sender.send(msg).unwrap();
    }

    async fn receive(&self) {
        use AppMessage as Msg;
        if let Ok(msg) = self.receiver.try_recv() {
            match msg {
                Msg::RunQuery(q) => {
                    let res = match self.surreal.query(&q).await {
                        Ok(mut r) => match r.take::<Value>(0) {
                            Ok(r) => r.to_string(),
                            Err(e) => e.to_string(),
                        },
                        Err(e) => e.to_string(),
                    };
                    self.send(DatabaseMessage::RunQuery(res));
                }
                Msg::InsertDocuments(m) => {
                    let res = self.insert_documents(m).await.output();
                    self.send(DatabaseMessage::InsertDocuments(res));
                }
                Msg::LinkDocuments(l) => {
                    let res = self.link_documents(l).await;
                    self.send(DatabaseMessage::LinkDocuments(res));
                }
                Msg::LinkUnlinkedDocs => {
                    let res = self.link_unlinked_docs().await.output();
                    self.send(DatabaseMessage::LinkUnlinkedDocs(res));
                }
                Msg::OpenAiSimilaritySearch(s) => {
                    let res = match self
                        .ai_similarity_search(s, "openai_embedding".to_string())
                        .await
                    {
                        Ok(o) => o.to_string(),
                        Err(e) => e.to_string(),
                    };
                    self.send(DatabaseMessage::OpenAiSimilaritySearch(res));
                }
                Msg::MistralSimilaritySearch(s) => {
                    let res = match self
                        .ai_similarity_search(s, "mistral_embedding".to_string())
                        .await
                    {
                        Ok(o) => o.to_string(),
                        Err(e) => e.to_string(),
                    };
                    self.send(DatabaseMessage::MistralSimilaritySearch(res));
                }
                Msg::FullTextSearch(s) => {
                    let res = match self.full_text_search(s).await {
                        Ok(o) => o,
                        Err(e) => e,
                    };
                    self.send(DatabaseMessage::FullTextSearch(res));
                }
                Msg::SeeAllDocumentTitles => {
                    let res = self.see_all_document_titles().await;
                    self.send(DatabaseMessage::SeeAllDocumentTitles(res));
                }
                Msg::SeeLinkedArticles(s) => {
                    let res = self.see_linked_articles(s).await;
                    self.send(DatabaseMessage::SeeLinkedArticles(res));
                }
                Msg::AddOpenAiEmbeddings => {
                    let res = self.add_openai_embeddings().await.output();
                    self.send(DatabaseMessage::AddOpenAiEmbeddings(res));
                }
                Msg::AddMistralEmbeddings => {
                    let res = self.add_mistral_embeddings().await.output();
                    self.send(DatabaseMessage::AddMistralEmbeddings(res));
                }
                Msg::MistralRrf(s) => {
                    let res = self.rrf(s, "mistral_embedding").await.output();
                    self.send(DatabaseMessage::MistralRrf(res));
                }
                Msg::OpenAiRrf(s) => {
                    let res = self.rrf(s, "openai_embedding").await.output();
                    self.send(DatabaseMessage::OpenAiRrf(res));
                }
            }
        }
    }

    async fn link_unlinked_docs(&self) -> Result<String, String> {
        let mut response = self
            .surreal
            .query("SELECT * FROM document")
            .await
            .map_err(|e| e.to_string())?;
        let unlinked_docs = response
            .take::<Vec<PageContent>>(0)
            .map_err(|e| format!("{e:?}"))?;
        let mut output = String::from("Docs linked: ");
        for doc in unlinked_docs {
            let possible_links = get_possible_links(&doc.title, &doc.extract);
            for link in possible_links {
                let first = RecordId::from(("document", &doc.title));
                let second = RecordId::from(("document", &link));
                if let Ok(mut o) = self
                    .surreal
                    .query("RELATE $first->link->$second")
                    .bind(("first", first))
                    .bind(("second", second))
                    .await
                {
                    if let Ok(Some(o)) = &o.take::<Option<LinkOutput>>(0) {
                        output += "\n";
                        output += &o.to_string();
                    }
                }
            }
        }
        Ok(output)
    }

    async fn ai_similarity_search(
        &self,
        doc: String,
        field_name: String,
    ) -> Result<Value, surrealdb::Error> {

        let mut current_doc = self
            .surreal
            // Grab just the embeds field from a document
            .query(format!("type::thing('document', '{doc}').{field_name};"))
            .await?;
        let embeds: Value = current_doc.take(0)?;

        let mut similar = self
            .surreal
            .query(format!(
                "(SELECT extract,
    title,
    vector::distance::knn() AS distance
        FROM document
        WHERE {field_name} <|4,COSINE|> $embeds
        ORDER BY distance).filter(|$t| $t.distance > 0.0001);",
            ))
            .bind(("embeds", embeds))
            .await?;
        similar.take::<Value>(0)
    }

    async fn rrf(&self, input: String, field: &str) -> Result<String, String> {
        let input = input.trim().to_string();
        let field = field.to_string();
        let Some((doc, fts_query)) = input.split_once(',') else {
            return Err("Must enter a document title followed by a comma and text to perform full-text search on".to_string());
        };

        let fts_query = fts_query.to_string();
        let doc = RecordId::from_table_key("document", doc);
        let mut current_doc = self
            .surreal
            // Grab just the embeds field from a document
            .query(format!("SELECT VALUE {field} FROM ONLY {doc}"))
            .bind(("doc", doc.clone()))
            .await
            .map_err(|e| e.to_string())?;

        let embeds: Value = current_doc.take(0).unwrap();

        let vs = self
            .surreal
            .query(
                "SELECT id, extract,
    title,
    vector::distance::knn() AS distance
        FROM document
        WHERE {field} <|4,COSINE|> $embeds
        ORDER BY distance;
        ",
            )
            .bind(("embeds", embeds))
            .bind(("field", field))
            .await
            .map_err(|e| e.to_string())?
            .take::<Value>(0)
            .map_err(|e| e.to_string())?;

        let ft = self
            .surreal
            .query(
                "SELECT id, 
                search::highlight('**', '**', 0) AS title,
                search::highlight('**', '**', 1) AS extract,
(search::score(0) * 3) + search::score(1) AS score
                FROM document
            WHERE title @0@ $input OR extract @1@ $input
            ORDER BY score DESC;",
            )
            .bind(("input", fts_query))
            .await
            .map_err(|e| e.to_string())?
            .take::<Value>(0)
            .map_err(|e| e.to_string())?;

        Ok(self.surreal
        .query("(SELECT id, rrf_score FROM search::rrf([$vs, $ft], 5, 60) ORDER BY rrf_score DESC).filter(|$v| $v.id != {doc})")
        .bind(("vs", vs))
        .bind(("ft", ft))
        .bind(("doc", doc))
        .await.map_err(|e| e.to_string())?.take::<Value>(0).map_err(|e| e.to_string())?.to_string())
    }

    async fn add_openai_embeddings(&self) -> Result<String, String> {
        let no_open_id: Vec<PageContent> = self
            .surreal
            .query("SELECT title, extract FROM document WHERE !openai_embedding")
            .await
            .map_err(|e| e.to_string())?
            .take(0)
            .map_err(|e| e.to_string())?;
        if !no_open_id.is_empty() {
            let embeddings = get_openai_embeddings(no_open_id.clone())
                .await
                .map_err(|e| e.to_string())?;
            let zipped = no_open_id.into_iter().zip(embeddings.into_iter());

            let mut results = String::from("Embeddings added for:");
            for (one, two) in zipped {
                let mut res = self
                    .surreal
                    .query(
                        "UPDATE type::thing('document', $title)
            SET openai_embedding = $embeds",
                    )
                    .bind(("title", one.title))
                    .bind(("embeds", two))
                    .await
                    .map_err(|e| e.to_string())?;
                if let Ok(Some(v)) = res.take::<Option<PageContent>>(0) {
                    results.push('\n');
                    results.push_str(&v.title);
                }
            }
            Ok(results)
        } else {
            Err(String::from("No documents found to update"))
        }
    }

    async fn add_mistral_embeddings(&self) -> Result<String, String> {
        let no_mistral_id: Vec<PageContent> = self
            .surreal
            .query("SELECT title, extract FROM document WHERE !mistral_embedding")
            .await
            .map_err(|e| e.to_string())?
            .take(0)
            .map_err(|e| e.to_string())?;
        if !no_mistral_id.is_empty() {
            let embeddings = get_mistral_embeddings(no_mistral_id.clone())
                .await
                .map_err(|e| e.to_string())?;
            let zipped = no_mistral_id.into_iter().zip(embeddings.into_iter());

            let mut results = String::from("Embeddings added for:");
            for (one, two) in zipped {
                let mut res = self
                    .surreal
                    .query(
                        "UPDATE type::thing('document', $title)
                         SET mistral_embedding = $embeds",
                    )
                    .bind(("title", one.title))
                    .bind(("embeds", two))
                    .await
                    .map_err(|e| e.to_string())?;
                match res.take::<Option<PageContent>>(0) {
                    Ok(Some(v)) => {
                        results.push('\n');
                        results.push_str(&v.title);
                    }
                    Ok(None) => return Ok("No PageContent found".to_string()),
                    Err(e) => return Err(e.to_string()),
                }
            }
            Ok(results)
        } else {
            Err(String::from("No documents found to update"))
        }
    }

    async fn insert_documents(&self, page_names: String) -> Result<String, String> {
        // Get each page name separated by a comma and add _ in between words
        let page_names = page_names
            .trim()
            .split(",")
            .map(|p| p.split_whitespace().collect::<Vec<&str>>().join("_"));
        let mut result = String::new();

        let results = page_names
            .map(|page| {
                std::thread::spawn(move || {
                    let url = page_for(&page);
                    let res = ureq::get(url).call();
                    match res {
                        Ok(mut o) => o
                            .body_mut()
                            .read_to_string()
                            .unwrap_or("Couldn't read to string".to_string()),
                        Err(_) => format!("No page {page} found"),
                    }
                })
            })
            .collect::<Vec<_>>();

        for res in results {
            let s = res.join().unwrap_or("Couldn't join thread".to_string());
            let mut content: PageContent = match serde_json::from_str(&s) {
                Ok(content) => content,
                Err(_) => return Err(s),
            };
            // Add an underscore again as response from Wikipedia won't have it
            content.title = content
                .title
                .split_whitespace()
                .collect::<Vec<&str>>()
                .join("_");
            result.push('\n');
            result.push_str(&self.add_document(content).await);
        }
        Ok(result)
    }

    async fn add_document(&self, content: PageContent) -> String {
        let doc = RecordId::from_table_key("document", &content.title);

        let res = self
            .surreal
            .query("CREATE ONLY $doc SET title = $title, extract = $extract;")
            .bind(("doc", doc))
            .bind(("title", content.title))
            .bind(("extract", content.extract));
        match res.await {
            Ok(mut r) => match r.take::<Option<PageContent>>(0) {
                Ok(Some(good)) => format!("{good:?}"),
                Ok(None) => "No PageContent found".to_string(),
                Err(e) => e.to_string(),
            },
            Err(e) => e.to_string(),
        }
    }

    async fn link_documents(&self, documents: String) -> String {
        let Some((one, two)) = documents.split_once(",") else {
            return "Please insert two document names separated by a comma".to_string();
        };
        let one = RecordId::from_table_key("document", one);
        let two = RecordId::from_table_key("document", two);

        match self
            .surreal
            .query("RELATE $one->link->$two;")
            .bind(("one", one))
            .bind(("two", two))
            .await
        {
            Ok(mut r) => match r.take::<Value>(0) {
                Ok(val) => format!("Link added: {val}"),
                Err(e) => e.to_string(),
            },
            Err(e) => e.to_string(),
        }
    }

    async fn raw_query(&self, query: &str) -> String {
        match self.surreal.query(query).await {
            Ok(mut r) => {
                let mut results = vec![];
                let num_statements = r.num_statements();
                for index in 0..num_statements {
                    match r.take::<Value>(index) {
                        Ok(good) => results.push(good.to_string()),
                        Err(e) => results.push(e.to_string()),
                    }
                }
                results.join("\n")
            }
            Err(e) => e.to_string(),
        }
    }

    async fn see_all_document_titles(&self) -> String {
        let res = self
            .raw_query("(SELECT VALUE title FROM document).sort()")
            .await;
        format!("All database article titles: {res}")
    }

    async fn full_text_search(&self, input: String) -> Result<String, String> {
        match self
            .surreal
            .query(
                "SELECT
            search::highlight('**', '**', 0) AS title,
            search::highlight('**', '**', 1) AS extract,
            (search::score(0) * 3) + search::score(1) AS score
            FROM document
            WHERE title @0@ $input OR extract @1@ $input
            ORDER BY score DESC;",
            )
            .bind(("input", input))
            .await
        {
            Ok(mut res) => Ok(res.take::<Value>(0).map_err(|e| e.to_string())?.to_string()),
            Err(e) => Err(e.to_string()),
        }
    }

    async fn see_linked_articles(&self, doc: String) -> String {
        self.raw_query(&format!(
            "type::thing('document', '{doc}').{{..3}}.{{ id, next: ->link->document.@ }};"
        ))
        .await
    }
}

struct Database {
    surreal: Surreal<Any>,
    receiver: Receiver<AppMessage>,
    sender: Sender<DatabaseMessage>,
}

#[derive(Debug)]
struct App {
    insert_documents: String,
    mistral_similarity_search: String,
    link_documents: String,
    full_text_search: String,
    openai_similarity_search: String,
    see_linked_articles: String,
    run_query: String,
    mistral_rrf: String,
    openai_rrf: String,
    receiver: Receiver<DatabaseMessage>,
    sender: Sender<AppMessage>,
    output: String,
    current_view: CurrentView,
    scroll: u16,
}

#[derive(Copy, Clone)]
enum LeftRight {
    Left,
    Right,
}

impl App {
    fn receive(&mut self) {
        use DatabaseMessage as Msg;
        if let Ok(msg) = self.receiver.try_recv() {
            match msg {
                Msg::RunQuery(s)
                | Msg::InsertDocuments(s)
                | Msg::LinkDocuments(s)
                | Msg::LinkUnlinkedDocs(s)
                | Msg::OpenAiSimilaritySearch(s)
                | Msg::MistralSimilaritySearch(s)
                | Msg::FullTextSearch(s)
                | Msg::SeeAllDocumentTitles(s)
                | Msg::SeeLinkedArticles(s)
                | Msg::AddOpenAiEmbeddings(s)
                | Msg::AddMistralEmbeddings(s)
                | Msg::MistralRrf(s)
                | Msg::OpenAiRrf(s) => self.output = s,
            }
        }
    }

    fn up(&mut self) {
        use CurrentView as C;
        self.current_view = match self.current_view {
            C::InsertDocuments => C::AddOpenAiEmbeddings,
            C::LinkDocuments => C::SeeAllDocumentTitles,
            C::LinkUnlinkedDocs => C::Output,
            C::AddOpenAiEmbeddings => C::Output,
            C::AddMistralEmbeddings => C::Output,
            C::OpenAiSimilaritySearch => C::FullTextSearch,
            C::MistralSimilaritySearch => C::SeeLinkedArticles,
            C::FullTextSearch => C::LinkDocuments,
            C::SeeLinkedArticles => C::InsertDocuments,
            C::SeeAllDocumentTitles => C::Output,
            C::RunQuery => C::MistralRrf,
            C::Output => C::RunQuery,
            C::MistralRrf => C::MistralSimilaritySearch,
            C::OpenAiRrf => C::OpenAiSimilaritySearch,
        }
    }
    fn down(&mut self) {
        use CurrentView as C;
        self.current_view = match self.current_view {
            C::InsertDocuments => C::SeeLinkedArticles,
            C::LinkDocuments => C::FullTextSearch,
            C::LinkUnlinkedDocs => C::LinkDocuments,
            C::AddOpenAiEmbeddings => C::InsertDocuments,
            C::AddMistralEmbeddings => C::InsertDocuments,
            C::OpenAiSimilaritySearch => C::OpenAiRrf,
            C::MistralSimilaritySearch => C::MistralRrf,
            C::FullTextSearch => C::OpenAiSimilaritySearch,
            C::SeeLinkedArticles => C::MistralSimilaritySearch,
            C::SeeAllDocumentTitles => C::LinkDocuments,
            C::RunQuery => C::Output,
            C::Output => C::AddOpenAiEmbeddings,
            C::MistralRrf => C::RunQuery,
            C::OpenAiRrf => C::RunQuery,
        }
    }
    fn left_right(&mut self, left_right: LeftRight) {
        use CurrentView as C;
        let right: bool = matches!(left_right, LeftRight::Right);

        self.current_view = match self.current_view {
            C::AddOpenAiEmbeddings => {
                if right {
                    C::AddMistralEmbeddings
                } else {
                    C::LinkUnlinkedDocs
                }
            }
            C::AddMistralEmbeddings => {
                if right {
                    C::SeeAllDocumentTitles
                } else {
                    C::AddOpenAiEmbeddings
                }
            }
            C::SeeAllDocumentTitles => {
                if right {
                    C::LinkUnlinkedDocs
                } else {
                    C::AddMistralEmbeddings
                }
            }
            C::LinkUnlinkedDocs => {
                if right {
                    C::AddOpenAiEmbeddings
                } else {
                    C::SeeAllDocumentTitles
                }
            }
            C::InsertDocuments => C::LinkDocuments,
            C::LinkDocuments => C::InsertDocuments,
            C::SeeLinkedArticles => C::FullTextSearch,
            C::FullTextSearch => C::SeeLinkedArticles,
            C::MistralSimilaritySearch => C::OpenAiSimilaritySearch,
            C::OpenAiSimilaritySearch => C::MistralSimilaritySearch,
            C::MistralRrf => C::OpenAiRrf,
            C::OpenAiRrf => C::MistralRrf,
            other => other,
        }
    }

    fn backspace(&mut self) {
        use CurrentView as C;
        let _ = match self.current_view {
            C::InsertDocuments => self.insert_documents.pop(),
            C::MistralSimilaritySearch => self.mistral_similarity_search.pop(),
            C::LinkDocuments => self.link_documents.pop(),
            C::FullTextSearch => self.full_text_search.pop(),
            C::RunQuery => self.run_query.pop(),
            C::LinkUnlinkedDocs => None,
            C::AddOpenAiEmbeddings => None,
            C::AddMistralEmbeddings => None,
            C::OpenAiSimilaritySearch => self.openai_similarity_search.pop(),
            C::SeeLinkedArticles => self.see_linked_articles.pop(),
            C::MistralRrf => self.mistral_rrf.pop(),
            C::OpenAiRrf => self.openai_rrf.pop(),
            C::SeeAllDocumentTitles => None,
            C::Output => None,
        };
    }

    fn enter(&mut self) {
        use CurrentView as C;
        let msg = match self.current_view {
            C::InsertDocuments => AppMessage::InsertDocuments(self.insert_documents.clone()),
            C::LinkDocuments => AppMessage::LinkDocuments(self.link_documents.clone()),
            C::LinkUnlinkedDocs => AppMessage::LinkUnlinkedDocs,
            C::AddOpenAiEmbeddings => AppMessage::AddOpenAiEmbeddings,
            C::AddMistralEmbeddings => AppMessage::AddMistralEmbeddings,
            C::OpenAiSimilaritySearch => {
                AppMessage::OpenAiSimilaritySearch(self.openai_similarity_search.clone())
            }

            C::MistralSimilaritySearch => {
                AppMessage::MistralSimilaritySearch(self.mistral_similarity_search.clone())
            }
            C::MistralRrf => AppMessage::MistralRrf(self.mistral_rrf.clone()),
            C::OpenAiRrf => AppMessage::OpenAiRrf(self.openai_rrf.clone()),
            C::FullTextSearch => AppMessage::FullTextSearch(self.full_text_search.clone()),
            C::SeeLinkedArticles => AppMessage::SeeLinkedArticles(self.see_linked_articles.clone()),
            C::SeeAllDocumentTitles => AppMessage::SeeAllDocumentTitles,
            C::RunQuery => AppMessage::RunQuery(self.run_query.clone()),
            C::Output => {
                execute!(
                    std::io::stdout(),
                    crossterm::clipboard::CopyToClipboard::to_clipboard_from(&self.output)
                )
                .unwrap();
                return;
            }
        };
        self.sender.send(msg).unwrap();
    }

    fn char(&mut self, c: char) {
        use CurrentView as C;

        match self.current_view {
            C::InsertDocuments => self.insert_documents.push(c),
            C::MistralSimilaritySearch => self.mistral_similarity_search.push(c),
            C::LinkDocuments => self.link_documents.push(c),
            C::FullTextSearch => self.full_text_search.push(c),
            C::RunQuery => self.run_query.push(c),
            C::OpenAiSimilaritySearch => self.openai_similarity_search.push(c),
            C::SeeLinkedArticles => self.see_linked_articles.push(c),
            C::OpenAiRrf => self.openai_rrf.push(c),
            C::MistralRrf => self.mistral_rrf.push(c),
            C::LinkUnlinkedDocs
            | C::AddOpenAiEmbeddings
            | C::AddMistralEmbeddings
            | C::SeeAllDocumentTitles
            | C::Output => {}
        };
    }

    fn esc(&mut self) {
        use CurrentView as C;

        match self.current_view {
            C::InsertDocuments => self.insert_documents.clear(),
            C::LinkDocuments => self.link_documents.clear(),
            C::OpenAiSimilaritySearch => self.openai_similarity_search.clear(),
            C::MistralSimilaritySearch => self.mistral_similarity_search.clear(),
            C::FullTextSearch => self.full_text_search.clear(),
            C::SeeLinkedArticles => self.see_linked_articles.clear(),
            C::SeeAllDocumentTitles => {}
            C::RunQuery => self.run_query.clear(),
            C::MistralRrf => self.mistral_rrf.clear(),
            C::OpenAiRrf => self.openai_rrf.clear(),
            C::LinkUnlinkedDocs | C::AddOpenAiEmbeddings | C::AddMistralEmbeddings | C::Output => {}
        }
    }
}

#[derive(Clone, Copy, Debug)]
enum CurrentView {
    InsertDocuments,
    LinkDocuments,
    LinkUnlinkedDocs,
    AddOpenAiEmbeddings,
    AddMistralEmbeddings,
    OpenAiSimilaritySearch,
    MistralSimilaritySearch,
    FullTextSearch,
    SeeLinkedArticles,
    SeeAllDocumentTitles,
    RunQuery,
    MistralRrf,
    OpenAiRrf,
    Output,
}

impl Display for CurrentView {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        let output = match self {
            CurrentView::InsertDocuments => " Insert documents ",
            CurrentView::LinkDocuments => " Link documents ",
            CurrentView::LinkUnlinkedDocs => " Link unlinked docs ",
            CurrentView::AddOpenAiEmbeddings => " Add OpenAI embeddings ",
            CurrentView::AddMistralEmbeddings => " Add Mistral embeddings ",
            CurrentView::OpenAiSimilaritySearch => " OpenAI similarity search ",
            CurrentView::MistralSimilaritySearch => " Mistral similarity search ",
            CurrentView::OpenAiRrf => " OpenAI RRF ",
            CurrentView::MistralRrf => " Mistral RRF ",
            CurrentView::FullTextSearch => " Full text search ",
            CurrentView::SeeLinkedArticles => " See linked articles ",
            CurrentView::SeeAllDocumentTitles => " See all document titles ",
            CurrentView::RunQuery => " Run query ",
            CurrentView::Output => " Output ",
        };
        write!(f, "{output}")
    }
}

fn square<'a>(
    title: &'a str,
    content: &'a str,
    view: CurrentView,
    offset: Option<u16>,
) -> Paragraph<'a> {
    let style = if title == view.to_string() {
        Style::new().bg(SURREAL_PURPLE)
    } else {
        Style::new().bg(LAVENDER_MIST)
    };
    Paragraph::new(content)
        .block(
            Block::bordered()
                .border_type(BorderType::Double)
                .style(style)
                .title(title),
        )
        .style(style)
        .wrap(Wrap { trim: true })
        .scroll((offset.unwrap_or_default(), 0))
}

fn block(title: &'static str, view: CurrentView) -> Block<'static> {
    let style = if title == view.to_string() {
        Style::new().bg(SURREAL_PURPLE)
    } else {
        Style::new().bg(LAVENDER_MIST)
    };
    Block::bordered()
        .border_type(BorderType::Double)
        .style(style)
        .title(title)
}

#[tokio::main]
async fn main() -> Result<(), Error> {
    let (msg_sender, msg_receiver) = channel();
    let (str_sender, str_receiver) = channel();

    let surreal = connect("memory").await?;
    surreal.use_ns("ns").use_db("db").await?;
    surreal.query(init()).await?;

    let database = Database {
        surreal,
        sender: str_sender,
        receiver: msg_receiver,
    };

    let mut app = App {
        sender: msg_sender,
        receiver: str_receiver,
        openai_similarity_search: String::new(),
        mistral_similarity_search: String::new(),
        full_text_search: String::new(),
        insert_documents: String::new(),
        link_documents: String::new(),
        see_linked_articles: String::new(),
        run_query: String::new(),
        mistral_rrf: String::new(),
        openai_rrf: String::new(),
        output: MESSAGE.to_string(),
        current_view: CurrentView::InsertDocuments,
        scroll: 0,
    };

    let mut terminal = ratatui::init();
    loop {
        database.receive().await;
        app.receive();

        terminal
            .draw(|frame| {
                // First split into four: buttons you don't type anything in, top buttons, query button, output
                let [non_type, top, run_query, output] = Layout::vertical([
                    Percentage(12),
                    Percentage(38),
                    Percentage(12),
                    Percentage(38),
                ])
                .areas(frame.area());
                let [top_left, top_right] = Layout::horizontal([Min(3); 2]).areas(top);

                let left_vertical = Layout::vertical([Min(3); 4]).areas(top_left);
                let right_vertical = Layout::vertical([Min(3); 4]).areas(top_right);
                let non_type_area = Layout::horizontal([Min(3); 4]).areas(non_type);

                let [
                    add_openai_embeddings,
                    add_mistral_embeddings,
                    see_all_document_titles,
                    link_unlinked_docs,
                ] = non_type_area;

                let [
                    insert_documents,
                    see_linked_articles,
                    mistral_similarity_search,
                    mistral_rrf,
                ] = left_vertical;

                let [
                    link_documents,
                    full_text_search,
                    openai_similarity_search,
                    openai_rrf,
                ] = right_vertical;

                frame.render_widget(
                    square(
                        " Insert documents ",
                        &format!("{} ▮", &app.insert_documents),
                        app.current_view,
                        None,
                    ),
                    insert_documents,
                );
                frame.render_widget(
                    square(
                        " Link documents ",
                        &format!("{} ▮", &app.link_documents),
                        app.current_view,
                        None,
                    ),
                    link_documents,
                );
                frame.render_widget(
                    block(" Link unlinked docs ", app.current_view),
                    link_unlinked_docs,
                );
                frame.render_widget(
                    block(" Add OpenAI embeddings ", app.current_view),
                    add_openai_embeddings,
                );
                frame.render_widget(
                    block(" Add Mistral embeddings ", app.current_view),
                    add_mistral_embeddings,
                );
                frame.render_widget(
                    square(
                        " OpenAI similarity search ",
                        &format!("{} ▮", &app.openai_similarity_search),
                        app.current_view,
                        None,
                    ),
                    openai_similarity_search,
                );
                frame.render_widget(
                    square(
                        " Mistral similarity search ",
                        &format!("{} ▮", &app.mistral_similarity_search),
                        app.current_view,
                        None,
                    ),
                    mistral_similarity_search,
                );

                frame.render_widget(
                    square(
                        " Full text search ",
                        &format!("{} ▮", &app.full_text_search),
                        app.current_view,
                        None,
                    ),
                    full_text_search,
                );

                frame.render_widget(
                    square(
                        " See linked articles ",
                        &format!("{} ▮", app.see_linked_articles),
                        app.current_view,
                        None,
                    ),
                    see_linked_articles,
                );

                frame.render_widget(
                    block(" See all document titles ", app.current_view),
                    see_all_document_titles,
                );

                frame.render_widget(
                    square(
                        " Run query ",
                        &format!("{} ▮", app.run_query),
                        app.current_view,
                        None,
                    ),
                    run_query,
                );
                frame.render_widget(
                    square(
                        " Mistral RRF ",
                        &format!("{} ▮", app.mistral_rrf),
                        app.current_view,
                        None,
                    ),
                    mistral_rrf,
                );
                frame.render_widget(
                    square(
                        " OpenAI RRF ",
                        &format!("{} ▮", app.openai_rrf),
                        app.current_view,
                        None,
                    ),
                    openai_rrf,
                );
                frame.render_widget(
                    square(" Output ", &app.output, app.current_view, Some(app.scroll)),
                    output,
                );
            })
            .expect("failed to draw frame");
        if let Event::Key(key) = event::read()? {
            match key.code {
                KeyCode::Up if key.modifiers == KeyModifiers::SHIFT => {
                    app.scroll = app.scroll.saturating_sub(1)
                }
                KeyCode::Up => app.up(),
                KeyCode::Down if key.modifiers == KeyModifiers::SHIFT => {
                    app.scroll = app.scroll.saturating_add(1)
                }
                KeyCode::Down => app.down(),
                KeyCode::Right => app.left_right(LeftRight::Right),
                KeyCode::Left => app.left_right(LeftRight::Left),
                KeyCode::Enter => app.enter(),
                KeyCode::Char(c) if c == 'c' && key.modifiers == KeyModifiers::CONTROL => break,
                KeyCode::Char(c) => app.char(c),

                KeyCode::Backspace => app.backspace(),
                KeyCode::Esc => app.esc(),
                _ => {}
            }
        }
    }
    ratatui::restore();
    Ok(())
}

Migrate your data directly to SurrealDB using Surreal Sync

Dave MacLeod — Thu, 07 Aug 2025 01:20:16 +0000

Note

Surreal Sync is currently in active development and is not yet stable. We are looking forward to any and all feedback on the tool, either via raising an issue or PR on the Surreal Sync repo, or anywhere else in the SurrealDB community.

Today we are excited to announce the launch of Surreal Sync, a new tool that allows existing data from another database to be migrated to SurrealDB.

Surreal Sync is run entirely on the command line and allows you to import data directly from a another source straight into a running SurrealDB instance. For example, the following command will sign into a Neo4J database at bolt://localhost:7687 as the user neo4j with the password password into the SurrealDB database graph_data inside the production namespace via a user named root with the password secret.

surreal-sync sync neo4j \
  --source-uri "bolt://localhost:7687" \
  --source-username "neo4j" \
  --source-password "password" \
  --neo4j-timezone "America/New_York" \
  --to-namespace "production" \
  --to-database "graph_data" \
  --surrealdb-username "root" \
  --surrealdb-password "secret"

Currently, the tool supports three sources: Neo4J, MongoDB, and JSON Lines.

Importing from another database

Importing data from an external source into SurrealDB requires a conversion from one datatype into another, and in the case of Neo4J, also a conversion of Neo4J graph edges into SurrealDB graph edges. This allows you to use the built-in -> graph syntax without needing to do any extra processing after the data migration is done.

The Surreal Sync documentation includes a page for each data source that explains how the conversion takes place. Most of the time the conversion will be from one type to a nearly identical one, such as from a MongoDB Map to a SurrealDB object. In this case, the data type is deemed to be "Fully Supported".

If a data type does not have a clear equivalent in SurrealQL, the data will be stored as an object that maintains as much of the original data and functionality as possible. For example, the Neo4J Point3D type does not have a direct SurrealQL equivalent, and is thus stored as a GeoJSON-like object with a coordinates field that holds three floats for the point's longitude, latitude, and elevation. In this case, the data type is deemed to be "Partially Supported".

As the Surreal Sync documentation puts it:

✅ Fully Supported: The data type is converted with
complete semantic preservation and no data loss

🔶 Partially Supported: The data is preserved but may lose
some type-specific semantics, precision, or functionality

Importing from JSON Lines

JSON Lines deserves a separate mention in this post because it shows that Surreal Sync is not strictly a tool to migrate data from one database to SurrealDB, but also data from one data format to SurrealDB. In addition, SurrealDB has recently added JSON Lines support for the terminal output of the SurrealDB server itself. That lets you move from this:

2025-08-06T04:00:21.564978Z  INFO surreal::env: src/env/mod.rs:12: Running 3.0.0+20250804.354010130 for macos on aarch64
2025-08-06T04:00:21.565738Z  INFO surrealdb::core::kvs::ds: crates/core/src/kvs/ds.rs:275: Starting kvs store in memory
2025-08-06T04:00:21.566431Z  INFO surrealdb::core::kvs::ds: crates/core/src/kvs/ds.rs:278: Started kvs store in memory
2025-08-06T04:00:21.583716Z  INFO surrealdb::net: src/net/signals.rs:95: Listening for a system shutdown signal.
2025-08-06T04:00:21.584067Z  INFO surrealdb::net: src/net/mod.rs:225: Started web server on 127.0.0.1:8000

To this.

{"timestamp":"2025-07-22T03:18:59.349350Z","level":"INFO","fields":{"message":"Running 3.0.0+20250721.eaff383ce for macos on aarch64"},"target":"surreal::env"}
{"timestamp":"2025-07-22T03:18:59.349604Z","level":"DEBUG","fields":{"message":"Database strict mode is false"},"target":"surreal::dbs"}
{"timestamp":"2025-07-22T03:18:59.349647Z","level":"WARN","fields":{"message":"❌🔒 IMPORTANT: Authentication is disabled. This is not recommended for production use. 🔒❌"},"target":"surreal::dbs"}
{"timestamp":"2025-07-22T03:18:59.349787Z","level":"DEBUG","fields":{"message":"Server capabilities: scripting=false, guest_access=false, live_query_notifications=true, allow_funcs=all, deny_funcs=none, allow_net=none, deny_net=none, allow_rpc=all, deny_rpc=none, allow_http=all, deny_http=none, allow_experimental=none, deny_experimental=none, allow_arbitrary_query=all, deny_arbitrary_query=none"},"target":"surreal::dbs"}

This page from the Surreal Sync repo goes into great detail on how to import data in JSON Lines format. The gist of it is as follows:

Each file that holds the JSON Lines will become a SurrealQL table of the same name.
Since an id field is needed for each and every SurrealQL record, the tool will look for an id field in each JSON object. If this field does not exist, use the --id-field flag to let it know which field you want to be converted into the id field.

For example, if the data above is saved in a file called log.jsonl and the following command is used:

surreal-sync sync jsonl \
  --source-uri /path/to/jsonl \
  --to-namespace myns \
  --to-database mydb \
  --id-field "timestamp"

The output from SELECT * FROM log will now look like this.

[
    {
        fields: {
            message: 'Running 3.0.0+20250721.eaff383ce for macos on aarch64'
        },
        id: log:⟨2025-07-22T03:18:59.349350Z⟩,
        level: 'INFO',
        target: 'surreal::env'
    },
    {
        fields: {
            message: 'Database strict mode is false'
        },
        id: log:⟨2025-07-22T03:18:59.349604Z⟩,
        level: 'DEBUG',
        target: 'surreal::dbs'
    },
    {
        fields: {
            message: '❌🔒 IMPORTANT: Authentication is disabled. This is not recommended for production use. 🔒❌'
        },
        id: log:⟨2025-07-22T03:18:59.349647Z⟩,
        level: 'WARN',
        target: 'surreal::dbs'
    },
    {
        fields: {
            message: 'Server capabilities: scripting=false, guest_access=false, live_query_notifications=true, allow_funcs=all, deny_funcs=none, allow_net=none, deny_net=none, allow_rpc=all, deny_rpc=none, allow_http=all, deny_http=none, allow_experimental=none, deny_experimental=none, allow_arbitrary_query=all, deny_arbitrary_query=none'
        },
        id: log:⟨2025-07-22T03:18:59.349787Z⟩,
        level: 'DEBUG',
        target: 'surreal::dbs'
    }
]

The SurrealDB documentation has a section for Surreal Sync, while further details can be found in the readme for the tool itself.

Beyond basic RAG: Building a multi-cycle reasoning engine on SurrealDB

Dave MacLeod — Mon, 04 Aug 2025 01:36:43 +0000

While this implementation uses Python, the architectural patterns and SurrealDB integration strategies apply to any language with SurrealDB SDK support.

Introduction

Standard Retrieval-Augmented Generation (RAG) models are powerful but operate on single shot principle: Retrieve then Generate. This approach often fails when faced with complex multi-faceted queries, providing incomplete answers that lack depth. The core limitation is the absence of a feedback loop.

This article introduces the Reflexion RAG Engine, a production-ready system that overcomes these limitations through a multi-cycle, self-correcting architecture powered by SurrealDB. It employs a Multi-LLM strategy for Generation, Evaluation, and Synthesis to iteratively refine answers, ensuring higher accuracy and comprehensiveness. SurrealDB serves as a unified data store that houses both vector embeddings and document metadata.

The architectural core: a unified vector and document store with SurrealDB

Traditional RAG architectures are often fragile combinations of multiple technologies: A vector database (e.g. Pinecone, ChromaDB), a separate document store for metadata (e.g. MongoDB, Postgres), and a caching layer (e.g. Redis). This fragmentation introduces complexity in data synchronisation and security.

Our engine leverages SurrealDB to consolidate these functions into a single, ACID-compliant database. This unified approach provides specific advantages :

Simplified architecture: Documents, their metadata and their 3072-dimensional vector embeddings coexist in the same table. This eliminates data duplication and synchronisation issues.
Transactional integrity: Ingesting a document and its vector is an atomic operation, guaranteeing consistency.
High performance search : SurrealDB’s native HNSW indexing on vector fields enables millisecond-latency similarity searches, even at scale.

The 3072-dimensional vectors from OpenAI text-embedding-3-large model from Azure provide superior semantic understanding compared to standard 1536-dimensional embeddings. SurrealDB’s HNSW (Hierarchical Navigable Small World) indexing enables millisecond-level similarity search across millions of vectors.

The schema in detail

We define a single index hnsw_embedding with a dimension of 3072 to match OpenAI’s text-embedding-3-large model. The use of Cosine distance is standard for normalised embeddings. The EFC Entry point Candidate and M Max connections parameters are tuned to provide a high recall and query speed, making it suitable for production environments.

-- Define a schema-full table named 'documents'
DEFINE TABLE documents SCHEMAFULL;

-- Define a 'content' field of type string
DEFINE FIELD content ON documents TYPE string;

-- Define a 'metadata' field of flexible type object
-- This allows for storing schemaless data
DEFINE FIELD metadata ON documents FLEXIBLE TYPE object;

-- Define an 'embedding' field of type array of floats
-- This is typically used for storing vector embeddings
DEFINE FIELD embedding ON documents TYPE array<float>;

-- Create an HNSW index on the 'embedding' field
-- This index is used for efficient vector similarity searches
DEFINE INDEX hnsw_embedding ON documents
    FIELDS embedding
    HNSW
    DIMENSION 3072  -- The number of dimensions in the embedding vectors
    DIST COSINE     -- Use cosine distance for measuring similarity
    TYPE F32        -- Use 32-bit floating point numbers
    EFC 500         -- Entry point candidate size for search performance
    M 16;           -- Maximum number of connections per node in the graph

Deep dive: multi-LLM reflexion engine

The core architecture is based on a loop that mimics critical thinking. For each query a series of steps occur, which are guided by specialised LLMs to ensure a detailed and accurate response.

Generate: The Generation LLM produces an initial answer based on retrieved context.
Evaluate: The Evaluation LLM assesses this answer, generates a confidence score and identifies knowledge gaps or uncertainties.
Decide: Based on the evaluation the system makes a decision using the ReflexionDecision enum:
COMPLETE: If confidence is above the threshold (e.g., 0.90), the loop terminates.
REFINE_QUERY: If specific gaps are found, a new, more targeted query is generated to fill them.
CONTINUE: If the answer is incomplete but on the right track, the loop proceeds.
INSUFFICIENT_DATA: If the knowledge base cannot answer, the loop terminates gracefully.
Synthesise: When the loop completes through multiple cycles, the Summary LLM synthesises the final response from earlier generated partial answers.

Multi-LLM orchestration strategy

The system strategically assigns different models to specialised tasks:

# From src/rag/reflexion_engine.py
class ReflexionRAGEngine:
    """Advanced RAG engine with dynamic reflexion loop architecture and web search integration"""

    def __init__(
        ....
    ):
        # Initialise different LLMs for different purposes
        self.generation_llm = generation_llm or GitHubLLM(
            model_override=settings.llm_model,
            temperature_override=settings.llm_temperature,
            max_tokens_override=settings.llm_max_tokens,
        )

        self.evaluation_llm = evaluation_llm or GitHubLLM(
            model_override=settings.evaluation_model,
            ...
        )

        self.summary_llm = summary_llm or GitHubLLM(
            model_override=settings.summary_model,
            ...
        )

Reflexion cycle

The reflexion cycle can be seen in the following diagram.

It works as follows:

Cache check: Looks for a cached answer to the question and streams it if found.
Reflexion loop: For a set number of cycles, it:

- Decides whether to use web search in addition to database retrieval.
- Retrieves relevant documents and/or web results.
- Prepares a prompt and streams the LLM’s partial answer as it is generated.
- If the answer is truncated, attempts to continue it.
- Evaluates the answer’s quality and confidence.
- Decides whether to stop, continue, or generate a follow-up query for the next cycle.

Finalisation: If no confident answer is found after all cycles, synthesises a final answer from all cycles.
Streaming: Streams the final comprehensive answer with metadata.
Error handling: If any step fails, falls back to a simpler RAG approach and streams that answer.

Features:

Streams answers in real time.
Integrates both database and web search.
Self-evaluates and iteratively improves answers.
Handles prompt/answer truncation and errors robustly.
Caches results for future queries.

Prompt engineering: YAML-based prompt management

Maintaining, updating and tuning prompts as per requirements is a must. To maintain flexibility, and enable rapid iteration, all prompts are managed in versioned YAML files located in ./prompts directory. This separates prompt engineering from the application code allowing easier updates.

---
metadata:
  name: "initial_generation"
  version: "1.0.0"
  ...

config:
    ...

variables:
  - name: "query"
    type: "string"
    required: true
    description: "User's question"
  - name: "context"
    ...
  - name: "cycle_number"
    ...

prompt_template: |
  You are an expert AI assistant providing detailed, accurate answers with proper source citations.
    ...
  Question: {{query}}

  Available Documents:
  {{context}}

    ...

  RESPONSE STRUCTURE:
  ...

  IMPORTANT GUIDELINES:
    ...

  Answer:

# A snippet from prompts/evaluation/response_evaluation.yaml
RESPONSE FORMAT (JSON):
{
    "confidence_score": 0.35,
    "decision": "continue|refine_query|complete|insufficient_data",
    "reasoning": "Detailed explanation of the assessment",
    ...
}

The Prompt Manager

The Prompt Manager is responsible for extracting prompts from the YAML prompt files and add the variables like **user query, **queries, **previous answers** and confidence scores at their dedicated places.

from dataclasses import dataclass
from pathlib import Path
from typing import Any, Dict, List, Optional

# --- Data Structures ---

@dataclass
class PromptMetadata:
    name: str
    version: str
    # ... other metadata fields

@dataclass
class PromptConfig:
    temperature: Optional[float] = None
    # ... other config fields

@dataclass
class PromptVariable:
    name: str
    type: str
    # ... other variable fields

@dataclass
class PromptTemplate:
    """A complete, renderable prompt object."""
    metadata: PromptMetadata
    config: PromptConfig
    variables: List[PromptVariable]
    prompt_template: str

    def render(self, **kwargs) -> str:
        """Renders the prompt template with the given variables."""
        ...

# --- Core Manager ---

class PromptManager:
    """Manages loading, caching, and rendering of YAML-based prompt templates."""

    def __init__(self, prompts_dir: Optional[str] = "prompts"):
        """Initialises the manager and loads all prompts from disk."""
        self._prompt_cache: Dict[str, PromptTemplate] = {}
        # ... implementation for loading all prompts on startup ...

    def get_prompt(self, name: str) -> Optional[PromptTemplate]:
        """Retrieves a parsed prompt template from the cache by its name."""
        ...

    def render_prompt(self, name: str, **kwargs) -> str:
        """Finds a prompt by name and renders it with provided variables."""
        ...

    def list_prompts(self) -> List[str]:
        """Returns a list of all loaded prompt names."""
        ...

    def reload_prompts(self):
        """Clears the cache and reloads all prompts from the directory."""
        ...

# --- Global Singleton Instance ---
# Provides a single, shared instance of the prompt manager across the application.
prompt_manager = PromptManager()

Hybrid retrieval: combining local documents and live web search

A simple RAG system is limited by the freshness of its data. The Reflexion Engine implements a hybrid retrieval strategy to enhance its knowledge base with real-time information from the web.

The SurrealDBVectorStore implements a similarity_search_combined method. Instead of a single vector search, it executes two parallel queries against the documents and web_search tables, each with a separate LIMIT (k_docs & k_web). The results are merged post vector search and re-ranked by their similarity score. This ensures that most relevant information, whether from a local PDF or a recent news article, is available to generation model.

Web Search is configurable via the WEB_SEARCH_MODE setting, which can be off, initial only, or every_cycle. For complex queries, running web search in every reflexion cycle allows the engine to dynamically seek external information, filling current knowledge gaps identified during the evaluation phase.

Hybrid search: documents + web results

The engine’s hybrid search combines local documents with real-time web results:

async def similarity_search_combined(self, query: str, k_docs: int = 3, k_web: int = 2) -> List[Document]:
    """
    Perform a combined similarity search over local documents and web search results.
    - Embeds the query.
    - Retrieves top-k similar documents and web results (with limits).
    - Combines results, enforcing a max token budget.
    - Sorts all results by similarity score (descending).
    - Returns a list of Document objects.
    """
    ....

    try:
        query_embedding = await self.embedding_function.embed_text(query)

        # Query local documents and web search tables
        docs_query = f"""
            SELECT id, content, metadata, vector::similarity::cosine(embedding, {query_embedding}) AS score
            FROM documents
            WHERE embedding <|300,COSINE|> {query_embedding}
            ORDER BY score DESC
            LIMIT {k_docs};
            """

        # Search web results with limit
        web_query = f"""
            SELECT id, content, metadata, vector::similarity::cosine(embedding, {query_embedding}) AS score
            FROM web_search
            WHERE embedding <|300,COSINE|> {query_embedding}
            ORDER BY score DESC
            LIMIT {k_web};
            """

        ...

        # Add document results first
        for result in docs_results or []:
            ...

        # Add web results if token budget allows
        for result in web_results or []:
            ...

        # Sort by similarity score
        all_documents.sort(key=lambda x: x.metadata.get("similarity_score", 0), reverse=True)
        ...

    except Exception as e:
        logger.error(f"Combined similarity search error: {e}")
        return []

This demonstrates SurrealDB’s ability to handle complex, multi-table vector operations efficiently.

Getting started: a practical implementation guide

This section provides a step by step guide to get the Reflexion RAG Engine running.

Prerequisites

Python 3.13+
uv package manager (recommended). If installed, skip the Install UV Package Manager step.
A SurrealDB instance (local or cloud). Refer to the official SurrealDB installation guide.
A GitHub Personal Access Token with repo and read:org scopes.
(Optional) Google Custom Search API Key and CSE ID for web search.

Install UV package manager

UV is a lightning-fast Python package manager written in Rust that significantly outperforms traditional pip:

# Linux/macOS
curl -LsSf https://astral.sh/uv/install.sh | sh

# Windows (PowerShell as Administrator)
powershell -ExecutionPolicy ByPass -c "irm https://astral.sh/uv/install.ps1 | iex"

# Alternative: via Homebrew
brew install uv

# Verify installation
uv --version

Installation

*# 1. Clone the repository*
git clone https://github.com/cloaky233/multi-cycle-rag.git
cd multi-cycle-rag

*# 2. Create virtual environment and install dependencies*
uv venv && source .venv/bin/activate *# macOS/Linux# .venv\Scripts\activate # Windows*
uv sync

*# 3. Configure your environment*
cp .env.example .env
*# Edit the .env file with your credentials for SurrealDB, GitHub, etc.*

uv sync: This single command installs all production dependencies including SurrealDB Python SDK, Azure AI Inference, Crawl4AI for web scraping, and all LLM related libraries.

Environment configuration

# Create environment file
# .env.example is included in the repository!
cp .env.example .env

Get GitHub models access token (Github.com/models)

Visit GitHub Personal Access Tokens
Click “Generate new token (classic)”
Select these scopes:
- repo (Full control of private repositories)
- read:org (Read org and team membership)
Copy the token and add it to your .env file

Define document and web search tables (run as a query or run in Surrealist)

The engine uses two specialised tables, each optimised for different data types while sharing the same vector search capabilities:

-- Define a schema-full table named 'documents'
DEFINE TABLE documents SCHEMAFULL;

-- Define a 'content' field of type string
DEFINE FIELD content ON documents TYPE string;

-- Define a 'metadata' field of flexible type object
-- This allows for storing schemaless data
DEFINE FIELD metadata ON documents FLEXIBLE TYPE object;

-- Define an 'embedding' field of type array of floats
-- This is typically used for storing vector embeddings
DEFINE FIELD embedding ON documents TYPE array<float>;

-- Create an HNSW index on the 'embedding' field
-- This index is used for efficient vector similarity searches
DEFINE INDEX hnsw_embedding ON documents
    FIELDS embedding
    HNSW
    DIMENSION 3072  -- The number of dimensions in the embedding vectors
    DIST COSINE     -- Use cosine distance for measuring similarity
    TYPE F32        -- Use 32-bit floating point numbers
    EFC 500         -- Entry point candidate size for search performance
    M 16;           -- Maximum number of connections per node in the graph

-- Define a schema-full table named 'web_search'
DEFINE TABLE web_search SCHEMAFULL;

-- Define a 'content' field of type string
-- This field will store the textual content of the web search results
DEFINE FIELD content ON web_search TYPE string;

-- Define a 'metadata' field of flexible type object
-- This allows for storing additional information in a schemaless manner
DEFINE FIELD metadata ON web_search FLEXIBLE TYPE object;

-- Define an 'embedding' field of type array of floats
-- This field is used for storing vector embeddings of the content
DEFINE FIELD embedding ON web_search TYPE array<float>;

-- Create an HNSW index on the 'embedding' field
-- This index is used for efficient vector similarity searches
DEFINE INDEX hnsw_embedding ON web_search
    FIELDS embedding
    HNSW
    DIMENSION 3072  -- The number of dimensions in the embedding vectors
    DIST COSINE     -- Use cosine distance for measuring similarity
    TYPE F32        -- Use 32-bit floating point numbers
    EFC 500         -- Entry point candidate size for search performance
    M 16;           -- Maximum number of connections per node in the graph

Custom SurrealQL functions for RAG operations

The system includes specialised SurrealQL functions that demonstrate SurrealDB’s programmability:

-- A function to count number of user documents stored
DEFINE FUNCTION OVERWRITE fn::countdocs() -> int {
    count(SELECT * FROM documents);
} PERMISSIONS FULL;

-- A function to count number of web documents stored
DEFINE FUNCTION OVERWRITE fn::count_web() -> int {
    count(SELECT * FROM web_search);
} PERMISSIONS FULL;

-- A function to delete user Documents with confirmation
DEFINE FUNCTION OVERWRITE fn::deldocs($confirm: string) -> string {
LET $word = 'CONFIRM';
RETURN IF $confirm == $word
{ DELETE documents; 'DELETED' }
ELSE
{ 'NOT DELETED' }
} PERMISSIONS FULL;

-- A function to delete web search Documents with confirmation
DEFINE FUNCTION OVERWRITE fn::delweb($confirm: string) -> string {
LET $word = 'CONFIRM';
RETURN IF $confirm == $word
{ DELETE web_search; 'DELETED' }
ELSE
{ 'NOT DELETED' }
} PERMISSIONS FULL;

-- Define a function to perform similarity search on user documents
DEFINE FUNCTION OVERWRITE fn::similarity_search($query_embedding: array<float>, $k: int) -> any {

    -- Set a default limit for the number of results if $k is not provided
    LET $limit = $k ?? 5;

    -- Perform a similarity search using cosine similarity
    RETURN (
        SELECT id, content, metadata, vector::similarity::cosine(embedding, $query_embedding) AS score
        FROM documents
        WHERE embedding <|300, COSINE|> $query_embedding  -- Use KNN operator with cosine distance
        ORDER BY score DESC  -- Order results by similarity score in descending order
        LIMIT $limit  -- Limit the number of results to the specified limit
    );
} PERMISSIONS FULL;  -- Grant full permissions for this function

-- Define a function to perform similarity search on web search results
DEFINE FUNCTION OVERWRITE fn::similarity_search_web($query_embedding: array<float>, $k: int) -> any {

    -- Set a default limit for the number of results if $k is not provided
    LET $limit = $k ?? 5;

    -- Perform a similarity search using cosine similarity
    RETURN (
        SELECT id, content, metadata, vector::similarity::cosine(embedding, $query_embedding) AS score
        FROM web_search
        WHERE embedding <|300, COSINE|> $query_embedding  -- Use KNN operator with cosine distance
        ORDER BY score DESC  -- Order results by similarity score in descending order
        LIMIT $limit  -- Limit the number of results to the specified limit
    );
} PERMISSIONS FULL;  -- Grant full permissions for this function

These functions showcase SurrealDB’s ability to handle complex vector operations with custom logic directly in the database layer, reducing network overhead and improving performance and security.

Set up Google custom search

1. Obtain a Google custom search API key

The API key authenticates your project's requests to Google's services.

Go to the Google Cloud Console: Navigate to the Google Cloud Console and create a new project if you don't have one already.
Enable the API: In your project's dashboard, go to the "APIs & Services" section. Find and enable the Custom Search API.
Create credentials: Go to the "Credentials" tab within "APIs & Services". Click "Create Credentials" and select "API key".
Copy and secure the key: A new API key will be generated. Copy this key and store it securely. It is recommended to restrict the key's usage to only the "Custom Search API" for security purposes.

2. Create a programmable search engine and get the CSE ID

The CSE ID (also called the Search Engine ID or cx) tells Google what to search (e.g., the entire web or specific sites you define).

Go to the Programmable Search Engine Page: Visit the Google Programmable Search Engine website and sign in with your Google account.
Create a new search engine: Click "Add" or "New search engine" to start the setup process.
Configure your engine:
- Give your search engine a name.
- Under "Sites to search," you can specify particular websites or enable the option to "Search the entire web."
- Click "Create" when you are done.
Find your search engine ID (CSE ID): After creating the engine, go to the "Setup" or "Overview" section of its control panel. Your Search engine ID will be displayed there. Copy this ID.

3. Update your project configuration

Finally, take the two values you have obtained and place them in your project's .env file:

# .env file
...
GOOGLE_API_KEY=your_google_api_key_here
GOOGLE_CSE_ID=your_google_cse_id_here
...

Setup Crawl4AI for web search

For web search, you must have the google api key and cse id, for

# Install Crawl4AI with browser dependencies
uv run crawl4ai-setup

# Verify installation
uv run crawl4ai-doctor

# Manual browser setup if needed
python -m playwright install chromium

Initial run and document ingestion

*# 1. Place your documents (.pdf, .md, .docx, .txt) into the /docs directory.
# 2. Ingest the documents into SurrealDB.*
uv run rag.py ingest

*# 3. Start the interactive chat interface.*
uv run rag.py chat

Showcase: a complex query in action

To prepare the CLI to accept a complex query, first we’ll need to give it some documents to ingest.

[ The CLI provides streamlined document ingestion with progress tracking and comprehensive file format support ]

Ingest documents from directory

# Ingest all supported documents from a directory
uv run rag.py ingest --docs_path=./docs

# Expected output:# 📚 Loading documents from ./docs
# 🔄 Processing 15 documents...
# ⚡ Generating embeddings...
# 💾 Storing in SurrealDB...
# ✅ Successfully ingested 15 documents (127 chunks)

The ingestion pipeline handles multiple formats: PDF .pdf, Text .txt, Markdown .md , and Word .docx

These formats are handled by the system's DocumentLoader, which is configured to recognise and parse these specific file extensions when you ingest documents from a directory.

Interactive chat and CLI usage

The system provides a rich command-line interface for all operations:

Start interactive chat session

# Launch interactive chat with reflexion engine
uv run rag.py chat

Document management commands

# View current configuration and status
uv run rag.py config
# Sample output: RAG Engine Configuration
========================
LLM Model: meta/Meta-Llama-3.1-405B-Instruct
Evaluation Model: cohere/Cohere-command-r
SurrealDB URL: wss://your-instance.surreal.cloud
Documents in store: 127
Web results in store: 45
Memory cache: Enabled (hit rate: 73%)

# Delete all documents (requires confirmation)
uv run rag.py delete

# Ingest new documents with progress tracking4
uv run rag.py ingest --docs_path=./new_docs

Sample output: reflexion in action

Here we see some real output from the reflexion engine processing a complex query about SurrealDB’s vector capabilities.

Query: compare SurrealDB's vector search with traditional vector databases

🔄 Cycle 1 - initial generation
📚 Retrieved: 3 documents (similarity: 0.85-0.91)
💭 SurrealDB offers several advantages over traditional vector databases:

1. **Unified data model**: unlike Pinecone or Weaviate, SurrealDB combines document
   storage, graph relationships, and vector search in one system [Source: architecture.md]

2. **Native SQL integration**: SurrealQL enables complex queries that join vector
   similarity with traditional filters [Source: surrealdb_guide.md]...

🔍 Self-evaluation - confidence: 0.78 (Below threshold: 0.85)
❓ Gap identified: missing performance benchmarks and indexing details

🔄 Cycle 2 - reflexion query: "SurrealDB HNSW indexing performance benchmarks"
📚 Retrieved: 5 documents (including web search)
💭 Enhanced response with performance data:

**Performance characteristics**:
- HNSW indexing provides ~10x faster queries than brute force [Source: benchmarks.md]
- Memory usage: ~4 bytes per dimension per vector for F32 type
- EFC parameter (500) balances accuracy vs speed...

🔍 Self-evaluation - confidence: 0.89
✅ Confidence threshold exceeded!

📊 Final metrics:
- Total cycles: 2
- Processing time: 6.8s
- Documents used: 8
- Final confidence: 0.89
- Cache hits: 3/8

Conclusion

The Reflexion RAG Engine demonstrates SurrealDB’s power as a unified platform for modern AI applications. By combining vector search, document storage, and real-time web integration in a single database, it eliminates architectural complexity while delivering superior performance.

Ready to build your own self-correcting AI system? Clone the repository, follow this setup guide, and start experimenting with SurrealDB’s vector capabilities. The future of AI is reflexive, multimodal, and built on unified data platforms.

Any questions about implementing reflexion loops with SurrealDB? Join the SurrealDB Discord, star the project repository, or share your own SurrealDB vector search experiments with the community!

Make a medical chatbot using GraphRAG with SurrealDB + LangChain

Dave MacLeod — Tue, 22 Jul 2025 04:58:40 +0000

Using LangChain

This post is a follow-up to this one from two weeks ago which detailed how to make a medical chatbot using SurrealDB and LangChain using Python.

Rust developers have an option to do the same too, thanks to a crate called langchain_rust which as of last year includes support for SurrealDB as a vector store. This implementation doesn't (yet!) include graph queries, but we can still use classic vector search to find recommendations for treatment for a patient.

To start off, use a command like cargo new medical_bot to create a new Cargo project, go into the project directory and add the following under [dependencies].

anyhow = "1.0.98"
langchain-rust = { version = "4.6.0", features = ["surrealdb", "mistralai"] }
serde = "1.0.219"
serde_json = "1.0.140"
serde_yaml = "0.9.34"
surrealdb = { version = "2.0.2", features = ["kv-mem"] }
tokio = "1.45.0"

The langchain-rust crate comes with OpenAI as a default, and includes a large number of features. We will add mistralai to show how easy it is to switch from one platform to another with only about two lines of different code.

The original post assumes that we have a big YAML document with a number of symptoms along with their possible treatments, which is what the serde_yaml dependency will let us work with.

- category: General Symptoms
  symptoms:
    - name: Fever
      description: Elevated body temperature, usually above 100.4°F (38°C).
      medical_practice: General Practice, Internal Medicine, Pediatrics
      possible_treatments:
        - Antipyretics (e.g., ibuprofen, acetaminophen)
        - Rest
        - Hydration
        - Treating the underlying cause

To keep the logic simple, we will take only the description of each symptom and its possible_treatments, giving us two structs that look like this.

#[derive(Debug, Deserialize)]
pub struct SymptomCategory {
    pub symptoms: Vec<Symptom>
}

#[derive(Debug, Deserialize)]
pub struct Symptom {
    pub description: String,
    pub possible_treatments: Vec<String>,
}

Then for each symptom, we will look through the possible treatments to create a document for each with text that looks like the following:

'Elevated body temperature, usually above 100.4°F (38°C).' can be treated by 'Antipyretics (e.g., ibuprofen, acetaminophen)'
'Elevated body temperature, usually above 100.4°F (38°C).' can be treated by 'Rest'
'Elevated body temperature, usually above 100.4°F (38°C).' can be treated by 'Hydration'
'Elevated body temperature, usually above 100.4°F (38°C).' can be treated by 'Treating the underlying cause'

This needs to be turned into a Document struct on the langchain-rust side, which looks like this.

pub struct Document {
    pub page_content: String,
    pub metadata: HashMap<String, Value>,
    pub score: f64,
}

The way to create a Document is via Document::new() which takes a String for the page_content, followed by an optional HashMap for any metadata. The score will be 0.0 when inserting and is only used later on when a similarity search is performed to return a Document.

For the metadata, we will add the other possible treatments so that any user will be able to first see a recommended treatment for a symptom, followed by all possible treatments for reference.

The Value part of the Document struct is a serde_json Value, which is why we have serde_json inside our Cargo.toml as well.

All in all, the logic to grab the YAML file and turn it ito a Vec of Documents looks like this.

fn get_docs() -> Result<Vec<Document>, Error> {
    let yaml_str = std::fs::read_to_string("symptoms.yaml")?;
    let categories: Vec<SymptomCategory> = serde_yaml::from_str(&yaml_str)?;

    let symptoms = categories
        .into_iter()
        .flat_map(|cat| cat.symptoms)
        .collect::<Vec<Symptom>>();
    Ok(symptoms
        .into_iter()
        .flat_map(|symptom| {
            let metadata = HashMap::from([
                (
                    "possible treatments".to_string(),
                    Value::from(symptom.possible_treatments.clone()),
                )
            ]);
            symptom
                .possible_treatments
                .into_iter()
                .map(|treat| {
                    Document::new(format!("'{}' can be treated by '{treat}'.", symptom.description.clone()))
                        .with_metadata(metadata.clone())
                })
                .collect::<Vec<Document>>()
        })
        .collect::<Vec<Document>>())
}

With this taken care of, it's time to do some setup inside main(). First we need to start running the database, which can be run in memory or via some other path such as an address to a Surreal Cloud or a locally running instance.

let database_url = std::env::var("DATABASE_URL").unwrap_or("memory".to_string());

let db = surrealdb::engine::any::connect(database_url).await?;
db.query("DEFINE NAMESPACE test; USE NAMESPACE test; DEFINE DATABASE test;")
    .await?;

//  Uncomment the following lines to authenticate if necessary
//  .user(surrealdb::opt::auth::Root {
//      username: "root".into(),
//      password: "secret".into(),
//  });

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

The next step is to initialize an embedder from the langchain-rust crate. Here we have the choice of an OpenAiEmbedder or MistralAIEmbedder thanks to the added feature flag.

After that comes a StoreBuilder struct used to initiate a SurrealDB Store, which takes an embedder, a database, and a number of dimensions - 1536 in this case for OpenAI. If using Mistral, the dimensions would be 1024.

Note that we are wrapping this in an Arc so that the store can start adding the documents on startup inside a separate task without making the user wait to see any CLI output.

At the very end is an .initialize() method which defines some tables and fields which will be used when doing similarity searches.

// Initialize Embedder
let embedder = OpenAiEmbedder::default();
// Embedding size is 1024 in this case
// let embedder = MistralAIEmbedder::try_new()?;

// Initialize the SurrealDB Vector Store
let store = Arc::new(
    StoreBuilder::new()
        .embedder(embedder)
        .db(db)
        .vector_dimensions(1536)
        .build()
        .await
        .map_err(|e| anyhow!(e.to_string()))?,
);

store
    .initialize()
    .await
    .map_err(|e| anyhow!(e.to_string()))?;

Then we will clone the Arc to allow the store to be passed into a new blocking task to add the Vec<Document> returned by our get_docs() function. Inside this is a method called add_documents() which is a built-in method from the rust-langchain crate.

let arced = Arc::clone(&store);

tokio::task::spawn_blocking(move || {
    let docs = get_docs()?;
    Handle::current()
        .block_on(arced.add_documents(&docs, &VecStoreOptions::default()))
        .map_err(|e| anyhow::anyhow!("{e}"))?;
    Ok::<(), Error>(())
});

While the store adds these documents in its own task, we will start a simple CLI that asks the user for a query, and then passes this into the built-in .similarity_search() method. This method allows us to specify the number of documents to return and a minimum similarity score, to which we will go with 2 and 0.6.

The rest of the code just involves setting up a simple loop to handle user output, along with the results of the output of the .similarity_search() method.

    loop {
        // Ask for user input
        print!("Query> ");
        stdout().flush()?;
        let mut query = String::new();
        stdin().read_line(&mut query)?;

        let results = store
            .similarity_search(
                &query,
                2,
                &VecStoreOptions::default().with_score_threshold(0.6),
            )
            .await
            .map_err(|e| anyhow!(e.to_string()))?;

        if results.is_empty() {
            println!("No results found.");
        } else {
            println!("Possible symptoms:");
            results.iter().for_each(|r| {
                println!("{}\n All possible treatments: ", r.page_content);
                if let Some(Value::Array(array)) = r.metadata.get("possible treatments") {
                    for val in array {
                        if let Value::String(s) = val {
                            println!("  {s}");
                        }
                    }
                };
                println!();
            });
        };
    }

As the output shows, our bot is capable of returning meaningful results despite only having access to data from 236 lines of YAML!

Query> I've been exercising a lot outside and it's really hot.
Possible symptoms:
'Elevated body temperature, usually above 100.4°F (38°C).' can be treated by 'Hydration'.
 All possible treatments: 
  Antipyretics (e.g., ibuprofen, acetaminophen)
  Rest
  Hydration
  Treating the underlying cause

'Elevated body temperature, usually above 100.4°F (38°C).' can be treated by 'Rest'.
 All possible treatments: 
  Antipyretics (e.g., ibuprofen, acetaminophen)
  Rest
  Hydration
  Treating the underlying cause

Query> I get dizzy sometimes
Possible symptoms:
'Feeling lightheaded, unsteady, or experiencing a sensation that the room is spinning.' can be treated by 'Medications to reduce nausea or dizziness'.
 All possible treatments: 
  Addressing underlying cause (e.g., inner ear issues, low blood pressure)
  Vestibular rehabilitation
  Medications to reduce nausea or dizziness
  Hydration

'Feeling lightheaded, unsteady, or experiencing a sensation that the room is spinning.' can be treated by 'Addressing underlying cause (e.g., inner ear issues, low blood pressure)'.
 All possible treatments: 
  Addressing underlying cause (e.g., inner ear issues, low blood pressure)
  Vestibular rehabilitation
  Medications to reduce nausea or dizziness
  Hydration

Query> What should I do in life?
Possible symptoms:
'Feeling unusually drained, lacking energy, or experiencing persistent exhaustion.' can be treated by 'Lifestyle modifications (diet, exercise)'.
 All possible treatments: 
  Rest and adequate sleep
  Lifestyle modifications (diet, exercise)
  Addressing underlying medical conditions (e.g., anemia, thyroid disorders)
  Stress management

'Feeling unusually drained, lacking energy, or experiencing persistent exhaustion.' can be treated by 'Stress management'.
 All possible treatments: 
  Rest and adequate sleep
  Lifestyle modifications (diet, exercise)
  Addressing underlying medical conditions (e.g., anemia, thyroid disorders)
  Stress management

Want to give it a try yourself? Save the content at this link to the filename symptoms.yaml and then copy the following code into your cargo project, then set the env var OPENAI_API_KEY or MISTRAL_API_KEY along with cargo run.

You can also give a crate called archiver a try, which has its own command-line interface to use SurrealDB with Ollama via the same crate we used in this post.

// To run this example execute: `cargo run` in the folder. Be sure to have an OpenAPI key
// set to the OPENAI_API_KEY env var
// or MISTRAL_API_KEY if using Mistral

use anyhow::{Error, anyhow};
use langchain_rust::{
    embedding::{MistralAIEmbedder, openai::openai_embedder::OpenAiEmbedder},
    schemas::Document,
    vectorstore::{VecStoreOptions, VectorStore, surrealdb::StoreBuilder},
};
use serde::Deserialize;
use serde_json::Value;
use std::{
    collections::HashMap,
    io::{Write, stdin, stdout},
    sync::Arc,
};
use tokio::runtime::Handle;

#[derive(Debug, Deserialize)]
pub struct SymptomCategory {
    pub category: String,
    pub symptoms: Vec<Symptom>,
}

#[derive(Debug, Deserialize)]
pub struct Symptom {
    pub name: String,
    pub description: String,
    pub possible_treatments: Vec<String>,
}

fn get_docs() -> Result<Vec<Document>, Error> {
    let yaml_str = std::fs::read_to_string("symptoms.yaml")?;
    let categories: Vec<SymptomCategory> = serde_yaml::from_str(&yaml_str)?;

    let symptoms = categories
        .into_iter()
        .flat_map(|cat| cat.symptoms)
        .collect::<Vec<Symptom>>();
    Ok(symptoms
        .into_iter()
        .flat_map(|symptom| {
            let metadata = HashMap::from([(
                "possible treatments".to_string(),
                Value::from(symptom.possible_treatments.clone()),
            )]);
            symptom
                .possible_treatments
                .into_iter()
                .map(|treat| {
                    Document::new(format!(
                        "'{}' can be treated by '{treat}'.",
                        symptom.description.clone()
                    ))
                    .with_metadata(metadata.clone())
                })
                .collect::<Vec<Document>>()
        })
        .collect::<Vec<Document>>())
}

#[tokio::main]
async fn main() -> Result<(), Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap_or("memory".to_string());

    let db = surrealdb::engine::any::connect(database_url).await?;
    db.query("DEFINE NAMESPACE test; USE NAMESPACE test; DEFINE DATABASE test;")
        .await?;

    //  Uncomment the following lines to authenticate if necessary
    //  .user(surrealdb::opt::auth::Root {
    //      username: "root".into(),
    //      password: "secret".into(),
    //  });

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

    // Initialize Embedder
    let embedder = OpenAiEmbedder::default();
    // Embedding size is 1024 in this case
    // let embedder = MistralAIEmbedder::try_new()?;

    // Initialize the SurrealDB Vector Store
    let store = Arc::new(
        StoreBuilder::new()
            .embedder(embedder)
            .db(db)
            .vector_dimensions(1536)
            .build()
            .await
            .map_err(|e| anyhow!(e.to_string()))?,
    );

    // Intialize the tables in the database. This is required to be done only once.
    store
        .initialize()
        .await
        .map_err(|e| anyhow!(e.to_string()))?;

    let arced = Arc::clone(&store);

    tokio::task::spawn_blocking(move || {
        let docs = get_docs()?;
        Handle::current()
            .block_on(arced.add_documents(&docs, &VecStoreOptions::default()))
            .map_err(|e| anyhow::anyhow!("{e}"))?;
        Ok::<(), Error>(())
    });

    loop {
        // Ask for user input
        print!("Query> ");
        stdout().flush()?;
        let mut query = String::new();
        stdin().read_line(&mut query)?;

        let results = store
            .similarity_search(
                &query,
                2,
                &VecStoreOptions::default().with_score_threshold(0.6),
            )
            .await
            .map_err(|e| anyhow!(e.to_string()))?;

        if results.is_empty() {
            println!("No results found.");
        } else {
            println!("Possible symptoms:");
            results.iter().for_each(|r| {
                println!("{}\n All possible treatments: ", r.page_content);
                if let Some(Value::Array(array)) = r.metadata.get("possible treatments") {
                    for val in array {
                        if let Value::String(s) = val {
                            println!("  {s}");
                        }
                    }
                };
                println!();
            });
        };
    }
    Ok(())
}

Semantic search in Rust using SurrealDB and Mistral AI

Dave MacLeod — Wed, 09 Jul 2025 06:04:51 +0000

The purpose of this post is to demonstrate how to use the Rust SDK to store Mistral AI embeddings as SurrealDB vectors, which can then be queried natively in SurrealQL to perform semantic search.

This guide uses Rust's mistralai-client crate to generate embeddings, but the code below can be modified to suit other languages that have clients for Mistral's AI platform. If you are a Python user, check out this page in the documentation for another ready-made example.

Setup

Setting up an embedded SurrealDB database requires no installation and can be done in just a few lines of code. After creating a new Cargo project with cargo new project_name and going into the project folder, add the following dependencies inside Cargo.toml:

anyhow = "1.0.98"
mistralai-client = "0.14.0"
serde = "1.0.219"
surrealdb = { version = "2.3", features = ["kv-mem"] }
tokio = "1.45.0"

You can add the same dependencies on the command line through a single command:

cargo add anyhow mistralai-client serde tokio surrealdb --features surrealdb/kv-mem

Setting up a SurrealDB database in Rust is as easy as calling the connect function with "memory" to instantiate an embedded database in memory. This code uses anyhow to allow the question mark operator to be used, but you can also just begin with .unwrap() everywhere and eventually move on to your own preferred error handling.

use anyhow::Error;
use surrealdb::engine::any::connect;

#[tokio::main]
async fn main() -> Result<(), Error> {
    let db = connect("memory").await?;
    Ok(())
}

If you have a running Cloud or local instance, you can pass that path into the connect() function instead.

// Cloud address
let db = connect("wss://cloud-docs-068rp16e0hsnl62vgooa7omjks.aws-euw1.staging.surrealdb.cloud").await?;

// Local address
let db = connect("ws://localhost:8000").await?;

After connecting, we will select a namespace and database name, such as ns and db.

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

Create a vector table and index

Next we'll create a table called document to store documents and embeddings, along with an index for the embeddings. The statements look like this:

DEFINE TABLE document;
DEFINE FIELD text ON document TYPE string;
DEFINE FIELD embedding ON document TYPE array<float>;
DEFINE INDEX hnsw_embed ON document FIELDS embedding HNSW DIMENSION 1024 DIST COSINE;

The important piece to understand is the relationship between the embedding field, a simple array of floats, and the index that we have given the name hnsw_embed. The size of the vector (1024 here) represents the number of dimensions in the embedding. This is to match Mistral AI's mistral-embed model, which uses 1024 as its length.

The HNSW index is not strictly necessary to use the KNN operator (<||>) to find an embedding's closest neighbours, and for our small sample code we will use the simple brute force method which chooses an algorithm such as Euclidean, Hamming, and so on. The following is the code that we will use, which uses the cosine of an embedding to find the four closest neighbours.

SELECT 
    text,
    vector::distance::knn() AS distance FROM document
    WHERE embedding <|4,COSINE|> $embeds
    ORDER BY distance;

As the dataset grows, however, the syntax can be changed to use the HNSW index, by replacing an algorithm with a number that represents the size of the dynamic candidate list. This index is recommended when a small loss of accuracy is acceptable in order to preserve performance.

SELECT 
    text,
    vector::distance::knn() AS distance FROM document
    WHERE embedding <|4,40|> $embeds
    ORDER BY distance;

Another option is to use the MTREE index method.

Inside the Rust SDK we can put all four of these inside a single .query() call and then add a line to see if there are errors inside any of them.

let mut res = db
    .query(
        "DEFINE TABLE document;
DEFINE FIELD text ON document TYPE string;
DEFINE FIELD embedding ON document TYPE array<float>;
DEFINE INDEX hnsw_embed ON document FIELDS embedding HNSW DIMENSION 1024 DIST COSINE;",
    )
    .await?;
for (index, error) in res.take_errors() {
    println!("Error in query {index}: {error}");
}

Generate Mistral AI embeddings

At this point, you will need a key to interact with Mistral AI's platform. They offer a free tier for experimentation, after which you will be able to create a key to interact with it via the code below.

The code in this page will still work without a proper code, but the request to the Mistral AI API will end up returning the following error message.

Error: ApiError: 401 Unauthorized: {"detail":"Unauthorized"}

The best way to set the key is as an environment variable, which we will set to be a static called KEY. The client will look for one called MISTRAL_API_KEY, though you can change this when setting up the Mistral AI Rust client if you like.

// Looks for MISTRAL_API_KEY
let client = Client::new(Some(KEY.to_string()), None, None, None)?;
// Looks for OTHER_ENV_VAR
let client = Client::new(Some(KEY.to_string()), Some("OTHER_ENV_VAR".to_string()), None, None)?;

Using a LazyLock will let us call it via std::env::var() function the first time it is accessed. You can of course simply put it into a const for simplicity when first testing, but always remember to never hard-code API keys in your code in production.

static KEY: LazyLock<String> = LazyLock::new(|| {
    std::env::var("MISTRAL_API_KEY").unwrap()
});

And then run the code like this:

MISTRAL_API_KEY=whateverthekeyis cargo run

Or like this if you are using PowerShell on Windows.

$env:MISTRAL_API_KEY = "whateverthekeyis"
cargo run

We can also create a const MODEL to hold the Mistral AI model used, which in this case is an EmbedModel::MistralEmbed.

const MODEL: EmbedModel = EmbedModel::MistralEmbed;

Inside main(), we will then create a client from the mistralai-client crate.

let client = Client::new(Some(KEY.to_string()), None, None, None)?;

We'll use that to generate a Mistral AI embedding using the mistral-embed model. The mistralai-client has both sync and async functions that take a Vec<String>, and since SurrealDB uses the tokio runtime, we'll call the async .embeddings_async() method.

let input = vec!["Joram is the main character in the Darksword Trilogy.".to_string()];

let result = client.embeddings_async(MODEL, input, None).await?;
println!("{:?}", result);

The output in your console should show a massive amount of floats, 1024 of them to be precise. That's the embedding for this input!

Store embeddings in database

Now that we have the embedding returned from the Mistral AI client, we can store it in the database. The response returned from the mistralai-client crate looks like this, with a Vec of EmbeddingResponseDataItem structs that hold a Vec<f32>.

pub struct EmbeddingResponse {
    pub id: String,
    pub object: String,
    pub model: EmbedModel,
    pub data: Vec<EmbeddingResponseDataItem>,
    pub usage: ResponseUsage,
}

pub struct EmbeddingResponseDataItem {
    pub index: u32,
    pub embedding: Vec<f32>,
    pub object: String,
}

We know that our simple request only returned a single embedding, so .remove(0) will do the job. In a more complex codebase you would probably opt for a match on .get(0) to handle any possible errors.

let embeds = result.data.remove(0).embedding;

There are a number of ways to work with or avoid structs when using the Rust SDK, but we'll just go with two basic structs: one to represent the input into a .create() statement, which will implement Serialize, and another that implements Deserialize to show the result.

#[derive(Serialize)]
struct DocumentInput {
    text: String,
    embedding: Vec<f32>,
}

#[derive(Debug, Deserialize)]
struct Document {
    id: RecordId,
    embedding: Vec<f32>,
    text: String,
}

Once that is done, we can print out the created documents as a Document struct. We'll fiddle with the code a bit to have the input start as a &str which will be turned into a String in order to get the embedding, as well as to create a Document struct.

let input = "Octopuses solve puzzles and escape enclosures, showing advanced intelligence.";

let mut result = client
    .embeddings_async(MODEL, vec![input.to_string()], None)
    .await?;
let embeds = result.data.remove(0).embedding;
let in_db = db
    .create::<Option<Document>>("document")
    .content(DocumentInput {
        text: input.into(),
        embedding: embeds.to_vec(),
    })
    .await?;
println!("{in_db:?}");

We should now add some more document records. To do this, we'll move the logic to create them inside a function of its own. Since the embeddings_async() method takes a single Vec<String>, we'll first clone it to keep the original Vec<String> around, then zip it together with the embeddings returned so that they can be put into the database along with the original input.

async fn create_embeds(
    input: Vec<String>,
    db: &Surreal<Any>,
    client: &Client,
) -> Result<(), Error> {
    let cloned = input.clone();
    let embeds = client.embeddings_async(MODEL, input, None).await?;
    let zipped = cloned
        .into_iter()
        .zip(embeds.data.into_iter().map(|item| item.embedding));

    for (text, embeds) in zipped {
        let _in_db = db
            .create::<Option<Document>>("document")
            .content(DocumentInput {
                text,
                embedding: embeds,
            })
            .await?;
    }
    Ok(())
}

Then we'll create four facts for each of four topics: sea creatures, Korean and Japanese cities, historical figures, and planets of the Solar System (including the dwarf planet Ceres).

let embeds = [
    "Octopuses solve puzzles and escape enclosures, showing advanced intelligence.",
    "Sharks exhibit learning behavior, but their intelligence is instinct-driven.",
    "Sea cucumbers lack a brain and show minimal cognitive response.",
    "Clams have simple nervous systems with no known intelligent behavior.",
    //
    "Seoul is South Korea’s capital and a global tech hub.",
    "Sejong is South Korea’s planned administrative capital.",
    "Busan a major South Korean port located in the far southeast.",
    "Tokyo is Japan’s capital, known for innovation and dense population.",
    //
    "Wilhelm II was Germany’s last Kaiser before World War I.",
    "Cyrus the Great founded the Persian Empire with tolerant rule.",
    "Napoleon Bonaparte was a French emperor and brilliant military strategist.",
    "Aristotle was a Greek philosopher who shaped Western intellectual thought.",
    //
    "Venus’s atmosphere ranges from scorching surface to Earth-like upper clouds.",
    "Mars has a thin, cold atmosphere with seasonal dust storms.",
    "Ceres has a tenuous exosphere with sporadic water vapor traces.",
    "Saturn’s atmosphere spans cold outer layers to a deep metallic hydrogen interior",
]
.into_iter()
.map(|s| s.to_string())
.collect::<Vec<String>>();

create_embeds(embeds, &db, &client).await?;

Semantic search

Finally let's perform semantic search over the embeddings in our database. We'll go with this query that uses the KNN operator to return the closest four matches to an embedding.

SELECT 
    text,
    vector::distance::knn() AS distance FROM document
    WHERE embedding <|4,COSINE|> $embeds
    ORDER BY distance;

You can customise this with other algorithms such as Euclidean, Hamming, and so on.

We will then put this into a separate function called ask_question() which looks similar create_embed(), except that it first prints out its input, and then uses its embedding retrieved from Mistral to query the database against existing documents instead of creating a new document.

async fn ask_question(input: &str, db: &Surreal<Any>, client: &Client) -> Result<(), Error> {
    println!("{input}");
    let embeds = client
        .embeddings_async(MODEL, vec![input.to_string()], None)
        .await?
        .data
        .remove(0)
        .embedding;

    let mut response = db.query("SELECT text, vector::distance::knn() AS distance FROM document WHERE embedding <|4,COSINE|> $embeds ORDER BY distance;").bind(("embeds", embeds)).await?;
    let as_val: Value = response.take(0)?;
    println!("{as_val}\n");
    Ok(())
}

Finally, we will call this function a few times inside main() to confirm that the results are what we expect them to be, printing out the results of each so that we can eyeball them and make sure that they are what we expect them to be.

ask_question("Which Korean city is just across the sea from Japan?", &db, &client).await?;
ask_question("Who was Germany's last Kaiser?", &db, &client).await?;
ask_question("Which sea animal is most intelligent?", &db, &client).await?;
ask_question("Which planet's atmosphere has a part with the same temperature as Earth?", &db, &client).await?;

The output shows that the facts that fit most to our questions end up displayed first, with differing distance depending on how close the other facts were. Octopuses end up smarter than sharks (which is true), but the "learning behavior" part of our input does end up making sharks score pretty close. On the other extreme, Wilhelm II is clearly the only input that comes anywhere close to "Germany's last Kaiser", with Napoleon Bonaparte way behind. Poor Aristotle doesn't make it into any results, with "Sejong is South Korea's planned administrative capital" slightly closer semantically in terms of "Who was Germany's last Kaiser".

Which Korean city is just across the sea from Japan?
[{ distance: 0.19170371029549582f, text: 'Busan is a major South Korean port located in the far southeast.' }, { distance: 0.2399314515762122f, text: 'Tokyo is Japan’s capital, known for innovation and dense population.' }, { distance: 0.2443623703771407f, text: 'Sejong is South Korea’s planned administrative capital.' }, { distance: 0.24488082839731895f, text: 'Seoul is South Korea’s capital and a global tech hub.' }]

Who was Germany's last Kaiser?
[{ distance: 0.11228576780228805f, text: 'Wilhelm II was Germany’s last Kaiser before World War I.' }, { distance: 0.2957177300085634f, text: 'Napoleon Bonaparte was a French emperor and brilliant military strategist.' }, { distance: 0.34394473621670896f, text: 'Cyrus the Great founded the Persian Empire with tolerant rule.' }, { distance: 0.34911517400935843f, text: 'Sejong is South Korea’s planned administrative capital.' }]

Which sea animal is most intelligent?
[{ distance: 0.2342596053829904f, text: 'Octopuses solve puzzles and escape enclosures, showing advanced intelligence.' }, { distance: 0.24131327939924785f, text: 'Sharks exhibit learning behavior, but their intelligence is instinct-driven.' }, { distance: 0.2426242772516931f, text: 'Clams have simple nervous systems with no known intelligent behavior.' }, { distance: 0.24474598154128135f, text: 'Sea cucumbers lack a brain and show minimal cognitive response.' }]

Which planet's atmosphere has a part with the same temperature as Earth?
[{ distance: 0.20653440713083582f, text: 'Venus’s atmosphere ranges from scorching surface to Earth-like upper clouds.' }, { distance: 0.23354208810464594f, text: 'Mars has a thin, cold atmosphere with seasonal dust storms.' }, { distance: 0.24560810032473468f, text: 'Saturn’s atmosphere spans cold outer layers to a deep metallic hydrogen interior' }, { distance: 0.2761595357544341f, text: 'Ceres has a tenuous exosphere with sporadic water vapor traces.' }]

As the database grows, you could also change the <|4,COSINE|> part of the query to something like <|4,40|> to see the results using the HNSW index instead of the brute force method.

Finally, here is all of the code for you to run and modify as you wish.

use std::sync::LazyLock;

use anyhow::Error;
use mistralai_client::v1::{client::Client, constants::EmbedModel};
use serde::{Deserialize, Serialize};
use surrealdb::{
    RecordId, Surreal, Value,
    engine::any::{Any, connect},
};

static KEY: LazyLock<String> = LazyLock::new(|| std::env::var("MISTRAL_API_KEY").unwrap());

// Experiment plan
const MODEL: EmbedModel = EmbedModel::MistralEmbed;

#[derive(Serialize)]
struct DocumentInput {
    text: String,
    embedding: Vec<f32>,
}

#[derive(Debug, Deserialize)]
struct Document {
    id: RecordId,
    embedding: Vec<f32>,
    text: String,
}

async fn create_embeds(
    input: Vec<String>,
    db: &Surreal<Any>,
    client: &Client,
) -> Result<(), Error> {
    let cloned = input.clone();
    let embeds = client.embeddings_async(MODEL, input, None).await?;
    let zipped = cloned
        .into_iter()
        .zip(embeds.data.into_iter().map(|item| item.embedding));

    for (text, embeds) in zipped {
        let _in_db = db
            .create::<Option<Document>>("document")
            .content(DocumentInput {
                text,
                embedding: embeds,
            })
            .await?;
    }
    Ok(())
}

async fn ask_question(input: &str, db: &Surreal<Any>, client: &Client) -> Result<(), Error> {
    println!("{input}");
    let embeds = client
        .embeddings_async(MODEL, vec![input.to_string()], None)
        .await?
        .data
        .remove(0)
        .embedding;

    let mut response = db.query("SELECT text, vector::distance::knn() AS distance FROM document WHERE embedding <|4,COSINE|> $embeds ORDER BY distance;").bind(("embeds", embeds)).await?;
    let as_val: Value = response.take(0)?;
    println!("{as_val}\n");
    Ok(())
}

#[tokio::main]
async fn main() -> Result<(), Error> {
    let db = connect("memory").await.unwrap();

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

    let mut res = db
        .query(
            "DEFINE TABLE document;
             DEFINE FIELD text ON document TYPE string;
             DEFINE FIELD embedding ON document TYPE array<float>;
             DEFINE INDEX hnsw_embed ON document FIELDS embedding HNSW DIMENSION 1024 DIST COSINE;",
        )
        .await
        .unwrap();
    for (index, error) in res.take_errors() {
        println!("Error in query {index}: {error}");
    }

    let client = Client::new(Some(KEY.to_string()), None, None, None)?;

    let embeds = [
        "Octopuses solve puzzles and escape enclosures, showing advanced intelligence.",
        "Sharks exhibit learning behavior, but their intelligence is instinct-driven.",
        "Sea cucumbers lack a brain and show minimal cognitive response.",
        "Clams have simple nervous systems with no known intelligent behavior.",
        //
        "Seoul is South Korea’s capital and a global tech hub.",
        "Sejong is South Korea’s planned administrative capital.",
        "Busan is a major South Korean port located in the far southeast.",
        "Tokyo is Japan’s capital, known for innovation and dense population.",
        //
        "Wilhelm II was Germany’s last Kaiser before World War I.",
        "Cyrus the Great founded the Persian Empire with tolerant rule.",
        "Napoleon Bonaparte was a French emperor and brilliant military strategist.",
        "Aristotle was a Greek philosopher who shaped Western intellectual thought.",
        //
        "Venus’s atmosphere ranges from scorching surface to Earth-like upper clouds.",
        "Mars has a thin, cold atmosphere with seasonal dust storms.",
        "Ceres has a tenuous exosphere with sporadic water vapor traces.",
        "Saturn’s atmosphere spans cold outer layers to a deep metallic hydrogen interior",
    ]
    .into_iter()
    .map(|s| s.to_string())
    .collect::<Vec<String>>();

    create_embeds(embeds, &db, &client).await?;

    ask_question("Which Korean city is just across the sea from Japan?", &db, &client).await?;
    ask_question("Who was Germany's last Kaiser?", &db, &client).await?;
    ask_question("Which sea animal is most intelligent?", &db, &client).await?;
    ask_question("Which planet's atmosphere has a part with the same temperature as Earth?", &db, &client).await?;

    Ok(())
}

Any questions or thoughts about this or semantic search using SurrealDB? Feel free to drop by our community to get in touch.

Ten tips and tricks for your database schema

Dave MacLeod — Thu, 26 Jun 2025 05:02:17 +0000

There are a lot of places to learn how to use SurrealDB, including the documentation, our fundamentals course, the Tour of SurrealDB, and even a full book that teaches SurrealDB through a story set centuries in the future!

But sometimes a quick rundown of a few tips and tricks hits the spot, and that's what this blogpost is about. In this post, let's look at some of the ways to put together a schema that works for you and not the other way around.

Did you know that you can...

1: Use a `set` for an array of distinct items

The lowly set is a subtype of array that doesn't get a great deal of attention, but has the convenience of holding no duplicate items.

DEFINE FIELD ordered_unique ON stuff TYPE set VALUE $value.sort();

CREATE ONLY stuff SET ordered_unique = [8,7,8,8,8,8,6,45,3] RETURN VALUE ordered_unique;
-- Returns this:
[ 3, 6, 7, 8, 45 ]

As the query above shows, a set is just a subtype and not its own type so you can pass in an array into anything expecting a set. The only difference is that it will never hold a duplicate item.

2: Define arrays and sets with a type and maximum size

In addition to a type, both arrays and sets can have a maximum number of items built into the type definition itself. The definition below pairs this with an assertion using the array::all() function to also ensure that every item in the bytes field is between 0 and 255.

DEFINE FIELD bytes ON data TYPE array<int, 640> ASSERT $value.all(|$int| $int IN 0..=255);

3: Define nested indexes

Even the individual indexes of an array can be defined. This is useful for data types like RGB colours that can must be exactly three items in length. This time the schema uses an ASSERT value.len() = 3 instead of array<3> to ensure that the array is an exact length instead of a maximum length.

DEFINE FIELD rgb ON colour TYPE array ASSERT $value.len() = 3;
DEFINE FIELD rgb[0] ON colour TYPE int ASSERT $value IN 0..=255;
DEFINE FIELD rgb[1] ON colour TYPE int ASSERT $value IN 0..=255;
DEFINE FIELD rgb[2] ON colour TYPE int ASSERT $value IN 0..=255;

CREATE colour SET rgb = [0, 2, 30];

4. Use `FLEXIBLE` objects and defined fields in `SCHEMAFULL` tables

The documentation mentions that a SCHEMAFULL table requires objects to have the FLEXIBLE keyword in order to treat them as free-form objects. Without this keyword, a TYPE object only tells the database that an object is to be expected but a SCHEMAFULL table disallows any field that hasn't been defined yet.

DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD name ON user TYPE string;
DEFINE FIELD metadata ON user TYPE object;

-- Returns {} for `metadata`
CREATE user SET name = "Billy", metadata = {
    created_at: time::now(),
    age: 5
};

With the FLEXIBLE keyword the metadata field will now work, accepting any and all input.

DEFINE FIELD metadata ON user TYPE object FLEXIBLE;

CREATE user SET name = "Billy", metadata = {
    created_at: time::now(),
    age: 5
};

However, you can also simply define each field of an object in the same way you would with the field of a table. This allows the metadata field to hold these fields and ignore all other data used during a CREATE or INSERT statement.

DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD name ON user TYPE string;
DEFINE FIELD metadata ON user TYPE object;
DEFINE FIELD metadata.created_at ON user TYPE datetime;
DEFINE FIELD metadata.age ON user TYPE int;

CREATE user SET name = "Billy", metadata = {
    created_at: time::now(),
    age: 5,
    wrong_field: "WRONG DATA"
};

5. Use `THROW` to add more detailed error messages to `ASSERT` clauses

A DEFINE FIELD statement allows an ASSERT clause to be added in order to ensure that the value, which here is represented as the parameter $value, meets certain expectations. A simple example here makes sure that the name field on the person table is under 20 characters in length.

DEFINE FIELD name ON person TYPE string ASSERT $value.len() < 20;

CREATE person SET name = "Mr. Longname who has much too long a name";

In this case, the default error message is pretty good.

"Found 'Mr. Longname who has much too long a name' for field `name`, with record `person:2gpvut914k1qfysqs3lc`, but field must conform to: $value.len() < 20"

However, ASSERT only expects a truthy value at the end and otherwise isn't concerned at all with what happens before. This means that you can outright customize the logic, including a custom error message. Let's give this a try.

DEFINE FIELD name ON person TYPE string ASSERT {
    IF $value.len() >= 20 {
        THROW "`" + <string>$value + "` too long, must be under 20 characters. Up to `" + $value.slice(0,19) + "` is acceptable";
    } ELSE {
       RETURN true;
    }
};

CREATE person SET name = "Mr. Longname who has much too long a name";

Not bad!

'An error occurred: `Mr. Longname who has much too long a name` too long, must be under 20 characters.
Up to `Mr. Longname who ha` is acceptable'

6. Use formatters on internal datetimes for strings with alternative formats

A lot of legacy systems require datetimes to be displayed in a format that doesn't quite match a datetime.

That doesn't mean that you have to give up the precision of a datetime though. By using the time::format() function, you can keep the actual stored date as a precise SurrealQL datetime and then use that to output a string in any format you like.

DEFINE FIELD created_at ON user VALUE time::now() READONLY;
DEFINE FIELD since ON user VALUE time::format(created_at, "%Y-%m-%d");

CREATE user RETURN id, since;

Output:

[
    {
        id: user:50s2riya8fm3cdbrhwpe,
        since: '2025-06-12'
    }
]

7. Use `!!$value` in `DEFINE` statements

As the ! operator reverses the truthiness of a value, using it twice in a row as !! returns a value's truthiness. As empty and default values (such as 0 for numbers) are considered to be non-truthy, this operator is handy if you want to ensure that a value is both present and not empty.

DEFINE FIELD name ON character TYPE string;
DEFINE FIELD metadata ON character TYPE object;
-- Works because "" is of type string
CREATE character SET name = "", metadata = {};

DEFINE FIELD OVERWRITE name ON character TYPE string ASSERT !!$value;
-- Now returns an error because "" and {} are non-truthy
CREATE character SET name = "", metadata = {};

8. Use `DEFINE PARAM` for clarity

If you find that parts of your table- or field-specific code are getting a bit long, it might be time to think about moving parts of it to a database-wide parameter.

DEFINE FIELD month_published ON book TYPE string ASSERT $value IN ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];

Doing so not only makes the code cleaner, but makes it easy to reuse in other parts of the schema as well.

DEFINE PARAM $MONTHS VALUE ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];

DEFINE FIELD month_published ON book TYPE string ASSERT $value IN $MONTHS;
DEFINE FUNCTION fn::do_something_with_month($input: string) {
    IF !($input IN $MONTHS) {
        THROW "Some error about wrong input";
    } ELSE {
        // do something with months here
    }
};

9. Use literals to return rich error output

Error types in programming languages often take the form of a long list of possible things that could go wrong. SurrealQL's literal type allows you to specify a list of all possible forms it could take, making it the perfect type for error logic.

DEFINE PARAM $ERROR_CODES VALUE [200, 300, 400, 500];

DEFINE FUNCTION fn::return_response($input: 
    { type: "internal_error", message: string } |
    { type: "bad_request", message: string } | 
    { type: "invalid_date", got: any, expected: "YYYY-MM-DD" } |
    int) {
    IF $input.is_int() {
        IF $input IN $ERROR_CODES {
            RETURN $input
        } ELSE {
            THROW "Input must be one of " + <string>$ERROR_CODES;
        }
    } ELSE {
        RETURN $input
    }
};

fn::return_response(500);
fn::return_response({ type: "internal_error", message: "You can't do that"});

10. Use graph queries in the schema

While graph queries are usually seen in SELECT statements in the documentation, they can live inside your database schema just like any other datatype or expression. In the schema below for a family tree, any inserted record must either have a parent (via the <-parent_of<-person path) or be first_generation.

DEFINE FIELD parents ON person ASSERT <-parent_of<-person OR first_generation;

CREATE person:one SET first_generation = true;
CREATE person:two;

RELATE person:one->parent_of->person:two;
CREATE person:two;

By the way, this pattern is possible because RELATE statements can be used before the records to relate exist. To disallow this, you can add the ENFORCED clause to a DEFINE TABLE table_name TYPE RECORD definition.

Got your own tip or have any other schema tips you'd like to see? Drop by anywhere in the SurrealDB community to share your thoughts!

Enhance your musical skills with Surrealist’s Graph View

Dave MacLeod — Mon, 09 Jun 2025 06:21:16 +0000

A few months ago we released Surrealist's graph view, a fantastic way to visualize your data. The blog post introducing the feature shows you how to use Surrealist to visualize train stations, cities and countries, company org charts, the complex web of international agreements in Europe, and even random art.

But did you know that you can also use Surrealist to let you know which chord to play next when practicing piano or guitar? (Or baritone ukelele, in my case...)

It's all thanks to something called the circle of fifths that I found out about recently and am just beginning to explore. So if you are a professional musician, apologies in advance if I butcher any terminology in this post.

This circle is a mysterious tool that has a lot of power. One thing it allows you to do is to see at a glance which chords "belong" with each other, making it possible to always move from the current chord you are playing into another one that is guaranteed to sound good!

Making a circle of notes

Before we build this circle, let's start with something more basic by visualizing all the possible notes. Notes go from A to G, and all of them except B and E can be sharp, making twelve notes in total. Notes can also be called flat, meaning that the note just below B can be called either A sharp or B flat, but we'll just show them in their sharp form for simplicity. That gives us the following possible notes.

["A", "A#", "B", "C", "C#", "D", "D#", "E", "F", "F#", "G", "G#"];

We can use each of these strings as the id for twelve note records.

FOR $note IN ["A", "A#", "B", "C", "C#", "D", "D#", "E", "F", "F#", "G", "G#"] {
    CREATE note SET id = $note;
};

A simple SELECT VALUE id FROM note statement shows them all. Looking good so far!

[
    note:A,
    note:⟨A#⟩,
    note:B,
    note:C,
    note:⟨C#⟩,
    note:D,
    note:⟨D#⟩,
    note:E,
    note:F,
    note:⟨F#⟩,
    note:G,
    note:⟨G#⟩
]

Now let's connect them to each other. We want to create a circle, so each note should be connected to the next, and the last note should be connected to the first. SurrealQL has a lot of fancy array operators that let us do this using just a little bit of code. We'll do the following:

Grab each note record by using SELECT * FROM note, which returns an array.
Call the .append() method on this array to add note:A once more on the end so that G# (the last note) can connect to it.
Then call the .windows(2) method to create a sliding window of two items at a time (because we passed in a 2 for two items each). This will give us the pair note:A, note:⟨A#⟩, followed by note:⟨A#⟩, note:B, then note:B, note:C, and so on, all the way to the end.

Then we can use INSERT RELATION to link the first with the second.

FOR $pair IN (SELECT * FROM note).append(note:A).windows(2) {
    INSERT RELATION INTO connected_to {
        in: $pair[0].id,
        out: $pair[1].id
    };
};

As the notes are now joined in a circle, we can use Surrealist's graph view to see what this looks like. Click on the button to the left of "Run selection" and change the view from "Combined", which shows the regular output of a query, to "Graph". Then run this query:

SELECT id, ->connected_to->note FROM note;

You'll probably see a circle that looks like this:

Or maybe a figure eight:

That's because Surrealist generates these visualizations randomly. You can click on the "Reset graph" button to try again if you want to regenerate the output.

To make the output a bit prettier, let's add a single record called the:centre and connect it to each note.

CREATE the:centre;

FOR $note IN SELECT VALUE id FROM note {
    LET $id = $note.id;
    RELATE the:centre->to->$id;  
};

Let's give the graph visualization another try, this time showing each note as it connects to the next, as well as each note as it is connected to by that record in the centre.

SELECT id, ->connected_to->note, <-to<-the FROM note;

That's looking pretty nice!

About the circle of fifths

With practice time over, we can now move on to the magic of the circle of fifths. This is also a circle of musical notes, but this time it's not each note followed by the next ne. Instead, it's each note followed by its perfect fifth.

A perfect fifth is called a fifth because it's the fifth note after the current one. Which means that...

The perfect fifth of A is E, because E is four letters after A.
The perfect fifth of E is B, because B is four letters after E. (E = 1, F = 2, G = 3, A = 4, so B = 5)

Now, the precise definition of a perfect fifth isn't "four notes up" but "seven steps up". This is because the notes B and C, and E and F, don't have a step between them. So if you go with seven steps up then you'll be moving at the same distance each time.

Looking at this again:

The perfect fifth of A is E because E is the note that is seven steps up (A, A#, B, C, C#, D, D#, E)
The perfect fifth of E is B because B is the note that is seven steps up (E, F, F#, G, G#, A, A#, B)
The perfect fifth of B is F# because F# is the note that is seven steps up (B, C, C#, D, D#, E, F, F#)

If you keep moving seven steps at a time starting from A, you'll go from A to E, then E to B, then B to F#, and so on until you get back to A again.

If that's hard to visualize, no problem! We'll get Surrealist to do the visualization for us.

Making the circle of fifths inside Surrealist

Time to get started. First we'll create a database-wide parameter called $NOTES which is an array of each possible note.

DEFINE PARAM $NOTES VALUE ["A", "A#", "B", "C", "C#", "D", "D#", "E", "F", "F#", "G", "G#"];

We can then use this parameter to define a function that will return the perfect fifth of any note. All we have to do is find the index of any note in the array, add seven, and return that. Here is how we will build the function:

Use the .any() method on $NOTES to see if the input matches any of its items. If it doesn't then we'll use THROW to fail. We are going for simplicity here, so no flats like B♭ will be accepted. Just the notes inside $NOTES.
Use .find_index() to get the index of the note. If the function receives a "B", that will be index 2.
Adding 7 to the index might overflow the $NOTES array. We could use an IF ELSE statement here, but the easiest way is to just use .concat() to join $NOTES with itself to double its size. With a length of 24 characters, there is now no danger of overflow.

DEFINE FUNCTION fn::get_perfect_fifth($input: string) -> string {
    IF !$NOTES.any($input) {
        THROW "Note must be one of " + <string>$NOTES;
    };
    LET $index = $NOTES.find_index($input);
    $NOTES.concat($NOTES)[$index + 7]
};

Let's test this function out a bit.

fn::get_perfect_fifth("A"); -- Returns 'E'
fn::get_perfect_fifth("D#"); -- Returns 'A#'
fn::get_perfect_fifth("C"); -- Returns 'G'

That's all correct!

Now we can use fn::get_perfect_fifth() to recreate our note records, this time joining them to their perfect fifth. An UPSERT statement will work great here by creating a note if it doesn't exist, and returning an existing note otherwise.

FOR $note IN $NOTES {
    LET $perfect_fifth = fn::get_perfect_fifth($note);
    LET $first_note = UPSERT note SET id = $note;
    LET $second_note = UPSERT note SET id = $perfect_fifth;
    RELATE $second_note->fifth_of->$first_note;
};

Then we'll create that the:centre record to help Surrealist make the circle look as pretty as possible.

CREATE the:centre;

FOR $note IN SELECT VALUE id FROM note {
    LET $id = $note.id;
  RELATE the:centre->to->$id;  
};

With this done, we'll display the circle. Since we want to see all the connections to each note, we can use a simple query that uses the wildcard ? operator. ->?->? will return everything that a note connects to, and <-?<-? to return the opposite: everything that connects to a note.

SELECT id, ->?->?, <-?<-? FROM note;

The output looks exactly the same as before! Except that this time it's a circle of fifths. So when you hover the mouse over an individual note, you will now see its neighbouring fifth, instead of the note one step away from it.

Now we can start to get into the magic of the circle of fifths.

Take out your guitar or piano, and try playing the major chord for one of the notes here. For example, D major. On the circle of fifths, chords that sound nice together are close to each other. So if you hover your mouse over the D note, you can see that it is connected to both A and G. That means that you can play the D chord a bit, then move to A, and then from A you can go to E or back to D, and it will always sound pretty nice. Usually a chord one or two steps away will always sound nice, while a chord that is far away will sound jarring and wrong.

(It all depends on the type of music you want to play, of course)

Now what if you don't know which notes make up the chord of D major or any other major chord? No problem, because this is another bit of magic that the circle of fifths provides. If you want to play a D major chord, you do the following:

Start with D on the circle.
Move one step clockwise. That's A.
Then move three more steps clockwise. That's F#.

And that's your major chord! It works every time. No matter where you are, the other two notes for a major chord will always be one step and then three more steps away.

And since it works every time, that means that we can use Surrealist to show us that too. Let's go through each note inside $NOTES and add its major chord by using UPSERT. The notes can then be joined by calling fn::get_perfect_fifth once for the second note, and four times for the third note.

FOR $note IN $NOTES {
    LET $chord = UPSERT ONLY chord SET id = $note;
    LET $base = SELECT VALUE id FROM ONLY note WHERE id.id() = $note LIMIT 1;
    LET $second = SELECT VALUE id FROM ONLY note WHERE id.id() = fn::get_perfect_fifth($note) LIMIT 1;
    LET $third = SELECT VALUE id FROM ONLY note WHERE id.id() = fn::get_perfect_fifth(fn::get_perfect_fifth(fn::get_perfect_fifth(fn::get_perfect_fifth($note)))) LIMIT 1;
    RELATE $chord->note->$base;
    RELATE $chord->note->$second;
    RELATE $chord->note->$third;
};

We can visualize the whole thing now using a query that shows all of the connections between chord and note.

SELECT id, ->?->?, <-?<-? FROM chord, note;

That gives us a more web-like network that looks like this.

Now get your guitar or piano out again, and we'll put it to use! Let's start with A, which looks like this.

Since we're at A, we'll play the A major chord a few times.

Now what if you forgot which notes make up the A major chord? No problem, just move the mouse over to the connected record chord:A. Now you can see which notes to play: it's A, C#, and E.

Now go back to A on the circle of fifths, where you can see that D and E match well with A. Let's move to E!

How do we play E major again? No problem, just mouseover chord:E and there are the notes: E, G# and B.

The circle of fifths has a mathematical precision that really appeals to developers. The awesome thing about each type of chord is that it always has the same shape, as you can see through a search on YouTube. This one I find sums up the shapes the best in the shortest time: https://www.youtube.com/shorts/1gz-8BUQbaM

And by the way, I can't believe that nobody ever mentioned this useful tool in Canada where I grew up during music class. But that's a subject for another day.

Now, our circle of fifths is a bit simple compared to the actual one, which includes a second circle on the inside with minor scales so that you can go from major to minor as well. The idea is that any chord next to or across from the current chord will sound good, and the farther away you go the riskier it gets.

If you are up for a challenge, try creating the full circle of fifths and see how close you can get it to the original one!

What’s a database anyway?? A blog post for kids

Dave MacLeod — Wed, 07 May 2025 06:44:31 +0000

Hello there! This article is only for children to read.

Well, adults can read it too if they want, but it won't have any long words that adults like to use, like "perception" or "idempotent". Just normal words that people use every day. So if you are an adult that likes explanations that don't use difficult words, feel free to read it too!

Anyway, we made this post for you because you might know someone who works with a database. This someone might be your mom or dad, or someone else.

And you might have heard this someone talking about work at dinner, saying things like:

"We couldn't access the database today because...", or
"We're looking to install a new database, which..."

And then you probably wondered just what a database is, and how old you have to be to understand what a database is.

The good news is that you can understand what a database is right now! That's because everyone uses databases, even you. Whenever you search for a book at the library, that is thanks to a database that holds the information for every book. And whenever you look through a list of shows on TV to watch, that uses a database too.

In fact, even your own mind has its own database. Because a database is really just something that you use to do two things.

The first thing is: Keeping information. Lots of information. It's like a box that never gets full.
The second thing is: Finding the information again when you need it.

And that's what your mind does for you every day!

The database inside your mind

Whenever you do or see something, your mind decides how important it is. Then it decides how long to remember it. The more important the memory, the longer your mind will keep it.

You certainly remember what you ate for breakfast this morning. But how about what you ate for breakfast 15 days ago? You probably don't remember that anymore, because your mind doesn't think that it was very important. But for important things, like how you first met your best friend, you'll remember for longer - maybe even for the rest of your life.

And whenever your mind makes a new memory, you don't just remember what it was - you also remember how it was. How big, how good, how bad, how much time, how much fun, and so on.

As time goes on, your mind keeps putting new information into its database and you get smarter and smarter.

Once these memories are made, you can access them whenever you like. Let's try it right now with a simple question:

What time did you wake up this morning?

Whatever the answer is, you just accessed your mind's database to get it.

Questions that are easy for people to answer

Here are some other questions to practice the database in your mind. Answering these questions doesn't take much time at all!

"What's your favourite food?" - The database in your mind might give you the answer "hamburgers".
"Can you pick a healthy food?" - Sure, you know lots of healthy foods. A good answer might be "carrots".
"Of the foods you know, which do you think is least healthy?" - This question takes a bit longer, but still not too long. You might answer "milkshakes".

If you think about it, it's pretty amazing that you can answer so fast. If you are ten years old, and eat three meals plus three snacks a day, that means that you've eaten food over twenty thousand times!

Here's the math to prove it. Every year has 365 days. Well, sometimes 366 days (that's why we sometimes have a February 29th), but let's just say 365. You can count them if you don't believe me.

Now if you eat something (including snacks) six times a day, that's 365 * 6 = 2190. Over a full year, your mouth eats something over 2000 times! And over ten years, just add an extra zero and you get over 20,000.

And since you don't eat the same thing every day, you have the memories of hundreds and hundreds of different types of food and drinks. That's a lot of food memories.

And yet, it's easy for your mind to go through all these memories to answer those three questions. The human mind is a really fast database when it comes to questions like this!

Questions that are hard for people to answer

However, some questions aren't quite as easy to answer. This is because human minds are a lot slower when the question is very exact. If the question is "exactly how many", "exactly how long", and "which is first, which is second, which is third", then you have to think for much longer.

Imagine trying to answer all of these questions in under a second!

"Which is the most healthy, the second most healthy, and the third most healthy food that you know?"
"Which is the tenth most tasty food that you know?"
"How many friends do you have that like milkshakes more than hamburgers?"

Questions like this are hard for humans, but easy for machines.

And that's why people made databases instead of just using their minds all the time!

So what is a database anyway?

We know that a database is sort of like a box that never gets full. Now imagine that this box is somewhere in your room. It has a small opening that is big enough to slide sheets of paper in. This box is a little bit magical, because if you write it a letter with important information, it will remember it all.

The box doesn't understand English the way you do though, so you can't write it a letter like this.

March 28 2025.

Dearest magical box,

How are you? I had a fun day today. We got to go to Stanley Park and see some geese as we sat on the grass. Then we went to the ice cream shop and sat on the benches outside!

But don't worry, because there is something called a database language that you can use. With a database language, you can give the box a letter that it will understand.

Here's what happens when we take your letter and rewrite it in a database language.

{
    date: d'2025-03-27',
    i_visited: park:stanley_park,
    with: [person:mother, person:father, person:brother],
    i_saw: [goose:one, goose:two, goose:three],
    i_ate: food:ice_cream,
    i_sat_on: ["bench", "grass"],
    the_day_was: "fun"
}

But why use a database language anyway? Why can't you just talk to a database like a normal person?

How languages work

A database language is a language that is made so that both humans and databases can understand it. Machines aren't good at languages like we are, so we have to write in a very exact way when working with them. Otherwise they get very confused.

So what makes humans so good at languages? It's because we understand the feeling behind words. Machines can't do that.

Let's think of an example.

Why humans are good at languages

Imagine that you are sitting on the couch with a friend who is eating a bag of chips. You'd like some too, so you say:

"Hey, can I have some of those chips?"

If you say that, then your friend will give you some chips!

But "Hey, can I have some of those chips?" isn't the only way to get chips, is it? These two would work too!

"Pass me those chips for a second."
"Can I try those chips too?"

You could even just say "Mmmmm, that looks tasty" and make a smacking sound with your lips.

If you think about it, it's pretty neat that you don't even have to say "can I have" or even the word "chips" to get some chips.

If you have a dog or a cat then you know this fact really well. All they need to do is make a sad face to get food from you, no words required.

Now let's think about what you could have meant, but didn't mean, when you said "Hey, can I have some of those chips?"

Well, you wanted "some" chips. But the word "some" can mean lots of things, don't you think? If you open up the dictionary and go to the word "some", you will see this explanation:

"Some": A certain number, at least two.

So if a bag has 100 chips, then that means that:

One chip is "a chip" (not "some").
Two chips might be "some", but usually three and above feels more like "some". For two, you'd usually use a word like "a pair" or "a couple".
100 chips is "all the chips" (not "some").

So 1 is not "some", and 2 is not "some", and 100 is not "some". That still leaves a lot of room, doesn't it? "Some" chips could mean anywhere from 3 to 99 chips!

So that's a bit funny, because when you said "some" you certainly didn't want three chips, or 99! You probably wanted about ten, and your friend understood without having to ask. In fact, your friend would give you about ten chips even if you asked for "a chip". It wouldn't be very nice to give you exactly one just because you asked for "a chip".

In a different situation, "some" could mean something else. Imagine that you're walking down the street one day and a gigantic dragon flies down - a dragon that can speak. It opens its mouth and says the same thing you said to your friend:

"HEY, CAN I HAVE SOME OF THOSE CHIPS?"

"Some" doesn't mean ten chips this time, does it? No, you'll give the dragon the whole bag, and any other bags of chips you have, as you run away while it eats them. "Some" in this case actually turned meant "everything"!

And there are lots of other funny things that happen when we talk too. Think about the word "have" in "Hey, can I have some of those chips?" Here, it doesn't exactly mean "have", does it? You didn't actually want to "have" the chips, as in to take them home and keep them and be their new owner. No, what you wanted was to "have" them for a second before you put them in your mouth.

That's the magic of human communication. You don't always have to think so deeply about how you're saying something. People are very good at this. Computers though...not so much!

Why computers are bad at languages

If you and your friend were computers, it would be very different. You would have to say "Give me ten of your chips" and nothing else, because computers are bad at languages.

And even if you made the smallest mistake, like saying "Give me ten of your chip", your friend wouldn't know what to do! Your friend would just look surprised and say "I don't understand. I have more than one chip, what are you talking about? What do you want me to do?"

Sigh. You will have to change what you said to "Sorry, please give me ten of your CHIPS." And only then would your computer friend understand. That's what talking to computers is like.

The good thing about computers though is that they are very fast. If you ask for ten of your computer friend's chips, you will have them in under a second!

The more you ask a computer to do, the more careful you have to be about how you say it. There is a good joke online that explains this better than I can. It looks like this:

Do you get the joke? It starts with "Please, can you go to the shop". That's nice and clear, even for a computer.

But after this point it's not so easy! What does the "get six" part mean? For a human it's clear. We know that it's silly to get six times more milk if there are avocados. But a computer doesn't know that. It doesn't even know that six cartons is a lot of milk.

And just look at how little we have to change to make the sentence mean "get six cartons of milk"! A small change from "avocados" to "a sale" completely changes the meaning.

"Please, can you go to the shop and get a carton of milk, if they have AVOCADOS get six"
"Please, can you go to the shop and get a carton of milk, if they have A SALE get six"

The poor computer is just doing its best, but human languages are so hard to understand! It doesn't know that "if they have AVOCADOS" doesn't mean milk but "if they have A SALE" it does mean milk.

Anyway, if the person you know that works with computers ever starts laughing for no reason, it's probably because of this. Sometimes computers give some pretty silly responses to what you ask them to do. Or rather, what you thought you asked them to do.

It's actually pretty fun to work with computers because of this. They are like innocent little creatures that do whatever you tell them to do. So you have to take very good care of them and make sure they don't make mistakes.

And when you work with databases and computers every day, you get to know how they work and how to communicate with them.

Turning human language into computer language

Okay, it's time to actually learn some computer language! You might be surprised at how easy it is to read.

Let's get back to that magic box again. Let's say you ate a hamburger that was the best thing you've ever eaten. There were some vegetables in it, but overall it probably wasn't that healthy. Your mind will remember this, but how can you let the database have the information too?

To help the database remember the same information, you'll need to write a "letter" in a way that it can understand. First, you'll have to decide what exactly you mean by "the best thing you've ever eaten" and "probably wasn't that healthy".

A good way to do this is to use numbers from 1 to 10, with 1 meaning "the least" and 10 meaning "the most". It's just like rating a movie.

In database language, the hamburger information will now look like this.

{
    food_name: 'hamburger',
    healthiness: 5,
    tastiness: 10
};

What about a milkshake? It's a little bit less tasty than a hamburger, and less healthy. We can give it the numbers 3 for healthiness and 8 for tastiness.

{
    food_name: 'milkshake',
    healthiness: 3,
    tastiness: 8
};

The third letter is carrots - very healthy, but not as tasty as the others.

{
    food_name: 'carrots',
    healthiness: 9,
    tastiness: 4
};

You then put these three letters into the box and they disappear.

Well, they sort of disappear.

What actually happens is that the box turns your letters into its own language that only it can understand. You could break the box open to look inside...but your letters now look something like this!

db/*ns*db!tbfoodZfood)/*ns*db*food*k97bz6oo294iz5t77ztz]
enyYW0t7MHz236cHuStGocubN7ByLl4EFY20FVwsWV9jWxD0Dh
/*ns*db!vs
/*ns*db!tsgۅ?

That's the "language" that the database uses to remember things, and there's no way that you can read that. So that's why we use a database language, which is like a bridge between human language and a database's own machine language.

Human language: It was the best thing I ever ate
Database language: tastiness: 10
Machine language: enyYW0t7MHz236cHuStGocubN7ByLl4EFY20FVwsWV9jWxD0Dh

Thinking like a database

The fun thing about a database language is that it makes you a better thinker too. The more you think about exactly what you want to say, the more thinking you do and the smarter you get.

It can even help you be a better person, because learning to see what a person is trying to say can help you understand them. And when people understand each other well, they get along better too.

Here's a good example to show you what I mean about being a better thinker.

Let's imagine that you are planning a weekend camping trip for you and some friends. But where to choose? You could always just pick a spot, bring your friends and hope that everyone likes it.

But if you start thinking like a database, you can pick a spot that you know they will like.

First you ask them what they like and don't like. You find out that some of your friends...

...don't like outhouses (because they smell awful).
...get carsick easily.
...want to explore and go on long walks.

So, the campsite needs to...

...have good clean bathrooms.
...be close.
...have hiking trails.

Now it's time to start thinking like a database. What exactly do we mean by "good bathrooms" or "be close" or "have hiking trails"? How close is "close"? How many hiking trails is enough?

Let's give each one of them a number to help us decide.

Now the campsite needs to...

...have at least three bathrooms. Why three? Because sometimes bathrooms are out of order. If there are at least three, then there's a good chance that you'll always have one that works.
...be within 50 kilometres. Why 50 kilometres? Because that's close enough that people won't get carsick.
...have at least three hiking trails. Why three? Because sometimes trails are closed. Also, some of your friends will want to try a new trail every day.

Next, we have to give some information to the database. Here are three "letters" to give it, each of which has information for one camping site. If you look at the information, you can see that:

One camping site is really rough, with 50 trails and no bathrooms.
The second one is really close, has lots of bathrooms. But only one trail. It's probably feels more like going to a cabin than actual camping in the woods.
The third one is just right.

-- This is letter 1!
INSERT INTO camping_site {
    name: "Outback Camping",
    address: "10800 Backway Lane",
    distance: 49.7,
    bathrooms: 0,
    outhouses: 3,
    trails: 50
};

-- This is letter 2!
INSERT INTO camping_site {
    name: "Easy Camping",
    address: "150 Rose Street",
    distance: 10.1,
    bathrooms: 20,
    outhouses: 0,
    trails: 1
};

-- This is letter 3!
INSERT INTO camping_site {
    name: "Happy Trails Camping Site",
    address: "55 Woodcreek Road",
    distance: 30.0,
    bathrooms: 3,
    outhouses: 10,
    trails: 5
};

All of these letters disappear into the magic box (the database). We could put in many more letters: hundreds, thousands, millions! But I think you get the idea from the three campsites we just saw.

Now the box has some memories, and we would like to ask it a question about it. Here is the question that we want to ask.

"What is the name and adress for camping sites that have more than two bathrooms, and at least two trails, and less than 50 kilometres away?"

That's pretty clear! But it's not database language yet, so we'll have to change it. Fortunately, database language is pretty close to English.

Here's how we can change our question to database language.

"What" is called "SELECT",
"Name and address" is called name, address,
"for camping sites" is called FROM camping_site,
"that have" is called WHERE,
more than two bathrooms is called bathrooms > 2,
"and" is the same as in English,
more than two trails is called trails > 2,
less than 50 kilometres away is called distance < 50

That's pretty close to regular spoken language, isn't it? Here's what the whole thing looks like when you put it together.

SELECT
    name, 
    address
FROM camping_site
WHERE
    bathrooms > 2 AND
    trails > 2 AND
    distance < 50;

And just like magic, the database gives us an answer. In the same way that we give it a letter, it can print out a letter to give to us. Here is what it looks like.

[
    {
        address: '55 Woodcreek Road',
        name: 'Happy Trails Camping Site'
    }
]

Looks like Happy Trails Camping Site is the place to choose!

The nice thing about database questions is that they will always work, even if you add more camping sites. If you copy this SELECT question and save it somewhere, you can use it again and it will always work. And it will get even better if you add more camping sites. If you put one hundred camping sites into the database, then maybe you'll get ten responses from it. And then you can choose the best one from among the ten.

Well done!

And with that, you now know what a database is! It's not all that mysterious anymore, is it?

It's just a computer mind that knows lots of things, with a special language so that people can ask it questions.

See you later!

Making your own PR to the SurrealDB source code

Dave MacLeod — Wed, 29 Jan 2025 01:55:04 +0000

by Dave MacLeod

Jan 24th, 2025

One of the first things people usually notice when they check out SurrealDB is the fact that it is written entirely in Rust, and is open source.

The source code for the SurrealDB database is open, and can be seen on GitHub. While the bulk of the changes to the database are made by the core engineering team, anyone is invited to put a PR together and many of these get merged! In fact, as of the writing of this blog, it has been less than a day since the most recent user-submitted PR was merged. This PR is a small one that fixed an error to do with handling file URLs. The whole PR is just under 50 lines, including a test.

So what happens if you find a bug that you think you know how to fix, or a feature that you'd like to add? Because although Rust continues to gain popularity (as of January 2025, it is in ninth place on the PYPL Index), the majority of the people using SurrealDB come from languages like Python and JavaScript.

That's what this post is for, to help you get set up and ready to make your first PR, even if you've never touched any Rust code before. To be honest, the reason why people don't usually experiment with other programming languages as much as they otherwise would is not because of the language, it's because it takes a while to get set up and install the tools to start writing code. And making a PR to an existing code base...that can be pretty intimidating.

Fortunately, Rust is particularly accessible in this respect, both in setting up the tools and adding code.

One of the reasons why user PRs are so frequent is that Rust itself is a pretty straightforward language to contribute to. While the language itself is on the complex side (to say the least), its strict compiler and single package manager make it relatively manageable to run and test any changes and to be confident that what you've submitted will work as expected.

Many other companies have discovered the same. In fact, Google discovered in 2023 that 2 out of 3 of the respondents to the survey were confident in contributing to a Rust codebase within two months or less.

So let's get started!

Setup to start testing your code

The first step is to install Rust, which doesn't take all that long. Once it is installed, you will begin using Cargo for everything such as building and testing your code.

Rust is a compiled language, and the first compilation always takes the longest because it builds the entire program from scratch. Once you have cloned the content from the SurrealDB repo, most likely using the git clone https://github.com/surrealdb/surrealdb command, you'll next want to type cargo build. This will begin compiling the code in debug mode. This mode produces a less optimized version but compiles faster, and is recommended unless the PR you are making involves testing performance.

On the computer I use to compile SurrealDB a clean build takes slightly over two minutes, while recompiling after making a small change takes about 20 seconds. On my previous slower computer this took about three times longer.

To test out your changes, you'll want two open terminal windows. The first will be used to start the database using the surreal start command, while the second will use the surreal sql command to connect using the CLI. (Or if you are the graphical type, then just use Surrealist and set up a new connection to the default localhost:8000).

Here are the commands that I recommend using when testing a new feature:

surreal start --unauthenticated --allow-all in one window to start the database. The --unauthenticated command will let anyone use the database for any purposes without logging in as a user, while --allow-all allows normally restricted functionality like SurrealDB's HTTP functions.
surreal sql --namespace ns --database db --pretty to start a CLI in the other window inside a namespace called ns and a database called db. The --pretty flag makes the output look a little nicer, and also displays the time it took for a query to execute.

These commands can be used by going to the /target/debug folder where your code has been built. Inside a Mac you'll want to add ./ to instruct the system to access the surrealdb executable inside this directory, instead of the general surrealdb executable that can be run in any directory. You'll know that it is running your build executable if the version number is really long, like this:

2025-01-10T03:31:17.177147Z  INFO surreal::env: src/env/mod.rs:12:
Running 2.1.4+20250109.40657042.dirty for macos on aarch64

Another way to run the executable is just by doing it through Cargo, adding a -- after which the SurrealDB commands can be passed through.

cargo run -- start --unauthenticated --allow-all
cargo run -- sql --namespace ns --database db --pretty

Personally I prefer going into the directory though because running through Cargo can take a tiny bit longer and produce some extra output like this:

mithr@mithr surrealdb % cargo run -- sql --namespace ns --database db --pretty
warning: unused variable: `val2`
  --> crates/core/src/doc/pluck.rs:86:10
   |
86 |                     let val2 = Value::Strand("test".into());
   |                         ^^^^ help: if this is intentional, prefix it with an underscore: `_val2`
   |
   = note: `#[warn(unused_variables)]` on by default

But overall, the difference is not that great and you can use whichever method you prefer.

Now that everything is all set up, it's time to make a PR! What change should we make?

Our tiny, unserious change to the database

Since a real PR that would actually be accepted requires a real issue, and real issues take a good amount of time and code to solve, we'll have to go with an unserious one. Let's put something together that creates a new predefined variable.

Before making a predefined variable, let's make sure that we know what one is.

A predefined variable is one that is automatically set and can be accessed inside a statement. An example of one is $session, which contains the data for the current session.

RETURN $session;

Output:

{
    ac: NONE,
    db: 'sandbox',
    exp: NONE,
    id: NONE,
    ip: NONE,
    ns: 'sandbox',
    or: NONE,
    rd: NONE,
    tk: NONE
}

Another example is the variables $before and $after. These variables exist because by default, SurrealDB returns the output after an operation is over, but you might want to customize your output to return something else instead.

CREATE person;
UPDATE person SET age = 25;
DELETE person;

Output:

-------- Query 1 --------

[
    {
        id: person:qbl3yz2wprvl0wp6zgm8
    }
]

-------- Query 2 --------

[
    {
        age: 25,
        id: person:qbl3yz2wprvl0wp6zgm8
    }
]

-------- Query 3 --------

[]

One way is by using the keywords RETURN BEFORE, RETURN DIFF, RETURN NONE, RETURN plus a list of fields, and so on.

CREATE person RETURN NONE;
UPDATE person SET age = 25 RETURN BEFORE;
DELETE person RETURN BEFORE;

Output:

-------- Query 1 --------

[]

-------- Query 2 --------

[
    {
        id: person:gqb09v97e33b8urd6old
    }
]

-------- Query 3 --------

[
    {
        age: 25,
        id: person:gqb09v97e33b8urd6old
    }
]

But you can also return custom fields, in which case you can also access the value before and after the operation by the parameters $before and $after.

CREATE person 
    RETURN
    "person created at " + <string>time::now() + "!" AS message,
    $session, 
    $after;

Output:

[
    {
        after: {
            id: person:b74fnyomgyd4zowipu84
        },
        message: 'person created at 2025-01-10T02:22:40.021Z!',
        session: {
            ac: NONE,
            db: 'sandbox',
            exp: NONE,
            id: NONE,
            ip: NONE,
            ns: 'sandbox',
            or: NONE,
            rd: NONE,
            tk: NONE
        }
    }
]

Now that we understand how they work, let's see if we can set up our own predefined variable! It might be nice to have one called $pep_talk that has a value of "You are awesome!". Developers can then access this predefined variable every time we feel like getting a little motivation.

Rummaging through the source code

To make this change, let's see if we can find where the variables $before and $after are set up. Since we are in no way familiar with the source code yet, let's just open up an IDE like Visual Studio Code and do a search for $before. Doing so takes us to this piece of code, which looks pretty close to the spot we want! This section deals with live queries in particular which is not what we want, but we can see some lines that are clearly setting variables including the ones we saw before: "session", "after", and "before".

Reading the code, it looks like these parameters are added using a method called .add_value() which and takes simple strings for their name. The $ must be added afterwards, and we won't have to think about that part.

// Add the session params to this LIVE query, so
// that queries can use these within field
// projections and WHERE clauses.
lqctx.add_value("access", sess.pick(AC.as_ref()).into());
lqctx.add_value("auth", sess.pick(RD.as_ref()).into());
lqctx.add_value("token", sess.pick(TK.as_ref()).into());
lqctx.add_value("session", sess.clone().into());
// Add $before, $after, $value, and $event params
// to this LIVE query so the user can use these
// within field projections and WHERE clauses.
lqctx.add_value("event", met.into());
lqctx.add_value("value", current.clone());
lqctx.add_value("after", current);
lqctx.add_value("before", initial);

Now let's do a search for .add_value(" to see what other places this method is being used. The search results show the method used in about a dozen or so places, but one of them stands out in particular thanks to a comment above the code. It appears to be inside a function called pluck(). Here is the comment:

/// Evaluates a doc that has been modified so that it can be further computed into a result Value
/// This includes some permissions handling, output format handling (as specified in statement),
/// field handling (like params, links etc).

That sounds about right! Further down in the code we can see a match statement that tells the program what to do when it gets a certain instruction on what sort of output to return. The Output::None, Output::Null, Output::Diff, Output::After, and Output::Before variants all seem to match with the statements we saw before like RETURN BEFORE and RETURN AFTER. The final variant is Output::Fields, which is when the user customizes the output like we did. And it's inside there that "before" and "after" are set!

// Process the desired output
let mut out = match stm.output() {
    Some(v) => match v {
        Output::None => Err(Error::Ignore),
        Output::Null => Ok(Value::Null),
        Output::Diff => {
            // Process the permitted documents
            let (initial, current) = match self.reduced(stk, ctx, opt, Both).await? {
                true => (&self.initial_reduced, &self.current_reduced),
                false => (&self.initial, &self.current),
            };
            // Output a DIFF of any changes applied to the document
            Ok(initial.doc.as_ref().diff(current.doc.as_ref(), Idiom::default()).into())
        }
        Output::After => {
            // Process the permitted documents
            match self.reduced(stk, ctx, opt, Current).await? {
                // This is an already processed reduced document
                true => Ok(self.current_reduced.doc.as_ref().to_owned()),
                // Output the full document before any changes were applied
                false => {
                    self.current
                        .doc
                        .as_ref()
                        .compute(stk, ctx, opt, Some(&self.current))
                        .await
                }
            }
        }
        Output::Before => {
            // Process the permitted documents
            match self.reduced(stk, ctx, opt, Initial).await? {
                // This is an already processed reduced document
                true => Ok(self.initial_reduced.doc.as_ref().to_owned()),
                // Output the full document before any changes were applied
                false => {
                    self.initial
                        .doc
                        .as_ref()
                        .compute(stk, ctx, opt, Some(&self.initial))
                        .await
                }
            }
        }
        Output::Fields(v) => {
            // Process the permitted documents
            let (initial, current) = match self.reduced(stk, ctx, opt, Both).await? {
                true => (&mut self.initial_reduced, &mut self.current_reduced),
                false => (&mut self.initial, &mut self.current),
            };
            // Configure the context
            let mut ctx = MutableContext::new(ctx);
            ctx.add_value("after", current.doc.as_arc());
            ctx.add_value("before", initial.doc.as_arc());
            let ctx = ctx.freeze();
            // Output the specified fields
            v.compute(stk, &ctx, opt, Some(current), false).await
        }
    },

So we should be able to add our own predefined variable here! We will start with ctx.add_value("pep_talk" and then follow it up with...some value. But what? The second parameter in this .add_value() function isn't clear to us.

ctx.add_value("after", current.doc.as_arc());
ctx.add_value("before", initial.doc.as_arc());

Letting the Rust compiler do the work for you

This is where Rust's strict typing system and helpful compiler come in handy. If the code won't compile unless it is correct, and the compiler often knows what we are trying to do, we can just try completing the method with whatever comes to mind and see what the compiler says.

Let's just end this with the string that we'd like to output and see what the compiler says.

ctx.add_value("pep_talk", "You are awesome!");

The error output is pretty good! Though the code didn't work, we now know that it's because the method wants to see some type called Arc<sql::value::value::Value>.

error[E0308]: mismatched types
   --> crates/core/src/doc/pluck.rs:87:32
    |
87  |                     ctx.add_value("pep_talk", "You are awesome!");
    |                         ---------             ^^^^^^^^^^^^^^^^^^ expected `Arc<Value>`, found `&str`
    |                         |
    |                         arguments to this method are incorrect
    |
    = note: expected struct `Arc<sql::value::value::Value>`
            found reference `&'static str`
note: method defined here
   --> crates/core/src/ctx/context.rs:236:16
    |
236 |     pub(crate) fn add_value<K>(&mut self, key: K, value: Arc<Value>)
    |                   ^^^^^^^^^                       -----------------

A quick Google for "Rust Arc" will take us directly to this page. Okay, it looks like an Arc is atomically safe reference counter. And creating one is as simple as calling Arc::new() and putting something inside, as one of the examples on that page shows.

let five = Arc::new(5);

So we will need to Arc::new() and put something called a Value in.

Now let's see what a Value is. The error message displays it as sql::value::value::Value, so that's the path we'll follow to find it. This takes us to something called an enum Value. It's pretty clear that this enum is a way to represent the various data types you'll see in the database such as numbers, durations, datetimes, objects, and so on.

pub enum Value {
    // These value types are simple values which
    // can be used in query responses sent to
    // the client. They typically do not need to
    // be computed, unless an un-computed value
    // is present inside an Array or Object type.
    // These types can also be used within indexes
    // and sort according to their order below.
    #[default]
    None,
    Null,
    Bool(bool),
    Number(Number),
    Strand(Strand),
    Duration(Duration),
    Datetime(Datetime),
    Uuid(Uuid),
    Array(Array),
    Object(Object),
    Geometry(Geometry),
    Bytes(Bytes),
    Thing(Thing),
    // These Value types are un-computed values
    // and are not used in query responses sent
    // to the client. These types need to be
    // computed, in order to convert them into
    // one of the simple types listed above.
    // These types are first computed into a
    // simple type before being used in indexes.
    Param(Param),
    Idiom(Idiom),
    Table(Table),
    Mock(Mock),
    Regex(Regex),
    Cast(Box<Cast>),
    Block(Box<Block>),
    #[revision(end = 2, convert_fn = "convert_old_range", fields_name = "OldValueRangeFields")]
    Range(OldRange),
    #[revision(start = 2)]
    Range(Box<Range>),
    Edges(Box<Edges>),
    Future(Box<Future>),
    Constant(Constant),
    Function(Box<Function>),
    Subquery(Box<Subquery>),
    Expression(Box<Expression>),
    Query(Query),
    Model(Box<Model>),
    Closure(Box<Closure>),
    // Add new variants here
}

The one that we are looking for is a string, and it will be one of the possibilities listed in the top which are said to be "values which can be used in query responses". The ones on the bottom aren't used in query responses, so we don't want that.

Let's see...we know that we don't want None, or Null, or Bool, or Number, or Duration, or Datetime, or Uuid, or Array, or Object, or Geometry, or Bytes. That leaves Strand and Thing. Strand is probably the one we want, so let's take a look at that page.

/// A string that doesn't contain NUL bytes.
#[revisioned(revision = 1)]
#[derive(Clone, Debug, Default, Eq, PartialEq, Ord, PartialOrd, Serialize, Deserialize, Hash)]
#[serde(rename = "$surrealdb::private::sql::Strand")]
#[cfg_attr(feature = "arbitrary", derive(arbitrary::Arbitrary))]
#[non_exhaustive]
pub struct Strand(#[serde(with = "no_nul_bytes")] pub String);

The comment describes it as A string that doesn't contain NUL bytes, that sounds about right!

So how do we construct a Value that is a Strand? Let's look at come more code inside the value.rs page. About 150 lines down there are tons of examples showing these Value variants being created.

impl From<bool> for Value {
    #[inline]
    fn from(v: bool) -> Self {
        Value::Bool(v)
    }
}

impl From<Uuid> for Value {
    fn from(v: Uuid) -> Self {
        Value::Uuid(v)
    }
}

impl From<Closure> for Value {
    fn from(v: Closure) -> Self {
        Value::Closure(Box::new(v))
    }
}

impl From<Param> for Value {
    fn from(v: Param) -> Self {
        Value::Param(v)
    }
}

So we should be able to use Value::Strand() with a string inside. Let's give that a try! We'll first try to create a variable called pep_talk which is a Value::Strand.

Output::Fields(v) => {
    // Process the permitted documents
    let (initial, current) = match self.reduced(stk, ctx, opt, Both).await? {
        true => (&mut self.initial_reduced, &mut self.current_reduced),
        false => (&mut self.initial, &mut self.current),
    };
    // Configure the context
    let mut ctx = MutableContext::new(ctx);
    ctx.add_value("after", current.doc.as_arc());
    ctx.add_value("before", initial.doc.as_arc());
    let pep_talk = Value::Strand("You are awesome!"); // This line
    let ctx = ctx.freeze();
    // Output the specified fields
    v.compute(stk, &ctx, opt, Some(current), false).await
}

Not quite, but once again the compiler tells us exactly what to do. The type that we entered is called a &str, but the compiler expected a Strand. On the line below, it lets us know that we can just add .into() because apparently our input can be converted into a Strand by doing this. Sounds easy enough!

error[E0308]: mismatched types
   --> crates/core/src/doc/pluck.rs:85:35
    |
85  |                     let pep_talk = Value::Strand("You are awesome!");
    |                                    ------------- ^^^^^^^^^^^^^^^^^^ expected `Strand`, found `&str`
    |                                    |
    |                                    arguments to this enum variant are incorrect
    |
note: tuple variant defined here
   --> crates/core/src/sql/value/value.rs:104:2
    |
104 |     Strand(Strand),
    |     ^^^^^^
help: call `Into::into` on this expression to convert `&'static str` into `sql::strand::Strand`
    |
85  |                     let pep_talk = Value::Strand("You are awesome!".into());
    |                                                                    +++++++

So now we can just stick this into a new line containing ctx.add_value. And don't forget to wrap the Value inside Arc::new()!

Output::Fields(v) => {
    // Process the permitted documents
    let (initial, current) = match self.reduced(stk, ctx, opt, Both).await? {
        true => (&mut self.initial_reduced, &mut self.current_reduced),
        false => (&mut self.initial, &mut self.current),
    };
    // Configure the context
    let mut ctx = MutableContext::new(ctx);
    ctx.add_value("after", current.doc.as_arc());
    ctx.add_value("before", initial.doc.as_arc());
    let pep_talk = Value::Strand("You are awesome!".into()); // Our code
    ctx.add_value("pep_talk", Arc::new(pep_talk)); // Our code
    let ctx = ctx.freeze();
    // Output the specified fields
    v.compute(stk, &ctx, opt, Some(current), false).await
}

Trying out the code and adding a test

No more errors are showing up in the IDE anymore, so let's give it a try.

Type cargo build and go into the /target/debug directory to use the surreal start and surreal sql commands above, or type cargo run -- start --unauthenticated --allow-all in one terminal window and cargo run -- sql --namespace ns --database db --pretty in another.

So does it work?

It sure does! We now have access to a quick $pep_talk any time we are working with a statement like CREATE or UPDATE.

ns/db> CREATE person RETURN $pep_talk;
-- Query 1 (execution time: 5.159042ms)
[
    {
        pep_talk: 'You are awesome!'
    }
]

ns/db> UPDATE person RETURN $before, $pep_talk AS nice_speech, time::now() AS time_of_query;
-- Query 1 (execution time: 2.79125ms)
[
    {
        before: {
            id: person:2lyuxch0cx31o09bfbfu
        },
        nice_speech: 'You are awesome!',
        time_of_query: d'2025-01-10T05:20:17.393290Z'
    }
]

Before submitting a PR, it might be a good idea to add a test or two. Tests in Rust can be located anywhere, and are simply made by adding an annotation to a function that designates it as a test function.

Because there are many such tests in the source code already, you can just find an existing test like this one that tests a query and its output...

#[tokio::test]
async fn field_definition_array_any() -> Result<(), Error> {
    let sql = "
        DEFINE TABLE user SCHEMAFULL;
        DEFINE FIELD custom ON user TYPE array<any>;
        INFO FOR TABLE user;
    ";
    let mut t = Test::new(sql).await?;
    t.skip_ok(2)?;
    t.expect_val(
        "
{
    events: {  },
    fields: { custom: 'DEFINE FIELD custom ON user TYPE array PERMISSIONS FULL' },
    indexes: {  },
    lives: {  },
    tables: {  }
}
        ",
    )?;
    Ok(())
}

...and then just modify the query and the expected output. A test for our $pep_talk variable could then look like this.

#[tokio::test]
async fn pep_talk() -> Result<(), Error> {
    let query = "CREATE person:one RETURN *, $pep_talk;";
    let mut t = Test::new(query).await?;
    t.expect_val(
        "
[
    {
        id: person:one,
        pep_talk: 'You are awesome!'
    }
]
        ",
    )?;
    Ok(())
}

Once this is over, then just submit the PR as you would to any other repo! A PR submitted to the /surrealdb repo contains an automatically generated template to fill out that contains a few fields such as:

What is the motivation?
What does this change do?
What is your testing strategy?

And so on. It also contains a checkbox to make sure that you have read the contributing guidelines. Once the PR is submitted, the tests will run and the SurrealDB team will see that a PR has been submitted. And if the PR is accepted, it will be merged into main and included in the next release!

DEV Community: Dave MacLeod

Hybrid vector + text Search in the terminal with SurrealDB and Ratatui

SurrealDB: shiny newcomer, low-level database, or both?

Designing a TUI

How does hybrid search work?

Final notes and app source code

Migrate your data directly to SurrealDB using Surreal Sync

Note

Importing from another database

Importing from JSON Lines

Beyond basic RAG: Building a multi-cycle reasoning engine on SurrealDB

Introduction

The architectural core: a unified vector and document store with SurrealDB

The schema in detail

Deep dive: multi-LLM reflexion engine

Multi-LLM orchestration strategy

Reflexion cycle

Prompt engineering: YAML-based prompt management

The Prompt Manager

Hybrid retrieval: combining local documents and live web search

Hybrid search: documents + web results

Getting started: a practical implementation guide

Prerequisites

Install UV package manager

Installation

Environment configuration

Get GitHub models access token (Github.com/models)

Define document and web search tables (run as a query or run in Surrealist)

Custom SurrealQL functions for RAG operations

Set up Google custom search

Setup Crawl4AI for web search

Initial run and document ingestion

Showcase: a complex query in action

Ingest documents from directory

Interactive chat and CLI usage

Start interactive chat session

Document management commands

Sample output: reflexion in action

Conclusion

Make a medical chatbot using GraphRAG with SurrealDB + LangChain

Using LangChain

Semantic search in Rust using SurrealDB and Mistral AI

Setup

Create a vector table and index

Generate Mistral AI embeddings

Store embeddings in database

Semantic search

Ten tips and tricks for your database schema

1: Use a set for an array of distinct items

2: Define arrays and sets with a type and maximum size

3: Define nested indexes

4. Use FLEXIBLE objects and defined fields in SCHEMAFULL tables

5. Use THROW to add more detailed error messages to ASSERT clauses

6. Use formatters on internal datetimes for strings with alternative formats

7. Use !!$value in DEFINE statements

8. Use DEFINE PARAM for clarity

9. Use literals to return rich error output

10. Use graph queries in the schema

Enhance your musical skills with Surrealist’s Graph View

Making a circle of notes

About the circle of fifths

Making the circle of fifths inside Surrealist

What’s a database anyway?? A blog post for kids

The database inside your mind

Questions that are easy for people to answer

Questions that are hard for people to answer

So what is a database anyway?

How languages work

Why humans are good at languages

Why computers are bad at languages

Turning human language into computer language

Thinking like a database

Well done!

Making your own PR to the SurrealDB source code

Setup to start testing your code

Our tiny, unserious change to the database

Rummaging through the source code

Letting the Rust compiler do the work for you

Trying out the code and adding a test

1: Use a `set` for an array of distinct items

4. Use `FLEXIBLE` objects and defined fields in `SCHEMAFULL` tables

5. Use `THROW` to add more detailed error messages to `ASSERT` clauses

7. Use `!!$value` in `DEFINE` statements

8. Use `DEFINE PARAM` for clarity