Back to top
Documentation SurrealQL Statements 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

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
;