Skip to main content
Version: 2.x(alpha)

DEFINE TABLE statement

The DEFINE TABLE statement allows you to declare your table by name, enabling you to apply strict controls to a table's schema by making it SCHEMAFULL, create a foreign table view, and set permissions specifying what operations can be performed on the field.

Requirements

Statement syntax

SurrealQL Syntax
DEFINE TABLE [ IF NOT EXISTS ] @name
[ DROP ]
[ SCHEMAFULL | SCHEMALESS ]
[ TYPE [ ANY | NORMAL | RELATION [ IN | FROM ] @table [ OUT | TO ] @table ] ]
[ AS SELECT @projections
FROM @tables
[ WHERE @condition ]
[ GROUP [ BY ] @groups ]
]
[CHANGEFEED @duration [INCLUDE ORIGINAL] ]
[ PERMISSIONS [ NONE | FULL
| FOR select @expression
| FOR create @expression
| FOR update @expression
| FOR delete @expression
] ]
[ COMMENT @string ]

Example usage

Below shows how you can create a table using the DEFINE TABLE statement.

-- Declare the name of a table.
DEFINE TABLE reading;

The following example uses the DROP portion of the DEFINE TABLE statement. Marking a table as DROP disallows creating or updating records.

DROP tables are useful in combination with events or foreign (view) tables, as you can compute a record and essentially drop the input.

-- By marking a table as DROP, you disallow any records to be created or updated.
-- Records that currently exist in the table will not automatically be deleted, you can still remove them manually.
DEFINE TABLE reading DROP;

The following expression shows how you can define a CHANGEFEED for a table. After creating, updating, and deleting records in the table as usual, using SHOW CHANGES FOR will show the changes that have taken place during this time.

-- Define the change feed and its duration
-- Optionally, append INCLUDE ORIGINAL to include info
-- on the current record before a change took place
DEFINE TABLE reading CHANGEFEED 3d;

-- Create some records in the reading table
CREATE reading SET story = "Once upon a time";
CREATE reading SET story = "there was a database";

-- Replay changes to the reading table
SHOW CHANGES FOR TABLE reading SINCE "2023-09-07T01:23:52Z" LIMIT 10;
Response without INCLUDE ORIGINAL
[
{
"changes": [
{
"define_table": {
"name": "reading"
}
}
],
"versionstamp": 29
},
{
"changes": [
{
"update": {
"id": "reading:h1gcbc7ykbpslellh2g2",
"story": "Once upon a time"
}
}
],
"versionstamp": 30
},
{
"changes": [
{
"update": {
"id": "reading:l9qfcncklhnlklby1avf",
"story": "there was a database"
}
}
],
"versionstamp": 31
}
]
Response with INCLUDE ORIGINAL
[
{
"changes": [
{
"define_table": {
"name": "reading"
}
}
],
"versionstamp": 29
},
{
"changes": [
{
"current": {
"id": "reading:2j3rc2yw1jzspcuvfe9v",
"story": "Once upon a time"
},
"update": [
{
"op": "replace",
"path": "/",
"value": null
}
]
}
],
"versionstamp": 30
},
{
"changes": [
{
"current": {
"id": "reading:iuiurhi0y2ka0by0skqi",
"story": "there was a database"
},
"update": [
{
"op": "replace",
"path": "/",
"value": null
}
]
}
],
"versionstamp": 31
}
]

Using IF NOT EXISTS clause Since 1.3.0

The IF NOT EXISTS clause can be used to define a table only if it does not already exist. If the table already exists, the DEFINE TABLE statement will return an error.

-- Create a TABLE if it does not already exist
DEFINE TABLE IF NOT EXISTS reading

Schemafull tables

The following example demonstrates the SCHEMAFULL portion of the DEFINE TABLE statement. When a table is defined as schemafull, the database strictly enforces any schema definitions that are specified using the DEFINE TABLE statement. New fields can not be added to a SCHEMAFULL table unless they are defined via the DEFINE FIELD statement.

-- Create schemafull user table.
DEFINE TABLE user SCHEMAFULL;

-- Define some fields.
DEFINE FIELD firstName ON TABLE user TYPE string;
DEFINE FIELD lastName ON TABLE user TYPE string;
DEFINE FIELD email ON TABLE user TYPE string
ASSERT string::is::email($value);
DEFINE INDEX userEmailIndex ON TABLE user COLUMNS email UNIQUE;

-- SEE IT IN ACTION
-- 1: Add a user with all required fields and an undefined one, 'photoURI'.
CREATE user CONTENT {
firstName: 'Tobie',
lastName: 'Hitchcock',
email: 'Tobie.Hitchcock@surrealdb.com',
photoURI: 'photo/yxCFi22Jw2.webp'
};
-- 2: Statement will not fail but photoURI will be ignored as it is not a
-- defined field.

-- 3: Query the data
SELECT * FROM user

Schemaless tables

The following example demonstrates the SCHEMALESS portion of the DEFINE TABLE statement. This allows you to explicitly state that the specified table has no schema.

-- Create schemaless user table.
DEFINE TABLE user SCHEMALESS;

-- Define some fields.
DEFINE FIELD firstName ON TABLE user TYPE string;
DEFINE FIELD lastName ON TABLE user TYPE string;
DEFINE FIELD email ON TABLE user TYPE string
ASSERT string::is::email($value);
DEFINE INDEX userEmailIndex ON TABLE user COLUMNS email UNIQUE;

-- SEE IT IN ACTION - Example 1
-- 1: Add a user with all required fields and an undefined one.
CREATE user SET firstName = 'Tobie', lastName = 'Hitchcock', email = 'Tobie.Hitchcock@surrealdb.com', photoURI = 'photo/yxCFi22Jw2.webp';
-- 2: Statement will succeed because user is a SCHEMALESS table.

-- SEE IT IN ACTION - Example 2
-- 1: Add a user with an invalid email address and include a new field that was never defined.
CREATE user SET firstName = 'Jamie', lastName = 'Hitchcock', email = 'Jamie.Hitchcock', photoURI = 'photo/yxCFi22Jw2.webp';
-- 2: Statement will fail because the value for email was not valid.

Pre-computed table views

The following shows how to make a table view using the DEFINE TABLE statement. This is similar to making a view in Relational databases.

-- Define a table as a view which aggregates data from the reading table
DEFINE TABLE temperatures_by_month AS
SELECT
count() AS total,
time::month(recorded_at) AS month,
math::mean(temperature) AS average_temp
FROM reading
GROUP BY city
;

-- SEE IT IN ACTION
-- 1: Add a new temperature reading with some basic attributes
CREATE reading SET
temperature = 27.4,
recorded_at = time::now(),
city = 'London',
location = (-0.118092, 51.509865)
;

-- 2: Query the projection
SELECT * FROM temperatures_by_month;

Defining permissions

The following shows how to set table level PERMISSIONS using the DEFINE TABLE statement. This allows you to set independent permissions for selecting, creating, updating, and deleting data.

-- Specify access permissions for the 'post' table
DEFINE TABLE post SCHEMALESS
PERMISSIONS
FOR select
-- Published posts can be selected
WHERE published = true
-- A user can select all their own posts
OR user = $auth.id
FOR create, update
-- A user can create or update their own posts
WHERE user = $auth.id
FOR delete
-- A user can delete their own posts
WHERE user = $auth.id
-- Or an admin can delete any posts
OR $auth.admin = true
;

Table with specialized TYPE-clause Since 1.4.0

With TYPE ANY, both relations and "normal" data can be stored on a table. Due to SurrealDB's schemaless nature, this is the default option if no TYPE-clause is specified.

DEFINE TABLE person TYPE ANY;
-- Since it's default, we can also omit it.
DEFINE TABLE person;

With TYPE NORMAL, you can specify a table to only store "normal" records, and not relations.

DEFINE TABLE person TYPE NORMAL;

With TYPE RELATION, you can specify a table to only store relation type content, and restrict what kind of relations can be stored.

-- Just a RELATION table, no constraints on the type of table
DEFINE TABLE likes TYPE RELATION;

-- Define a relation table, and constraint the type of relation which can be stored
DEFINE TABLE likes TYPE RELATION FROM user TO post;
-- 
DEFINE TABLE assigned_to SCHEMAFULL TYPE RELATION IN tag OUT sticky
PERMISSIONS
FOR create, select, update, delete
WHERE in.owner == $auth.id AND out.author == $auth.id;