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

• You must be authenticated as a root or namespace user 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 [ IF NOT EXISTS ] @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 necessary for working with nested object types, which by nature do not have defined fields.

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

Taking the following CREATE statement:

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

Without FLEXIBLE, the database will recognize that an object has been inserted, but has no way of knowing whether the fields inside conform to the strict schema. As a result, metadata will show up as an empty object. Note the difference between this and the name field. The CREATE statement will simply err if a string for name is not provided, while metadata will not err because some object has been passed in.

[
    {
        "id": "user:6nv0vymyutpvajzrfjuw",
        "metadata": {},
        "name": "User1"
    }
]

With FLEXIBLE, the output will be as expected as the schema now flexibly allows any sort of object to be a field on the user table.

Response
[
    {
        "id": "user:4a9amtnwzrvbya6p9l8x",
        "metadata": {
            "age": 25,
            "country_code": "ee",
            "time_zone": "EEST"
        },
        "name": "User1"
    }
]

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.

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

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

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

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

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

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

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.

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 Since 1.2.0

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 IF NOT EXISTS clause Since 1.3.0

The IF NOT EXISTS clause can be used to create a field only if it does not already exist. If the field already exists, the DEFINE FIELD statement will not create a new field but return an error.

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

Setting permissions on fields

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, UPDATE, and DELETE operations.

-- 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
    FOR delete WHERE user=$auth.id OR $auth.role="admin";

The PERMISSIONS clause can also be used to set permissions for all operations using the FULL keyword.

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

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

What's on your mind?

Create GitHub Issue

Quickest way to alert
our team of a problem.