Version: 1.x

`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 [ 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

The following shows how to make a table view using the DEFINE TABLE statement. This is similar to making a view in Relational databases.

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;

DEFINE TABLE statement

Requirements​

Statement syntax​

Example usage​

Using DROP clause​

Using CHANGEFEED clause​

Response without INCLUDE ORIGINAL​

Using IF NOT EXISTS clause Since 1.3.0​

Schemafull tables​

Schemaless tables​

Pre-computed table views​

Defining permissions​

Table with specialized TYPE-clause Since 1.4.0​