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 changes definitions in place without always rewriting a full DEFINE block.
When you are ready to version and apply those changes across environments (for example from CI or with reviewed rollouts), use SurrealKit schema migration - the official .surql-based workflow documented in the Manage section.
For a wholesale replacement, you can always use DEFINE with the OVERWRITE clause.
Changing the type of a field
Changing the TYPE of a field changes its definition, but the records already stored keep their old values. A record whose value does not match the new type can still be read, but any later write to that record fails until the value is converted. To change the type of a field on a table that has data, change the definition and then convert the stored values with an UPDATE:
DEFINE FIELD price ON product TYPE string;
CREATE product:1 SET price = '10';
CREATE product:2 SET price = '12.5';
ALTER FIELD price ON product TYPE number;
-- Fails, because product:1 still holds a string
UPDATE product:1 SET name = 'Lamp';
//- "Couldn't coerce value for field `price` of `product:1`: Expected `number` but found `'10'`"
-- Convert every stored value to the new type
UPDATE product SET price = <number> price;
//- [{ id: product:1, price: 10 }, { id: product:2, price: 12.5 }]The UPDATE fails for the whole table if any one value cannot be converted, such as a price of 'ten', and then changes nothing. On a large table, run it in batches with a WHERE clause, such as UPDATE product SET price = <number> price WHERE price.is_string(), so that each run converts what remains.
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;
REMOVE TABLE IF EXISTS writer;Where to read more
SurrealKit schema migration - sync and rollout workflows for schema files