# Schema best practices

Best practices for creating schemas in SurrealDB.

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.

When those definitions live in source control and need to roll out safely to shared databases, follow [SurrealKit schema migration](/docs/manage/schema-migration.md) in the **Manage** section for sync, rollouts, testing in CI, and related tooling.

### 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()`](/docs/reference/query-language/functions/database-functions/array.md#arrayall) function to also ensure that every item in the `small_bytes` field is between 0 and 255.

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

Learn more about [database functions](/docs/reference/query-language/functions/database-functions.md).

### 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.

```surql
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`](/docs/reference/query-language/statements/define/field.md#assertions)

### 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](/docs/reference/query-language/language-primitives/data-types/literals.md) syntax.

```surql
-- 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, a field typed as `object` or `array<object>` (or any other definition that contains an `object`) is schemafull by default. Add the `FLEXIBLE` field clause after `TYPE` to allow arbitrary keys on every object in that field's type.

```surql
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>;

-- Field clause makes objects inside these fields schemaless
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.

```surql
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.

```surql
-- 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.

```surql
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.

```surql
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.

```surql
"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 customise the logic, including a custom error message. Let's give this a try.

```surql
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!

```surql
'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()`](/docs/reference/query-language/language-primitives/formatters.md) 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.

```surql
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;
```

```surql title="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.

```surql
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](/docs/reference/query-language/statements/define/param.md).

```surql
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.

```surql
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](/docs/reference/query-language/language-primitives/data-types/literals.md) type allows you to specify a list of all possible forms it could take, making it the perfect type for error logic.

```surql
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);
```

```surql title="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`.

```surql
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`](/docs/reference/query-language/statements/define/table.md#using-enforced-to-ensure-that-related-records-exist) clause to a `DEFINE TABLE table_name TYPE RECORD` definition.