This course requires authentication.
Please sign in to continue
In this lesson, we’ll cover
Yes, we’ll start by defining schemaless tables, or rather exploring the schemaless tables we’ve already defined so far.
We’ll do this with the INFO
statement, which gives us information about things we can DEFINE
or have already defined.
INFO FOR DB;
When we run the query INFO FOR DB
, we’ll see everything that can be defined at the level of the database. Right now we’re just going to focus on the tables.
When looking at the results, we can see that we’ve defined multiple schemaless tables, but we never ran a DEFINE TABLE
statement before.
DEFINE TABLE IF NOT EXISTS product SCHEMALESS;
The reason for this is because every time we CREATE
a table, like have done multiple times up till now, SurrealDB behind the scenes does a DEFINE TABLE
for that table. The reasons why SurealDB can DEFINE
a schema for a schemaless table is because it’s not just either or. You can have mixed tables where some parts are schemaless and some parts are schemafull, we’ll explore this more fully in our next lesson when we look at how to DEFINE FIELDS
.
DEFINE TABLE IF NOT EXISTS product SCHEMAFULL;
In order to make tables schemafull, we use the SCHEMAFULL
clause on the DEFINE TABLE
statement. When a table is defined as schemafull, the database strictly enforces the schema we’ve defined. That means new fields can not be added to a SCHEMAFULL
table unless they are defined via the DEFINE FIELD
statement.
DEFINE TABLE graph_only TYPE RELATION; DEFINE TABLE no_graph TYPE NORMAL; DEFINE TABLE yolo TYPE ANY;
SurrealDB makes a distinction between two types of tables:
TYPE RELATION
is used to add a constraint to the table, only allowing graph relations.TYPE NORMAL
is also used to add a constraint to the table with the opposite effect, not allowing graph relations.CREATE
or DEFINE
a table however, it will be TYPE ANY
, which allows both graph and non-graph data.
DEFINE TABLE IF NOT EXISTS order TYPE RELATION FROM person TO product; DEFINE TABLE IF NOT EXISTS product_sku TYPE RELATION IN product OUT product;
You can add further constraints to tables which are TYPE RELATION
, specifying which tables they are related to.
There are two ways of specifying this:
FROM .. TO
, as an example, FROM person TO product
IN .. OUT
, as an example, IN product OUT product
Remembering that you can make a relationship between just two tables, like in our product_sku
example.
Defining the schema using TYPE RELATION
is the recommended way of defining graph relations. This means you don’t have to DEFINE
the in
or out
fields using the DEFINE FIELD
statement.
DEFINE TABLE IF NOT EXISTS avg_product_review AS SELECT count() AS number_of_reviews, math::mean(<float> rating) AS avg_review, ->product.id AS product_id, ->product.name AS product_name FROM review GROUP BY product_id, product_name; -- Query it like a table SELECT * FROM avg_product_review;
In SurrealDB, like in other databases, you can create views. The way you create views is using the DEFINE TABLE
statement like you would for any other table, then adding the AS
clause at the end with your SELECT
query.
There are a few important things which make our views far more powerful than a typical relational database view and also a few limitations to keep in mind.
Starting with what makes them powerful. Our pre-computed table views are most similar to event-based, incrementally updating, materialised views. That was a mouthful, let’s explain what that means.
review
table, it triggers a matching event on the avg_product_review
table view.SELECT
statement, but then materialise the result. Instead of normal views which behave like bookmarked SELECT
queries, that just look like tables to the user.SELECT
statement again.While this functionality can be replicated in many other databases, it is usually only done by expert users as it can be very complicated to set up and maintain. Therefore, the true power of our pre-computed table views is making this advanced functionality accessible to everyone.
As mentioned though, there are a few limitations to keep in mind.
SELECT
statement. Therefore indexing and query optimisation are still very important.FROM
clause. In our case, just the review
table, not the product
we are also using in the query. Meaning that if you delete a review
the avg_product_review
will reflect that in near real-time. However, if you delete a product
, it will still show up in avg_product_review
.If you are an advanced user, we also have you covered, as you can define any functionality you can think of using custom events.
DEFINE TABLE logs DROP;
DROP
tables can also be useful in combination with our pre-computed table views or custom events, as you can do computation on a record and then drop (delete) it in near real-time. A typical use case for this would be time-series or log data, where you only care about storing the aggregate information.
-- Define the Change Feed and its duration DEFINE TABLE IF NOT EXISTS product CHANGEFEED 1d; -- Update a single record UPDATE product:01GRTTE7DG94R864R67MGDT0QM SET colours -= "Pink", colours += "Bubble Gum Pink", time.updated_at = time::now(); -- Replay the update to the product table -- The show timestamp must be after the Change Feed was created -- Change it to be a few seconds after the time::now() we did above SHOW CHANGES FOR TABLE product SINCE d"2024-07-20T10:00:00Z" LIMIT 10;
Change Feeds can record and replay any change to the database, following the Change Data Capture pattern. This enables SurrealDB to play a role within the wider ecosystem of enterprise, cloud, or micro-service based platforms, giving users the ability to retrieve and sync changes from SurrealDB to external systems and platforms.
Change Feeds work by first defining a CHANGEFEED
and its duration for our table. Then it will start recording changes as we do any CRUD operation like we normally would. An important thing to note, is that even though in this example we just defined a CHANGEFEED
on the product
table, SurrealDB behind the scenes defines a CHANGEFEED
on the entire database as well.
After doing some changes to the product
table, we can replay them using the SHOW CHANGES FOR TABLE product
. It’s important to note that the SINCE <time>
needs to be after the time the CHANGEFEED
was defined.
Some typical use cases for Change Feeds include ingesting data into third-party systems, archiving data to object storage for backup or analysis purposes, or for real-time synchronisation with other platforms. Change Feeds are therefore a core feature for the enterprise.
-- Specify access permissions for the order table DEFINE TABLE IF NOT EXISTS order PERMISSIONS FOR select -- A user can select all their own orders WHERE in.email = $auth.id FOR create, update -- A user can create or update their own orders WHERE in.email = $auth.id FOR delete -- A user can delete their own order WHERE in.email = $auth.id; -- Can also be specified all at once DEFINE TABLE IF NOT EXISTS order PERMISSIONS FOR select, update, delete WHERE in.email = $auth.id;
We’ll cover authentication in more detail in part 4, we’ll just briefly touch on here that you can define table-level PERMISSIONS
using the DEFINE TABLE
statement. This allows you to set independent permissions for selecting, creating, updating, and deleting data. You can also however specify them all in one group.
Let’s just briefly summarise what we’ve learned
The DEFINE TABLE
statement allows us to define:
SCHEMALESS
or SCHEMAFULL
tablesRELATION
, NORMAL
and ANY
AS
clause followed by the SELECT
statementCHANGEFEED
clausePERMISSIONS
, which can be done independently for each CRUD operation or all in one group.