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 TABLE
statement. - You must select your namespace and database before you can use the
DEFINE TABLE
statement.
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;
Using DROP
clause
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;
Using CHANGEFEED
clause
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 TABLE
will show the changes that have taken place during this time.
Response without INCLUDE ORIGINAL
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
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 a table as a view which aggregates data from the review table
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;
-- Query the projection
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 theavg_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 bookmarkedSELECT
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 thereview
table, not theproduct
we are also using in the query. Meaning that if you delete areview
theavg_product_review
will reflect that in near real-time. However if you delete aproduct
, it will still show up inavg_product_review
.
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;