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 table.
The fields of a table are not defined using DEFINE TABLE, but via individual DEFINE FIELD statements.
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 TABLE statement. - You must select your namespace and database before you can use the
DEFINE TABLE statement.
Statement syntax
SurrealQL Syntax
DEFINE TABLE [ OVERWRITE | IF NOT EXISTS ] @name
[ DROP ]
[ SCHEMAFULL | SCHEMALESS ]
[ TYPE [ ANY | NORMAL | RELATION [ IN | FROM ] @table [ OUT | TO ] @table [ ENFORCED ]]]
[ AS SELECT @projections
FROM @tables
[ WHERE @condition ]
[ GROUP [ BY @groups | ALL ] ]
]
[ 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.
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.
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 TABLE reading CHANGEFEED 3d;
CREATE reading SET story = "Once upon a time";
CREATE reading SET story = "there was a database";
SHOW CHANGES FOR TABLE reading SINCE d"2023-09-07T01:23:52Z" LIMIT 10;
SHOW CHANGES FOR TABLE reading SINCE 0 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
}
]
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.
Since v2.0.0, schemafull tables are implicitly type NORMAL tables by default.
DEFINE TABLE user SCHEMAFULL;
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;
CREATE user CONTENT {
firstName: 'Tobie',
lastName: 'Hitchcock',
email: 'Tobie.Hitchcock@surrealdb.com',
photoURI: 'photo/yxCFi22Jw2.webp'
};
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.
DEFINE TABLE user SCHEMALESS;
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;
CREATE user:tobie SET firstName = 'Tobie', lastName = 'Hitchcock', email = 'Tobie.Hitchcock@surrealdb.com', photoURI = 'photo/yxCFi22Jw2.webp';
CREATE user:jaime SET firstName = 'Jamie', lastName = 'Hitchcock', email = 'Jamie.Hitchcock', photoURI = 'photo/yxCFi22Jw2.webp';
Interaction between fields
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, a DEFINE FIELD statement gives access to the record’s other fields through their names, as well as the current field through the $value parameter.
DEFINE TABLE person SCHEMAFULL;
DEFINE FIELD first_name ON TABLE person TYPE string ASSERT string::len($value) < 20;
DEFINE FIELD last_name ON TABLE person TYPE string ASSERT string::len($value) < 20;
DEFINE FIELD name ON TABLE person VALUE first_name + ' ' + last_name;
CREATE person SET first_name = "Bob", last_name = "Bobson";
Pre-computed table views
In SurrealDB, like in other databases, you can create views. The way you create views is using the DEFINE TABLE statement like you would for any other table, then adding the AS clause at the end with your SELECT query.
DEFINE TABLE review DROP;
DEFINE TABLE avg_product_review TYPE NORMAL AS
SELECT
count() AS number_of_reviews,
math::mean(<float> rating) AS avg_review,
->product.id AS product_id,
->product.name AS product_name
FROM review
GROUP BY product_id, product_name;
SELECT * FROM avg_product_review;
There are a few important things which make our views far more powerful than a typical relational database view and a few limitations to keep in mind.
Starting with what makes them powerful. Our pre-computed table views are most similar to event-based, incrementally updating, materialised views. Let’s explain what that means.
- Event-based, meaning that when you run add or remove data from the underlying table, in our example, the
review table, it triggers a matching event on the avg_product_review table view. - Materialised view, meaning that the first time we run the table view query, it will run the query like a normal
SELECT statement, but then materialise the result. Instead of normal views which behave like bookmarked SELECT queries, that just look like tables to the user. - Incrementally updating, meaning that for any subsequent run, it will listen for the event trigger and perform the most efficient operation possible to always keep the result up to date, instead of just running the
SELECT statement again.
While this functionality can be replicated in many other databases, it is usually only done by expert users as it can be very complicated to set up and maintain. Therefore, the true power of our pre-computed table views is making this advanced functionality accessible to everyone.
As mentioned though, there are a few limitations to keep in mind.
- First, while subsequent runs are very efficient, the initial run of large analytical queries can be slow and use a lot of resources, because its just a normal
SELECT statement. Therefore indexing and query optimisation are still very important. - Second, while both graph relations and record links are supported, the table view update event, only gets triggered based on the table we have in our
FROM clause. In our case, just the review table, not the product we are also using in the query. Meaning that if you delete a review the avg_product_review will reflect that in near real-time. However if you delete a product, it will still show up in avg_product_review.
Also note that table views are not triggered when importing data.
Defining permissions
By default, the permissions on a table will be set to NONE unless otherwise specified.
CREATE some_table;
DEFINE TABLE some_other_table;
INFO FOR DB;
Response
{
analyzers: {},
functions: {},
models: {},
params: {},
scopes: {},
tables: {
some_other_table: 'DEFINE TABLE some_other_table TYPE ANY SCHEMALESS PERMISSIONS NONE',
some_table: 'DEFINE TABLE some_table TYPE ANY SCHEMALESS PERMISSIONS NONE'
},
tokens: {},
users: {}
}
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.
DEFINE TABLE post SCHEMALESS
PERMISSIONS
FOR select
WHERE published = true
OR user = $auth.id
FOR create, update
WHERE user = $auth.id
FOR delete
WHERE user = $auth.id
OR $auth.admin = true
;
Using IF NOT EXISTS clause
The IF NOT EXISTS clause can be used to define a table only if it does not already exist. You should use the IF NOT EXISTS clause when defining a table in SurrealDB if you want to ensure that the table is only created if it does not already exist. If the table already exists, the DEFINE TABLE statement will return an error.
It’s particularly useful when you want to safely attempt to define a table 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 table 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 table and overwrite an existing one if it already exists, ensuring that the latest version of the table definition is always in use
DEFINE TABLE IF NOT EXISTS reading;
Using OVERWRITE clause
Available since: v2.0.0
The OVERWRITE clause can be used to define a table and overwrite an existing one if it already exists. You should use the OVERWRITE clause when you want to modify an existing table definition. If the table already exists, the DEFINE TABLE statement will overwrite the existing table definition with the new one.
DEFINE TABLE OVERWRITE example;
Table with specialized TYPE-clause
Not available in this version
When defining a table in SurrealDB, you can specify the type of data that can be stored in the table. This can be done using the TYPE clause, followed by either ANY, NORMAL, or RELATION.
With TYPE ANY, you can specify a table to store any type of data, whether it’s a normal record or a relational record.
With TYPE NORMAL, you can specify a table to only store “normal” records, and not relations. When a table is defined as TYPE NORMAL, it will not be able to store relations this can be useful when you want to restrict the type of data that can be stored in a table in schemafull mode.
Finally, with TYPE RELATION, you can specify a table to only store relational type content. This can be useful when you want to restrict the type of data that can be stored in a table.
DEFINE TABLE person TYPE ANY;
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 relational type content, and restrict what kind of relations can be stored.
DEFINE TABLE likes TYPE RELATION;
DEFINE TABLE likes TYPE RELATION FROM user TO post;
DEFINE TABLE likes TYPE RELATION IN user OUT post;
DEFINE TABLE likes TYPE RELATION FROM user TO post|video;
DEFINE TABLE likes TYPE RELATION IN user OUT post|video;
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;
Available since: v2.0.0
As relations are represented by standalone tables, they can be constructed before any linked records exist.
RELATE city:one->road_to->city:two SET
distance = 12.4,
slope = 5.4;
Output
[
{
distance: 12.4f,
id: road_to:pacwucj25a056hhs2s5h,
in: city:one,
out: city:two,
slope: 5.4f
}
]
As such, a query on the relation will return nothing until the records it has been defined upon are created.
SELECT ->road_to->city FROM city;
CREATE city:one, city:two;
SELECT ->road_to->city FROM city;
Output
[]
[
{
"->road_to": {
"->city": [
city:two
]
}
},
{
"->road_to": {
"->city": []
}
}
]
If this behaviour is not desirable, the ENFORCED clause can be used on a table of TYPE RELATION to disallow a RELATE statement from working unless it points to existing data.
DEFINE TABLE road_to TYPE RELATION IN city OUT city ENFORCED;
RELATE city:one->road_to->city:three SET
distance = 5.5,
slope = 30.0;
Output
"The record 'city:one' does not exist"
Inserting data from undefined fields on a SCHEMAFULL table
Previously, an insert into a SCHEMAFULL table would work even if extra data was present. The query below shows this behaviour, in which the data inside unneeded_data is simply filtered out.
DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD name ON user TYPE string;
DEFINE FIELD user_num ON user TYPE int;
CREATE ONLY user CONTENT {
name: "Billy",
user_num: 100,
unneeded_data: {
some: "other",
needless: "data"
}
};
Output
{
id: user:r38bg4fnp9nurksd06nl,
name: 'Billy',
user_num: 100
}
While convenient, this led to the possibility of a typo leading to unexpected results.
DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD name ON user TYPE string;
DEFINE FIELD user_num ON user TYPE option<int>;
CREATE ONLY user CONTENT {
name: "Billy",
User_num: 100
};
The output shows that the user lacks a value for user_num even though the query above intended to provide this data.
{
id: user:jn4762t7oyure86fe3qk,
name: 'Billy'
}
The same query in SurrealDB 3.0 now returns an error.
"Found field 'User_num', but no such field exists for table 'user'"
To avoid an error when working with data that contains unneeded fields, use .{} (the destructuring operator) to pass on only the necessary fields.
DEFINE TABLE user SCHEMAFULL;
DEFINE FIELD name ON user TYPE string;
DEFINE FIELD user_num ON user TYPE int;
CREATE ONLY user CONTENT {
name: "Billy",
user_num: 100,
unneeded_data: {
some: "other",
needless: "data"
}
}.{
name,
user_num
};
