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
- You must be authenticated as a root or namespace user before you can use the
DEFINE TABLEstatement. - You must select your namespace and database before you can use the
DEFINE TABLEstatement.
Statement syntax
SurrealQL SyntaxDEFINE 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;