DEFINE FIELD

The DEFINE FIELD statement allows you to instantiate a named field on a table, enabling you to set the field's data type, set a default value, apply assertions to protect data consistency, and set permissions specifying what operations can be performed on the field.

Requirements

You must be authenticated as a root owner or editor, namespace owner or editor, or database owner or editor before you can use the DEFINE FIELD statement.
You must select your namespace and database before you can use the DEFINE FIELD statement.

Statement syntax

SurrealQL Syntax

DEFINE FIELD [ OVERWRITE | IF NOT EXISTS ] @name ON [ TABLE ] @table
	[ TYPE @type [ FLEXIBLE ] ]
	[ INLINE ]
	[ REFERENCE 
		[ ON DELETE REJECT | 
			ON DELETE CASCADE | 
			ON DELETE IGNORE |
			ON DELETE UNSET | 
			ON DELETE THEN @expression ]
		[ MATERIALIZE INTO @table.@field [ WHERE @condition ] ]
	]
	[ DEFAULT [ALWAYS] @expression ]
  [ READONLY ]
	[ VALUE @expression ]
	[ ASSERT @expression ]
	[ PERMISSIONS [ NONE | FULL
		| FOR select @expression
		| FOR create @expression
		| FOR update @expression
	] ]
  [ COMMENT @string ]
  [ MIGRATE EXISTING ROWS AS @name USING @expression [ FALLBACK @value ] ]
  [ GRAPHQL_ALIAS @string ]
  [ GRAPHQL_DEPRECATED @string ]

Example usage

The following expression shows the simplest way to use the DEFINE FIELD statement.

-- Declare the name of a field.
DEFINE FIELD email ON TABLE user;

The fields of an object and the items in an array can be defined individually using the . operator for objects, or the indexing operator for arrays.

-- Define nested object property types, after the object itself
DEFINE FIELD emails ON TABLE user TYPE object;
DEFINE FIELD emails.address ON TABLE user TYPE string;
DEFINE FIELD emails.primary ON TABLE user TYPE bool;

-- Define individual fields on an array, after the array itself
DEFINE FIELD metadata ON person TYPE array;
DEFINE FIELD metadata[0] ON person TYPE datetime;
DEFINE FIELD metadata[1] ON person TYPE int;

Non-unicode fields can be defined and set using backticks where necessary. Be sure that any periods to indicate nested fields are not inside the backticks, as anything enclosed in backticks will be treated as a literal string.

DEFINE FIELD name          ON user TYPE object;
DEFINE FIELD name.first    ON user TYPE string;
DEFINE FIELD `nómine`      ON user TYPE object;
DEFINE FIELD `nómine`.prim ON user TYPE string;
DEFINE FIELD `nómine.prim` ON user TYPE string;

CREATE user:one SET 
	-- Nested field
    name.first = "Billy",
	-- Also nested
    `nómine`.prim = "Billy",
	-- Not nested
    `nómine.prim` = "Billy";

As the output shows, the . enclosed inside backticks in the last field results in a single non-nested field name that includes the period, while the one immediately preceding it is nested.

[
	{
		id: user:one,
		name: {
			first: 'Billy'
		},
		"nómine": {
			prim: 'Billy'
		},
		"nómine.prim": 'Billy'
	}
]

Defining data types

The DEFINE FIELD statement allows you to set the data type of a field. For a full list of supported data types, see Data types.

From SurrealDB 4.0, a nested field can only be defined inside a field that is already defined and can hold fields, such as an object. Define the parent first, as in DEFINE FIELD address ON person TYPE object; before DEFINE FIELD address.city ON person TYPE string;. Otherwise the statement fails with an error such as Cannot define field `address.city`: the field `address` it is nested in is not defined. Define `address` first, or, for a parent of another type such as int, any or no type, only an object holds a field. Nested definitions that a datastore already holds from an earlier version are kept.

When defining nested fields, if both the parent and the nested fields have types defined, those types must agree. Mismatching types are rejected to prevent impossible schema states.

For example, the following will fail:

DEFINE FIELD OVERWRITE fd ON c TYPE { a: string, b: number };
DEFINE FIELD OVERWRITE fd.* ON c TYPE number;

The above will fail with the following error:

'Cannot set field `fd.*` with type `number` as it mismatched with field `fd` with type `{ a: string, b: number }`'

Simple data types

-- Set a field to have the string data type
DEFINE FIELD email ON TABLE user TYPE string;

-- Set a field to have the datetime data type
DEFINE FIELD created ON TABLE user TYPE datetime;

-- Set a field to have the bool data type
DEFINE FIELD locked ON TABLE user TYPE bool;

-- Set a field to have the number data type
DEFINE FIELD login_attempts ON TABLE user TYPE number;

A | vertical bar can be used to allow a field to be one of a set of types. The following example shows a field that can be a UUID or an int, perhaps for user records that have varying data due to two different legacy ID types.

-- Set a field to have either the uuid or int type
DEFINE FIELD user_id ON TABLE user TYPE uuid|int;

Sized number types

Available since: v4.0.0

A field can hold a number of a fixed width: i8, u8, i16, u16, i32, u32 and u64 for integers, and f16 and f32 for floating-point numbers. i64 and f64 are other names for int and float, and INFO FOR TABLE shows them as int and float. A value written to the field is converted to its type, and keeps that type when it is read back. A literal can name its type with a suffix, such as 255u8 or 1.5f16.

DEFINE TABLE reading SCHEMAFULL;
DEFINE FIELD sensor ON reading TYPE u16;
DEFINE FIELD level ON reading TYPE f32;

CREATE reading:1 SET sensor = 65535, level = 1.25;
SELECT sensor, type::of(sensor) AS kind FROM reading:1;

Output

[{ kind: 'u16', sensor: 65535u16 }]

A value is converted only when it fits the type exactly. An integer outside the range of the type, or a number with a fractional part for an integer type, is refused. A whole number written as a float or a decimal, such as 20.0, is accepted by an integer type, and an f16 or f32 field rounds a float to the nearest value it can hold, unless the value is too large for it.

CREATE reading:2 SET sensor = 65536, level = 1;

Error output

"Couldn't coerce value for field `sensor` of `reading:2`: Expected `u16` but found `65536`"

Array type

You can also set a field to have the array data type. The array data type can be used to store a list of values. You can also set the data type of the array's contents, as well as the required number of items that it must hold.

-- Set a field to have the array data type
DEFINE FIELD roles ON TABLE user TYPE array<string>;

-- Set a field to have the array data type, equivalent to `array<any>`
DEFINE FIELD posts ON TABLE user TYPE array;

-- Set a field to have the array object data type
DEFINE FIELD emails ON TABLE user TYPE array<object>;

-- Set a field that holds exactly 640 bytes
DEFINE FIELD bytes ON TABLE data TYPE array<int, 640> ASSERT $value.all(|$val| $val IN 0..=255);

-- Field for a block in a game showing the possible distinct directions a character can move next.
-- The array can contain no more than four directions
DEFINE FIELD next_paths ON TABLE block 
  TYPE array<"north" | "east" | "south" | "west"> 
  VALUE $value.distinct() 
  ASSERT $value.len() <= 4;

Making a field optional

You can make a field optional by wrapping the inner type in an option, which allows you to store NONE values in the field.

What other databases call a nullable column can mean either of two values in SurrealDB. NONE means the field is absent, and NULL means the field is present with an empty value. Choose the type by which of the two a field can hold:

Type	Field left out (`NONE`)	Field set to `NULL`
`option<string>`	Accepted	Refused
`string \\| null`	Refused	Accepted
`option<string \\| null>`	Accepted	Accepted

A value that the type does not accept is refused with an error such as Couldn't coerce value for field `bio` of `user:one`: Expected `none | string` but found `NULL` .

-- A user may enter a biography, but it is not required.
-- By using the option type you also allow for NONE values.
DEFINE FIELD biography ON TABLE user TYPE option<string>;

The example below shows how to define a field user on a POST table. The field is of type record. This means that the field can store a record<user> or NONE.

DEFINE FIELD user ON TABLE post TYPE option<record<user>>;

Flexible data types

On a SCHEMAFULL table, every object is schemafull by default, meaning that only fields you define with nested DEFINE FIELD statements are allowed. The FLEXIBLE field clause relaxes that rule for a single field. It must appear immediately after TYPE, and it applies to every object reachable in that field's type - including objects inside array<object>, option<object>, union arms, and object literals.

FLEXIBLE is not part of the type expression. Writing TYPE object FLEXIBLE declares a field of type object, then sets the field's flexible flag. The same applies to TYPE array<object> FLEXIBLE: the stored type is array<object>, and each object element accepts arbitrary keys.

The field's type must contain at least one object. FLEXIBLE cannot be used with types such as any or number that do not include object.

DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD name ON TABLE user TYPE string;
DEFINE FIELD metadata ON TABLE user TYPE object FLEXIBLE;
DEFINE FIELD metadata.user_id ON TABLE user TYPE int;

You can still define nested fields such as metadata.user_id. Defined subfields keep their types and assertions; FLEXIBLE only allows additional keys that are not declared in the schema.

Taking the following CREATE statement:

CREATE ONLY user SET
  name = "User1",
  metadata = {
      user_id: 8876687,
      country_code: "ee",
      time_zone: "EEST",
      age: 25
};

Without FLEXIBLE, the metadata field is a schemafull object and only declared subfields such as metadata.user_id are accepted.

In versions of SurrealDB before 3.0, the result of the above statement was a record in which the metadata field was only able to populate the user_id field.

{
	id: user:ke8w4u38gbm3ofp2u8fb,
	metadata: {
		user_id: 8876687
	},
  name: "User1"
}

As of version 3.0, the statement now returns an error upon finding the first field that was not defined in the schema.

"Found field 'metadata.age', but no such field exists for table 'user'"

With FLEXIBLE, the field accepts any extra keys on metadata while still requiring name and a valid metadata.user_id.

Output

{
	id: user:lsdk473e279oik1k484b,
	metadata: {
		age: 25,
		country_code: 'ee',
		time_zone: 'EEST',
		user_id: 8876687
	},
	name: 'User1'
}

The same field clause works when objects are nested inside other types. For example, TYPE array<object> FLEXIBLE makes every object in the array schemaless, while nested DEFINE FIELD paths such as items.*.num still type-check declared subfields:

DEFINE TABLE test SCHEMAFULL;
DEFINE FIELD items ON test TYPE array<object> FLEXIBLE;
DEFINE FIELD items.*.num ON test TYPE number;

CREATE test:1 SET items = [{ num: 1 }];
CREATE test:2 SET items = [{ num: 2, extra: 'allowed' }];
-- Fails: 'extra' is allowed but 'num' must be a number, not a string
CREATE test:3 SET items = [{ num: '3', extra: 'allowed' }];

Using the `DEFAULT` clause to set a default value

You can set a default value for a field using the DEFAULT clause. The default value will be used if no value is provided for the field.

-- A user is not locked by default.
DEFINE FIELD locked ON TABLE user TYPE bool
-- Set a default value if empty
  DEFAULT false;

Using the `DEFAULT` and `ALWAYS` clauses

DEFAULT ALWAYS applies a default on CREATE and on UPDATE when the value is NONE. The ALWAYS keyword distinguishes this from plain DEFAULT, which only runs on CREATE.

DEFINE TABLE product SCHEMAFULL;
-- Set a default value of 123.456 for the primary field
DEFINE FIELD primary ON product TYPE number DEFAULT ALWAYS 123.456;

With the above definition, the primary field will be set to 123.456 when a new product is created without a value for the primary field or with a value of NONE, and when an existing product is updated if the value is specified the result will be the new value.

In the case of NULL or a mismatching type, an error will be returned.

-- This will return an error
CREATE product:test SET primary = NULL;

-- result 
"Couldn't coerce value for field `primary` of `product:test`: Expected `number` but found `NULL`"

On the other hand, if a valid number is provided during creation or update, that number will be used instead of the default value. In this case, 123.456.

-- This will set the value of the `primary` field to `123.456`
CREATE product:test;

-- This will set the value of the `primary` field to `463.456`
UPSERT product:test SET primary = 463.456;

-- This will set the value of the `primary` field to `123.456`
UPSERT product:test SET primary = NONE;

Query

DEFINE TABLE post SCHEMAFULL;
DEFINE FIELD tags ON post TYPE array<object> DEFAULT ALWAYS [];
DEFINE FIELD tags.*.color ON post TYPE string DEFAULT ALWAYS 'red';
DEFINE FIELD tags.*.name ON post TYPE string;
--
CREATE post:test;
UPSERT post:test SET tags += { name: 'test' };
UPSERT post:test SET tags += { name: 'test', color: 'blue' };

Output

[{ id: post:test, tags: [] }]

[{ id: post:test, tags: [{ color: 'red', name: 'test' }] }]

[{ id: post:test, tags: [{ color: 'red', name: 'test' }, { color: 'blue', name: 'test' }] }]

Using the `VALUE` clause to set a field's value

The VALUE clause differs from DEFAULT in that a default value is calculated if no other is indicated, otherwise accepting the value given in a query.

DEFINE FIELD updated ON TABLE user DEFAULT time::now();

-- Set `updated` to the year 1900
CREATE user SET updated = d"1900-01-01";
-- Then set to the year 1910
UPDATE user SET updated = d"1910-01-01";

A VALUE clause, on the other hand, will ignore attempts to set the field to any other value.

DEFINE FIELD updated ON TABLE user VALUE time::now();

-- Ignores 1900 date, sets `updated` to current time
CREATE user SET updated = d"1900-01-01";
-- Ignores again, updates to current time
UPDATE user SET updated = d"1900-01-01";

As the example above shows, a VALUE clause sets the value every time a record is modified (created or updated). However, the value will not be recalculated in a SELECT statement, which simply accesses the current set value.

DEFINE FIELD updated ON TABLE user VALUE time::now();

CREATE user:one;
SELECT * FROM ONLY user:one;
-- Sleep for one second
SLEEP 1s;
-- `updated` is still the same
SELECT * FROM ONLY user:one;

To create a field that is calculated each time it is accessed, a computed field can be used.

DEFINE FIELD accessed_at ON TABLE user COMPUTED time::now();

CREATE user:one;
SELECT * FROM ONLY user:one;
-- Sleep for one second
SLEEP 1s;
-- `accessed_at` is a different value now
SELECT * FROM ONLY user:one;

Altering a passed value

You can alter a passed value using the VALUE clause. This is useful for altering the value of a field before it is stored in the database.

In the example below, the VALUE clause is used to ensure that the email address is always stored in lowercase characters by using the string::lowercase function.

-- Ensure that an email address is always stored in lowercase characters
DEFINE FIELD email ON TABLE user TYPE string
  VALUE string::lowercase($value);

A VALUE clause runs on every write to the record, not only on writes that set the field. When a statement does not set the field, $value is the value already stored, so the clause runs on its own output. Lowercasing a lowercase string changes nothing, but a transformation that changes its input each time does. VALUE crypto::argon2::generate($value) hashes the password on CREATE, then hashes the stored hash on the next UPDATE of any other field, and the password no longer matches.

For such a transformation, compare $value with $before, which is the field's value before the statement, and only transform a value that has changed:

DEFINE FIELD pass ON TABLE user TYPE string
  VALUE IF $value != $before { crypto::argon2::generate($value) } ELSE { $value };

With this definition, UPDATE user:one SET name = 'Ann' leaves the hash as it is, and UPDATE user:one SET pass = 'new-password' stores a hash of the new password. Hashing the password in the statement that sets it, as the record access examples do with crypto::argon2::generate($pass) in SIGNUP, avoids the question.

Adding a field to a table with existing records

Defining a field does not change the records already in the table. The clauses apply the next time each record is written:

VALUE runs on the record's next write, including an UPDATE that sets no fields.
DEFAULT only applies when a record is created, so existing records never receive it. DEFAULT ALWAYS also applies on an UPDATE when the field is NONE.
ASSERT and TYPE check each record's next write, so an existing record that does not conform can make its next UPDATE fail.

To bring existing records in line straight away, update them after defining the field:

CREATE item:1 SET name = "a";

DEFINE FIELD upper ON item VALUE string::uppercase(name);
DEFINE FIELD status ON item DEFAULT "new";

-- Runs VALUE on every record
UPDATE item;
-- Sets the default on records that do not have the field
UPDATE item SET status = "new" WHERE status IS NONE;

Output

[
	{
		id: item:1,
		name: 'a',
		status: 'new',
		upper: 'A'
	}
]

A computed field needs no update, because it is calculated when the record is read.

On a SCHEMAFULL table in a database at compatibility level 4, a new field whose type does not accept NONE, such as TYPE int, is refused while the table holds records, because none of them has a value for it yet. A DEFAULT does not change this, since it only applies to records created later. Either define the field as option<int>, or give the existing records a value as described in Migrating existing records.

Migrating existing records

Available since: v4.0.0

A field's definition describes the values its records hold, but changing the definition does not rewrite records already stored. A MIGRATE EXISTING ROWS clause on DEFINE FIELD or ALTER FIELD says what those records become:

SurrealQL Syntax

MIGRATE EXISTING ROWS AS @name USING @expression [ FALLBACK @value ]

AS @name names the migration, so that it runs once however many times the statement runs. See Running a schema script again.
USING @expression computes each record's new value from $value, its old value. The expression can call built-in functions, but not a custom function, a model, a script or an extension, and it cannot hold a statement, which includes a SELECT and an IF block. To choose a value by case, look it up in an object instead, as in { published: 'live' }[$value] ?? $value.
FALLBACK @value is a constant to store when the expression fails or its result does not fit the new definition. It may be left out only when SurrealDB can prove that the expression never fails and always produces a value that fits, which a cast such as <string> $value does not yet satisfy, so include a FALLBACK unless the statement is accepted without one.

-- In a database at compatibility level 4
DEFINE TABLE product SCHEMAFULL;
DEFINE FIELD stock ON product TYPE string;
CREATE product:1 SET stock = '7';
CREATE product:2 SET stock = 'unknown';

DEFINE FIELD OVERWRITE stock ON product TYPE int
    MIGRATE EXISTING ROWS AS stock_to_int USING <int> $value FALLBACK 0;

SELECT * FROM product;

Output

[
	{ id: product:1, stock: 7 },
	{ id: product:2, stock: 0 }
]

A migration takes effect when the statement commits: a record is converted the first time it is read, and a background job rewrites every record of the table. The same clause adds a required field to a table that already has records, with the expression giving each record its value:

DEFINE FIELD reorder_level ON product TYPE int
    MIGRATE EXISTING ROWS AS add_reorder_level USING 10;

To remove the fields that the table no longer defines from every record, use REMOVE UNDECLARED FIELDS on ALTER TABLE:

ALTER TABLE product MIGRATE EXISTING ROWS AS drop_old_fields REMOVE UNDECLARED FIELDS;

A migration cannot change a field that something else is derived from: a field an index reads, a field of a view or of a view's source table, a field with REFERENCE, INLINE or COMPUTED, and id, in and out. For a field an index reads, remove the index, migrate the field, then define the index again.

INFO FOR TABLE lists the migrations a table has run under migrations, and names the fields whose stored values are all known to fit their definition under guaranteed.

Running a schema script again

A schema script is often run more than once: on every deploy, by an application that applies its schema when it starts, or through surreal db import. DEFINE FIELD OVERWRITE gives the same result each time, but a transform applied twice usually does not. A table therefore records the name of every migration it has run, and skips a migration whose name it has already run.

DEFINE TABLE item SCHEMAFULL;
DEFINE FIELD price ON item TYPE int;
CREATE item:1 SET price = 5;

-- Prices move from whole units to cents
DEFINE FIELD OVERWRITE price ON item TYPE int
    MIGRATE EXISTING ROWS AS price_to_cents USING $value * 100 FALLBACK 0;

-- Running the same statement again leaves the prices as they are
DEFINE FIELD OVERWRITE price ON item TYPE int
    MIGRATE EXISTING ROWS AS price_to_cents USING $value * 100 FALLBACK 0;

SELECT price FROM item;

Output

[{ price: 500 }]

Without the name, the second statement would multiply the prices again, to 50000. Running a name that the table has already run with a different transform is refused, because the name no longer says which transform the records went through. Choose a new name for each new migration, in the same way as the migration IDs of other schema tools.

The names stay with the table and appear under migrations in INFO FOR TABLE, so they also record which conversions the table's records have been through. This is also why ALTER TABLE ... REMOVE UNDECLARED FIELDS takes a name, although running it twice would do no harm. A migration cannot be undone: once it has run, the old values are gone.

When a migration is required

A database has a compatibility level, 3 or 4. In a database at level 4, a SCHEMAFULL table that holds records refuses a change to a field unless every value the old definition accepted also fits the new one, or the statement carries MIGRATE EXISTING ROWS. Widening a type, such as option<int> to option<int | string>, needs no migration. Narrowing it, or changing it to another type, does:

-- In a database at compatibility level 4
DEFINE TABLE person SCHEMAFULL;
DEFINE FIELD age ON person TYPE int;
CREATE person:1 SET age = 37;

DEFINE FIELD OVERWRITE age ON person TYPE string;

Error output

'Cannot change field `age` of table `person`: the table is SCHEMAFULL in a database at compatibility level 4 and has rows, and the new definition does not accept every value the old one did: add MIGRATE EXISTING ROWS'

Adding a field counts as a change from no definition, under which every existing record holds NONE. A new field with a type that does not accept NONE therefore needs a migration that gives those records a value:

DEFINE FIELD stock ON item TYPE int
    MIGRATE EXISTING ROWS AS add_stock USING 0;

A field defined as option<int> needs no migration, and records can be given a value later. Changing it to TYPE int afterwards is still a narrowing, though, and needs MIGRATE EXISTING ROWS even when every record already holds an integer, because the check compares the two definitions and not the stored values.

At level 4, REMOVE FIELD on a SCHEMAFULL table also removes the field's stored values, so a field defined again later with the same name starts empty.

At level 3, a table accepts any change to its fields, as in SurrealDB 3.x, and records written before the change keep the values they held. MIGRATE EXISTING ROWS works at both levels, and a SCHEMALESS table accepts any change at either level.

Computed fields

Available since: v3.0.0

A COMPUTED field is not stored. Its value is calculated from its expression every time the field is read, so it always reflects the rest of the record as it is now:

DEFINE FIELD can_drive ON person COMPUTED age >= 18;

CREATE person:one SET age = 17;
UPDATE person:one SET age = 18;
SELECT age, can_drive FROM person:one;

Output

[
	{
		age: 18,
		can_drive: true
	}
]

Compared with a VALUE clause, which is calculated when a record is written and then stored, a computed field needs no update to stay current and takes up no storage, but it is calculated again on every read.

A computed field takes only the TYPE, PERMISSIONS and COMMENT clauses, as the computed field syntax above shows. It must be a top-level field, so it cannot be defined on the id field or on a nested path such as metadata.can_drive, and a statement that sets it is ignored, because the value always comes from the expression.

The body of a computed field has further rules about what it may read and write, described in Restrictions on computed fields.

Note

Before SurrealDB 3.0.0, computed values were written with a data type called a future. See the page on futures for those versions.

Defining a literal on a field

A field can also be defined as a literal type, by specifying one or more possible values and/or permitted types.

DEFINE FIELD coffee
  ON TABLE order TYPE "regular" | "large" | { special_order: string };

CREATE order:good SET coffee = { special_order: "Venti Quadruple Ristretto Half-Decaf Soy Latte with 4 pumps of sugar-free vanilla syrup" };
CREATE order:bad SET coffee = "small";

Output

-------- Query --------

[
	{
		coffee: {
			special_order: 'Venti Quadruple Ristretto Half-Decaf Soy Latte with 4 pumps of sugar-free vanilla syrup'
		},
		id: order:good
	}
]

-------- Query --------
"Found 'small' for field `coffee`, with record `order:bad`, but expected a 'regular' | 'large' | { special_order: string }"

One more example of a literal containing settings for a full text search filter:

DEFINE FIELD filter ON TABLE search_settings TYPE
      "None"
    | { type: "Ascii" }
    | { type: "EdgeNgram", from: int, to: int }
    | { type: "Lowercase" }
    | { type: "Ngram", from: int, to: int }
    | { type: "Snowball", language: string }
    | { type: "Uppercase" };

Comments

A COMMENT documents how the field is meant to be used. Comments can be useful when describing how someone (or an agent) would write a query: comparison rules, units, allowed values, or invariants. The comment is stored with the definition and shown by INFO. See also Comments on definitions.

DEFINE FIELD time ON meeting TYPE string
	COMMENT "Meeting date as an ISO string, 'YYYY-MM-DD'. It is a string, not a datetime: compare and sort lexicographically (time >= '2026-07-10').";

DEFINE FIELD is_organizer ON attended TYPE bool
	COMMENT "True if this person ran the meeting, false if they only attended. Exactly one attendee per meeting has it true.";

DEFINE FIELD score ON review TYPE float
	COMMENT "Rating from 0.0 to 5.0 inclusive. Always assert score >= 0 AND score <= 5.";

Asserting rules on fields

You can take your field definitions even further by using asserts. Assert can be used to ensure that your data remains consistent. For example you can use asserts to ensure that a field is always a valid email address, or that a number is always positive.

-- Give the user table an email field. Store it in a string
DEFINE FIELD email ON TABLE user TYPE string
  -- Check if the value is a properly formatted email address
  ASSERT string::is_email($value);

To limit the length of a string, compare its length in the assertion:

DEFINE FIELD username ON TABLE user TYPE string ASSERT $value.len() <= 16;

CREATE user:1 SET username = 'a_much_too_long_username';

Error output

"Found 'a_much_too_long_username' for field `username`, with record `user:1`, but field must conform to: $value.len() <= 16"

A field of type record<author> accepts the ID of a record that does not exist. To refuse one, assert that the record exists:

DEFINE FIELD author ON TABLE book TYPE record<author> ASSERT $value.exists();

CREATE author:ann;
CREATE book:1 SET author = author:ann;
CREATE book:2 SET author = author:nobody;

Error output

"Found author:nobody for field `author`, with record `book:2`, but field must conform to: $value.exists()"

The assertion is checked when the field is written, so deleting author:ann later leaves book:1 linking to a record that no longer exists. To prevent that as well, add REFERENCE ON DELETE REJECT to the field.

As the ASSERT clause expects an expression that returns a boolean, an assertion with a custom message can be manually created by returning true in one case and using a THROW clause otherwise.

DEFINE FIELD num ON data TYPE int ASSERT {
    IF $input % 2 = 0 {
        RETURN true
    } ELSE {
        THROW "Tried to make a " + <string>$this + " but `num` field requires an even number"
    }
};

CREATE data:one SET num = 11;

Error output

'An error occurred: Tried to make a { id: data:one, num: 11 } but `num` field requires an even number'

Making a field `READONLY`

The READONLY clause can be used to prevent any updates to a field. This is useful for fields that are automatically updated by the system. To make a field READONLY, add the READONLY clause to the DEFINE FIELD statement. As seen in the example below, the created field is set to READONLY.

DEFINE FIELD created ON resource VALUE time::now() READONLY;

Using regex to validate a string

You can use the ASSERT clause to apply a regular expression to a field to ensure that it matches a specific pattern. In the example below, the ASSERT clause is used to ensure that the countrycode field is always a valid ISO-3166 country code.

-- Specify a field on the user table
DEFINE FIELD countrycode ON user TYPE string
	-- Ensure country code is ISO-3166
	ASSERT $value = /[A-Z]{3}/
	-- Set a default value if empty
	VALUE $value OR $before OR 'GBR'
;

Array with allowed values

By using an Access Control List as an example we can show how we can restrict what values can be stored in an array. In this example we are using an array to store the permissions for a user on a resource. The permissions are restricted to a specific set of values.

-- An ACL can be applied to any kind of resource (record)
DEFINE FIELD resource ON TABLE acl TYPE record;
-- We associate the acl with a user using record<user>
DEFINE FIELD user ON TABLE acl TYPE record<user>;

-- The permissions for the user+resource will be stored in an array.
DEFINE FIELD permissions ON TABLE acl TYPE array
  -- The array must not be empty because at least one permission is required to make a valid ACL
  -- The items in the array must also be restricted to specific permissions
  ASSERT
      array::len($value) > 0
      AND $value ALLINSIDE ["create", "read", "write", "delete"];

-- SEE IT IN ACTION
-- 1: Add users
CREATE user:tobie SET firstName = 'Tobie', lastName = 'Hitchcock',
  email = 'Tobie.Hitchcock@surrealdb.com';
CREATE user:abc SET firstName = 'A', lastName = 'B',
  email = 'c@d.com';
CREATE user:efg SET firstName = 'E', lastName = 'F',
  email = 'g@h.com';

-- 2: Create a resource
CREATE document:SurrealDB_whitepaper SET
  name = "some messaging queue";

-- 3: Associate with ACL
CREATE acl SET user = user:tobie, resource = document:SurrealDB_whitepaper, permissions = ["create", "write", "read"];
CREATE acl SET user = user:abc, resource = document:SurrealDB_whitepaper, permissions = ["read", "delete"];

-- Test Asserts using failure examples
-- A: Create ACL without permissions field
CREATE acl:invalid SET
  user = user:efg,
  permissions = [], # FAIL - permissions must not be empty
  resource = document:SurrealDB_whitepaper;
-- B: Create acl with invalid permisson
CREATE acl:also_invalid SET
  user = user:efg,
  permissions = ["all"], # FAIL - This value is not allowed in the array
  resource = document:SurrealDB_whitepaper;

GraphQL names and deprecation

Available since: v3.1.0

GRAPHQL_ALIAS sets the name a field has in the GraphQL schema, in place of the field name. The alias is used on the object type, the create and update input types, and the filter. It must be a valid GraphQL name, matching [_A-Za-z][_0-9A-Za-z]*, and any other value is rejected when the field is defined.

GRAPHQL_DEPRECATED takes a reason, which the schema adds to the start of the field's description as [Deprecated: reason]. The isDeprecated flag in GraphQL introspection stays false.

DEFINE FIELD first_name ON person TYPE option<string> GRAPHQL_ALIAS "firstName" GRAPHQL_DEPRECATED "Use name instead";

A GraphQL query then selects the field as firstName:

Query

{ persons { id firstName } }

Output

{"data":{"persons":[{"id":"person:amy","firstName":null}]}}

Fields defined with READONLY or COMPUTED are not part of the create and update input types, because a mutation cannot set them. A COMPUTED field appears in the object type only if it has a TYPE.

Setting permissions on fields

By default, the permissions on a field will be set to FULL unless otherwise specified. The table is the main access gate, while field permissions only narrow further when you need to (for example, when hiding a password). With FULL, a field follows the table's rules without adding its own. Tables default the other way: omitting table PERMISSIONS in a DEFINE statement stores PERMISSIONS NONE.

DEFINE FIELD some_info ON TABLE some_table TYPE string;
INFO FOR TABLE some_table;

Output

{
	events: {},
	fields: {
		some_info: 'DEFINE FIELD some_info ON some_table TYPE string PERMISSIONS FULL'
	},
	indexes: {},
	lives: {},
	tables: {}
}

You can set permissions on fields to control who can perform operations on them using the PERMISSIONS clause. The PERMISSIONS clause can be used to set permissions for SELECT, CREATE, and UPDATE operations. The DELETE operation only relates to records and, as such, is not available for fields.

Like table permissions, field permissions apply to record users (and guests when enabled), not to system users such as root. A query run as root, including one in SurrealDB Studio or surreal db sql signed in as root, therefore still returns a field defined with PERMISSIONS FOR select NONE. To check that a field is hidden, sign in as a record user and run the query again.

-- Set permissions for the email field
DEFINE FIELD email ON TABLE user
  PERMISSIONS
    FOR select WHERE published=true OR user=$auth.id
    FOR update WHERE user=$auth.id OR $auth.role="admin";

Checking the new value of a field

Inside a field's PERMISSIONS FOR update clause, $before holds the value the field had before the statement and $after the value it would have afterwards. A condition on $after can therefore decide whether a particular new value is allowed, such as an owner that must stay the user making the change.

DEFINE TABLE doc SCHEMAFULL PERMISSIONS FULL;
DEFINE FIELD owner ON doc TYPE record<user>
  PERMISSIONS FOR select, create FULL
  FOR update WHERE $after = $auth.id;
DEFINE FIELD title ON doc TYPE string;

A record user signed in as user:ann who updates doc:one to hand it to another user does not receive an error. The field keeps its previous value and the rest of the statement goes ahead:

UPDATE doc:one SET owner = user:bob RETURN AFTER;

Output when doc:one is owned by user:ann

[{ id: doc:one, owner: user:ann, title: 'x' }]

A table-level FOR update permission does not offer this. The condition is evaluated against the record itself, once before the update and again on the updated record, so a table defined with PERMISSIONS FOR update WHERE owner = $auth.id also stops an owner from handing a record to someone else. In that case the whole UPDATE returns an empty array and changes nothing.

Interacting with other fields of the same record

While a DEFINE TABLE statement represents a template for any subsequent records to be created, a DEFINE FIELD statement pertains to concrete field data of a record. As such, a DEFINE FIELD statement gives access to the record's other fields through their names, as well as the current field through the $value parameter.

DEFINE TABLE person SCHEMAFULL;

DEFINE FIELD first_name
  ON TABLE person TYPE string VALUE string::lowercase($value);
DEFINE FIELD last_name 
  ON TABLE person TYPE string VALUE string::lowercase($value);
DEFINE FIELD name      
  ON TABLE person             VALUE first_name + ' ' + last_name;

-- Creates a `person` with the name "bob bobson"
CREATE person SET first_name = "BOB", last_name = "BOBSON";

The $this parameter gives access to the entire record on which a field is defined.

DEFINE FIELD extra_self ON TABLE person VALUE $this;
CREATE person:one SET name = "Little person", age = 6;

Output

[
	{
		age: 6,
		extra_self: {
			age: 6,
			id: person:one,
			name: 'Little person'
		},
		id: person:one,
		name: 'Little person'
	}
]

Field evaluation order

Available since: v3.3.0

When a record is written, field clauses run in dependency order: a field whose clause reads another field is evaluated after that field. DEFAULT, VALUE, and COMPUTED all take part, because each produces a value another field may need. Fields that do not read each other are evaluated in the order they were defined.

DEFINE FIELD z_src ON v TYPE int VALUE 10;
DEFINE FIELD a_dep ON v TYPE int VALUE z_src + 1;

CREATE v:1;
-- a_dep is 11

Note

Before 3.3.0, fields were evaluated in name order. The example above failed because a_dep ran before z_src had a value, which meant that renaming a field could break a working schema. Nested fields are still evaluated after their parent.

ASSERT is different, because it produces no value and so imposes no order. Assertions run in a second pass, once every field holds its final value, so a clause that reads a sibling always sees that sibling's stored value. Two fields asserting against each other is ordinary rather than a cycle:

DEFINE FIELD in ON follows TYPE record<user> ASSERT in != out;
DEFINE FIELD out ON follows TYPE record<user> ASSERT out != in;

A genuine cycle between value-producing clauses is rejected when the field is defined rather than when a record is written.

Note

Computed fields are also populated on the records that events, live queries, and changefeeds receive. Before 3.3.0, $before and $after reported every computed field as NONE.

Using `IF NOT EXISTS` clause

The IF NOT EXISTS clause can be used to define a field only if it does not already exist. You should use the IF NOT EXISTS clause when defining a field in SurrealDB if you want to ensure that the field is only created if it does not already exist. If the field already exists, the DEFINE FIELD statement will return an error.

It's particularly useful when you want to safely attempt to define a field without manually checking its existence first.

On the other hand, you should not use the IF NOT EXISTS clause when you want to ensure that the field definition is updated regardless of whether it already exists. In such cases, you might prefer using the OVERWRITE clause, which allows you to define a field and overwrite an existing one if it already exists, ensuring that the latest version of the definition is always in use.

-- Create a field if it does not already exist
DEFINE FIELD IF NOT EXISTS email ON TABLE user TYPE string;

Using `OVERWRITE` clause

The OVERWRITE clause can be used to define a field and overwrite an existing one if it already exists. You should use the OVERWRITE clause when you want to modify an existing field definition. If the field already exists, the DEFINE FIELD statement will overwrite the existing definition with the new one.

-- Overwrite the current field definition if it already exists
DEFINE FIELD OVERWRITE example ON TABLE user TYPE string;

Defining a reference

A field that is a record link (type record, option<record>, array<record<person>>, and so on) can be defined as a REFERENCE. If this clause is used, any linked to record will be able to define a computed field of its own using the <~ syntax, which will be aware of the incoming links.

For more information, see the page in the datamodel section on references.

REFERENCE is only accepted on a field at the root of the record. A nested field such as meta.thumbnail or items.*.object is refused:

DEFINE TABLE file;
DEFINE FIELD meta ON file TYPE object;
DEFINE FIELD meta.thumbnail ON file TYPE option<record<storage_object>> REFERENCE;

Error output

'Cannot use the `REFERENCE` keyword on nested field `meta.thumbnail`. Specify a referencing field at the root level instead.'

To keep a reference that belongs with nested data, define it as a root-level field, such as DEFINE FIELD thumbnail ON file TYPE option<record<storage_object>> REFERENCE;.

Where the links sit inside an array of objects, a root-level array field can collect them with a VALUE clause. The clause runs on every write, so the array always holds the links that the nested objects hold, and the REFERENCE on it applies to each of them.

DEFINE TABLE storage_object SCHEMAFULL;
DEFINE FIELD name ON storage_object TYPE string;
DEFINE FIELD media ON storage_object COMPUTED <~media_item;

DEFINE TABLE media_item SCHEMAFULL;
DEFINE FIELD video ON media_item TYPE object;
DEFINE FIELD video.captions ON media_item TYPE option<array<object>>;
DEFINE FIELD video.captions.*.lang ON media_item TYPE string;
DEFINE FIELD video.captions.*.object ON media_item TYPE record<storage_object>;
-- Collects every video.captions.*.object, or an empty array when there are no captions
DEFINE FIELD caption_objects ON media_item TYPE array<record<storage_object>>
	VALUE video.captions.*.object ?? []
	REFERENCE ON DELETE REJECT;

CREATE storage_object:en SET name = 'captions-en.vtt';
CREATE storage_object:fr SET name = 'captions-fr.vtt';
CREATE media_item:intro SET video.captions = [
	{ lang: 'en', object: storage_object:en },
	{ lang: 'fr', object: storage_object:fr }
];

DELETE storage_object:fr;
//- 'Cannot delete `storage_object:fr` as it is referenced by `media_item:intro` with an ON DELETE REJECT clause'

storage_object:fr.media;
//- [media_item:intro]

Removing the caption from the nested array removes the link from caption_objects as well, and the delete then succeeds:

UPDATE media_item:intro SET video.captions = video.captions.filter(|$c| $c.lang != 'fr');
DELETE storage_object:fr;

ON DELETE REJECT and ON DELETE CASCADE work with this pattern. ON DELETE UNSET does not: it removes the link from caption_objects, but the VALUE clause runs again on that update and reads the link back from the nested object, so the record is deleted while video.captions still links to it.

Keeping a list of references with `MATERIALIZE INTO`

Available since: v4.0.0

A <~ lookup finds the records that reference a record each time it is read. MATERIALIZE INTO stores that list instead, in a field of the referenced record, and keeps it up to date in the same transaction as each write to the referencing field. Reading the list is then a plain field read.

DEFINE TABLE author SCHEMAFULL;
DEFINE FIELD name ON author TYPE string;
DEFINE FIELD posts ON author TYPE array<record<post>> DEFAULT [] READONLY;

DEFINE TABLE post SCHEMAFULL;
DEFINE FIELD title ON post TYPE string;
DEFINE FIELD author ON post TYPE record<author> REFERENCE ON DELETE CASCADE MATERIALIZE INTO author.posts;

CREATE author:a, author:b SET name = 'x';
CREATE post:3 SET author = author:a, title = 'c';
CREATE post:1 SET author = author:a, title = 'a';
CREATE post:2 SET author = author:b, title = 'b';
UPDATE post:1 SET author = author:b;

SELECT id, posts FROM author;

Output

[
	{ id: author:a, posts: [post:3] },
	{ id: author:b, posts: [post:1, post:2] }
]

The list holds each record once, in ascending order of record ID, which is the order a <~ lookup returns. Creating a referencing record adds it to the list, changing the reference moves it from one list to the other, and deleting it removes it. With a field that holds an array of references, each element is handled in the same way. A WHERE condition, such as MATERIALIZE INTO author.posts WHERE archived != true, lists only the referencing records for which it is true, and a write that changes the result of the condition adds or removes the record. The condition must not have effects, in the same way as the clauses that must not have effects.

The destination field must already be defined on the referenced table, at the top level, as a READONLY field of type array<record<...>> of the referencing table with no length limit. It cannot be id, a COMPUTED field or the referencing field itself, and only one MATERIALIZE INTO clause can fill it. A statement that does not meet these rules is refused, for example:

Error output

'An error occurred: MATERIALIZE INTO author.plain requires field `plain` to be READONLY: the engine keeps its list'

Because the destination is READONLY, a statement that writes to it directly fails. While the clause fills it, the destination field cannot be redefined, altered or removed, and its table cannot be removed. Removing the clause, for example with ALTER FIELD ... DROP REFERENCE, leaves the lists in place as ordinary data.

DEFINE FIELD fills the lists from the records that already exist, within the same statement. Updates to a list do not fire the events of the referenced table, but live queries and change feeds see them like any other write. An import restores the lists with the records.

Defining a `TYPE` for the `id` field

The DEFINE FIELD statement can be defined for the id field to specify the acceptable type of ID.

DEFINE FIELD id ON TABLE something TYPE string;
DEFINE FIELD id ON TABLE something TYPE int;
DEFINE FIELD id ON TABLE something TYPE uuid;

Complex IDs can be specified as well.

-- using multiple data types for a Complex Record ID
DEFINE FIELD id
  ON TABLE log TYPE [record, "info" | "warn" | "error", datetime];

-- Incorrect ID format, generates an error
CREATE log:bad SET level = "info", time = time::now(), message = "Database started";

-- Acceptable ID format
CREATE log:[user:one, "info", time::now()] SET message = "Database started";

Output

-------- Query --------

"Couldn't coerce value for field `id` of `log:bad`: Expected `[record, 'info' | 'warn' | 'error', datetime]` but found `'bad'`"

-------- Query --------

[
	{
		id: log:[
			user:one,
			'info',
			d'2025-03-25T03:36:16.323Z'
		],
		message: 'Database started'
	}
]

Definitions inside the `id` field

Available since: v4.0.0

An object or array ID can also be described with a definition for each of its parts, such as id.parent or id[0], after id itself is defined. The TYPE of each such definition is checked against the matching part of the record ID when the record is created, and a record whose ID does not fit is refused with an error that names the definition:

DEFINE TABLE test SCHEMAFULL;
DEFINE FIELD id ON test TYPE object;
DEFINE FIELD id.id ON test TYPE string;
DEFINE FIELD id.parent ON test TYPE record<accounts>;

CREATE test:{ id: 'aaa', parent: accounts:0 };
CREATE test:{ id: 1, parent: accounts:0 };

Error output

"Couldn't coerce value for field `id.id` of `test:{ id: 1, parent: accounts:0 }`: Expected `string` but found `1`"

The record ID is checked and stored as it was written. An ASSERT on such a definition is checked in the same way, but a VALUE, DEFAULT or PERMISSIONS clause on it has no effect, because a record ID does not change after the record is created.

A definition inside id needs a definition of id itself, and the statement is refused when the table has none:

DEFINE FIELD id.id ON other TYPE int;

Error output

"Cannot define field `id.id`: the field `id` it is nested in is not defined. Define `id` first"

Naming the components of an array id

Available since: v4.0.0

The elements of an array record ID can be given names in the type of the id field. Each name becomes a read-only field that holds the matching element of the ID, so city reads id[0] and time reads id[1]. The names appear in the output of SELECT *, but they are not stored in the body of the record.

DEFINE FIELD id ON reading TYPE [city: string, time: datetime];

CREATE reading SET city = 'London', time = d'2024-01-01T00:00:00Z', temp = 5.1;
CREATE reading SET city = 'London', time = d'2024-01-02T00:00:00Z', temp = 6.3;
CREATE reading SET city = 'Rome', time = d'2024-01-02T00:00:00Z', temp = 11.0;

SELECT * FROM reading
WHERE city = 'London' AND time >= d'2024-01-02T00:00:00Z' AND time < d'2024-01-03T00:00:00Z';

Output

[
	{
		city: 'London',
		id: reading:[
			'London',
			d'2024-01-02T00:00:00Z'
		],
		temp: 6.3f,
		time: d'2024-01-02T00:00:00Z'
	}
]

A WHERE clause on the named components is read as a range over the record keys, so the query reads only the matching part of the table instead of scanning all of it. The plan uses the longest run of equality tests from the first component, followed by one range on the next component, and EXPLAIN shows it as a KeyRangeScan. A WHERE clause on a component with IN or OR is read as several ranges. The remaining conditions of the clause, such as temp > 6, are still applied to each record that the range returns, and a condition on a field that is not a component reads the whole table as before.

ORDER BY on a component that follows the tests in the WHERE clause can be read from the scan in key order. A query for the latest records of one city reads the keys backwards from the end of that city's range, so LIMIT stops the scan early:

SELECT * FROM reading WHERE city = 'London' ORDER BY time DESC LIMIT 10;

A datetime in a record key is compared to the whole second, so the planner keeps the sort and the filter for a datetime component, and these decide the final order and the final result.

Writing records

A record gets its ID from an explicit ID in the statement, or else from the values of all components set with SET or CONTENT, or else from a DEFAULT on the id field. The values of the components are taken into the ID, and the named fields still read them back. A component that is given both in the ID and in SET has to have the same value in both places.

CREATE reading:['London', d'2024-02-01T00:00:00Z'] SET city = 'Rome';
//- "Found 'Rome' for the `city` field, but it is part of the record id of `reading` and does not match it"

CREATE reading:['London'];
//- 'The record id of `reading` has the components [city, time], but 1 value was given'

CREATE reading SET city = 'London', temp = 3;
//- 'Cannot create a record in `reading` without the record id component `time`: set `time`, give the record id, or define a DEFAULT on the `id` field'

CREATE reading SET city = 'London', time = 'yesterday';
//- "Couldn't coerce value for the record id component `time` of `reading`: Expected `datetime` but found `'yesterday'`"

An UPDATE cannot change a component, because a component is part of the ID that the record is stored under.

Rules for components

A component can be a string, int, datetime, uuid, duration, bool or record. These are the types that keep their order when stored in a key, so other types, such as decimal, are refused when the field is defined.
An int component accepts only whole numbers. A float or decimal with no fractional part, such as 2.0, is converted to an int, and any other number is refused.
A name has to be a plain identifier of ASCII letters, digits and underscores that does not start with a digit, and it cannot be the name of another field of the table.
The names can be used only as the whole type of the id field. They are not accepted inside a type such as array<[city: string]>.
A component can be indexed like any other field, for example with DEFINE INDEX by_time ON reading FIELDS time.

DEFINE FIELD id ON reading TYPE [price: decimal, time: datetime];

Error output

'The record id component `price` of `reading` cannot have the type `decimal`: only string, int, datetime, uuid, duration, bool and record components keep their order in storage'

Existing records

When the statement is run on a table that already has records, every record is checked inside the same transaction. The statement is refused, and nothing is defined, if a record does not fit the type, or if it stores a field with the name of a component and a different value from its ID.

CREATE reading:['London', d'2024-01-01T00:00:00Z'] SET city = 'Rome';
DEFINE FIELD id ON reading TYPE [city: string, time: datetime];

Error output

"Cannot give `reading` the record id [city: string, time: datetime]: the record reading:['London', d'2024-01-01T00:00:00Z'] stores a `city` field that differs from its record id. Remove it first, for example with UPDATE reading UNSET city"

Warning

A table whose id field names its components cannot be read by an earlier release. Define the names only when every node of the deployment runs SurrealDB 4.0 or later, because a node that runs an earlier release cannot load the table. Nothing is stored differently, so removing the names makes the table readable again.

Removing the names, with REMOVE FIELD id, or with ALTER FIELD or DEFINE FIELD OVERWRITE and a type without names, is refused while the table holds records whose keys are stored in a different order from their record IDs. This happens with a datetime component, because a datetime in a key is compared to the whole second, so d'2024-01-01T00:00:00.500Z' is stored before d'2024-01-01T00:00:00Z'. Only the names let ORDER BY id return such records in the right order. Delete those records, or copy them into another table, before removing the names:

DEFINE FIELD id ON coarse TYPE [at: datetime];
CREATE coarse SET at = d'2024-01-01T00:00:00Z';
CREATE coarse SET at = d'2024-01-01T00:00:00.500Z';
REMOVE FIELD id ON coarse;

Error output

"Cannot remove the names from the record id of `coarse`: it holds coarse:[d'2024-01-01T00:00:00.500Z'] and coarse:[d'2024-01-01T00:00:00Z'], whose keys are stored in the opposite order to their record ids, which only the names account for. Delete those records, or copy them into a table with the record id you want, first"

`ASSERT` and `DEFAULT` on `id`

Available since: v3.2.0

ASSERT on the id field is evaluated like any other field assertion. Inside the assertion, $value is the whole record id; use id.id() (or record::id($value)) to inspect the key portion. Assertions run on create for generated, default-supplied, and explicitly supplied ids, and are skipped on update and under OPTION IMPORT.

DEFAULT supplies the record id when none is given in CREATE or INSERT, evaluated in the session context and coerced to the declared type. An explicit id in the statement always wins. DEFAULT ALWAYS is not allowed on id.

DEFINE FIELD id ON user TYPE string DEFAULT rand::ulid() ASSERT id.id().is_ulid();
CREATE user SET name = 'Ada';

VALUE, REFERENCE, COMPUTED, READONLY, FLEXIBLE, and non-key TYPE clauses are forbidden on id, a restriction which applies to ALTER FIELD statements as well.

Clauses that must not have effects

The COMPUTED, ASSERT, VALUE and DEFAULT clauses of a field, and the PERMISSIONS clauses of a field, a table, a function or a parameter, are code that the database runs on behalf of whoever writes or reads a record. None of them may have an effect that lasts after the clause is evaluated. A clause is refused when it is defined if it:

writes data, with CREATE, UPDATE, UPSERT, INSERT, DELETE or RELATE
changes the schema, with a DEFINE, ALTER or REMOVE statement
calls sequence::nextval(), except in a VALUE or DEFAULT clause
waits with SLEEP
calls a JavaScript function, eval::surql() or an extension function, whose effects cannot be known when the clause is defined
calls a closure whose body is not written in the clause, such as one held in a parameter, or passes one to a function or method such as array::map(). A closure written in the clause itself is checked like the rest of the clause, so array::map([1, 2], |$v| $v * 2) is allowed

DEFINE FIELD view_count ON article COMPUTED (UPDATE stats:articles SET views += 1);

Error output

'The COMPUTED clause must not have effects: `UPDATE` writes data'

A custom function that the clause calls is checked as well, including the functions that it calls in turn:

DEFINE FUNCTION fn::record_view() -> int { CREATE view_log SET at = time::now(); RETURN 1; };
DEFINE FIELD view_count ON article COMPUTED fn::record_view();

Error output

'The COMPUTED clause must not have effects: it calls `fn::record_view`, in whose body `CREATE` writes data'

A VALUE or DEFAULT clause runs once, when a record is written, so it may still draw the next number of a sequence. Random values and the clock are allowed in every clause:

DEFINE SEQUENCE invoice_number;
DEFINE FIELD number ON invoice TYPE int DEFAULT sequence::nextval('invoice_number');
DEFINE FIELD token ON invoice TYPE uuid DEFAULT rand::uuid();
DEFINE FIELD updated_at ON invoice TYPE datetime VALUE time::now();

A function that does not exist yet is not checked when the clause is defined, and a function can be redefined later to write. In both cases the clause fails with the same error each time it is evaluated and reaches the effect, and nothing is written. The same applies to a definition stored by an earlier release that allowed the effect. Check which definitions call a function before changing its body. See Functions called from schema clauses.

A side effect that should happen when a record changes, such as a write to an audit log, belongs in DEFINE EVENT, whose body may write.

Note

These rules are new in SurrealDB 4.0. In 3.3, only a COMPUTED body and a FOR select clause were stopped from writing, and only when the write was reached, while the FOR create, FOR update and FOR delete clauses, VALUE, DEFAULT and ASSERT could write.

Restrictions on computed fields

Available since: v3.3.0

A COMPUTED body is evaluated on every read of the field, inside the transaction of the statement that reads it. It is one of the clauses that must not have effects, and the rules below also apply to it.

A computed body is capped at the definer's permissions

A COMPUTED body is evaluated with the reader's own authentication, narrowed so that it can never exceed the level and role of the user who defined the field. As with DEFINE FUNCTION, the limit is a ceiling: it only ever removes access.

A reader with broader permissions than the definer therefore gains nothing from the field. In the example below, the body needs a root-level identity, so it is refused under the definer's database-level Editor ceiling however privileged the reader is.

-- Defined by a database-level Editor
DEFINE FIELD server_info ON article COMPUTED (INFO FOR ROOT);

-- Selected by a root Owner: the body is still capped at database-level Editor
SELECT server_info FROM article;

The reverse does not hold, and a computed field is not a way to grant access. A reader with narrower permissions than the definer keeps their own: the body runs as them, and every permission that applies to their own queries applies inside it.

-- Defined by a root user
DEFINE TABLE audit_log SCHEMALESS PERMISSIONS NONE;
DEFINE TABLE article SCHEMALESS PERMISSIONS FULL;
DEFINE FIELD recent_audits ON article COMPUTED (SELECT * FROM audit_log);

-- Selected by a record user
SELECT id, title, recent_audits FROM article;

The record user can read article, so the row is returned. They cannot read audit_log, so the computed field is empty rather than exposing the table.

Output

[
	{
		id: article:1,
		recent_audits: [],
		title: 'Hello'
	}
]

Note

For record and anonymous readers the narrowing is skipped altogether, because a system user's ceiling cannot narrow them any further. Auth limiting never escalates the reader - see Capabilities.

A computed field's own select permission is enforced everywhere

PERMISSIONS FOR select on a COMPUTED field applies wherever the field is read, not only in a SELECT projection. That includes the pre-mutation image an UPDATE reads, a WHERE condition, a write statement's RETURN list, and RETURN DIFF.

DEFINE TABLE item PERMISSIONS FOR select, update FULL;
DEFINE FIELD hidden ON item COMPUTED 'secret' PERMISSIONS FOR select NONE;
DEFINE FIELD leak ON item TYPE any PERMISSIONS FOR select, update FULL;

-- As a record user, both read the field as NONE
SELECT * FROM item:1;
UPDATE item:1 SET leak = hidden RETURN leak;

Warning

Before SurrealDB 3.3.0, a caller allowed to update a record could read a computed field denied to them and copy it into a field they were allowed to select, as in the UPDATE above. If you relied on a computed field's select permission to hide a value, check whether any writable field on the same table was used to copy it out.

Computed fields and array elements in reduced results

Field-level select permissions narrow a record before it is returned. Before 3.3.0 that pass could remove whole elements of an array field rather than narrowing them, which also made RETURN DIFF come back empty for every session that was not the record's owner. Both now return the expected values.

Using INLINE to filter a traversal during the scan

Available since: v3.3.0

An INLINE field on a relation table is stored twice: once on the edge record as usual, and again inside the adjacency entries that the graph traversal walks. A traversal filtered on that field can then test the predicate while it scans, and fetch only the edge records that match.

DEFINE TABLE likes TYPE RELATION IN person OUT post;
DEFINE FIELD score ON likes TYPE number INLINE;

Inlining a field has no effect on the way it is queried. It is read and written exactly as before, and a traversal such as person:one->(likes WHERE score > 8)->post returns the same records. What changes is how many edge records the traversal has to read to produce them, which is why the clause helps on a filter that may run over many edges.

The condition has to sit inside the graph path for the inlined value to be reached. Written that way it becomes the scan's own predicate, which EXPLAIN reports as predicate_scope: payload - the inlined copy is what the filter reads. The same condition written as an array filter, person:one->likes[WHERE score > 8]->post, walks every edge first and filters the resulting array afterwards, so the inlined value is never consulted and the clause brings no improvement in performance.

An inlined field can be thought of as an index in that it is a write-side cost paid for a read-side gain. Conversely, a field that is filtered rarely and written constantly is not one that you would want to inline.

Values for an inlined field should also be kept small. A payload larger than SURREAL_GRAPH_INLINE_PROPS_CAP (64 bytes by default) is spilled, meaning the value stays on the record only and the traversal falls back to fetching it. See environment variables.

ALTER FIELD adds the clause to an existing field and removes it again, so the decision is reversible without redefining the field.

What INLINE rejects

Rejected	Error
A field on a table that is not `TYPE RELATION`	`INLINE requires an existing TYPE RELATION table`
The `id`, `in` or `out` fields	`the 'id', 'in' and 'out' fields already live in the adjacency keys and cannot be INLINE`
A nested field path, such as `meta.score`	`only a top-level field can be INLINE`
A `COMPUTED` field	There is no stored value to embed
A field on a `LIGHTWEIGHT` relation	A lightweight relation has no edge records, so it has no fields at all

Demoting a relation table to a normal table while it still carries INLINE fields is also rejected.

Requirements

Statement syntax

Example usage

Defining data types

Simple data types

Sized number types

Array type

Making a field optional

Flexible data types

Using the DEFAULT clause to set a default value

Using the DEFAULT and ALWAYS clauses

Using the VALUE clause to set a field's value

Altering a passed value

Adding a field to a table with existing records

Migrating existing records

Running a schema script again

When a migration is required

Computed fields

Defining a literal on a field

Comments

Asserting rules on fields

Making a field READONLY

Using regex to validate a string

Array with allowed values

GraphQL names and deprecation

Setting permissions on fields

Checking the new value of a field

Interacting with other fields of the same record

Field evaluation order

Using IF NOT EXISTS clause

Using OVERWRITE clause

Defining a reference

Keeping a list of references with MATERIALIZE INTO

Defining a TYPE for the id field

Definitions inside the id field

Naming the components of an array id

Writing records

Rules for components

Existing records

ASSERT and DEFAULT on id

Clauses that must not have effects

Restrictions on computed fields

A computed body is capped at the definer's permissions

A computed field's own select permission is enforced everywhere

Computed fields and array elements in reduced results

Using INLINE to filter a traversal during the scan

What INLINE rejects

Using the `DEFAULT` clause to set a default value

Using the `DEFAULT` and `ALWAYS` clauses

Using the `VALUE` clause to set a field's value

Making a field `READONLY`

Using `IF NOT EXISTS` clause

Using `OVERWRITE` clause

Keeping a list of references with `MATERIALIZE INTO`

Defining a `TYPE` for the `id` field

Definitions inside the `id` field

`ASSERT` and `DEFAULT` on `id`