Evolving SurrealDB Schemas Safely with DEFINE FIELD

The problem: schemaless prototypes need guarantees

When a project starts in SurrealDB, developers often take advantage of its schemaless nature to iterate quickly. As the application matures, however, teams usually want to enforce guarantees such as non‑null values, specific data types, or format constraints (e.g., a valid email address) without taking the database offline or writing complex migration scripts.

How DEFINE FIELD works

The DEFINE FIELD statement attaches a type, optional default value, and a CHECK expression to a field on an existing table. The definition is stored in the schema metadata. On every subsequent INSERT or UPDATE that touches the field, SurrealDB evaluates the type and CHECK expression. Existing rows are left untouched; they continue to return whatever value they currently hold (or the default if the field has never been set). This approach lets you evolve the schema incrementally while keeping the database available for writes.

Worked example: adding an email field to a user table

1. Start a local SurrealDB instance (e.g., surreal start --log trace) and connect with the SurrealQL CLI. You need administrator privileges to create namespaces, databases, and schema definitions.

   surreal sql -u root -p root

2. Create a test namespace and database, then a simple user table with only a name field.

   USE NS test; USE DB demo;
   DEFINE TABLE user SCHEMAFULL;
   DEFINE FIELD name ON user TYPE string;
   INSERT INTO user SET name = 'Alice';
   INSERT INTO user SET name = 'Bob';
   

3. Verify that selecting the new email field returns NULL (or undefined) for existing rows.

   SELECT name, email FROM user;

4. Attach the email definition with a default empty string and a simple regex CHECK.

   DEFINE FIELD email ON user TYPE string DEFAULT '' CHECK (email ~ '.+@.+\..+');
   

5. Attempt to insert a valid email – the write should succeed.

   INSERT INTO user SET name = 'Carol', email = '[contact removed]';
   

6. Attempt to insert an invalid email – SurrealDB should reject the transaction and return an error similar to "Constraint check failed for field email".

   INSERT INTO user SET name = 'Dave', email = 'not-an-email';
   

7. Re‑query the original rows to confirm they still show the default empty string for email, proving that existing data was not altered.

   SELECT name, email FROM user WHERE name IN ('Alice', 'Bob');
   

8. Optionally, update one of the old rows to set a proper email; the CHECK will pass and the row will now store the supplied value.

   UPDATE user SET email = '[contact removed]' WHERE name = 'Alice';
   

Trade‑offs and limitations

• Validation occurs only on write operations. Legacy rows that violate the new CHECK (for example, a row that already contains an malformed email) remain readable until the row is rewritten. This means you cannot rely on the constraint for existing data without a back‑fill or migration step.

• Complex CHECK expressions (e.g., heavy regular expressions or calls to user‑defined functions) are evaluated per write and can add latency, especially under high‑throughput workloads. Monitor write latency and error rates after adding a definition.

• Removing a field later with REMOVE FIELD does not erase the column’s values from storage; the data stays accessible until you explicitly clear it or drop the table.

Putting it into practice

Begin with a low‑risk table, use a permissive default (like an empty string) and a simple CHECK. Watch the SurrealDB error log for rejected writes; as confidence grows, tighten the constraint or run a background job to back‑fill non‑conforming rows. Once all rows satisfy the CHECK, you can consider making the field NOT NULL by issuing another DEFINE FIELD with a stricter default or by updating the rows and then removing the default.

This incremental approach lets you evolve your SurrealDB schema safely, avoiding downtime while gradually strengthening data guarantees.