# 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`). A [`COMMENT`](/docs/reference/query-language/statements/define/overview.md#comments-on-definitions) is a good place for comparison rules and invariants that are not fully captured by the type alone.

You need database-level access and an active [`USE`](/docs/reference/query-language/statements/use.md) scope, same as for tables.

Computed fields (derived on every read) have their own learn page: [Computed fields](/docs/learn/schema-management/computed-data/computed-fields.md).

## Example usage

The following expression shows the simplest way to use the `DEFINE FIELD` statement.

```surql
-- 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.

```surql
-- 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

```surql
-- 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`](/docs/reference/query-language/language-primitives/data-types/uuids.md) or an [`int`](/docs/reference/query-language/language-primitives/data-types/numbers.md#integer-numbers), perhaps for `user` records that have varying data due to two diffent legacy ID types.

```surql
-- 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.

```surql
-- 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.

```surql
-- 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](/docs/reference/query-language/language-primitives/record-links.md). This means that the field can store a `record<user>` or `NONE`.

```surql
DEFINE FIELD user ON TABLE post TYPE option<record<user>>;
```

### Flexible objects in schemafull tables

On a `SCHEMAFULL` table, objects are schemafull by default. The `FLEXIBLE` field clause (written after `TYPE`) marks a field so that every `object` in its type accepts arbitrary keys. This includes objects nested in arrays, options, and unions - not only a top-level `TYPE object` field.

```surql
DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD name ON TABLE 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:

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

Without `FLEXIBLE`, the `metadata` field is a schemafull object and only declared subfields such as `metadata.user_id` are accepted.

With `FLEXIBLE`, the field accepts any extra keys on `metadata` while still requiring `name` and a valid `metadata.user_id`.

```surql title="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.

```surql
-- 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).

```surql
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.

```surql
-- 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`.

```surql
-- 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.

```surql
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.

```surql
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.

```surql
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`](/docs/reference/query-language/statements/define/field.md#computed-fields) can be used.

```surql
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`](/docs/reference/query-language/functions/database-functions/string.md#stringlowercase) function.

```surql
-- 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.

```surql
-- 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`](/docs/reference/query-language/statements/throw.md) clause otherwise.

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

```surql title="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`.

```surql
DEFINE FIELD created ON resource VALUE time::now() READONLY;
```

## Setting permissions on fields

By default, the permissions on a field are set to `FULL` unless otherwise specified. That means once a [table](/docs/learn/schema-management/tables-and-fields/tables.md#defining-permissions) allows an operation for a record user, the field is included unless you narrow it with your own `PERMISSIONS` clause. Tables default the other way: omitting table `PERMISSIONS` stores `PERMISSIONS NONE`.

```surql
DEFINE FIELD some_info ON TABLE some_table TYPE string;
INFO FOR TABLE some_table;
```

```surql title="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.

Like table permissions, field permissions apply to [record users](/docs/learn/security/authentication/authentication.md#record-users) (and guests when enabled), not to system users.

```surql
/[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`.

```surql
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 organise 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](/docs/reference/query-language/language-primitives/data-types/literals.md), by specifying one or more possible values and/or permitted types.

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

```surql title="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 }"
```