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, namespace, or database 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

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

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

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;

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 should be defined for any kind of resource (any record)
DEFINE FIELD resource ON TABLE acl TYPE record;
-- An ACL should be defined for a user
DEFINE FIELD user ON TABLE acl TYPE record<user>;

-- A user can have multiple permissions on an ACL
DEFINE FIELD permissions ON TABLE acl TYPE array
  -- The array must not be empty because at least one permission is required
  ASSERT array::len($value) > 0;

-- Assigned permissions are identified by strings
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 a user
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';
-- 2: Create a resource
CREATE resource:write_to_some_queue SET
  name = "some messaging queue", type = ["read", "write"];
-- 3: Associate with ACL
CREATE acl SET user = user:tobie, resource = resource:write_to_some_queue, permissions = ["allow"];
CREATE acl SET user = user:abc, resource = resource:write_to_some_queue, permissions = ["deny"];

-- Assert failure examples
-- A: Create ACL without permissions field
CREATE acl SET user = user:tobie,
  permissions = [], # FAIL - permissions must not be empty
  resource = resource:write_to_some_queue;
-- B: Create resource with incorrect type
CREATE resource:random_some_database SET
  name = "some database",
  type = ["all"]; # FAIL - This is not a part of the array

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