Schema internals and migrations

Welcome to Schema internals and migrations, a SurrealDB University course on how schema lives inside the database, how DEFINE statements shape it, and how to change the schema of a live database without unpleasant surprises.

SurrealDB's philosophy on defined schema is that you can have as little or as much of it as you like. With schemaless as the default, you can run a full database without ever touching a DEFINE statement if that is what you want. However, production systems usually end up wanting typed fields, indexes, permissions, and a plan for evolving all of that safely.

This course covers what the database stores, how to tighten or loosen constraints, and how to migrate data without taking downtime you did not mean to take.

What you'll learn

By the end you should be able to:

• •

  Read INFO FOR TABLE / INFO FOR DB output, and know how that differs from INFO … STRUCTURE

• •

  Choose between schemaless and schemafull tables, and use FLEXIBLE and union types when full strictness gets in the way

• •

  Change field types on live data with backfills, union types, and gradual normalisation

• •

  Use field clauses (DEFAULT, VALUE, COMPUTED, ASSERT, REFERENCE) to keep logic close to the data and cut down on code elsewhere

• •

  Decide when hand-written SurrealQL migrations are enough, and when SurrealKit is worth adopting

• •

  Grow a project planning schema from one table to graph relations, COMPUTED roll-ups, indexes, and DEFINE EVENT, using SurrealKit sync, rollouts, and test along the way

Course outline

The schema used in this course

The latter part of this course builds a project planning schema, one of many sample schemas divided by industry that you can find in the documentation. While a modest size at about 25 lines in length, it nevertheless represents the core of the sort of schema you will see in production.

This schema was chosen because it is fairly general but contains a lot of interesting tidbits such as COMPUTED fields, assertions, graph links, and closure functions. You can see what the schema looks like below. In the latter half of this course we will be building it up over several files instead of one to demonstrate how SurrealKit is able to combine them all and perform syncs or rollouts every time a change is made.

-- Activities in a project schedule
DEFINE TABLE activity SCHEMAFULL;
DEFINE FIELD name         ON activity TYPE string;
DEFINE FIELD description  ON activity TYPE option<string>;
DEFINE FIELD start        ON activity TYPE datetime;
DEFINE FIELD end          ON activity TYPE datetime;
DEFINE FIELD duration     ON activity COMPUTED end - start;
DEFINE FIELD progress     ON activity TYPE float ASSERT $value IN 0.0..=1.0;
DEFINE FIELD assigned_to  ON activity TYPE option<record<employee>>;
DEFINE FIELD followed_by  ON activity COMPUTED <-depends_on<-activity;

-- Milestones
DEFINE TABLE milestone SCHEMAFULL;
DEFINE FIELD project      ON milestone TYPE record<project>;
DEFINE FIELD activities   ON milestone TYPE array<record<activity>>;
DEFINE FIELD name         ON milestone TYPE string;
DEFINE FIELD last_updated ON milestone VALUE time::now();
DEFINE FIELD progress     ON milestone COMPUTED math::mean(activities.progress);
DEFINE FIELD is_complete  ON milestone COMPUTED activities.all(|$a| $a.progress > 0.95);

-- Graph-style dependency links
DEFINE TABLE depends_on SCHEMAFULL TYPE RELATION IN activity OUT activity;
DEFINE TABLE activity_of SCHEMAFULL TYPE RELATION IN activity OUT project;

Before you start

You should be comfortable running SurrealQL in the CLI or SurrealDB Studio, and with basic CREATE / SELECT / UPDATE. If any of that sounds unfamiliar, the Tour of SurrealDB and SurrealDB Fundamentals are good places to start first.