REMOVE

The REMOVE statement is used to remove resources such as databases, tables, indexes, events and more. Similar to an SQL DROP statement.

Statement syntax

REMOVE [
    ACCESS    [ IF EXISTS ] @name ON [ NAMESPACE | DATABASE ]
  | ANALYZER  [ IF EXISTS ] @name
  | API       [ IF EXISTS ] @name
  | BUCKET    [ IF EXISTS ] @name [ ON [ ROOT | NAMESPACE | DATABASE ] ]
  | CONFIG    [ IF EXISTS ] [ GRAPHQL | API | DEFAULT ]
  | DATABASE  [ AND EXPUNGE ] [ IF EXISTS ] @name
  | EVENT     [ IF EXISTS ] @name ON [ TABLE ] @table
  | FIELD     [ IF EXISTS ] @name ON [ TABLE ] @table
  | FUNCTION  [ IF EXISTS ] @name
  | INDEX     [ IF EXISTS ] @name ON [ TABLE ] @table
  | NAMESPACE [ AND EXPUNGE ] [ IF EXISTS ] @name
  | PARAM     [ IF EXISTS ] @name
  | TABLE     [ AND EXPUNGE ] [ IF EXISTS ] @name
  | TYPE      [ IF EXISTS ] @name
  | USER      [ IF EXISTS ] @name ON [ ROOT | NAMESPACE | DATABASE ]
]

Example usage

Basic usage

The following queries show an example of how to remove resources.

REMOVE NAMESPACE surrealdb;

REMOVE DATABASE blog;

REMOVE USER writer ON NAMESPACE;

REMOVE USER writer ON DATABASE;

REMOVE ACCESS token ON NAMESPACE;

REMOVE ACCESS user ON DATABASE;

-- Without ON, removes a bucket defined on the database
REMOVE BUCKET my_bucket;

REMOVE BUCKET shared ON ROOT;

REMOVE EVENT new_post ON TABLE article;

-- Only works for Schemafull tables (i.e. tables with a schema)
REMOVE FIELD tags ON TABLE article;

REMOVE INDEX authors ON TABLE article;

-- Fails if a full-text index still references this analyzer (remove the index first)
REMOVE ANALYZER example_ascii;

REMOVE FUNCTION fn::update_author;

REMOVE PARAM $author;

REMOVE TABLE article;

-- Fails while another definition, such as a field or a function, names the type
REMOVE TYPE priority;

Using if exists clause

The following queries show an example of how to remove resources using the IF EXISTS clause, which will only remove the resource if it exists.

REMOVE NAMESPACE IF EXISTS surrealdb;

REMOVE DATABASE IF EXISTS blog;

REMOVE USER IF EXISTS writer ON NAMESPACE;

REMOVE USER IF EXISTS writer ON DATABASE;

REMOVE ACCESS IF EXISTS token ON NAMESPACE;

REMOVE ACCESS IF EXISTS user ON DATABASE;

REMOVE BUCKET IF EXISTS my_bucket;

REMOVE EVENT IF EXISTS new_post ON TABLE article;

REMOVE FIELD IF EXISTS tags ON TABLE article;

REMOVE INDEX IF EXISTS authors ON TABLE article;

REMOVE ANALYZER IF EXISTS example_ascii;

REMOVE FUNCTION IF EXISTS fn::update_author;

REMOVE PARAM IF EXISTS $author;

REMOVE TABLE IF EXISTS article;

REMOVE TYPE IF EXISTS priority;

Usage in table views

Available since: v3.0.0

A table used as a source for a table view cannot be removed until the table view itself has been removed.

DEFINE TABLE pc;
DEFINE TABLE pc_agg AS SELECT count(), class FROM pc GROUP BY class;
CREATE |pc:3| SET class = "Wizard";
CREATE |pc:10| SET class = "Warrior";
SELECT * FROM pc_agg;
//- Error: pc_agg requires pc to work
REMOVE TABLE pc;
REMOVE TABLE pc_agg;
-- pc_agg is now gone, pc can be removed too
REMOVE TABLE pc;

The SELECT * FROM pc_agg query shows that the table view is pulling data from the pc table. As long as the pc table exists, pc_agg cannot be removed.

-------- Query --------

[
  { 
    class: 'Warrior', 
    count: 10, 
    id: pc_agg:['Warrior'] 
  }, 
  { 
    class: 'Wizard', 
    count: 3, 
    id: pc_agg:['Wizard'] 
  }
]

-------- Query --------

'Invalid query: Cannot delete table `pc` on which a view is defined, table(s) `pc_agg` are defined as a view on this table.'

Behaviour after removal

While all REMOVE statements remove the definition for a resource, some resources have additional actions when removed. They are:

• •

  REMOVE DATABASE: This effectively deletes the database by removing the index stores, deleting all definitions, and clearing the cache. Since SurrealDB 3.2.0, and 2.7.0 on the 2.x line, this definition removes the database from the catalog immediately. This is followed by physical deletion of the underlying keys which is deferred to a background reaper (tune with SURREAL_RECLAIM_INTERVAL and, on 3.x, SURREAL_RECLAIM_GRACE), allowing REMOVE DATABASE to return quickly even for large tenants. Recreating the same database name yields a fresh, empty database. On 2.x, recreating it while more than 10,000 keys of the old database remain fails until the reaper catches up, as described in Reusing a removed name.

• •

  REMOVE NAMESPACE: Same deferred reclaim as REMOVE DATABASE since SurrealDB 3.2.0 and 2.7.0, in addition to performing a remove on each database inside the namespace.

• •

  REMOVE TABLE: Similar to the two previous statements but on a single table, and will fail if a table view depends on it. Removing a table will also send a KILL notification for each live query defined on it.

• •

  REMOVE INDEX: This statement also removes the index store cache and index data. Since 3.2.0, it removes the index definition immediately and thereafter reclaims index data in the background in the same way as namespace and database removal. If you are considering removing an index but want to test the behaviour out first, use an ALTER INDEX PREPARE REMOVE statement. This will decommission the index, after which you can test out queries to see their behaviour as they would function after the index is removed. If acceptable then the index can then be removed, or the change can be reverted by rebuilding the index. Standard, unique, spatial, full-text and vector indexes share one store of document IDs for each table, and COUNT indexes do not use it. From SurrealDB 4.0, when the removed index was the last one on the table to use that store, a DEFINE INDEX or REBUILD INDEX of an index that uses it on the same table, while that background reclaim is still running, finishes the reclaim first and then succeeds. Inside BEGIN ... COMMIT or a transaction that a client holds open, the statement fails instead with a retryable transaction conflict, and a retry succeeds once the reclaim is complete.

• •

  REMOVE TYPE: This statement fails while another definition names the type, and the error names that definition. A field names it in its type, or in a cast in its DEFAULT, VALUE or ASSERT clause, a function in its arguments or return type, and another type in its body, ASSERT or DEFAULT. An event, a view, a table or field permission, a parameter's permission, an API, an access method's SIGNIN or AUTHENTICATE clause, the COUNT WHERE condition of an index and a bucket's permission also name it when one of their expressions casts to it. Remove or change those definitions first.

On a versioned SurrealKV datastore, a REMOVE TABLE deletes only the current version of the table's data, so a SELECT ... VERSION at a time before the removal still reads the records. REMOVE TABLE AND EXPUNGE also deletes every earlier version of the table's records, its reference keys and the reference caches that point at it, so such a query no longer finds the table. AND EXPUNGE comes directly after the keyword, before IF EXISTS and the name, and REMOVE NAMESPACE and REMOVE DATABASE accept it as well.

REMOVE TABLE AND EXPUNGE article;

Another REMOVE statement to note is REMOVE FIELD. It removes the definitions of the fields nested inside the field as well, at any depth, so REMOVE FIELD address ON person also removes the definition of address.city. This applies to every table and at every compatibility level. In SurrealDB 3.x, each nested definition stays until it is removed with its own REMOVE FIELD.

On a SCHEMAFULL table in a database at compatibility level 4, the default for databases created from SurrealDB 4.0, it also removes the field's stored values:

-- In a database at compatibility level 4
DEFINE TABLE person SCHEMAFULL;
DEFINE FIELD name ON person TYPE string;
CREATE person:one SET name = "Billy";
REMOVE FIELD name ON person;

SELECT * FROM person;

[{ id: person:one }]

In every other case it removes only the definition, and the stored values stay until each record is updated. That covers a SCHEMALESS table at either level, and any table in a database at level 3, which is how SurrealDB 3.x behaves. To remove the existing data, perform an UPDATE or UPSERT statement that uses UNSET on the field or sets the field's value to NONE.

DEFINE FIELD name ON person TYPE string;
CREATE person:one SET name = "Billy";
REMOVE FIELD name ON person;

-- 'name' data is still there
SELECT * FROM person;
-- Does nothing
UPDATE person;
-- Unsetting the field removes the 'name' data
UPDATE person UNSET name;

At level 3, a SCHEMAFULL table can still be read while its records hold a field that is no longer defined, but an update to such a record fails until the field is unset:

-- In a database at compatibility level 3
DEFINE TABLE person SCHEMAFULL;
DEFINE FIELD name ON person TYPE string;
CREATE person:one SET name = "Billy";
REMOVE FIELD name ON person;

UPDATE person;

"Found field 'name', but no such field exists for table 'person'"