Chapter 9: A peaceful interlude

Time elapsed: 2y

Two years have passed since Landevin joined. You have set up a service for anyone who wishes to get information from the database. At first you allowed visitors to print the information, but soon ran out of ink and don’t know how to produce the “cartridges” that printers need. They certainly don’t use regular ink! Visitors now bring their own paper and write down what they see on the computer screens.

‎

This is the case with a lot of the ancient technology, like those “DVDs” in the library that nobody knows how to use. There is certainly no device anywhere that fits them. On the other hand, a lot of ancient knowledge has been useful from day one. Simple facts like “lead is poisonous” and “boiling water makes it clean” have already led to a much healthier — and faster growing — population.

‎

Landevin is now a core member of the team. He has a good understanding of the database, but spends more time out in the open — which you envy him a bit for. He has been spreading the word about the coming new era, even to the cities across the bay.

‎

Landevin has shown an interest in the mysterious grey book you discovered so long ago, and keeps reading it over lunch. One day he asks you if he can borrow it “for inspiration” and to try to figure out the formula. Sure, why not? You lend it to him and get back to other tasks.

‎

You have doubled the guard outside the tunnel on the advice of the Toria government. They trust the local people, but other cities might react differently to the news of your project. Better safe than sorry.

‎

The mountains to the east keep the rest of the world ignorant for now. They will find out one day, but there is much work to do in the meantime…

The last chapter marked the end of our first small multi-chapter project, and it’s time to relax a bit after all that practice with graph queries. Now that we are somewhat familiar with SurrealDB, let’s turn to a somewhat more philosophical discussion. What sort of database is SurrealDB anyway?

What multi-model means

SurrealDB a database written in Rust that is what is known as a multi-model database. Multi-model means that you have the choice of one or more data models to choose from.

Fortunately, we have already used all of SurrealDB’s data models! And this is why we’ve waited until Chapter 9 to introduce the subject, because explaining concepts like this is always easier after you’ve had some practical experience.

The major models used in databases are:

Document databases, which use JSON-like documents to store and query data.
Relational databases, which use a strictly defined schema to specify the relationship between one table and another.
Graph databases, which use nodes and edges to represent and store data.

SurrealDB has all three.

You can think of the first model as SurrealDB’s default state, because a running SurrealDB instance will always be ready to store and query data of this nature. Thus, you can start out with schemaless records (document database style):

CREATE town:toria SET
    name = "Toria",
    population = 21520;

And later decide to define a schema to make the data more predictable (relational database style):

-- Strictly defining just a single field
DEFINE FIELD name ON TABLE town type string;

-- Now this CREATE statement won't work
CREATE town:black_bay SET
    name = 100,
    population = 4304;

Response
"Found 100 for field `name`, with record `town:black_bay`, but expected a string"

And you can add and query relations as you see fit (graph database style) like this:

CREATE person:harris_thomas SET
	name = "Harris Thomas",
	salary = 100;
CREATE town:black_bay SET
	name = 'Black Bay',
	population = 4304,
	mayor = person:harris_thomas;
SELECT name, mayor.salary FROM town;

Response
[
	{
		mayor: {
			salary: 100
		},
		name: 'Black Bay'
	},
	{
		mayor: {
			salary: NONE
		},
		name: 'Toria'
	}
]

Or like this:

CREATE town:sukh SET
	name = 'Sukh',
	population = 3074;
RELATE [town:black_bay, town:sukh]->suburb_of->town:toria;
SELECT name, <-suburb_of<-town AS suburbs FROM town;

Response
[
	{
		name: 'Black Bay',
		suburbs: []
	},
	{
		name: 'Sukh',
		suburbs: []
	},
	{
		name: 'Toria',
		suburbs: [
			town:black_bay,
			town:sukh
		]
	}
]

One point to remember is that all three of these models have to do with the query language, and are unrelated to the method you use to store your data. SurrealDB documentation calls this the “Query layer” and the “Storage layer”, which are kept completely separate from each other. As a result, all of the queries above have the same behaviour, regardless of whether we keep the data in memory, or use the available backends of SurrealKV, RocksDB, TiKV, or FoundationDB.

Flexible by default but as strict as you like

A philosophical point about SurrealDB is that it is flexible by default, with as much strictness added on as you like. Strictness tends to increase over time as a project develops and you understand what sort of data you want to allow and disallow.

On the table level, there are generally three levels of strictness.

SCHEMALESS

This is the default in which anything goes. In this mode, SurrealDB will not only stay out of your way, but unseen to us will use DEFINE statements to make a query work. We can demonstrate this by opening up a new sandbox inside Surrealist, and using some INFO FOR commands.

We are quite familiar with INFO FOR DB already, but there are two levels above this: INFO FOR NS to see the databases inside a namespace, and INFO FOR ROOT to see all of the defined namespaces.

Using those three INFO FOR statements will all show nothing, as expected.

INFO FOR ROOT;
INFO FOR NS;
INFO FOR DB;

Response
-------- Query --------

{
	namespaces: {},
	users: {}
}

-------- Query --------

{
	accesses: {},
	databases: {},
	users: {}
}

-------- Query --------

{
	accesses: {},
	analyzers: {},
	functions: {},
	models: {},
	params: {},
	tables: {},
	users: {}
}

But if we type CREATE person SET name = "Aeon" and then try the same three INFO statements, all of a sudden the output is no longer empty! This is because:

A person table must be defined to create a person record,
but a database must exist to define a person table on it,
and a namespace must exist to define a database inside it.

SurrealDB did all of this for us, as the three INFO FOR statements now show.

CREATE person SET name = "Aeon";
INFO FOR ROOT;
INFO FOR NS;
INFO FOR DB;

Response
-------- Query --------

{
	namespaces: {
		sandbox: 'DEFINE NAMESPACE sandbox'
	},
	users: {}
}

-------- Query --------

{
	accesses: {},
	databases: {
		sandbox: 'DEFINE DATABASE sandbox'
	},
	users: {}
}

-------- Query --------

{
	accesses: {},
	analyzers: {},
	functions: {},
	models: {},
	params: {},
	tables: {
		person: 'DEFINE TABLE person TYPE ANY SCHEMALESS PERMISSIONS NONE'
	},
	users: {}
}

Note, however, that a SCHEMALESS table doesn’t need any field definitions to work. So SurrealDB did not define a name field for us when we created a person record, as the INFO FOR TABLE statement shows. There’s no need to define any fields on a table when a table by nature will accept anything.

CREATE person SET name = "Aeon";
INFO FOR TABLE person;

Response
{
	events: {},
	fields: {},
	indexes: {},
	lives: {},
	tables: {}
}

SCHEMALESS with defined fields

To make a SCHEMALESS table more strict, we can define its fields and its types. Take a look at the following field statements for a person table.

DEFINE FIELD name      ON TABLE person TYPE string;
DEFINE FIELD job_title ON TABLE person TYPE option<string>;
DEFINE FIELD friends   ON TABLE person TYPE option<array<record<person|cat|dog>>>;

There is a variety of flexibility inside these statements, from most to least flexible:

A person might or might not have friends (so it’s an option), and a friend can be one of three record types: a person, cat, or dog. The | operator can be used whenever you want to accept more than a single type: bool|string, string|object|array<string>, and so on.
A person might or might not have a job title, but if present then it must be a string and nothing else.
A person must have a name, and it must be a string.

But outside of these three defined fields, person itself is still SCHEMALESS so anything goes! The field definitions won’t stop you from creating a record like the one in the query following them in this next example.

DEFINE FIELD name      ON TABLE person TYPE string;
DEFINE FIELD job_title ON TABLE person TYPE option<string>;
DEFINE FIELD friends   ON TABLE person TYPE option<array<record<person|cat|dog>>>;

CREATE person SET
    name = "Citizen of Toria",
    friend = snake:one,
    job_titles = ["Snake seller", "Coffee shop owner"];

SCHEMAFULL

If you add a DEFINE TABLE person SCHEMAFULL statement before the DEFINE FIELD statements above, there will no longer be any way to set a value for a field that isn’t already defined.

DEFINE TABLE person SCHEMAFULL;
DEFINE FIELD name ON TABLE person TYPE string;
DEFINE FIELD friends ON TABLE person TYPE option<array<record<person|cat|dog>>>;
DEFINE FIELD job_title ON TABLE person TYPE option<string>;

Note, however, that the “Citizen of Toria” record shown above can still be created because it satisfies the minimum requirements for the fields (it has a name).

DEFINE TABLE person SCHEMAFULL;
DEFINE FIELD name ON TABLE person TYPE string;
DEFINE FIELD friends ON TABLE person TYPE option<array<record<person|cat|dog>>>;
DEFINE FIELD job_title ON TABLE person TYPE option<string>;

CREATE person SET
    name = "Citizen of Toria",
    friend = snake:one,
    job_titles = ["Snake seller", "Coffee shop owner"];

However, only the id and name fields will be populated this time. SurrealDB will simply ignore the other two fields in the CREATE statement.

Response
[
	{
		id: person:nvwu65qmdm5ohfs1gxd4,
		name: 'Citizen of Toria'
	}
]

A SCHEMAFULL table can be a convenient way to filter outside data that might vary in structure or include unneeded information. The following example shows some pretend data that has come in to create Landevin’s person record, which is filled with a lot of information that we don’t need.

LET $pretend_outside_data = {
    name: "Landevin",
    date_of_birth: "2745-09-01",
    friends: [person:aeon, person:bronwyn, cat:mr_meow],
    job_title: "Administrator / Explorer",
    hobbies: ["hiking", "swordsmanship"],
    alignment: "Chaotic good",
    mother: person:landevins_mom,
    father: person:landevins_father
};
CREATE person CONTENT $pretend_outside_data;

Thanks to the SCHEMAFULL table and its defined fields, SurrealDB will keep all the info for the defined fields that we care about and ignore the rest.

Response
[
	{
		friends: [
			person:aeon,
			person:bronwyn,
			cat:mr_meow
		],
		id: person:6xlaz3hms1hdvzx02hn2,
		job_title: 'Administrator / Explorer',
		name: 'Landevin'
	}
]

Other combinations of strictness and flexibility

Besides the three general levels of strictness mentioned above, there are a lot of other areas that allow you to refine your database schema.

Setting a table to TYPE NORMAL and TYPE RELATION

A table by default can be used as either a normal table or a relation table. This means that there is nothing stopping you from using the same name as a relation table to create a regular record.

CREATE person:the_nobleman SET name = "The Nobleman";
CREATE person:adelaide     SET name = "Adelaide";
RELATE person:the_nobleman->parent_of->person:adelaide;
CREATE parent_of SET name = "I'm the parent of Adelaide!";
SELECT in.name AS parent_name, out.name AS daughter_name FROM parent_of;

The response makes it look like we have two person records that lack a value for the name field, when really it’s just the random parent_of non-relation record that is showing up in the query!

Response
[
	{
		daughter_name: NONE,
		parent_name: NONE
	},
	{
		daughter_name: 'Adelaide',
		parent_name: 'The Nobleman'
	}
]

This can be prevented with a quick TYPE RELATION in the table declaration.

DEFINE TABLE parent_of TYPE RELATION;

With this in place, the attempt to create a random parent_of record fails, and the following query shows only the parent_of relation between the Nobleman and his second daughter.

DEFINE TABLE parent_of TYPE RELATION;
CREATE person:the_nobleman SET name = "The Nobleman";
CREATE person:adelaide     SET name = "Adelaide";
RELATE person:the_nobleman->parent_of->person:adelaide;
CREATE parent_of SET name = "I'm the parent of Adelaide!";
SELECT in.name AS parent_name, out.name AS daughter_name FROM parent_of;

Response
-------- Query --------

'Found record: `parent_of:5d13w4hqkjzticd7bz5b` which is a relation, but expected a `target_type`'

-------- Query --------

[
	{
		daughter_name: 'Adelaide',
		parent_name: 'The Nobleman'
	}
]

Similarly, if you define a table as a TYPE NORMAL, it won’t be able to be used in a RELATE statement.

DEFINE TABLE person TYPE NORMAL;

For further type safety on TYPE RELATION tables, you can set what the IN and OUT types must be.

DEFINE TABLE parent_of TYPE RELATION IN person OUT person;
CREATE person:the_nobleman SET name = "The Nobleman";
CREATE person:adelaide     SET name = "Adelaide";
CREATE book:demian;
RELATE person:the_nobleman->parent_of->person:adelaide;
RELATE person:the_nobleman->parent_of->book:demian;

With the last query, we were unable to make The Nobleman the parent of a book (it must be a person).

Response
'Found book:demian for field `out`, with record `parent_of:8eq51zjwoqffs9z8e1py`, but expected a record<person>'

SCHEMAFULL with FLEXIBLE objects

Let’s say that Aeon wants to define a building table to keep track of all the buildings in a town or city, but still isn’t sure how strictly to define it.

As we learned before, with SCHEMAFULL we can still add some flexibility with | for multiple types or option for a field that might or might not have data. On top of that, using the object type gives us the option to accept any object type at all. That gives us a schema that looks like this for building:

DEFINE TABLE building SCHEMAFULL;
DEFINE FIELD identifier ON TABLE building TYPE string | number;
DEFINE FIELD metadata ON TABLE building TYPE option<object>;

This lets us create a building with a name:

CREATE building SET identifier = "Mayor's House";

Response
[
	{
		id: building:29pcok5o9rib5iicgt1g,
		identifier: "Mayor's House"
	}
]

Or a building with a number instead of a name as its identifier, and some metadata instead to give some idea of what sort of building it is.

CREATE building SET identifier = 0, metadata = {
    location: "Central Toria next to Mayor's House",
    next_to: (SELECT * FROM building WHERE name = "Mayor's House"),
    floors: 3
};

But wait a second…where did the metadata go?

Response
[
	{
		id: building:mdu2ptma2az6qwufj6zf,
		identifier: 0,
		metadata: {}
	}
]

The issue here is that we are using a SCHEMAFULL table. The metadata field does indeed accept an object, but none of its fields have been defined. And when SurrealDB comes across a field that hasn’t been defined, it will simply ignore it instead of generating an error. The metadata field expected an object, we gave it an object, but no fields were defined so they were all ignored. In other words, this field was only ever able to store an empty object!

The FLEXIBLE keyword can be used here to override this behaviour. Our schema declaration is identical except for this new addition:

DEFINE TABLE building SCHEMAFULL;
DEFINE FIELD identifier ON TABLE building TYPE string | number;
DEFINE FIELD metadata ON TABLE building FLEXIBLE TYPE option<object>;

As a result, the object for the metadata field can now hold anything we like. Let’s try the query with the metadata again:

CREATE building SET identifier = "Mayor's House";
CREATE building SET identifier = 0, metadata = {
    location: "Central Toria next to Mayor's House",
    next_to: (SELECT * FROM building WHERE identifier = "Mayor's House"),
    floors: 3
};

The output is now what we hoped to see.

Response
[
	{
		id: building:q0gmuxi1o29daankmfbv,
		identifier: 0,
		metadata: {
			floors: 3,
			location: "Central Toria next to Mayor's House",
			next_to: [
				{
					id: building:s5kylxossy75s37ix956,
					identifier: "Mayor's House"
				}
			]
		}
	}
]

There are even more ways to refine the behaviour of this building table! For example, we might want to make the metadata object a little bit stricter by making sure that its floors field has to be a number, and that the location description can’t be more than 50 characters long - if they exist. The DEFINE FIELD statement also works on fields inside flexible objects by using the dot operator. In our case, we can use DEFINE FIELD metadata.floors to ensure that this part of the object is strictly defined. Meanwhile, an ASSERT on metadata.location will allow us to ensure that the string - if it exists - is not too long.

DEFINE TABLE building SCHEMAFULL;
DEFINE FIELD name              ON TABLE building TYPE string | number;
DEFINE FIELD metadata          ON TABLE building FLEXIBLE TYPE option<object>;
DEFINE FIELD metadata.floors   ON TABLE building TYPE option<number>;
DEFINE FIELD metadata.location ON TABLE building TYPE option<string> ASSERT string::len($value) < 50;

CREATE building SET name = 0, metadata = {
    location: "Central Toria next to Mayor's House",
    floors: 2,
    next_to: (SELECT * FROM building WHERE name = "Mayor's House")
};

Making fields read-only

You can ensure that a field can only be created once and never modified by adding a READONLY clause. The code below shows two examples of this:

A field called created_at which should be automatically generated and never modified.
Another field called legacy_id, perhaps the IDs from another database that is being moved to SurrealDB by a developer who has decided that random IDs are a better solution.

DEFINE FIELD created_at  ON TABLE user READONLY VALUE time::now();
DEFINE FIELD legacy_id   ON TABLE user TYPE string READONLY;
// Plus make sure the old IDs are unique too
DEFINE INDEX only_unique ON TABLE user FIELDS legacy_id UNIQUE;

Non-defined fields after move to SCHEMAFULL

What happens if you create a record without a schema, but then define the table as schemafull? Is the data inside fields not in the schema now deleted?

The answer to this is technically yes, but not right away.

The query below will explain what this means. It uses the same building schema as the sample directly above, but also has a building record that snuck in before the schema was defined.

CREATE building SET
	identifier = "Toria apartments",
    height = 10,
    kind = "apartment";

DEFINE TABLE OVERWRITE building SCHEMAFULL;
DEFINE FIELD identifier ON TABLE building TYPE string | number;
DEFINE FIELD metadata ON TABLE building FLEXIBLE TYPE option<object>;

CREATE building SET
    height = 20,
    kind = "skyscraper";

SELECT * FROM building;

The second building record we try to create fails, because at this point the table requires an identifier field. However, look at the output for SELECT * FROM building! The kind and height fields for the first record is still there.

Response
[
	{
		height: 10,
		id: building:tgemvaf9raf5u6ng98bs,
		kind: 'apartment',
		identifier: 'Toria apartments'
	}
]

The only reason why these fields are still around is that DEFINE simply sets the rules for a schema, a SELECT query is a read operation.

But once we perform a write operation like UPDATE on building, that’s the end of the fields height and kind.

CREATE building SET
	identifier = "Toria apartments",
    height = 10,
    kind = "apartment";

DEFINE TABLE OVERWRITE building SCHEMAFULL;
DEFINE FIELD identifier ON TABLE building TYPE string | number;
DEFINE FIELD metadata ON TABLE building FLEXIBLE TYPE option<object>;

UPDATE building;
SELECT * FROM building;

Response
[
	{
		id: building:tgemvaf9raf5u6ng98bs,
		identifier: 'Toria apartments'
	}
]

So make sure to confirm whether any data would be lost when moving to a strict schema. For the record above, we could have quickly taken the height and kind data and put them into the metadata field, which is a flexible object. The parameter $this gives us access to the current record inside an UPDATE, so we can use it to grab the data before it is gone.

CREATE building SET
	identifier = "Toria apartments",
    height = 10,
    kind = "apartment";

UPDATE building SET metadata = {
    height: $this.height,
    kind: $this.kind
};

Response
[
	{
		id: building:sls8he2uz92cyh2bpe64,
		metadata: {
			height: 10,
			kind: 'apartment'
		},
		identifier: 'Toria apartments'
	}
]

Strictness across the entire database

You might have noticed that the declarations so far only relate to tables, and don’t make the rest of SurrealDB’s behaviour strict by default. No matter how many SCHEMAFULL tables you have, creating a single record for a table that isn’t defined will cause SurrealDB to define a new table to allow the operation to happen.

To prevent this from happening, you can run SurrealDB with strictness by default. A single --strict parameter to the surreal start command is all that is needed. In this mode, anything that is not defined will not be allowed to exist. Let’s see what the behaviour looks like now with the following command:

surreal start --user root --password root --strict

Then sign in with Surrealist or the CLI using a namespace called ns, and a database called db: surreal sql --user root --pass root --namespace ns --database db.

Having done that, let’s create a person without giving it any fields.

CREATE person;

Response
"The namespace 'namespace' does not exist"

This time SurrealDB did not create anything unknown to us in order to let the query happen! If we didn’t define it, it doesn’t exist.

We saw a DEFINE NAMESPACE ns statement above that SurrealDB used when we were outside of strict mode, so let’s use that. You can probably guess what the next error message will be:

DEFINE NAMESPACE ns;
CREATE person;

Response
"The database 'db' does not exist"

So we’ll need to define a database. And after that we will get an error that the person table doesn’t exist, so will need to define a person table. After all that is done, we will finally be able to create a person record.

However, we can still choose to define a table as SCHEMALESS even inside strict mode, as the following example shows.

DEFINE NAMESPACE ns;
DEFINE DATABASE db;
DEFINE TABLE person SCHEMALESS;
CREATE person SET name = "Aeon", metadata = {
    city: "Toria"
};
CREATE person SET name = 0;

Response
[
    {
        id: person:6rjhmfxs5pyo11sz9070,
        metadata: {
            city: 'Toria'
        },
        name: 'Aeon'
    }
]

[
    {
        id: person:caa7u5uvb8zktbej80tg,
        name: 0
    }
]

So you can think of strict mode as something more like “explicit” mode. It’s a mode where anything you didn’t explicitly define does not exist, but you can still define in a flexible way if that’s what you prefer.

On that note, what exactly is the relationship between a database and a namespace anyway?

Databases, namespaces, and users

The easiest way to start learning this is by comparing the INFO FOR NS and INFO FOR DB statements to see how they differ.

The output for INFO FOR NS is surprisingly simple:

{
	accesses: {},
	databases: {
		db: 'DEFINE DATABASE sandbox'
	},
	users: {}
}

It looks like a namespace is a space that is able to contain multiple databases, plus users, and rules for access.

In practice, namespaces are most useful when you need a separate space for each tenant in an application. To do this, you can create a namespace with DEFINE NAMESPACE, and then create a user with the role OWNER (the other two roles are EDITOR and VIEWER). That user will be isolated from all other namespaces, but will be free to do anything inside the namespace in which it was defined.

We can then switch from one namespace (or database) to another via a USE statement. USE NS or USE DB are extremely simple, only taking the name of the namespace or database to switch to.

DEFINE NAMESPACE my_namespace;
USE NS my_namespace;
DEFINE USER someuser ON NAMESPACE PASSWORD '123456' ROLES OWNER;
INFO FOR NS;

Response
{
	accesses: {},
	databases: {},
	users: {
		someuser: "DEFINE USER someuser ON NAMESPACE PASSHASH '$argon2id$v=19$m=19456,t=2,p=1$cNT8cgxedIUhA/n2eTT/rw$vjXY8ZIn6CVabuFkFbTlnkzzbp1K4dKObrN/TC0b7uY' ROLES OWNER"
	}
}

So are users and accesses always kept on the namespace level? They are not! The output for INFO FOR DB shows this:

{
	accesses: {},
	analyzers: {},
	functions: {},
	models: {},
	params: {},
	tables: {
		person: 'DEFINE TABLE person TYPE ANY SCHEMALESS PERMISSIONS NONE'
	},
	users: {}
}

This is because there are three levels of system users: root users, namespace users, and database users. INFO FOR ROOT rounds out these three levels at the top by showing us the namespace we defined, along with the root user that was created when we used the surreal start --user root --password root command.

{
    namespaces: {
        ns: 'DEFINE NAMESPACE sandbox'
    },
    users: {
        root: 'DEFINE USER root ON ROOT PASSHASH '$argon2id$v=19$m=19456,t=2,p=1$XNvnRHWx9T19twkxbNY8fA$PHZD2BS/ElijNmxswE8uhlamE97mXkFJNIQJ/3GKHI8' ROLES OWNER'
    }
}

We will get into more detail on users and accesses in Chapter 15.

By the way, did you notice that every non-empty item inside these INFO FOR statements contains one or more DEFINE statements of the same name? Indeed, just about every item inside the output from the INFO FOR statements is related to a DEFINE statement. So when you see analyzers: {} and params: {}, that means that there are DEFINE ANALYZER and DEFINE PARAM statements as well. There is only one exception: models, which are created automatically from ML models instead of through a DEFINE statement. This book does not get into the subject of machine learning, but if you are curious about this subject then see the docs for SurrealML.

Statement parameters

We learned a few chapters ago that statements will sometimes contain predetermined parameters, such as $before and $after.

These are determined automatically by the type of operation, and not necessarily the statement name. For example, the SELECT statement allows you to reference $this for the current item, but won’t have a value for $parent unless it is a subquery inside a larger query.

We can see this in a regular query that returns both $this and $parent:

INSERT INTO person (name, member_of) VALUES
    ("Aeon", "Management"),
    ("Landevin", "Management"),
    ("Asmodean", "Database student");

SELECT $this, $parent FROM person;

Response
[
	{
		parent: NONE,
		this: {
			id: person:2cpxa1zi62m2ltqpjgjj,
			member_of: 'Management',
			name: 'Landevin'
		}
	},
	{
		parent: NONE,
		this: {
			id: person:8qy61122gcifvu4um2gl,
			member_of: 'Database student',
			name: 'Asmodean'
		}
	},
	{
		parent: NONE,
		this: {
			id: person:co2dt3l4u1v0o4t9z72k,
			member_of: 'Management',
			name: 'Aeon'
		}
	}
]

As it was not a subquery, there is no $parent to refer to. Now let’s change the query to one that does reference a parent query. This time, instead of just returning the information for each record, we want to know which other person records are in the same group. To do that, we will need to compare person records that have the same member_of values. We can’t simply write WHERE member_of = member_of, because that would simply compare the current record with itself.

SELECT
	name,
	member_of,
	(SELECT VALUE name FROM person WHERE member_of = member_of) AS group_members
	FROM person;

But since this second SELECT statement is a subquery, we now have access to the parent record through $parent and the query will work.

SELECT
	name,
	member_of,
	(SELECT VALUE name FROM person WHERE $parent.member_of = member_of) AS group_members
	FROM person;

Response
[
	{
		group_members: [
			'Aeon',
			'Landevin'
		],
		member_of: 'Management',
		name: 'Aeon'
	},
	{
		group_members: [
			'Asmodean'
		],
		member_of: 'Database student',
		name: 'Asmodean'
	},
	{
		group_members: [
			'Aeon',
			'Landevin'
		],
		member_of: 'Management',
		name: 'Landevin'
	}
]

Currently, there are 11 types of parameters that may be set during an operation. The first six are:

$before and $after
$input
$this and $parent
$value

Another parameter called $event is used with the DEFINE EVENT statement which we will learn to use in Chapter 13.

Finally, these next four will be useful later in Chapter 15 when we learn about creating users and authentication.

$auth
$access
$session
$token

But if you are curious now, feel free to stick them into any queries you use to have a look at the output generated. Here is what you will see when signed in as a root user to a running database:

RETURN [$auth, $access, $session, $token];

Response
[
	NONE,
	NONE,
	{
		ac: NONE,
		db: 'aeons_database',
		exp: NONE,
		id: '6da6462a-ae28-491e-a860-fe0a1890f449',
		ip: '127.0.0.1',
		ns: 'aeons_namespace',
		or: NONE,
		rd: NONE,
		tk: {
			ID: 'root',
			exp: 1731394141,
			iat: 1731390541,
			iss: 'SurrealDB',
			jti: '5fd7087c-b944-4fa1-8474-f9d5875fff8b',
			nbf: 1731390541
		}
	},
	{
		ID: 'root',
		exp: 1731394141,
		iat: 1731390541,
		iss: 'SurrealDB',
		jti: '5fd7087c-b944-4fa1-8474-f9d5875fff8b',
		nbf: 1731390541
	}
]

The $session data is particularly interesting, as it contains ns: 'aeons_namespace' and db: 'aeons_database'. It is this data that SurrealDB uses when in non-strict mode to define the namespace and database unseen to you when you first create a table.

Comments

We’ll finish this chapter with a very small item, namely that every DEFINE statement can also have a COMMENT. A comment can only be a string, and will show up inside INFO statements.

DEFINE DATABASE my_database COMMENT "Just a test database, do not use for production";
DEFINE USER someuser ON NAMESPACE PASSWORD '123456' ROLES OWNER COMMENT "Just a test user, password is 123456. Seriously, do not use for production";
INFO FOR NAMESPACE;

Response
{
	databases: {
		my_database: "DEFINE DATABASE my_database COMMENT 'Just a test database, do not use for production'"
	},
	tokens: {},
	users: {
		someuser: "DEFINE USER someuser ON NAMESPACE PASSHASH '$argon2id$v=19$m=19456,t=2,p=1$RtvoWppsaZRb51dGwCxwfQ$AZM+9t+oy7pEpSIzbFVu4zwD+UnExuVS9F2GugFncf0' ROLES OWNER COMMENT 'Just a test user, password is 123456. Seriously, do not use for production'"
	}
}

Hopefully you enjoyed this peaceful interlude and a firming up of your structural knowledge of SurrealDB. In the next chapter we are going to move into the world of geography and making maps, thanks to SurrealDB’s built-in geo functions.

Practice time

1. What will the output of this query be, and why?

CREATE person SET name = "Just a person";
DEFINE TABLE OVERWRITE person SCHEMAFULL;
CREATE person SET name = "Just a person";
SELECT * FROM person;

Answer

The output will be a single person record with data for the name field, along with one more that doesn’t have any data for the same field. This is because SELECT is a read-only operation that never modifies data.

[
	{
		id: person:8y3ciyto4278na3j72le,
		name: 'Just a person'
	},
	{
		id: person:av6d23llmg7hea6ja2ah
	}
]

But once an UPDATE operation is performed, the name field data will no longer exist.

2. On which level(s) can a system user be defined in SurrealDB, and how can you find their definitions?

Answer

The answer to this one is nice and simple:

On the root level (INFO FOR ROOT)
On the namespace level (INFO FOR NS)
On the database level (INFO FOR DB)

3. What definitions should you use for a graph table called `wrote` that includes a field `written_at` set to `time::now()`?

Answer

Since this is a relation table, let’s make sure that SurrealDB knows that it should only be used for these purposes with TYPE RELATION.

DEFINE TABLE wrote TYPE RELATION;

After that, we’ll add the written_at field which will be set to READONLY to ensure that it is never tampered with.

DEFINE FIELD written_at ON TABLE wrote READONLY VALUE time::now();

4. A database’s `user` records need to hold a lot of metadata, in which two fields must be present. How should this be defined?

Here is an example of some user data. The fields user_id and game_server_id must always be present.

LET $metadata = {
    last_move: time::now(),
    location: [8.9, 10],
    user_id: 870987,
    game_server_id: 236746,
    enemies_defeated: ["Gargoyle", "Lesser demon", "Troglodyte"],
    items: {
        weapons: ["Short sword", "Morning star"],
        armour: ["Breastplate", "Leather shield"]
    }
};

Answer

We don’t know anything about the user table besides this metadata, so we’ll just focus on the DEFINE FIELD statement for the user’s metadata field. This should be a flexible object, but not an option<object> because the fields inside the object called user_id and game_server_id must always be present.

DEFINE FIELD metadata ON TABLE user FLEXIBLE TYPE object;

DEFINE FIELD metadata.user_id ON TABLE user TYPE int;
DEFINE FIELD metadata.game_server_id ON TABLE user TYPE int;

With these definitions set up, a user can be created with the metadata above but won’t be able to be created without its two required fields.

DEFINE FIELD metadata ON TABLE user FLEXIBLE TYPE object;
DEFINE FIELD metadata.user_id ON TABLE user TYPE int;
DEFINE FIELD metadata.game_server_id ON TABLE user TYPE int;

LET $metadata = {
    last_move: time::now(),
    location: [8.9, 10],
    user_id: 870987,
    game_server_id: 236746,
    enemies_defeated: ["Gargoyle", "Lesser demon", "Troglodyte"],
    items: {
        weapons: ["Short sword", "Morning star"],
        armour: ["Breastplate", "Leather shield"]
    }
};

CREATE user SET metadata = $metadata;

5. You have a database with `person`, `cat`, and `dog` tables that can `like` each other, but those with the name Gaston can only like themselves or others that are also called Gaston. How would you represent this?

Answer

First we will want to define the table likes to ensure that all three record types can like the other.

DEFINE TABLE likes TYPE RELATION FROM person|dog|cat TO person|dog|cat;

Once this is done, we will need to add an assertion to the in field to ensure that the name is either not equal to Gaston, or that both in and out are equal to each other.

DEFINE FIELD OVERWRITE in ON TABLE likes ASSERT
	$value.name != "Gaston" OR
	($value.name = "Gaston" AND out.name = "Gaston");

With this done, the person and dog and cat records below will be unable to like anyone but themself or anyone else with the same name, as the example below shows.

CREATE person:one;
CREATE person:gaston, dog:gaston, cat:gaston SET name = "Gaston";
// Regular people can like Gaston
RELATE person:one->likes->person:gaston;
// Won't work because Gaston doesn't like regular people/dogs/cats
RELATE person:gaston->likes->person:one;
RELATE cat:gaston->likes->person:one;
// ...unless they are also named Gaston
RELATE cat:gaston->likes->cat:gaston;
RELATE cat:gaston->likes->dog:gaston;

Previous Chapter 8: A nice surprise

Next Chapter 10: The ancient world begins to reappear

Chapter 9: A peaceful interlude

What multi-model means

Flexible by default but as strict as you like

SCHEMALESS

SCHEMALESS with defined fields

SCHEMAFULL

Other combinations of strictness and flexibility

Setting a table to TYPE NORMAL and TYPE RELATION

SCHEMAFULL with FLEXIBLE objects

Making fields read-only

Non-defined fields after move to SCHEMAFULL

Strictness across the entire database

Databases, namespaces, and users

Statement parameters

Comments

1. What will the output of this query be, and why?

2. On which level(s) can a system user be defined in SurrealDB, and how can you find their definitions?

3. What definitions should you use for a graph table called wrote that includes a field written_at set to time::now() ?

4. A database’s user records need to hold a lot of metadata, in which two fields must be present. How should this be defined?

5. You have a database with person , cat , and dog tables that can like each other, but those with the name Gaston can only like themselves or others that are also called Gaston. How would you represent this?

3. What definitions should you use for a graph table called `wrote` that includes a field `written_at` set to `time::now()`?

4. A database’s `user` records need to hold a lot of metadata, in which two fields must be present. How should this be defined?

5. You have a database with `person`, `cat`, and `dog` tables that can `like` each other, but those with the name Gaston can only like themselves or others that are also called Gaston. How would you represent this?