DEFINE
statement
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, 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'
;