Skip to main content
Version: 1.2.x

DEFINE FIELD

DEFINE FIELD statement

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

Statement syntax

SurrealQL Syntax
DEFINE FIELD @name ON [ TABLE ] @table
[ [ FLEXIBLE ] TYPE @type ]
[ DEFAULT @expression ]
[ READONLY ]
[ VALUE @expression ]
[ ASSERT @expression ]
[ PERMISSIONS [ NONE | FULL
| FOR select @expression
| FOR create @expression
| FOR update @expression
| FOR delete @expression
] ]

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;

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.

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;

Flexible data types

Flexible types allow you to have SCHEMALESS functionality on a SCHEMAFULL table. This is especially useful for working with nested objects.

DEFINE FIELD metadata ON TABLE user FLEXIBLE TYPE object;

Array type

-- Set a field to have the array data type
DEFINE FIELD roles ON TABLE user TYPE array<string>;
-- A respective subfield for the array field will automatically be defined.
-- If there are any existing definitions, clauses like VALUE and ASSERT will automatically be merged.
DEFINE FIELD roles.* ON TABLE user TYPE string;

-- Set a field to have the array data type, equavalent to `array<any>`
DEFINE FIELD posts ON TABLE user TYPE array;
-- The subfield will automatically be defined with the `any` data type, but we can overwrite it
-- Set the contents of the array to only support a record data type
DEFINE FIELD posts.* ON TABLE user TYPE record<string>;

Making a field optional

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

Setting a default value

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

DEFAULT inherited from VALUE

If the VALUE clause contains a static query, meaning not depending on any variables, and in case no DEFAULT clause is specified, then the VALUE clause will be used as the DEFAULT clause aswell.

DEFINE FIELD updated ON resource VALUE time::now();

Alter a passed value

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

Asserting rules on fields

You can take your field definitions even further by using asserts. Assert is a powerful feature that can be used to ensure that your data remains consistent.

Email is required

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

Making a field READONLY

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

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.

-- 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 SET
user = user:efg,
permissions = [], # FAIL - permissions must not be empty
resource = document:SurrealDB_whitepaper;
-- B: Create acl with invalid permisson
CREATE acl SET
user = user:efg,
permissions = ["all"], # FAIL - This value is not allowed in the array
resource = document:SurrealDB_whitepaper;

Using RegEX to validate a string

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