• Start
Sign In

Tables and fields

Fields and validation

Defining fields: types, defaults, VALUE, ASSERT, permissions, and references.

Fields are where you can spell out what each table column means by using certain clauses, such as its type (TYPE), optional default values (DEFAULT), how writes are normalised (VALUE), what counts as valid data (ASSERT), or who may see or change it (PERMISSIONS).

You need database-level access and an active USE scope, same as for tables.

Computed fields (derived on every read) have their own learn page: Computed fields.

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;

The fields of an object and the items in an array can be defined individually using the . operator for objects, or the indexing operator for arrays.

-- Define nested object property types
DEFINE FIELD emails.address ON TABLE user TYPE string;
DEFINE FIELD emails.primary ON TABLE user TYPE bool;

-- Define individual fields on an array
DEFINE FIELD metadata[0] ON person TYPE datetime;
DEFINE FIELD metadata[1] ON person TYPE int;

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;

A | vertical bar can be used to allow a field to be one of a set of types. The following example shows a field that can be a UUID or an int, perhaps for user records that have varying data due to two diffent legacy ID types.

-- Set a field to have either the uuid or int type
DEFINE FIELD user_id ON TABLE user TYPE uuid|int;

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, as well as the required number of items that it must hold.

-- Set a field to have the array data type
DEFINE FIELD roles ON TABLE user TYPE array<string>;

-- Set a field to have the array data type, equivalent to `array<any>`
DEFINE FIELD posts ON TABLE user TYPE array;

-- Set a field to have the array object data type
DEFINE FIELD emails ON TABLE user TYPE array<object>;

-- Set a field that holds exactly 640 bytes
DEFINE FIELD bytes ON TABLE data TYPE array<int, 640> ASSERT $value.all(|$val| $val IN 0..=255);

-- Field for a block in a game showing the possible distinct directions a character can move next.
-- The array can contain no more than four directions
DEFINE FIELD next_paths ON TABLE block 
  TYPE array<"north" | "east" | "south" | "west"> 
  VALUE $value.distinct() 
  ASSERT $value.len() <= 4;

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

Flexible objects in schemafull tables

The FLEXIBLE keyword allows you to have SCHEMALESS functionality for an object on a SCHEMAFULL table.

DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD name ON TABLE user TYPE string;
DEFINE FIELD metadata ON TABLE user FLEXIBLE TYPE object;
DEFINE FIELD metadata.user_id ON TABLE user TYPE int;
DEFINE FIELD name ON user TYPE string;
DEFINE FIELD metadata ON TABLE user TYPE object FLEXIBLE;
DEFINE FIELD metadata.user_id ON TABLE user TYPE int;

Taking the following CREATE statement:

CREATE ONLY user SET
  name = "User1",
  metadata = {
      user_id: 8876687,
      country_code: "ee",
      time_zone: "EEST",
      age: 25
};

Without FLEXIBLE, the metadata field will effectively be a SCHEMAFULL object with only a single defined field.

With FLEXIBLE, the output will be as expected as the schema now allows any sort of object to be a field on the user table, as long as values for name and metadata.user_id are present.

Response

{
	id: user:lsdk473e279oik1k484b,
	metadata: {
		age: 25,
		country_code: 'ee',
		time_zone: 'EEST',
		user_id: 8876687
	},
	name: 'User1'
}

Using the DEFAULT clause to set 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;

Using the DEFAULT ALWAYS clause

In addition to the DEFAULT clause, you can use the DEFAULT ALWAYS clause to set a default value for a field. The ALWAYS keyword indicates that the DEFAULT clause is used not only on CREATE, but also on UPDATE if the value is empty (NONE).

DEFINE TABLE product SCHEMAFULL;
-- Set a default value of 123.456 for the primary field
DEFINE FIELD primary ON product TYPE number DEFAULT ALWAYS 123.456;

With the above definition, the primary field will be set to 123.456 when a new product is created without a value for the primary field or with a value of NONE, and when an existing product is updated if the value is specified the result will be the new value.

In the case of NULL or a mismatching type, an error will be returned.

-- This will return an error
CREATE product:test SET primary = NULL;

-- result 
"Couldn't coerce value for field `primary` of `product:test`: Expected `number` but found `NULL`"

On the other hand, if a valid number is provided during creation or update, that number will be used instead of the default value. In this case, 123.456.

-- This will set the value of the `primary` field to `123.456`
CREATE product:test;

-- This will set the value of the `primary` field to `463.456`
UPSERT product:test SET primary = 463.456;

-- This will set the value of the `primary` field to `123.456`
UPSERT product:test SET primary = NONE;

Using the VALUE clause to set a field's value

The VALUE clause differs from DEFAULT in that a default value is calculated if no other is indicated, otherwise accepting the value given in a query.

DEFINE FIELD updated ON TABLE user DEFAULT time::now();

-- Set `updated` to the year 1900
CREATE user SET updated = d"1900-01-01";
-- Then set to the year 1910
UPDATE user SET updated = d"1910-01-01";

A VALUE clause, on the other hand, will ignore attempts to set the field to any other value.

DEFINE FIELD updated ON TABLE user VALUE time::now();

-- Ignores 1900 date, sets `updated` to current time
CREATE user SET updated = d"1900-01-01";
-- Ignores again, updates to current time
UPDATE user SET updated = d"1900-01-01";

As the example above shows, a VALUE clause sets the value every time a record is modified (created or updated). However, the value will not be recalculated in a SELECT statement, which simply accesses the current set value.

DEFINE FIELD updated ON TABLE user VALUE time::now();

CREATE user:one;
SELECT * FROM ONLY user:one;
-- Sleep for one second
SLEEP 1s;
-- `updated` is still the same
SELECT * FROM ONLY user:one;

To create a field that is calculated each time it is accessed, a computed field can be used.

DEFINE FIELD accessed_at ON TABLE user COMPUTED time::now();

CREATE user:one;
SELECT * FROM ONLY user:one;
-- Sleep for one second
SLEEP 1s;
-- `accessed_at` is a different value now
SELECT * FROM ONLY user:one;

Altering 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.

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

As the ASSERT clause expects an expression that returns a boolean, an assertion with a custom message can be manually created by returning true in one case and using a THROW clause otherwise.

DEFINE FIELD num ON data TYPE int ASSERT {
    IF $input % 2 = 0 {
        RETURN true
    } ELSE {
        THROW "Tried to make a " + <string>$this + " but `num` field requires an even number"
    }
};

CREATE data:one SET num = 11;

Error output

'An error occurred: Tried to make a { id: data:one, num: 11 } but `num` field requires an even number'

Making a field READONLY

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;

Setting permissions on fields

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

DEFINE FIELD some_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.

/[test]

[[test.results]]
value = "NONE"

*/

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

Order of operations when setting a field's value

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 first_name + ' ' + 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 so that the field definitions show up in the same order as that in which they are computed.

Defining a literal on a field

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:good SET coffee = { special_order: "Venti Quadruple Ristretto Half-Decaf Soy Latte with 4 pumps of sugar-free vanilla syrup" };
CREATE order:bad 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:good
	}
]

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

Was this page helpful?

Previous

Tables

Next

Record IDs and addressing