DEFINE
statement
TABLE
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, namespace, or database 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
DEFINE TABLE @name
[ DROP ]
[ SCHEMAFULL | SCHEMALESS ]
[ AS SELECT @projections
FROM @tables
[ WHERE @condition ]
[ GROUP [ BY ] @groups ]
]
[ PERMISSIONS [ NONE | FULL
| FOR select @expression
| FOR create @expression
| FOR update @expression
| FOR delete @expression
] ]
Example usage
The following expression shows the simplest way to use 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. This would be like telling the database to drop any table that has the given name and replace it with a new one of the same name.
-- Drop the table if it exists and create a new one with the same name.
DEFINE TABLE reading DROP;
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
ASSERT $value != NONE;
DEFINE FIELD lastName ON TABLE user TYPE string
ASSERT $value != NONE;
DEFINE FIELD email ON TABLE user TYPE string
ASSERT $value != NONE AND 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.
CREATE user SET firstName = 'Tobie', lastName = 'Hitchcock', email = 'Tobie.Hitchcock@surrealdb.com', photoURI = 'photo/yxCFi22Jw2.webp';
-- 2: Statement will fail because photoURI is not a defined field.
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
ASSERT $value != NONE;
DEFINE FIELD lastName ON TABLE user TYPE string
ASSERT $value != NONE;
DEFINE FIELD email ON TABLE user TYPE string
ASSERT $value != NONE AND 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.
The following shows how to make a projection using the DEFINE TABLE
statement. This is similar to making a view in a RDBMS.
-- 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;
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
;