Schema evolution

Production databases rarely stay still, and in SurrealDB they tend to move from less to more strict over time as you become more aware of the expected behaviour of your database. Some examples of schema changes are tightening a table from schemaless to schemafull, changing permissions, or preparing an index for removal. SurrealDB's ALTER statement is the lever for mutating definitions in place without always rewriting a full DEFINE block.

For a wholesale replacement, you can always use DEFINE with the OVERWRITE clause.

Tightening a table from schemaless to schemafull

A common pattern is to start with a completely schemaless table, adding defined fields one at a time to ensure that they are present in all records of a certain table. If you are certain that no other fields should be present, you can move the entire table from SCHEMALESS to SCHEMAFULL to ensure that only defined fields are present.

DEFINE TABLE user SCHEMALESS;
DEFINE FIELD name ON TABLE user TYPE string;
CREATE user SET name = "LordofSalty";

-- Now make it schemafull so undeclared fields are rejected
ALTER TABLE user SCHEMAFULL;

Adjusting permissions without restating the whole table

ALTER only needs the parts you are changing. Here the table stays schemafull; only create permission moves from the default to FULL.

DEFINE TABLE user SCHEMAFULL;

ALTER TABLE user PERMISSIONS FOR create FULL;

Idempotent alterations

In scripts, IF EXISTS avoids failing when the resource name was never created in that environment.

ALTER TABLE IF EXISTS user SCHEMAFULL;
DEFINE TABLE IF EXISTS writer;

Where to read more

• ALTER overview

• Schema best practices