DEFINE 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 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 Syntax

DEFINE TABLE @name
    [ DROP ]
    [ SCHEMAFULL | SCHEMALESS ]
    [ AS SELECT @projections
        FROM @tables
        [ WHERE @condition ]
        [ GROUP [ BY ] @groups ]
    ]
    [CHANGEFEED @duration]
    [ PERMISSIONS [ NONE | FULL
        | FOR select @expression
        | FOR create @expression
        | FOR update @expression
        | FOR delete @expression
    ] ]

Example usage

Below shows how you can create a namespace 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. You create, update, delete records in the table as usual

-- Add changefeed for table reading with a duration of 1 day
DEFINE TABLE reading CHANGEFEED 1d;

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
;