DEFINE DATABASE

The DEFINE DATABASE statement allows you to instantiate a named database, enabling you to specify security and configuration options.

Requirements

You must be authenticated as a root owner or editor, or namespace owner or editor before you can use the DEFINE DATABASE statement.
You must select your namespace before you can use the DEFINE DATABASE statement.

Statement syntax

SurrealQL Syntax

DEFINE DATABASE [ OVERWRITE | IF NOT EXISTS ] @name
	[ STRICT ]
	[ COMPATIBILITY 3 | 4 ]
	[ COMMENT @string ]

Example usage

Below shows how you can create a database using the DEFINE DATABASE statement.

-- Specify the namespace for the database
USE NS abcum;

-- Define database
DEFINE DATABASE app_vitalsense;

Using `IF NOT EXISTS` clause

The IF NOT EXISTS clause can be used to define a database only if it does not already exist. You should use the IF NOT EXISTS clause when defining a database in SurrealDB if you want to ensure that the database is only created if it does not already exist. If the database already exists, the DEFINE DATABASE statement will return an error.

It's particularly useful when you want to safely attempt to define a database without manually checking its existence first.

On the other hand, you should not use the IF NOT EXISTS clause when you want to ensure that the database definition is updated regardless of whether it already exists. In such cases, you might prefer using the OVERWRITE clause, which allows you to define a database and overwrite an existing one if it already exists, ensuring that the latest version of the definition is always in use.

-- Create a database if it does not already exist
DEFINE DATABASE IF NOT EXISTS app_vitalsense;

Using `OVERWRITE` clause

The OVERWRITE clause can be used to define a database and overwrite an existing one if it already exists. You should use the OVERWRITE clause when you want to modify an existing database definition. If the database already exists, the DEFINE DATABASE statement will overwrite the existing definition with the new one.

-- Create a database and overwrite if it already exists
DEFINE DATABASE OVERWRITE app_vitalsense;

Defining a `STRICT` database

Available since: v3.0.0

A strict database is one that does not allow a resource to be used unless it has already been defined. The default behaviour in SurrealDB works otherwise, by allowing statements like CREATE, INSERT and UPSERT to work.

CREATE some_new_table;
INFO FOR DATABASE.tables;

The output of the INFO statement shows that a table called some_new_table has been created with a few default clauses.

{
	some_new_table: 'DEFINE TABLE some_new_table TYPE ANY SCHEMALESS PERMISSIONS NONE'
}

Such an operation within a strict database is simply not allowed.

DEFINE DATABASE new_db STRICT;
USE DATABASE new_db;
CREATE some_new_table;

Output

"The table 'some_new_table' does not exist"

Compatibility level

Available since: v4.0.0

A database has a compatibility level, 3 or 4, which decides how a SCHEMAFULL table treats the records it already holds when one of its fields changes:

Level	A field change that existing records may not satisfy	`REMOVE FIELD`
`4`	Refused unless the statement says what the records become, with `MIGRATE EXISTING ROWS`	Removes the field's stored values
`3`	Accepted, and the records keep the values they held, as in SurrealDB 3.x	Removes only the definition

A database created by SurrealDB 4.0 is at level 4 unless the statement says otherwise. A database that a 3.x release created stays at level 3 after an upgrade, so its schema changes behave as they did before. INFO FOR DB shows the level as compatibility.

DEFINE DATABASE legacy_app COMPATIBILITY 3;

To change the level of an existing database, use ALTER DATABASE, and to change the level that databases created later start at, use ALTER SYSTEM.

Level 4 and MIGRATE EXISTING ROWS need the 4.0 record format. A datastore that still writes the 3.x format, such as a TiKV cluster that has not yet been switched with SURREAL_FEATURE_LEVEL=4.0, refuses them.