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 owner or editor, namespace owner or editor, or database owner or editor 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 [ OVERWRITE | 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
	] ]
  [ COMMENT @string ]

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;

-- Set a field to have the array object data type
DEFINE FIELD emails ON TABLE user TYPE array<object>;
-- Define nested object property types
DEFINE FIELD emails.*.address ON TABLE user TYPE string;
DEFINE FIELD emails.*.primary ON TABLE user TYPE bool;

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 as well.

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

Available since: v1.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

Available since: v1.3.0

The IF NOT EXISTS clause can be used to define a field only if it does not already exist. You should use the IF NOT EXISTS clause when defining a field in SurrealDB if you want to ensure that the field is only created if it does not already exist. If the field already exists, the DEFINE FIELD statement will return an error.

It’s particularly useful when you want to safely attempt to define a field without manually checking its existence first.

On the other hand, you should not use the IF NOT EXISTS clause when you want to ensure that the field definition is updated regardless of whether it already exists. In such cases, you might prefer using the OVERWRITE clause, which allows you to define a field and overwrite an existing one if it already exists, ensuring that the latest version of the definition is always in use

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

Using OVERWRITE clause

Available since: v2.0.0

The OVERWRITE clause can be used to define a field and overwrite an existing one if it already exists. You should use the OVERWRITE clause when you want to modify an existing field definition. If the field already exists, the DEFINE FIELD statement will overwrite the existing definition with the new one.

-- Create an FIELD and overwrite if it already exists
DEFINE FIELD OVERWRITE example ON TABLE user TYPE string;

Setting permissions on fields

By default, the permissions on a field will be set to FULL unless otherwise specified.

DEFINE FIELD info ON TABLE some_table TYPE string;
INFO FOR TABLE some_table;

Response
{
	events: {},
	fields: {
		info: 'DEFINE FIELD info ON some_table TYPE string PERMISSIONS FULL'
	},
	indexes: {},
	lives: {},
	tables: {}
}

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, and UPDATE operations. The DELETE operation only relates to records and, as such, is not available for fields.

-- 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 OR $auth.role="admin";

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

Interacting with other fields of the same record

While a DEFINE TABLE statement represents a template for any subsequent records to be created, a DEFINE FIELD statement pertains to concrete field data of a record. As such, access to both the field itself and its parent record are granted inside DEFINE FIELD through the $value and $this parameters, respectively.

DEFINE TABLE person SCHEMAFULL;

DEFINE FIELD first_name ON TABLE person TYPE string VALUE string::lowercase($value);
DEFINE FIELD last_name  ON TABLE person TYPE string VALUE string::lowercase($value);
DEFINE FIELD name       ON TABLE person             VALUE $this.first_name + ' ' + $this.last_name;

// Creates a `person` with the name "bob bobson"
CREATE person SET first_name = "BOB", last_name = "BOBSON";

As DEFINE FIELD statements are computed in alphabetical order, be sure to keep this in mind when using fields that rely on the values of others.

The following example is identical to the above except that full_name has been chosen for the previous field name. The full_name field will be calculated after first_name, but before last_name.

DEFINE TABLE person SCHEMAFULL;

DEFINE FIELD first_name ON TABLE person TYPE string VALUE string::lowercase($value);
DEFINE FIELD last_name  ON TABLE person TYPE string VALUE string::lowercase($value);
DEFINE FIELD full_name  ON TABLE person             VALUE $this.first_name + ' ' + $this.last_name;

// Creates a `person` with `full_name` of "bob BOBSON", not "bob bobson"
CREATE person SET first_name = "Bob", last_name = "Bobson";

A good rule of thumb is to organize your DEFINE FIELD statements in alphabetical order.

Defining a literal on a field

Available since: v2.0.0

A field can also be defined as a literal type, by specifying one or more possible values and/or permitted types.

DEFINE FIELD coffee ON TABLE order TYPE "regular" | "large" | { special_order: string };

CREATE order SET coffee = { special_order: "Venti Quadruple Ristretto Half-Decaf Soy Latte with 4 pumps of sugar-free vanilla syrup" };
CREATE order SET coffee = "small";

Response
-------- Query --------

[
	{
		coffee: {
			special_order: 'Venti Quadruple Ristretto Half-Decaf Soy Latte with 4 pumps of sugar-free vanilla syrup'
		},
		id: order:ga3m9qxtv8m02wdgoe73
	}
]

-------- Query --------
"Found 'small' for field `coffee`, with record `order:juq3twfic1s6gxw9ljgj`, but expected a 'regular' | 'large' | { special_order: string }"

One more example of a literal containing settings for a full text search filter:

DEFINE FIELD filter ON TABLE search_settings TYPE
      "None"
    | { type: "Ascii" }
    | { type: "EdgeNgram", from: int, to: int }
    | { type: "Lowercase" }
    | { type: "Ngram", from: int, to: int }
    | { type: "Snowball", language: string }
    | { type: "Uppercase" };