NEW

SurrealDB 4 is here, introducing SurrealDB Earth and SurrealDS!

Learn more
Course content preview

12: People and indexes

The project planning sample assigns work to employees, and it leans on indexes to keep lookups fast and data honest. This chapter adds people to the schema, then looks at REFERENCE and DEFINE INDEX, including UNIQUE constraints.

Note

SurrealKit-wise, only one small thing is new here: planning a rollout that changes a definition you already have, which comes up in the REFERENCE section. You already know sync for a disposable local database and rollout plan → start → complete for a shared one (parts 10–11). Put the definitions below into database/schema/, then apply them the same way you applied milestone. The new material is the SurrealQL: employees, REFERENCE, and indexes.

Add a table for the people doing the work. Create database/schema/employee.surql:

DEFINE TABLE employee SCHEMAFULL;
DEFINE FIELD name ON employee TYPE string;
DEFINE FIELD email ON employee TYPE option<string> ASSERT $value IS NONE OR string::is_email($value);


An activity will not always have someone assigned yet, so the field on activity needs to allow for that. Add this to database/schema/activity.surql:

DEFINE FIELD assigned_to ON activity TYPE option<record<employee>>;


option means that "nobody assigned yet" is a valid state, not a missing field. If a later requirement says every activity must have an owner, you would tighten the field with a MIGRATE EXISTING ROWS clause (part 3). A SCHEMAFULL table that holds records refuses to narrow a field from option<record<employee>> to record<employee> without one, even if every record already holds a value, because the check compares the two definitions and not the data. The clause says what an activity without an owner should get:

DEFINE FIELD OVERWRITE assigned_to ON activity TYPE record<employee>
    MIGRATE EXISTING ROWS AS require_owner USING $value FALLBACK employee:unassigned;


SurrealKit's sync sends the clause the first time and records the migration's name. After that it applies the definition without the clause, so you can leave it in the file and a later sync will not run the migration again.

Create an employee and assign them to an existing activity so you have something to query:

CREATE employee:ada SET name = "Ada", email = "ada@example.com";
UPDATE activity:kickoff SET assigned_to = employee:ada;


As things stand, nothing stops you deleting employee:ada even though an activity points at her. If you would rather SurrealDB refused that delete, add a REFERENCE clause. Replace the assigned_to line you just wrote rather than adding a second one, for the reason given in part 8: with two definitions for one table and field name, the second silently replaces the first.

DEFINE FIELD assigned_to ON activity TYPE option<record<employee>>
    REFERENCE ON DELETE REJECT;


With employee:ada still assigned to activity:kickoff, the delete is now refused:

DELETE employee:ada;


Output
'Cannot delete `employee:ada` as it is referenced by `activity:kickoff` with an ON DELETE REJECT clause'


ClauseEffect
REFERENCE ON DELETE REJECTCannot delete employee while an activity points at them
ON DELETE CASCADEDeleting the parent removes or clears children (use carefully)
ON DELETE IGNOREParent delete allowed; links may dangle unless you clean up


REFERENCE is catalog metadata that SurrealDB enforces on delete. That is different from a bare record<> field (part 4), which just stores a pointer and does nothing special when the record it points to disappears.

If you apply this change with a rollout instead of a local sync, rollout plan stops here:

error: rollout plan found 1 modified entity and will not plan it automatically without --allow-modified:
  - field:activity.assigned_to (schema/activity.surql)


Every change up to now added something new, and the undo for something new is to remove it. assigned_to already existed, so the undo has to put the old definition back, and SurrealKit wants you to confirm that this is what should happen before it writes the plan. Here it is, so run the plan again with --allow-modified.

A change with a MIGRATE EXISTING ROWS clause, like the require_owner example above, is different, because putting the old definition back would not put the old values back. rollout plan plans it without --allow-modified, and adds a revert_migration step to the rollback phase with an empty using and fallback. Those say how to turn a migrated value back into what it was. rollout lint and rollout start refuse the rollout until you fill them in, or until you delete the step, which makes the rollout one that cannot be rolled back.

DEFINE INDEX project_name ON project FIELDS name;


This supports WHERE name = … on a long list of projects. It does not need to be UNIQUE unless project names must be distinct.

DEFINE INDEX milestone_name_per_project ON milestone FIELDS project, name UNIQUE;


UNIQUE indexes stop duplicates at write time, which is exactly what you want for (project, name). Before adding one on a table that already has data, check for duplicates first (part 3).

We can do this by filtering the grouped result with an outer SELECT:

SELECT * FROM (
    SELECT project, name, count() AS total FROM milestone GROUP BY project, name
) WHERE total > 1;


On a small table you can also just read the counts and skip the outer filter:

SELECT project, name, count() AS total FROM milestone GROUP BY project, name;


If either returns records, backfill or rename the duplicates in a data script before you add the index, whether that is a local sync or a rollout start on a shared database.

Scheduling queries usually filter on start or end:

DEFINE INDEX activity_start ON activity FIELDS start;


Composite indexes are worth adding once your actual query patterns justify them. See DEFINE INDEX for the full syntax.

MechanismEnforcesAlso helps
ASSERT on a fieldRules for a single recordn/a
UNIQUE indexNo duplicate combinations across recordsFast lookups


Uniqueness of (project, name) is a cross-row rule, so it belongs on an index, not a handwritten ASSERT over a subquery.

Adding an index is non-destructive, so it is safe on rollout start (or a local sync). Dropping one is destructive, so save that for rollout complete, once nothing depends on it any more.

Remember that SurrealKit applies whole files. Whenever a later change touches activity.surql, the file is applied again with DEFINE INDEX … OVERWRITE, and SurrealDB rebuilds activity_start from scratch even though the index itself did not change. That costs nothing on a course database, but on a large table it is a reason to keep indexes in a file of their own.

surreal kit sync --user root --pass secret --ns main --db main   # local
# or
surreal kit rollout plan --name add_planning_indexes


  • REFERENCE ON DELETE REJECT (or whichever delete policy fits)

  • DEFINE INDEX for lookups, UNIQUE where duplicates would be a bug

THE PLATFORM

Everything an application and its agents know. Five surfaces, one engine.

IN PRODUCTION

Trusted at scale. Samsung, Nvidia, Verizon, Tencent, and Walmart run on SurrealDB.

14,000+

Developers building on SurrealDB Cloud

4M+

Developers building on SurrealDB worldwide

FROM THE TEAMS

SurrealDB gives us a foundation where we can unify semantic search, knowledge graphs, and AI-driven decision making without stitching together multiple systems. Collapsing responsibility into SurrealDB has become our default engineering posture.
Justin Foley

VP of Engineering, Later