Schema creation best practices

With SurrealDB, you can create a schema that is as simple or as complex as you need it to be. This page contains a number of best practices for creating schemas that are both easy to understand and easy to maintain.

Define arrays and sets with a type and maximum size

In addition to a type, both arrays and sets can have a required 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 small_bytes field is between 0 and 255.

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

Learn more about database functions.

Define individual indexes of an array

Even the individual indexes of an array can be defined. This is useful for data types like RGB colours that must be exactly three items in length.

DEFINE FIELD rgb ON colour TYPE <array, 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];
-- Fails: must have three items
CREATE colour SET rgb = [0, 2, 30];
-- Fails: must be between 0 and 255
CREATE colour SET rgb = [0, 2, 400];

Learn more about assertions in DEFINE FIELD

How to work with objects inside `SCHEMALESS` and `SCHEMAFULL` tables

The behaviour of an object as a field of a table depends on whether the table is SCHEMALESS (the default) or SCHEMAFULL.

Inside a schemaless table, the only time an object will be schemafull is if the structure of the object is indicated using the literal syntax.

-- This table is schemaless
DEFINE TABLE some_table;

-- So an object defined on it will be schemaless
DEFINE FIELD some_object ON some_table TYPE object;
-- Same for an array of objects
DEFINE FIELD some_objects ON some_table TYPE array<object>;

-- But this is schemafull because it has a set structure
DEFINE FIELD some_specific_objects ON some_table TYPE array<{ a: string }>;

Inside a schemafull table, the opposite is the case in that a field defined as an object will be schemafull. To override this, the FLEXIBLE keyword can be used.

DEFINE TABLE some_table SCHEMAFULL;

-- Schemafull object and array of objects
DEFINE FIELD some_object ON some_table TYPE object;
DEFINE FIELD some_objects ON some_table TYPE array<object>;

-- Schemaless thanks to the `FLEXIBLE` keyword
DEFINE FIELD flexible_object ON some_table TYPE object FLEXIBLE;
DEFINE FIELD flexible_objects ON some_table TYPE array<object> FLEXIBLE;

Inside a schemafull table, any input value that does not match the defined schema will cause an error.

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"
};
-- "Found field 'metadata.wrong_field', but no such field exists for table 'user'"

If you have data that includes a non-defined field in such a table, you can use the destructuring operator to access the current structure and only pass on the necessary fields for the operation.

-- This object has too much info and capitalization doesn't match
LET $chaotic_content = {
    name: "Billy",
    unneeded_number: 10,
    metadata: {
        CREATED_AT: time::now(),
        age: 5,
        wrong_field: "WRONG DATA"
    }
};

-- Pass on the needed fields and rename CREATED_AT to lowercase
CREATE user CONTENT $chaotic_content.{
    name,
    metadata.{
        created_at: CREATED_AT,
        age
    }
};

Defining a specific function to return the expected structure can be a nice convenience in this case.

DEFINE FUNCTION fn::filter_for_user($obj: object) {
    $obj.{ name, metadata.{ created_at: CREATED_AT, age }};
};

LET $chaotic_content = {
    name: "Billy",
    unneeded_number: 10,
    metadata: {
        CREATED_AT: time::now(),
        age: 5,
        wrong_field: "WRONG DATA"
    }
};

CREATE user CONTENT fn::filter_for_user($chaotic_content);

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'

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: '2026-02-12'
	}
]

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 = {};

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
    }
};

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 {
            $input
        } ELSE {
            THROW "Input must be one of " + <string>$ERROR_CODES;
        }
    } ELSE {
        $input
    }
};

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

Response

-------- Query --------
500

-------- Query --------
'An error occurred: Input must be one of [200, 300, 400, 500]'

-------- Query --------
{
	message: "You can't do that",
	type: 'internal_error'
}

-------- Query --------
"Expected `{ message: string, type: 'internal_error' } | { message: string, type: 'bad_request' } | { expected: 'YYYY-MM-DD', got: any, type: 'invalid_date' } | int` but found `a:wrong_argument`"

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;

-- Is first_generation, doesn't need to indicate parents
CREATE person:one SET first_generation = true;

-- Error: 
-- 'Found NONE for field `parents`, with record `person:two`,
-- but field must conform to: <-parent_of<-person OR first_generation'
CREATE person:two;

-- Give person:two a parent
RELATE person:one->parent_of->person:two;
-- CREATE now works 
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.