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
[ TYPE @type ]
[ 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 object data type
DEFINE FIELD metadata ON TABLE user TYPE object;
-- Set a field to have the bool data type
DEFINE FIELD locked ON TABLE user TYPE bool;
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;
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
VALUE $value OR false;
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
-- Make this field required
ASSERT $value != NONE
-- Check if the value is a properly formatted email address
AND 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.
DEFINE FIELD resource on acl TYPE record
ASSERT $value != NONE;
DEFINE FIELD user ON TABLE acl TYPE record (user)
ASSERT $value != NONE;
-- A user can have multiple permissions on a acl
DEFINE FIELD permission 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 type.* ON TABLE resource TYPE string
-- Allow only these values in the array
ASSERT $value INSIDE ["create", "read", "write", "delete"];
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 != NONE AND $value = /[A-Z]{3}/
-- Set a default value if empty
VALUE $value OR 'GBR'
;
Field data types
The DEFINE FIELD
statement allows specify the following data types on the field.
Type | Description |
---|---|
any
|
Use this when you explicitly don't want to specify the field's data type. The field will allow any data type supported by SurrealDB. |
array
|
|
bool
|
|
datetime
|
An ISO 8601 compliant data type that stores a date with time and time zone. |
decimal
|
Uses BigDecimal for storing any real number with arbitrary precision. |
duration
|
Store a value representing a length of time. Can be added or subtracted from datetimes or other durations. |
float
|
Store a value in a 64 bit float. |
int
|
Store a value in a 64 bit integer. |
number
|
Store numbers without specifying the type. SurrealDB will detect the type of number and store it using the minimal number of bytes. For numbers passed in as a string, this field will store the number in a BigDecimal. |
object
|
Store formatted objects containing values of any supported type with no limit to object depth or nesting. |
string
|
|
record
|
Store a reference to another record. The value must be a Record ID. |
geometry
|
RFC 7946 compliant data type for storing geometry in the GeoJson format.
Geometric Types include:
|