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

• 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 @name ON [ TABLE ] @table
    [ [ FLEXIBLE ] TYPE @type ]
    [ DEFAULT @expression ]
    [ 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. SurrealDB supports the following 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;
-- Set the contents of the array to only support a string data type
DEFINE FIELD roles.* ON TABLE user TYPE string;

-- Set a field to have the array data type
DEFINE FIELD posts ON TABLE user TYPE array;
-- Set the contents of the array to only support a record data type
DEFINE FIELD posts.* ON TABLE user TYPE record();

Option type

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

Readonly field

One common pattern is to have a field that is set once and never changed. This can be achieved by setting the latest VALUE of a field to $before, a.k.a. the same value as before.

-- Once a value is set for this field, it will never change
DEFINE FIELD created ON TABLE user TYPE datetime
  VALUE $before OR $value;

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

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
  ASSERT array::len($value) > 0;

-- Assigned permissions are identified by specific string values.
DEFINE FIELD permissions.* ON TABLE acl TYPE string
  -- Allow only these values in the array
  ASSERT $value INSIDE ["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'
;