SurrealDB - Improve data integrity by adding schema information

In the concluding chapter of this series, we'll delve into the intriguing world of SurrealDB's schema possibilities and explore how they can elevate our data integrity to new heights.

You can define fields for tables. We will take the user table as an example here.

Our initial definition was:

-- Define the user table
DEFINE TABLE user SCHEMALESS;

We will add now some field definitions:

-- Define the user table
DEFINE TABLE user SCHEMALESS;
DEFINE FIELD name ON TABLE user TYPE string;
DEFINE FIELD email ON TABLE user TYPE string;
DEFINE FIELD firstName ON TABLE user TYPE string;
DEFINE FIELD lastName ON TABLE user TYPE string;
DEFINE FIELD createdAt ON TABLE user TYPE datetime;

We're on the right track so far. We've defined the necessary fields, and we've also specified their types.
But there are some improvements, we can make.

Now, let's focus on the createdAt attribute. In this case, we should establish a default value that automatically captures the creation timestamp when the entity is generated.

DEFINE FIELD createdAt ON TABLE user TYPE datetime DEFAULT time::now();

We should also enhance the email field.
There are two improvements we can make.
First, we should specify the field type as more than just a generic string, as we know it must adhere to the email pattern.

DEFINE FIELD email ON TABLE user TYPE string ASSERT string::is::email($value);

Secondly, we want to ensure, that the email is globally unique. To ensure this, we define a unique index.

DEFINE INDEX uniqueEmailIndex ON TABLE user COLUMNS email UNIQUE;

Here's our ultimate definition for our user table:

-- Define the user table
DEFINE TABLE user SCHEMALESS;
DEFINE FIELD name ON TABLE user TYPE string;
DEFINE FIELD email ON TABLE user TYPE string ASSERT string::is::email($value);
DEFINE FIELD firstName ON TABLE user TYPE string;
DEFINE FIELD lastName ON TABLE user TYPE string;
DEFINE FIELD createdAt ON TABLE user TYPE datetime DEFAULT time::now();

DEFINE INDEX uniqueEmailIndex ON TABLE user COLUMNS email UNIQUE;

Concerning the relationships we've established between various entities, it's important to avoid duplicates between the same entities.
In this context, we can create a unique index to prevent multiple connections of a specific user to a particular tenant.

-- prevent linking a user to the same tenant multiple times
DEFINE INDEX tenant_memberIndex
    ON TABLE tenant_member
    COLUMNS in, out UNIQUE;

Final thoughts

As we conclude this series, we've only just scratched the surface of SurrealDB's capabilities, with a strong focus on its graph structures.

SurrealDB has an exciting set of features and upcoming announcements that make it a truly fascinating unicorn in the database world.

The handling of unique record-IDs and the possibilities they unlock are nothing short of amazing.
The freedom to choose between a relational database, key-value store, NoSQL/document database, graph database, or specialized time series database while using one database simplifies life significantly.
Tailoring how you store and access data based on your specific needs and mixing things up as required is the way forward.

While SurrealDB is still in its early stages and not officially production-ready, I encourage you to give it a try in one of your upcoming small (or side) projects.

The adventure with SurrealDB may just be the unique and rewarding experience you've been looking for.