DEV Community: Alexander Fridriksson

Building Smarter Product Recommendations with SurrealDB

Alexander Fridriksson — Thu, 06 Mar 2025 13:36:56 +0000

Personalised experiences can make or break the conversion of a visitor into a customer, which is why recommendation engines are no longer a luxury — they’re essential.

You’ve seen them everywhere:

“Gift ideas inspired by your shopping history.”

“Customers who viewed this item also bought…”

“Just arrived for you.”

However, behind these helpful nudges are complex systems. Traditionally, building these systems meant building out a team of experts who build out stacks of disconnected tools, which then need to be glued together again. This might include relational, document and graph databases along with advanced machine learning and vector stores.

Systems like these trend toward ever increasing complexity, but is that really necessary?

What if complex systems don’t need to be complicated?

What if you could do it all in one stack?

Unify disconnected tools with SurrealDB

SurrealDB gives you the power of 10+ products in one unified multi-model database.

It's natively real-time, relational, document, graph, vector and more. Built from the ground up using cutting-edge research and the Rust language to ensure runtime safety, combined with the performance and low-latency requirements you need from a high performance system.

For recommendation systems, this enables you to build simpler systems and achieve better personalisation. Instead of juggling multiple systems to stitch together context, relationships, and meaning, SurrealDB lets you keep everything in one tightly integrated platform, without the need to install or interact with multiple databases, libraries or frameworks.

Why Recommendations Need to Be Smarter

Traditional recommendation systems rely on techniques like:

Collaborative Filtering (CF): "People who bought this also bought…"
Content-Based Filtering: "Products similar to the ones you like."
Hybrid Models: Combining CF and content-based for more accuracy.

These work, but if you could increase conversion rates by a few percent, why wouldn’t you?

The current cutting-edge trends are moving towards real-time, more context-aware systems - making using of LLM driven recommendations powered by centralised knowledge graphs.

This is where SurrealDB shines. Let’s therefore explore a high-level recipe for how you can build such recommendation systems, simply using SurrealDB and an LLM provider. No need for Python glue code, frameworks, vector stores and more.

How to Build a Smart Recommendation Engine with SurrealDB

Build Out Your Knowledge Graph

This makes it easy to provide context that can be used to answer questions such as:

Who bought what?
Who’s similar to whom?
What’s trending?

SELECT array::distinct(
  ->order->product
  <-order<-person
  ->order->product.{id, name}
) AS recommended_products
FROM person:01FVRH055G93BAZDEVNAJ9ZG3D;

Turn Data Into Embeddings for Vector Search and LLMs

Create vector embeddings from product descriptions or user behaviour.

DEFINE FUNCTION fn::create_embeddings($input: string) {
  RETURN http::post("https://example.com/api/embeddings", {
    "model": "embedding model",
    "input": $input
  });
};

These embeddings unlock semantic search, so you can say:

“Show me products similar to a ‘casual sweatshirt’”

And get results based on meaning, not just keywords.

SELECT id, name, vector::similarity::cosine(details_embedding, $prompt)
FROM product
ORDER BY similarity DESC;

Mix Semantic Search with Full-Text Search

Combine keyword relevance and vector similarity for the best of both worlds.

DEFINE FUNCTION fn::hybrid_search($search_term: string) {
  LET $prompt = fn::create_embeddings($search_term);
  LET $semantic_search = (…);
  LET $full_text_search = (…);
  RETURN { semantic_results: $semantic_search, full_text_results: $full_text_search };
};

Add Context from the Graph

Retrieve personalised context: purchase history, preferences, popular items, and more.

DEFINE FUNCTION fn::person_context($record_id: string) {
  LET $context = (SELECT id, name, address, ->order.* AS order_history FROM $record_id);
  RETURN $context;
};

Send the context to an LLM

Send all this rich context into an LLM for hyper-personalised recommendations.

DEFINE FUNCTION fn::get_recs($template: any) {
  RETURN http::post('https://example.com/api/chat', {
    "model": 'reasoning model',
    "input": $template
  });
};

A Real-World Example: Luxury Fashion Retail

A luxury fashion retailer was struggling with low website conversion rates. They had personal shoppers in-store, but scaling that experience online was a massive challenge.

Their solution? Build a virtual personal shopper!

The Results:

4 million customers served monthly
1.5 million recommendations every week
3x increase in conversion rates

By creating a real-time recommendation pipeline powered end-to-end by SurrealDB. Want to see it in action? Check out the full case study on YouTube.

Want help making your recommendations smarter? Reach out to us

Challenge accepted: announcing SurrealDB 2.0

Alexander Fridriksson — Tue, 17 Sep 2024 15:40:55 +0000

When we announced our 1.0 release, we planted our flag in the database world as a new and upcoming database with the potential of being the future of database technology.

With the massive community adoption that followed came massive expectations and challenges to live up to these expectations.

However, the people of SurrealDB - employees and community champions, stepped up with a challenge accepted attitude and solved these challenges with our 2.0 release!

We've made major improvements in:

Stability and Performance

New SurrealQL parser

We've rebuilt the SurrealQL parser from the ground up to be faster and more resilient.

It addresses many of the shortcomings of the previous version, offering better error messages and overcoming the limitations of system stack constraints, ensuring that performance remains consistent even as query complexity increases.

Unlike the previous parser, which was based on the nom parser-combinator library, the new parser is an optimised recursive descent parser with a separate lexing step. This change allows for more efficient parsing by separating the tokenisation of the input from the parsing logic itself, streamlining the parsing process.

Additionally, the new parser includes new memory management techniques to ensure optimal resource usage, minimising memory overhead during parsing operations.

Improved caching and transaction layer

The new caching mechanisms introduced in 2.0 significantly improve the performance of SurrealDB by reducing the need to refetch data from the underlying storage engine. The caching layer stores the results of frequently executed requests, allowing subsequent processes to be served from the caching layer. This improvement in the core engine will help to speed up queries which select from and modify the database.

In addition to the revamped transaction layer now providing better consistency and lower latency in distributed environments, which ensures your applications can seamlessly manage high-throughput workloads.

We can see this in practice with our graph queries, where before each field needed to fetched individually and now we fetch them all at once. We have also added a nicer syntax for that.

-- Before we used to fetch each field individually
SELECT
    ->product.category,
    ->product.sub_category,
    ->product.details
FROM order:01G07Z3RJ098G8GK8MCRZCPNF9;

-- Now we fetch them all at once and have a nicer syntax as well
SELECT ->product.{category, sub_category, details}
FROM order:01G07Z3RJ098G8GK8MCRZCPNF9;

Optimised index performance

2.0 introduces several enhancements to indexing. These include improvements to hashing mechanisms, the addition of the HNSW algorithm for AI-driven searches, and the option for asynchronous indexing and rebuilding indexes. These updates ensure that your queries are executed quickly, even as your data grows in complexity and volume.

-- Build the HNSW index without blocking operations
DEFINE INDEX mt_pts ON pts FIELDS point 
HNSW DIMENSION 4 DIST EUCLIDEAN EFC 150 M 12
CONCURRENTLY;

-- Rebuild the index
REBUILD INDEX mt_pts ON pts;

With all these advancements in stability and performance, SurrealDB 2.0 is more than just an upgrade to your database architecture - it's a platform built to handle the complex, high-performance needs of modern enterprises.

Querying

SurrealQL enhancements

In SurrealDB 2.0, we've made several improvements to our schema definitions such as the introduction of literal types to simplify the definition of complex types

-- Before: define the object then define its contents one by one
DEFINE FIELD addresses 
    ON TABLE person 
    TYPE object;
DEFINE FIELD addresses.address_line_1 
    ON TABLE person 
    TYPE string;
DEFINE FIELD addresses.city 
    ON TABLE person 
    TYPE string;
DEFINE FIELD addresses.country 
    ON TABLE person 
    TYPE string;
DEFINE FIELD addresses.post_code 
    ON TABLE person 
    TYPE string;

-- After: define a literal type of an object and its contents
DEFINE FIELD address ON TABLE person TYPE {
    address_line_1: string,
    city: string,
    country: string,
    post_code: string
};

We have also changed our default DEFINE statement behaviour to only run once, with a new OVERWRITE clause and ALTER statement for updating the schema.

-- Define a table or overite the definition if it exists
DEFINE TABLE OVERWRITE product TYPE NORMAL SCHEMALESS;

-- Make a specific change to a table definition
ALTER TABLE product SCHEMAFULL;

Speaking of updating, we’ve also changed the default behaviour of the UPDATE statement to update only and added a new UPSERT statement for the previous create or update behaviour.

-- Will fail if the record doesn't exist
UPDATE product:01GRTTE7DG94R864R67MGDT0QM 
SET name = "Surreal T-shirt";

-- Will create the record if it doesn't exist
UPSERT product:01GRTTE7DG94R864R67MGDT0QM 
SET name = "Surreal T-shirt";

GraphQL support

SurrealDB 2.0 now has experimental support for GraphQL, which together with Surrealist makes querying with GraphQL not just possible but a delight.

-- SurrealQL
SELECT name, price FROM person

-- GraphQL
{
    product {
        name
        price
    }
}

We’ve added automatic schema generation to enhance your experience. As you define your data models in GraphQL, Surrealist can automatically generate schemas for SurrealDB, saving you time and ensuring your database structure is always in sync with your application’s needs.

DEFINE CONFIG GRAPHQL AUTO;
DEFINE CONFIG GRAPHQL TABLES AUTO FUNCTIONS [fn::foo];
DEFINE CONFIG GRAPHQL TABLES {product: [name, price]};

Whether using SurrealQL or GraphQL, 2.0 offers powerful, flexible tools that make data management intuitive and efficient for your applications.

Security

Overhauled security framework

Security has been a major focus in 2.0, with significant enhancements aimed at providing robust protection and secure access to your data. Much of this is thanks to your input!

The introduction of the DEFINE ACCESS statement, which replaces DEFINE SCOPE and DEFINE TOKEN, offers greater control over user authentication, session management, and using 3rd-party authentication providers. These changes ensure that your databases are secure by default, with options for fine-tuning security settings to meet your specific needs.

DEFINE ACCESS token ON DATABASE TYPE RECORD WITH JWT
ALGORITHM RS256 KEY
"-----BEGIN PUBLIC KEY-----
MUO52Me9HEB4ZyU+7xmDpnixzA/CUE7kyUuE0b7t38oCh+sQouREqIjLwgHhFdhh3cQAwr6GH07D….";

Enhanced security features

From JWT and token-based authentication to new sanitation functions, 2.0 is designed with enterprise-grade security in mind. These features help safeguard your data against common vulnerabilities while offering the flexibility to integrate with third-party authentication providers, enabling a more interconnected infrastructure.

With SurrealDB 2.0 you now have a security framework that is both comprehensive and adaptable, ensuring that your data is protected in all scenarios.

SDKs

Native types

Across our JavaScript, C and Python SDKs, we've implemented powerful new handling for strings, numbers, floats, and booleans.

JavaScript, .Net and PHP now leverage our brand-new CBOR protocol, while C and Python are now built on top of the Rust SDK, for even more efficient and accurate data processing. With custom datatypes like Uuid, RecordId, and Geometry now supported, you can expect more versatile and efficient data handling, no matter which language you're working with.

Type Safety

We’re also committed to making our JavaScript SDK as type-safe and secure as possible. That’s why we’ve ensured out-of-the-box support for TypeScript! You can now enjoy seamless integrations with your favourite frameworks like React, SolidJS, and Vue, making your development process smoother and more efficient. PHP developers will be pleased to know that the same enhanced data handling is now at your fingertips, ensuring that your web applications benefit from the same cutting-edge technology.

SurrealML

With SurrealML, you can now seamlessly integrate machine learning into your workflows without changing your existing tech stack, enabling you to store, load, and execute models right next to your data, streamlining your processes and saving you valuable time.

You can perform complex queries that compute columns based on calculations from the ML model, and then use these values to filter, group, or order your data.

SELECT * FROM (
    SELECT *,
    ml::house_price_prediction<0.0.1>({
        squarefoot: squarefoot_col,
        num_floors: num_floors_col
    }) AS price_prediction
    FROM house_listing
)
WHERE price_prediction > 177206.21875;

You also have the ability to summarise statistics on these results with a single SQL query, keeping your insights up-to-date.

SELECT 
    math::min(price_prediction) AS min,
    math::max(price_prediction) AS max,
    math::median(price_prediction) AS median,
    math::mean(price_prediction) AS mean,
    math::stddev(price_prediction) AS stddev,
    math::variance(price_prediction) AS variance,
    math::interquartile(price_prediction) AS interquartile
FROM (
    SELECT *,
    ml::house_price_prediction<0.0.1>({
        squarefoot: squarefoot_col,
        num_floors: num_floors_col
    }) AS price_prediction
    FROM house_listing
)
GROUP BY squarefoot_col

SurrealML specialises in seamlessly running your pre-trained models directly within your database, ensuring accurate and efficient data processing right where your data is stored. This allows data scientists and engineers to focus on driving smarter decisions and insights more easily and accurately.

SurrealKV

We are excited to announce that in SurrealDB 2.0, we are now debuting our own native key value storage engine - SurrealKV. Built entirely in Rust - like the rest of SurrealDB - SurrealKV is an embedded ACID-compliant key-value storage engine with built-in versioning, that allows for historical or temporal querying.

Versioned querying

SurrealKV enables immutable data querying, data change auditing, historic aggregate query analysis, and versioned queries across the graph.

Currently, versioning is only possible when running on SurrealKV and you can access this using the VERSION keyword and a datetime. Here's an example to demonstrate how simple it is to view a snapshot of a table's records at any point in time.

First, let's create the initial version of a record:

CREATE user:john SET name = 'John v1'

Let's say that the current time is 2024-08-12 T11:30:00Z
Now update the record:

UPDATE user:john SET name = 'John v2'

As expected, normal SELECT without a version returns the latest update

SELECT * FROM user
[[{ id: user:john, name: 'John v2' }]]

To get the initial record, use the VERSION clause with the timestamp

SELECT * FROM user VERSION d'2024-08-12T11:03:00Z'
[[{ id: user:john, name: 'John v1' }]]

This ground-breaking query functionality, when combined with the advanced multi-model capabilities in SurrealDB, allows you to query data with an additional dimension, without sacrificing on performance, or forcing you to change your query in any other way. By enabling this functionality across records, record links, and graph edges, now you have the ability to time-travel across highly-connected graph data.

Now that SurrealKV is a built-in option to SurrealDB, you can try it out too. To start a database instance with SurrealKV in SurrealDB 2.0, just add the surrealkv prefix and a file name to the surreal start command.

surreal start surrealkv://my_database

What’s next?

Building a database is never finished, when you think you’ve reached the summit, you instead see another summit to climb.

The next summit we’re climbing leads into the Surreal Cloud, which our cloud team has been moving heaven and earth to make happen, as you’ll see soon.

Until then, we want to thank you for being on this journey with us!

Why SurrealDB is the Future of Database Technology - An In-Depth Look

Alexander Fridriksson — Thu, 09 May 2024 15:09:03 +0000

Here's to the crazy ones, the misfits, the rebels, the troublemakers, the round pegs in the square holes... the ones who see things differently -- they're not fond of rules, and they have no respect for the status quo... You can quote them, disagree with them, glorify or vilify them, but the only thing you can't do is ignore them because they change things... They push the human race forward, and while some may see them as the crazy ones, we see genius, because the people who are crazy enough to think that they can change the world, are the ones who do. - Apple, 1997

The conventional wisdom would be to build upon an existing ecosystem, focusing on one thing and then gradually expanding your capabilities carefully over years or even decades.

A crazy thing to do would be to create the ultimate multi-model database, or in simple terms, an "everything database" from scratch, all at once.

The conventional wisdom would be to join a start-up accelerator in Silicon Valley and grow the team locally as a US-based company.

A crazy thing to do would be to start a London-based but globally distributed company with your brother, and have your mother do the first screening of all your early employees.

The conventional wisdom would be to choose black, blue or green as your brand colours.

A crazy thing to do would be choosing pink and purple as your brand colours.

The conventional wisdom would be to choose C or C++ for building your database.

A crazy thing to do would be betting on Rust for building your database.

The conventional wisdom would be to hire top engineers from prestigious companies as your first employees.

A crazy thing to do would be hiring an opera singer as your first employee and a founder with an accounting degree, based in Zimbabwe as your first engineering employee.

As the results show, however, not being blinded by conventional wisdom has been vital in assembling our team of truly world-class rebel geniuses, pushing the boundaries of what databases (and database companies) can or even should do.

Now that we've established that we are, by all accounts, "the crazy ones", let's dive into the change we want to see in the world and how we are pushing the database industry forward!

Ghosts of databases past

欲知未來先察已然 / Study the Past if You Would Define the Future. - Confucius

Ebenezer Scrooge needed to understand his past and present to realise the painful future ahead of him unless he made significant changes. Just like Scrooge, we need to understand a bit about the past and present of database decisions to realise why the changes we made in SurrealDB enable you to avoid a painful future as well.

The most crucial thing to understand is that the developers of every database, old and new, are presented with a choice that has greater implications than any other.

Which database, if any, are you going to be compatible with?

The Faustian bargain

Unless the change you want to see in the world is simply to be the best hosting provider of a commodity database, that question quickly turns into a Faustian bargain.

A Faustian bargain is a deal where you trade something of supreme importance such as your values or even your soul, in exchange for material benefits such as power or riches.

For startups, the conventional wisdom says you should take this deal, which often means building upon or being compatible with a popular legacy database to more easily capture market share.

This sounds reasonable, and at first it might make things easier, but that one decision will force you to make countless more and, perhaps without even realising it, you have now chained yourself to design decisions made in the previous century, decisions that made sense for a world that no longer exists.

Not to mention that to be truly compatible, you need to be bug-for-bug compatible which means you either have to introduce matching bugs in your code or spend a lot of your limited time fixing bugs in the legacy database at the expense of features in your new database.

There is also the option of using marketing instead of engineering and claiming compatibility but with a lot of fine print:

We're legacy database compatible*** - trust me bro

This also puts a ceiling on your innovation by constraining yourself to only incremental innovation:

legacy database, but faster
legacy database, but distributed
legacy database, but with better marketing

A classic innovator's dilemma:

If I had asked people what they wanted, they would have said faster horses. - Henry Ford

This type of innovation can certainly help you improve the world. However, only disruptive innovation, like Henry Ford's cars, can change the world and move us into the future.

You might have started out with a grand vision, but through a series of reasonable compromises ended up same same, but different. An innovative square peg that forced itself through a cookie cutter to fit through the round hole.

The reasonable man adapts himself to the world: the unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

Faustian bargains are therefore by their nature tragic or self-defeating for those who make them, because what is surrendered is ultimately far more valuable than what is obtained.

To build or not to build

The second most important choice the developers of every database make is choosing which parts to build as your core product and which parts to rely on others for.

The conventional wisdom here again is focusing on one key idea. This usually means being best-of-breed for one thing and integrating with other best-of-breed products. It may also mean integrating with an ecosystem of extensions for functionality outside this one key idea.

This is also reasonable advice as it allows database developers to offload a lot of complexity to the end users.

As an end user of a database however, this will come back to haunt you, unless you're making the simplest of CRUD applications.

For most modern applications you now have to hunt down multiple databases, extensions or services and evaluate them to decide which ones are the best fit. They all come with their own limitations, various APIs and release cycles.

Not to mention each needing approval from IT and dealing with different maintenance guarantees. Either you put your critical infrastructure into the hands of a random person in Nebraska or you pay a premium for each best-of-breed database or extension, which often costs the same or more as your original database did because the other database or extension developers also need to make a living.

Before you know it, you'll have abstractions upon abstractions, managing a microservice architecture made up of expensive managed services in a data merry-go-round of your best-of-breed databases and extensions.

complexity very, very bad. - Grug

Nobody plans an overly complex system, but as the eight-time entrepreneur-turned-educator Steve Blank famously said:

No Plan Survives First Contact With Customers. - Steve Blank

With all their requests, customers will force you into complexity, one way or another.
This sets off a chain reaction resulting in databases being forced to abandon the idea of just doing one key thing and supporting more features, whether it makes sense to or not.

As an example, PostgreSQL was forced to add JSON support and MongoDB was forced to add SQL support. Giving rise to patchwork systems, which force new incompatible models on top of the old ones and call it a stack (don't forget to read the fine print).

Everyone has a plan until they get punched in the face. - Mike Tyson

After being repeatedly punched in the face by the complex systems customers require you to build, you have three choices:

Give up and go work on a farm
Roll with the punches and learn to live with it
Dream of something better and fight back against the complexity!

A new hope

An elegant weapon for a more civilized age. - Obi-Wan Kenobi

At a high level, SurrealDB aims to be an elegant weapon cutting through the complexity of traditional systems and making building fun again!

Our vision is to be the ultimate data platform for tomorrow’s technology by being the most powerful multi-model database platform and serverless cloud offering for developers, SMEs and enterprises.

To achieve this vision, we have had to let go of conventional wisdom because:

We can't solve problems by using the same kind of thinking we used when we created them. - Albert Einstein

But it’s not enough to think differently, we also need to execute differently!

If you want something new, you have to stop doing something old. - Peter Drucker

This is why we have been innovating across every layer of the database to bring you a data platform where features from document, graph, transactional, time-series, temporal, full-text search, vector search, machine learning and more all work seamlessly together, because it’s all one coherent system.

Storage layer innovation

Separation of storage and compute

One key architectural decision was the separation of storage and compute.
This has been a trend that started with analytical databases (OLAP) and which has since made its way into operational databases (OLTP).

The key reason for separating storage and compute is so that you can scale your resources separately. This has several benefits such as:

Less wasted capacity as scale is optimised for each workload
Greater cost efficiency
Greater flexibility
Better fault tolerance

As we'll cover in more detail when we get to deployment, SurrealDB takes storage and compute separation to the next level by offering unparalleled deployment flexibility!

Creating a new data structure for our new key-value store

We currently use a range of leading key-value stores depending on whether you're running SurrealDB embedded, single node, distributed, or directly in the browser. While they are all the best at what they do, we need them to do more!

In order to achieve our mission of enabling the world to access data in the most powerful way, we need more powerful key-value stores!

That is why we are building SurrealKV, our own native ACID-compliant storage engine, written in Rust. SurrealKV will enable versioned queries over time, immutable data querying, data change auditing, historic aggregate query analysis, and versioned graph queries.

This goes well beyond the simple time travel or temporal tables you might be familiar with. It enables SurrealDB to be a native temporal database, further extending our multi-model approach to cover Bitemporal modeling efficiently (optimised storage) and performantly (optimised compute) at the storage layer without all the complexity and effort of trying to do it yourself at the query layer.

To achieve this we needed to design a new data structure, an Immutable Versioned Adaptive Radix Trie (VART).

The term "Versioned" in VART signifies the use of versioning, indicating a chronological record of every modification made to the tree.

The "Adaptive" attribute refers to the dynamic node sizes and the path compression technique, aimed at optimizing space utilization and enhancing overall performance.

Lastly, the "Radix Trie" component underscores the adoption of a tree-like data structure, specifically tailored for storing associative arrays. - Farhan Ali Khan

Our journey building SurrealKV is still in its early stages but moving at an unbelievably fast pace and we already have an experimental release.

If you also want a more powerful key-value store, we'd love for you to contribute, provide feedback, or report issues to help shape the future of SurrealKV!

Query layer innovation

Record IDs

It all starts with the life-changing magic of SurrealDB record IDs.
A record ID has two parts, a table name and a record identifier, which looks like this table:record.

The key innovation here is that when doing CRUD with a record ID, you don't have to do a table scan, it just directly fetches the record from the key-value store!

SELECT * FROM the_future:m3laj0u0r5gb03d9b6rq;

Because it is directly fetching the record, it will always be fast, regardless of scale.

Compared with having to rely on table scans, which get slower the more data you have.

SELECT * FROM the_past
WHERE id = 124316249;

This brings us to the second key point, introducing helpful default options that work well as you scale.

Instead of the traditional default of auto-increment or serial IDs you might be used to from legacy databases, when you create a record in the future table:

CREATE the_future;

A random ID is assigned:

the_future:m3laj0u0r5gb03d9b6rq

This allows you to avoid common scaling problems such as:

Auto incrementing that locks, impacting concurrency and scalability of your database.
Accidental information disclosure through using IDs in URLs, giving away data size and velocity.
Non-uniqueness across tables or table shards across distributed nodes.

If sequential IDs are needed, we’d recommend you use either a ulid or a uuid v7.

CREATE the_future:ulid();
CREATE the_future SET id = rand::uuid::v7();

The integer as primary key defaults made sense for the world in which they were taken, the world before the internet, but they don't make sense anymore.

By having helpful defaults we want to make it as easy as possible to fall into the pit of success:

The Pit of Success: in stark contrast to a summit, a peak, or a journey across a desert to find victory through many trials and surprises, we want our customers to simply fall into winning practices by using our platform and frameworks. To the extent that we make it easy to get into trouble we fail. - Rico Mariani

Native multi-model

Earlier we talked about customers forcing complexity on you and databases needing to adapt by forcing new incompatible models on top of the old ones, JSON on top of SQL and SQL on top of JSON.

The problem you end up with there is a great experience for the original data model but a second-class experience, or worse, for the other data models forced on top of it.
You might need to learn a whole different query language for the different data models and have all sorts of limitations, which the original data model doesn't have.

The native multi-model database solves these problems by making all the data models (document, graph, relational, etc) the original data model and having one unified query language. In our case, the SQL-like query language SurrealQL, which comes with many quality-of-life improvements over other SQL dialects.

For SurrealDB this means integrating the good parts of each data model seamlessly together.

For example, being schemaless by default allows for rapid prototyping, which is a great thing about the document model.

CREATE free_future CONTENT {
    message: "Freedom!!"
};

As well as being fully ACID compliant and schemafull, which are great things about the relational model.

DEFINE TABLE safe_future SCHEMAFULL;
DEFINE FIELD id ON safe_future TYPE record<safe_future>;
DEFINE FIELD message ON safe_future TYPE string
-- lets also make sure the message is more then 1 letter
ASSERT string::len($value) > 1;

INSERT INTO safe_future (message) VALUES ('lock it down!');

Or even something in between, a schemafull table with schemaless flexible fields.

DEFINE TABLE flexible_future SCHEMAFULL;
DEFINE FIELD metadata ON flexible_future FLEXIBLE TYPE object;

INSERT INTO flexible_future {
    metadata: {
        message: "so meta!"
    }
};

When we were taking the decision of how to make relationships between our data, we decided to go beyond SQL Joins and instead use document record links and graph relationships because establishing the relationships at write time makes it simpler and easier for everyone at query time.

-- Using record links to select from the person and product table
SELECT
rating,
person.name,
product.name AS product_name
FROM review;

-- Using graph relations to select from the person and product table
SELECT
order_date,
product_name,
<-person.name AS person_name,
->product.description
FROM order;

Real-time data sync

Streaming real-time transactional data is a difficult problem to solve, which is exactly why we made it extremely easy for you.

Live Queries in SurrealDB enable a simple yet seamless way of building modern, responsive applications, whether connecting to SurrealDB as a traditional backend database, or when connecting directly to the database from the frontend.

Live Queries give developers the ability to receive real-time change notifications to data as it is happening. Integrated directly within the table, row, and field level permissions of SurrealDB, each Live Query notification is unique and tailored to the authentication of the user who issued the query.

Regardless of what a user has subscribed to, notifications will only be delivered based on the authenticated session of that user.

To make a query like this one a Live Query...

SELECT message FROM the_future;

...all it takes is one magic word to unlock streaming data magic

LIVE SELECT message FROM the_future;

And by bringing the simplicity of Live Queries alongside the advanced nature of pre-defined aggregate views, you can now build powerful dashboards that rely on aggregate data queries, computationally expensive analytics queries, and filtered collections of massive data sets - that update in real-time as your data in the database changes.

Machine learning made easy

SurrealML enables machine learning models to be greatly simplified, ensuring reproducibility and consistency in machine learning pipelines. Running on our Rust engine, models can be built in Python and imported into SurrealDB for inference within the database runtime.

With support for model versioning, machine learning pipelines can be further simplified, bringing consistency and reproducibility to data normalisation and model inference, allowing for execution across multiple different Python versions, environments, and platforms.

It's easy to get started and once a model is uploaded to SurrealDB you can use it directly in SurrealQL.

SELECT
    ml::house-price-prediction<0.0.1>({
        squarefoot: squarefoot_col,
        num_floors: num_floors_col
    }) AS price_prediction
FROM house_listing;

Indexing reimagined

SurrealDB has support for traditional indexing, unique indexes and constraints, full-text search indexes, and vector-embedding indexing.

We reimagined how indexing might be implemented - opting for a completely custom-built indexing engine, which sits within the SurrealDB core itself without the need for an additional external query language, or for indexing-specific functions or plugins.

DEFINE INDEX userEmailIndex ON TABLE user COLUMNS email UNIQUE;

The indexing engine is agnostic to its deployment environment - whether running on top of IndexedDB in the browser, an embedded run-time in Rust or Python, or distributed over multiple nodes in a highly scalable cluster.

Using the full-text search indexing engine, SurrealDB allows developers to define custom analysers which specify exactly how their text data should be processed, with support for multiple tokenizers, advanced filters including Ngram, EdgeNGram, and Snowball, and support for 17 languages from English to Arabic.

DEFINE INDEX userNameIndex ON TABLE user COLUMNS name SEARCH ANALYZER ascii BM25 HIGHLIGHTS;

With vector-embedding indexing, our initial implementation supports exact nearest neighbour retrieval for vectors of arbitrary size using Metric Trees, with support for HNSW-based approximate nearest neighbours retrieval coming soon.

DEFINE INDEX mt_pt ON pts FIELDS point MTREE DIMENSION 4;

In the example below, the query searches for points closest to the vector [2,3,4,5] and uses vector functions to calculate the distance between two points, indicated by <|2|>.

LET $pt = [2,3,4,5];
SELECT
    id,
    vector::distance::euclidean(point, $pt) AS dist
FROM pts
WHERE point <|2|> $pt;

Deployment innovation

As mentioned above, SurrealDB takes storage and compute separation to the next level!

Some companies have gone through the herculean effort of retrofitting a legacy database with decades of tech debt into a cloud-native solution with a custom storage backend, usually including some form of S3 object storage.

Others have created a cloud-native database from scratch, which is compatible with a particular legacy database.

What they all tend to have in common is that they are cloud-native, but also cloud-only because the architecture is custom-made with the cloud in mind.

SurrealDB was designed from the start to have unparalleled deployment flexibility,
combining the ease of use of embedded databases such as SQLite and the power of client-server databases with all our multi-model features into a single Rust binary!

This means you have access to all the features wherever you are running SurrealDB:

As seeing is believing, you can check out the same SurrealDB that runs distributed, running in Web Assembly and storing data to your browser right now using our official management interface, you don't even have to log in!

With unparalleled deployment flexibility, you naturally also need flexibility in how you connect to the database. That is why we offer SDKs for various languages such as Rust, JavaScript, Python, Golang, Java, and more!

You can also connect directly to the database using our RESTful HTTP endpoints or our unified RPC protocol, supporting both WebSockets and using HTTP to POST to the /rpc endpoint.

Stepping into the future

You cannot predict the future, but you can create it. - Peter Drucker

As we have seen, the change SurrealDB wants to see is to enable the world to access data in the most powerful way while simultaneously reducing your total system complexity, by offering a coherent data platform where everything works seamlessly together.

Help us push the database industry forward by taking a step ahead into the future with us by signing up for the SurrealDB cloud waitlist or exploring SurrealDB right in your browser using Surrealist.

Eli5 - Why SurrealDB, explained through building with LEGO

Alexander Fridriksson — Mon, 15 Apr 2024 15:48:34 +0000

Can databases inspire a sense of child-like play?

Some people might take offence at that question because of the belief that of course databases are supposed to be the pinnacle of boring technology #justusepostgres

While we are all aware of the benefits of boring technology—nobody wants to be on call at 4 AM firefighting database downtime—what about the work you'll do 99% of the time?

That's when there is an opportunity to make your life significantly less boring while getting more work done because "work made fun gets done", and what's more fun than child-like play?

I therefore invite you to take off your "jaded adult" hat for a moment and channel that inner child energy. The energy you get from the love of building things, no roadmap, no managers, just pure building bliss!

When you read that last sentence, what image did you have in mind?

For me, when I'm picturing just pure building bliss, I'm immediately transported to my grandmother's living room as a child. I'm sitting on the floor with my friends, with random Lego bricks all around, and we're just putting that child-like imagination to full use making up stories and building as we go!

Then it happens!

Our building goes from pure bliss to frustration and frantic searching... the random LEGO pieces stop fitting together... but the story must go on, so we search and search through the pile of random LEGO bricks all over the floor and... it's worse than we thought! We're missing some pieces we needed to complete our masterpiece!

What do you think comes next?

Of course, it's persuading the adults to buy more LEGO sets!

This obviously doesn't always work, and even if it works it can take a lot of time to get the new set...

Do we then just leave our masterpiece unfinished and do something else because the adults ruined everything... or do we accept their suggestion to “Just use what you have” and ruin it ourselves by forcing random pieces to fit together?

As an adult that might not seem like a big deal, you don't have infinite money and your child has a lot of LEGO sets already. But as kids we're now upset, maybe even crying because the adults refused to let us get the LEGO sets which we obviously need!

When play becomes work

Now you might be thinking, cool story bro, but what has this got to do with SurrealDB?

Quite a lot, actually. See if you notice any similarities in the mindset we had as children with the one we have as adults working on databases.

We started building full of excitement and made great progress until we realised we needed more LEGO sets to finish our masterpiece. After a lot of searching, we told the adults what we needed to complete our masterpiece and they told us no, use what you have, which left us with a difficult choice.

We therefore learn that we often have to compromise our vision. We learn to be careful when we're starting something because we might end up disappointed. We learn to expect the adults to say no, so why even ask?

But most of all, we learn that starting things is play but finishing things is work.

Because at the start, we have all the LEGO pieces we need, and we can just be in builder mode, building as fast as we can think!

...until the LEGO pieces run out or stop fitting together and we're now kicked out of builder mode and are forced into compromise mode. We go from being full of excitement to being full of anxiety. Because our thoughts go from imagining what else we can build, to imagining what compromises we need to make and the potential conflicts that might cause.

Play has now become work.

But it doesn't have to be this way!

It's hard to compete with someone having fun

At SurrealDB we're pushing the boundaries of what databases can or even should do, all so that you can spend as much time as possible having fun in the builder mode and as little time as possible in the anxiety-inducing compromise mode.

We've looked at all the various LEGO sets that builders end up needing and created the one LEGO set to rule them all! ...or in other words, the ultimate multi-model database!

The one LEGO set makes it easy for you to start building and, more importantly, keep building without getting stuck searching for missing pieces, asking adults (managers) to buy more pieces or hoping all the random free pieces fit together. We've already made sure everything fits together so that you can have fun building!

This journey to remove as much friction as possible from the building process has resulted in a ton of technological innovation, which we'll cover more seriously in another blog post.

The key message here is that work made fun gets done and we make databases fun to play around with!

"Work made fun gets done" isn't just some meaningless slogan either, but backed up with science. As the "marshmallow problem" demonstrates, kindergarten students having fun consistently outperformed well-educated adults in this well-known challenge to build a structure of dry spaghetti, one yard of tape and a marshmallow. It turns out that it's hard to compete with someone who is having fun!

The key lesson from the challenge was about prototyping and iterating quickly, and what is prototyping if not playing around? There is a lot to be learned from child-like play!

I would therefore encourage you to play around with SurrealDB and have some fun!

Why we are betting on Rust!

Alexander Fridriksson — Wed, 13 Mar 2024 16:12:15 +0000

SurrealDB has been built from the ground up to be the ultimate database for developers who want to build tomorrow's applications.

In order to build the future of databases that can support the future of applications, we needed the future of low-level programming languages. We believe this language is Rust, which quite literally has allowed us to bring the future to our SQL-like query language SurrealQL, inspired by the Rust future.

Go slow to go fast

Front and centre on our home page, you'll see these three sentences:

Develop easier.
Build faster.
Scale quicker.

This is not only what SurrealDB can do for your application, but what Rust does for SurrealDB. Due to Rust's notoriously steep learning curve, develop easier and build faster might not be the first thing that comes to mind when you think of using Rust.

However, the ways Rust supports you to think differently, through using the borrow checker, lifetimes and more, pays compounding interest as your application starts achieving a phenomenal balance of safety and performance.

Yes, you may not be as fast going from 0 to 1 in Rust as in some other languages, but it's easier than you think. This might however mean prototyping in a different language and then re-writing it in Rust once you see a long-term potential for the project. SurrealDB itself was first prototyped in Go and then entirely re-written in Rust.

The key point here is that once you pay the upfront cost of learning Rust, you can go further faster, or as is often said: go slow to go fast.

Now, before we go any further, we need to address the question I'm sure many of you will have: why not Zig or other newer languages?

While it's true that you can build a database with Zig or other newer languages, they don't have the features of Rust when it comes to memory safety by default while not having a garbage collector for the highest performance. However, there is one thing that stands out more than any feature difference.

Critical adoption

There is no denying that Rust has captured the hearts and minds of the developer community, having topped the chart as “the most admired programming language” in Stack Overflow’s annual developer survey for 8 years in a row. Far from being just admired, it's also one of the fastest-growing languages on GitHub.

Rust has even achieved adoption where no other language aside from C has been, not even C++, namely the Linux kernel.

This begs the question, why?

As Software Engineer Wedson Almeida Filho said

We feel that Rust is now ready to join C as a practical language for implementing the kernel. It can help us reduce the number of potential bugs and security vulnerabilities in privileged code while playing nicely with the core kernel and preserving its performance characteristics.

To put this into perspective, a talk at the 2019 Linux Security Summit showed that memory safety issues are estimated to account for 65-88% of security vulnerabilities in major systems, including Android and Ubuntu.

This also aligns with Microsoft's experience

~70% of the vulnerabilities Microsoft assigns a CVE each year continue to be memory safety issues

This goes to show how even after decades of development from some of the best engineers using C and C++ best practices, issues such as accessing memory that has already been freed, data races, and other problems still persist. Therefore it's hard to claim it's just skill issues, but rather more likely fundamental issues with the languages being used.

With this context in mind, you can see why Microsoft Azure CTO Mark Russinovich said

Speaking of languages, it's time to halt starting any new projects in C/C++ and use Rust for those scenarios where a non-GC language is required. For the sake of security and reliability. the industry should declare those languages as deprecated.

This trust in Rust does not come from nowhere, there has been a tremendous amount of work put into making Rust fully usable in safety-critical environments. This can be seen in Rust's recent qualification under the ISO 26262 and IEC 61508 standards through the Ferrocene toolchain developed by Ferrous Systems.

You might have also heard that governments are taking notice of these developments, such as the US White House Office of the National Cyber Director (ONCD). This office issued a report last month (February 2024) recommending a move to memory-safe programming languages and pointing to Rust as an example of a potential replacement for C and C++ in safety-critical space systems.

It's not rocket science

While databases are not life-critical space systems, they often play a life-critical role in organisations. You want to keep your data safe and secure because if you lose your data, you can lose your business.

Therefore if between 65-88% of security vulnerabilities in major systems can be caused by memory issues, it's not rocket science to understand why we are betting on Rust to keep your data as safe as possible.

At SurrealDB we are passionate about building the future we want to see in the world.

If you want to be a part of building this future, you can join our exceptional team of Rust engineers and contribute to shaping the future of SurrealDB.

If you want to be a part of experiencing this future, get started with SurrealDB.

Thinking Inside The Box: Relational Style Joins in SurrealDB

Alexander Fridriksson — Wed, 21 Feb 2024 16:23:07 +0000

SurrealDB doesn't do traditional SQL joins. What we have instead done is think from first principles what developers need from database relationships to be able to develop easier and scale quicker.

This led us to our primary ways of creating relationships at write time to simplify scaling and improve developer experience at query time. To dig more into that, see our previous Beyond SQL Joins blog post.

That blog post ended with the question:

However, what if we want to make arbitrary relationships on the fly similar to relational style joins?

Let's now explore the answer to that question where, spoiler alert, we will learn about semi-joins, anti-joins, and correlated subqueries. Those are fancy terms, but don't worry, it's all going to make sense soon enough 😉

Starting with:

It's me, subquery, I'm the problem, It's me

Now that the secret is out of the bag, let's dive into the code to show how it works in practice.

Our data and examples for today will be based on the w3schools SQL tutorial(great resource btw) where we will compare the SQL approach shown there with SurrealQL.

This is the very simple schema that we will be using, consisting of a product table and a supplier table.

ProductID	ProductName	SupplierID	Price
1	Chais	1	18
2	Chang	1	19
3	Aniseed Syrup	1	10
4	Chef Anton's Cajun Seasoning	2	22
5	Chef Anton's Gumbo Mix	2	21.35

SupplierID	SupplierName
1	Exotic Liquid
2	New Orleans Cajun Delights
3	Grandma Kelly's Homestead
4	Tokyo Traders

If you want to follow along, you can go to https://surrealist.app/
→ Create session
→ Click on sandbox
→ Type any name for namespace and database
→ Copy and paste the two queries below, which work both for SurrealDB and most relational DBs

Products table:

INSERT INTO Products (ProductID, ProductName, SupplierID, Price)
VALUES (1, "Chais", 1, 18), (2, "Chang", 1, 19), (3, "Aniseed Syrup", 1, 10), (4, "Chef Anton's Cajun Seasoning", 2, 22), (5, "Chef Anton's Gumbo Mix", 2, 21.35);

Supplier table:

INSERT INTO Suppliers (SupplierID, SupplierName)
VALUES (1, "Exotic Liquid"), (2, "New Orleans Cajun Delights"), (3, "Grandma Kelly's Homestead"), (4, "Tokyo Traders");

Semi-Joins

It's a subquery that filters the outer query with the results of the inner query.

Now that we've got the dictionary definition out of the way, let's get to the exciting stuff, where we'll explore the same query written in 3 different ways in both SQL and SurrealQL.

This will really help you see the difference between a semi-join, correlated subquery and a traditional SQL join.

`IN` example

As SurrealQL is a SQL-like language, you’ll notice that the examples will look very similar, such as just a single word difference like in the below example, see if you can spot it.

SQL Example

-- Outer query
SELECT SupplierName
FROM Suppliers
WHERE SupplierID IN (
    -- Inner query
    SELECT SupplierID 
    FROM Products 
    WHERE Price < 20
    );

SurrealQL Example

-- Outer query
SELECT SupplierName
FROM Suppliers
WHERE SupplierID IN (
    -- Inner query
    SELECT VALUE SupplierID 
    FROM Products 
    WHERE Price < 20
    );

Since inner queries run before outer queries, it's often good to read subqueries from the inside out.

Starting with us filtering for products in the Products table that have a price less than 20, then "joining" the Products table to the Suppliers table using the SupplierID.

This then allows us to see the names of the suppliers which have products where the price is less than 20.

The reason why you see the word VALUE added to the SELECT in SurreaQL is because by default SurrealDB returns objects. Since we want to check if an ID is in an array of IDs, SELECT VALUE allows you to return an array of values instead.

A key thing to notice here is that the entirety of the inner query is run before the outer query, which would make it an uncorrelated subquery. This does have some pros and cons, as we'll explore in our next example.

Correlated subquery example

It's still a subquery that filters the outer query with the results of the inner query and is a type of semi-join.

SQL example

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (
    SELECT SupplierID FROM Products 
    WHERE Products.SupplierID = Suppliers.supplierID 
    AND Price < 20
    LIMIT 1);

SurrealQL example

SELECT SupplierName
FROM Suppliers
WHERE (
    SELECT SupplierID FROM Products
    WHERE SupplierID = $parent.SupplierID AND Price < 20
    LIMIT 1
);

The key difference here is that the inner query can't run without the outer query, as you can see in the WHERE statement. Where the defining characteristic of correlated subqueries is that it references a column in the outer query and executes the subquery once for each row in the outer query.

Now you might be wondering, why would we do that?

There is a straightforward answer to that: it might be more performant, but as always, it depends™

One of the things it depends on is how your query optimiser treats it, but it also depends on how large the table of the inner query is and how complicated the logic is.

In our example, the product table is small and only filters on price, so it's not a big deal to scan the whole table. The reason why WHERE SupplierID = $parent.SupplierID can be more performant is because we are limiting the range of possible records/rows to scan to only those relevant to the outer query. In addition we are also adding LIMIT 1 to the inner query since we are executing the subquery once for each row in the outer query, therefore we can further reduce the rows returned by the inner query.

There are also reasons to do it that are not directly related to performance, such as you could literally run out of memory if the product table were large enough... which we won't cover here so that you won't run out of memory for this blog post 😉

The key thing to remember is that you cannot assume one way is always better as it depends on the above factors and more. Therefore its worth testing both if you need the best performance as the results might surprise you.

Anti-Joins

You're in for a surprise here... nah, just kidding, It's still a subquery that filters the outer query with the results of the inner query.

The only difference is that it's the opposite of the queries above. Instead of IN, it is NOT IN, and instead of EXISTS, it's NOT EXISTS. However since SurrealDB doesn’t use EXIST you instead put ! in front of the subquery, to indicate NOT EXISTS like in this SurrealQL example.

SELECT SupplierName
FROM Suppliers
WHERE !(
    SELECT SupplierID FROM Products
    WHERE SupplierID = $parent.SupplierID AND Price < 20
    LIMIT 1
);

SQL join example

Ok ok, enough with the subqueries, let's look at how this compares to a normal SQL join.

`LEFT JOIN` example

SELECT DISTINCT Suppliers.SupplierName
FROM Suppliers
LEFT JOIN Products ON Products.SupplierID = Suppliers.supplierID
WHERE Products.price < 20

Just to reiterate, the IN, EXIST, and this LEFT JOIN examples all give the same results, they just have a different way of getting there.

This LEFT JOIN approach would probably be most people's initial approach, but can you spot why it might be better to use the subquery approach as seen above?

It's because with the LEFT JOIN we get duplicate results that we then need to de-duplicate. This might be less performant than the subqueries, but again, it depends™

SurrealDB does not do these typical joins and one of the reasons is this unnecessary computation of getting more data than you need and then spending even more computing resources on de-duplicating/filtering the data back down to what you actually need.

But that leaves the question, how would you do it instead in SurrealDB?

One possibility is using record links:

SurrealQL record links example

SELECT Suppliers.SupplierName  
FROM Products
WHERE price < 20

As you can see in this example, things don't need to be complicated.

This example will produce the same result as the semi-joins and the left join, but without any table scan at all due to how our record IDs work.(if you're following along, this query will not work with the imported dataset, because that is using SQL numeric IDs instead of SurrealDB record IDs).

If you can make relationships at write time, you can have peace of mind at query time that you're not going to get duplicate data that you then need to remember to de-duplicate. That is even if everyone who has access to this can remember to use the right join conditions to begin with 😅

I hope this clears some of the fears of missing out (FOMO) that you might have about SurrealDB not having traditional SQL joins. You can still do the things you need to do such as with the subqueries. When it comes to the traditional joins though, we think about it more in terms of the joy of missing out (JOMO) because the best way to reduce errors in your code is by writing less code, as seen in our record links example.

If this has piqued your interest, why not check out our previous Beyond SQL Joins blog post or just get started with SurrealDB.

Faster horses and flying cars

Alexander Fridriksson — Mon, 07 Aug 2023 14:14:01 +0000

If I had asked people what they wanted, they would have said faster horses. - Henry Ford

Most people didn’t see the need for a car when everything depended on horses.

I’m sure Henry Ford heard a lot of this back in his day:

“ Why are you spending so much time on this useless car idea, cars will never become as reliable as horses. Just stick to what works, you don’t have to reinvent the wheel! “

Now we all know that Henry Ford did not give up and, for all intents and purposes, did reinvent the wheel in terms of changing how the world does transportation.

Now my question is: why don’t we have flying cars?

Have we, just like our ancestors, settled for faster cars instead of taking the next step into the future of transportation?

Let’s bring this home to us, SurrealDB is a database company, not a car maker. However, there are plenty of similarities between our vision for the future of databases and Henry Ford’s vision for the future of transportation.

In our case, Henry’s famous quote would say

If I would have asked people what they wanted, they would have said a faster Postgres

Right?

It certainly feels like that at times:

“ Why are you spending so much time on this useless database idea, SurrealDB will never become as reliable as Postgres. Just stick to what works, you don’t have to reinvent the wheel! “

Therefore the question is, will we give up?
This question is on the minds of a lot of people that want to adopt our technology.

And I’m here to assure you that the answer is no, we will not give up!

Why am I so sure of this?

The people who are crazy enough to think they can change the world are the ones that do. - Steve Jobs

This company is made up of people who have overcome a lot to get where we are today therefore, we know we can make a change and are not afraid to overcome the challenges ahead!

We are daring to reinvent the wheel and take the next step into the future of databases.

If you want to join us, then I encourage you to sign up for our version 1.0 launch event on September 13 to see what the future holds!

And, of course, join us on Discord, we’d love to hear from you!

The life-changing magic of SurrealDB - record IDs

Alexander Fridriksson — Thu, 11 May 2023 15:25:54 +0000

At SurrealDB, we are all about doing things that spark joy for developers ✨
One of those things that constantly surprises and delights is the humble record ID, which we discussed in our live stream.

Far from being just a boring number, your queries can be magical.

Without further ado, then, let's learn some surreal magic! ✨

The look and feel

The first thing to be aware of is that in SurrealDB, a record ID has two parts, a table name and a record identifier, which looks like this table:record.

By default, when you create a table, create internet, a random id is assigned. This differs from the traditional default of auto-increment or serial IDs you might be used to.

This allows you to avoid common problems such as:

Impact on the concurrency and scalability of your database.
Accidental information disclosure through using IDs in URLs, giving away data size and velocity.
Non-uniqueness across tables or table shards across distributed nodes

The typical solution to this is to use cryptographically secure randomly generated identifiers such as our default rand(), but you can also use ulid() and uuid()

In a nutshell, we want to make it as easy as possible to fall into the pit of success, where the default option works well as you scale.

Let's step back a bit and look at the simplest example of comparing SQL and SurrealQL, where we select one id from a table.

SQL

SELECT * FROM internet
WHERE id = 1337

Just a completely run-of-the-mill example, nothing special about it.

SurrealQL

SELECT * FROM internet:leet

Here we can see something new. We can filter to a record right from the from statement!

This would be the shorthand example for:

SELECT * FROM internet
WHERE id = internet:leet

Now let's step it up a bit. What if we needed to select a range of ids?

SQL

-- Over 9000!
SELECT * FROM internet
WHERE id > 9000

-- less than 9000
SELECT * FROM internet
WHERE id < 9000

-- between 9000 and 10000
SELECT * FROM internet
WHERE id > 9000
AND id < 10000

SurrealQL

-- Over 9000!
SELECT * FROM internet:9000..

-- less than 9000
SELECT * FROM internet:..9000

-- between 9000 and 10000
SELECT * FROM internet:9000..10000

However, this is much more than just some syntactic sugar!

To understand why, we need to talk a bit about algorithmic complexity... don't worry. It's not that complicated 😉

The performance at scale

In the simplest of terms, an algorithm is just a recipe for a sequence of steps (do this, then that). Therefore, algorithmic complexity (often talked about in terms of asymptotic/big O notation) is just a way to estimate how many steps are likely to be in the sequence. The fewer steps the better.

With SurrealQL, when doing CRUD with a record ID, you don't have to do a table scan, which gets slower the more data you have. This is because the algorithm most databases use for this is O(log n) instead of the O(1) key-value lookup in SurrealDB, which has near-constant performance regardless of scale.

This makes working with IDs extremely fast as you scale and opens up new use cases.

The flexibility

Now it's time for some real magic ✨

It's time to free your mind and rethink what is possible with an ID.

SurrealDB's Complex Record IDs support dynamic expressions, allowing parameters and function expressions to be used as values within the IDs!

This is useful in various ways, such as a time series context or ensuring locality between specific records in a table. Effectively creating clustered indexes & partitions naturally in your data as you scale with the performance of the ID lookup regardless of size!

While this does not replace traditional indexes or partitions for your data, it offers additional flexibility to model the data in a performant way.

Let's look at an example of creating Object-based Record IDs

-- Set a new parameter
LET $now = time::now();
-- Create a record with a complex ID using an object
CREATE temperature:{ location: 'London', date: $now } SET
    location = 'London',
    date = $now,
    temperature = 23.7
;

You can also create Array-based Record IDs

-- Set a new parameter
LET $now = time::now();
-- Create a record with a complex ID using an array
CREATE temperature:['London', $now] SET
    location = 'London',
    date = $now,
    temperature = 23.7
;

Which you can query like this

-- Select all records for a particular location, inclusive
SELECT * FROM temperature:['London', NONE]..=['London', time::now()];

-- Select all temperature records with IDs between the specified range
SELECT * FROM temperature:['London', '2022-08-29T08:03:39']..['London', '2022-08-29T08:09:31'];

As you can see, pretty much the only limit to what a record ID can be is your imagination.

The future

We have just scratched the surface of what you can do with record IDs. If you want to dig deeper, take a look at our stream about Record IDs, featuring even more powerful capabilities, or check out our documentation.

If this has sparked some ideas, perhaps something you've always wanted was possible, we'd love to hear from you!

You'll find us at all the usual places, including our discord and GitHub

The ultimate beginners guide to databases

Alexander Fridriksson — Fri, 21 Apr 2023 11:20:34 +0000

What is a database, and why do we need one?

Better yet, why do companies keep creating new databases since we already have over 300 of them?

If you are curious about those questions, you have come to the right place.

We will strip away all the fancy jargon and technical details and focus on understanding the essential concepts of databases through simple analogies.

How do we make sense of the word?

As long as humans have existed on this planet, we have tried to make sense of it.
Just as cavemen drew symbols on walls, we draw symbols on paper... or perhaps more accurately... we type emojis into our smartphones.

Regardless of how we do it, it's always about storing and sharing information in some form.

That, in its essence, is what databases do, store and share information.

We have gotten a lot better at storing and sharing information over the years, from cave walls to the printing press and many things in between.

However, something fundamentally changed when computers arrived. We transitioned from storing and sharing information physically to storing and sharing information digitally.

From pen and paper to CRUD with digital paper

Instead of just using paper to store and share information, we now had access to digital paper: databases.

We couldn't interact with this digital world physically. Instead, we had to tell the computer how to interact with it on our behalf. This required new mental models and new languages.

The most fundamental mental model is CRUD, a term coined in 1983, which stands for Create, Read, Update and Delete.

Those are the four fundamental ways we interact with data of any kind, anywhere.

Put simply:

Create = make data / let's write a poem on a piece of paper.
Read = retrieve data / find the piece of paper you wrote on and read the poem.
Update = change data / let's change a few lines in the poem.
Delete = remove data / the poem wasn't good, let's throw the paper in the trash bin

While databases trace their origin back to the 1960s with the Integrated Data Store (IDS), databases, as we know them today, got started in the 1970s.

In June of 1970, it's fair to say that Edgar F. Codd made a dent in the universe with his paper titled A Relational Model of Data for Large Shared Data Banks.

That paper gave rise to most of the concepts we still use today in relational databases, which we will explore soon.

The most notable thing missing from his paper was how to put these genius ideas into practice, since most people (myself included) are not set theory geniuses.

It wasn't until a few years later that Donald D. Chamberlin and Raymond Boyce, after reading Codd's paper, came up with another genius breakthrough. A new language for making practical use of the relational model ideas. They called it Structured English Query Language or SEQUEL for short.

Genius researchers can apparently also be somewhat petty, where part of the reason it was called SEQUEL was to tease other researchers who made the QUEL language. As in, SEQUEL is the sequel to QUEL. Well... I guess QUEL got the last laugh because they had to change the name to SQL (Structured Query Language) due to trademark infringement.

It wasn't until 1979 though, when Relational Software, Inc. (now Oracle) introduced the first commercially available implementation of SQL in a relational database.

That brings us to the other fundamental models we are going to cover today:

The relational model -> relational databases
The document model -> document databases
The graph model -> graph databases
Multi-model -> SurrealDB

Relational databases - like a jigsaw puzzle

We have already touched on the origin of the relational model. However, we have yet to explore what it is.

In the simplest terms, relational databases are like a jigsaw puzzle, where each puzzle piece represents an Excel-like table.

One of the key innovations of the relational model is how you connect (or join) each puzzle piece together.

The basic workflow for working with a relational database is as follows:

Plan how the puzzle pieces should look so they fit together (define a schema)
Create the puzzle pieces (insert statements)
Assemble the puzzle pieces (select queries)
Then, you can see the complete picture of the puzzle (result set)

Just like for jigsaw puzzles, for each picture you want to see, you might need to assemble many different puzzle pieces.

Document model - like a Word document

Now we come to the question people have been asking since the 1990s and will be asking for a long time.

Why do we need other database models when we have the relational database model?

It was because the internet was just getting started and becoming popular at the time.

Since relational databases were not invented with the internet in mind (because the internet did not exist then), they became very slow. You can imagine the chaos of trying to organise 1000 or more people who want to work on assembling the same puzzle.

Therefore the internet gave rise to other models like the document model.

In the simplest terms, document databases are like a Word document, where you make sure that the document has all the relevant information in one place, kind of like this blog post.

The thinking goes that it's easier to make 1000 copies of a single document for each of the 1000 people that need it rather than making 1000 puzzles, each with many oddly shaped puzzle pieces.

This also gave rise to the term NoSQL in 1998 by Carlo Strozzi who, interestingly enough, used it to describe a relational database which didn't use SQL. As we mentioned before, in the history of the relational model, SQL came much later as a way to implement the relational model, but it's entirely possible to find other ways to implement the relational model other than using standard SQL.

However, NoSQL, which originally literally meant "No SQL", as in this database does not use SQL, is most commonly used to refer to databases that don't use the relational model.

For example, MongoDB, a popular document database, which came out in 2009 uses the MongoDB Query Language (MQL).

However, as relational databases started adopting internet technology and fixing the problem of how to organise the 1000 people who want to work on the same puzzle, people began wanting to use SQL in more and more places.

That is why the term NoSQL changed meaning to "Not only SQL", as even popular databases like MongoDB started adding support for SQL.

The basic workflow for working with a document database is as follows:

Just like in a Word document, you just open a new document and start writing in all the information that you need.
Then anyone can just read that document to get the information.

That works great when you don't have the time or the desire for so much upfront work of creating and solving a puzzle every time you want an answer.

However, this apparent simplicity comes with other disadvantages. We won't touch on that much here except to say, which organisation doesn't have a problem organising Word documents? Important_document_v5_final_final.docx seem familiar?

Graph model - like your social network

As we were searching for new ways to interact with this digital world, especially with the advent of e-commerce and social media, we found that it can be very tricky to express complex networks as text in Word documents (document model) or jigsaw puzzles (relational model).

Therefore, a new mental model was needed to express complex networks more simply. In this new graph model, we think about relationships slightly differently than in the relational model.

In the simplest terms, graph databases are like your social network, where when you're looking for help you might know somebody that knows somebody that can help you.

The basic workflow for working with a graph database is as follows:

You make a map of who knows what and how they are connected
Then, you ask around the network for what you want until you find it.

Multi-model - SurrealDB

If you've read this far, you might be beginning to understand why companies keep creating new databases. As the world keeps evolving, databases need to keep evolving as well. New technology enables us to take advantage of new ways of storing and sharing information.

The question is, how should databases evolve?

The answers to that generally fall into three categories:

Let's make the relational model better. How oddly shaped we need to make the puzzle pieces doesn't matter. We can force them to fit using some clever tricks.
Let's create a new model or new database for every different kind of task we have. If you could choose, wouldn't you choose the clothes that were tailor-made for you instead of the retail store clothes, which might be an awkward fit?
What if we could do both? What if we could combine different innovative approaches to create a database that both feels custom tailored in its model flexibility but retains the simplicity we have come to expect from the relational model?

The third option is what we at SurrealDB have chosen, as we were dreaming of something better.

SurrealDB combines aspects of many different kinds of database models, including the major ones we talked about in the post. For an excellent technical introduction, you can watch this short video.

In the simplest terms, however, SurrealDB is like WD-40, use SurrealDB on problems that need lubricating for things to go much more smoothly. But, of course, if you want things to go smoothly from the beginning, you also use it as a preventative measure.

This is just the start

We have covered a lot of ideas and concepts in the post. If you want to learn more about the technical side and how to do CRUD practically across all these models and SurrealDB, subscribe so you don't miss our next post.

Behind the scenes of the exciting beta 9 release

Alexander Fridriksson — Thu, 13 Apr 2023 11:29:15 +0000

Our team has been working very hard on the new release, which introduces a ton of new features, bug fixes and performance improvements you can see here.

However, the story of the beta 9 release is not about our team; it's about you!

How you use the database and the ideas you have to improve it.

Let's explore a few of these ideas and how they ended up getting released.

Returning a single field from `SELECT` statements

Brian (Du-z) joined our community last September and has made several great contributions, including the one you’re reading about.

He noticed that the query SELECT name FROM user would result in something like the following.

[
  {
    "Name": "Name 1"
  },
  {
   "Name": "Name 2"
  }
]

However, he felt it would be far easier to deserialize and work with a simple string array.

[
  "Name 1",
  "Name 2"
]

He gave this very helpful suggestion for a solution based on CosmosDB's FIELD keyword:

SELECT VALUE name FROM user

After some discussion, this was implemented by our team, and now you can SELECT VALUE name FROM user, thanks to Brian!

Custom functions with `DEFINE FUNCTION` statements

Sebastian (mathe42) has been a very active community member since last September, with around 20 pull requests and many other great contributions like this one.

For his use case, he was looking for some sort of user-defined functions that would allow us to have a callable list of SurrealQL instructions with arguments etc.

He came up with this example of how it could be done:

-- Define a procedure to get a person

DEFINE PROCEDURE get_person ($firstname, $lastname, $birthdate) {
  LET $person = SELECT * FROM person WHERE firstname = $firstname AND lastname = $lastname AND birthdate = $birthdate;

  IF (COUNT($person)==1) THEN
     RETURN 1;
  ELSE
    RETURN CREATE person {
       firstname: $firstname,
       lastname: $lastname,
       birthdate: $birthdate
    }
  END
}

-- and call it by
LET $myPerson = CALL get_person('Sebastian', 'Krüger', '2022-09-21')

After some discussion and some magic from Tobie, you can now define global database-wide custom functions!

-- Define a global function which can be used in any query
DEFINE FUNCTION fn::get_person($first: string, $last: string, $birthday: string) {

    LET $person = SELECT * FROM person WHERE [first, last, birthday] = [$first, $last, $birthday];

    RETURN IF $person[0].id THEN
        $person[0];
    ELSE
        CREATE person SET first = $first, last = $last, birthday = $birthday;
    END;

};

-- Call the global custom function, receiving the returned result
LET $person = fn::get_person('Tobie', 'Morgan Hitchcock', '2022-09-21');

This allows complicated or repeated user-defined code to run seamlessly within any query across the database.

Custom functions support typed arguments and multiple nested queries with custom logic.

All this started with Sebastian’s idea for what he wanted in the database, thanks Sebastian!

Code blocks and advanced expressions

Tom (tomsseisums) also joined us on our journey in September and has been sharing some great insights into what can be done better, such as this use case.

He was trying to fit his project into SurrealDB from the perspective of "SurrealDB as a Backend", where with the power of SurrealDB, we could handle all data operations in SurrealDB without the need for any intermediate layer.

He tried to set up events and fields with rich expressions in CREATE / UPDATE but was faced with parse errors.

This is one of several things Tom was trying to do:

CREATE metrics SET average_sales = (
    LET $sales = (SELECT quantity FROM sales);
    LET $total = math::sum($sales);
    LET $count = count($sales);

    RETURN ($total / $count);
);

However he was disappointed when he received an unexpected parse error.

{
    "code": 400,
    "details": "Request problems detected",
    "description": "There is a problem with your request. Refer to the documentation for further information.",
    "information": "There was a problem with the database: Parse error on line 1 at character 15 when parsing 'SET average_sales = (\n    LET $sales = (SELECT quantity FROM sales);\n    LET $total = math::sum($sal'"
}

That didn’t stop him from wanting this to exist, and therefore he created a feature request for it.

After some discussion and some more magic, it is now possible, thanks to Tom!

DEFINE FIELD average_sales ON metrics VALUE {
    LET $sales = (SELECT VALUE quantity FROM sales);
    LET $total = math::sum($sales);
    LET $count = count($sales);
    RETURN ($total / $count);
};

You can now run blocks of code with support for an arbitrary number of statements, including LET and RETURN!

This allows for writing advanced custom logic and allows for more complicated handling of data operations.

Thank you, everyone, for being a part of this ambitious journey with us!

We only scratched the surface of what was released, and you can find more here.

We are always happy to work on things together on GitHub and our Discord server.

We’re looking forward to seeing what ideas and improvements you have in mind next!

Until then we’ll be sharing more deep-dive blog posts about some of the new features coming out.