Define tables, views and Change Feeds

In this lesson, we’ll cover

• How to define schemaless and schemafull tables
• The different table types
• Pre-computed table views
• Change Feeds
• Table permissions

Defining schemaless tables

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 .

Defining schemafull tables

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.

The different table types

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.
• By default, when you 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:

• Using FROM .. TO , as an example, FROM person TO product
• Using 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.

Pre-computed table views

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.

• Event-based, meaning that when you run add or remove data from the underlying table, in our example, the review table, it triggers a matching event on the avg_product_review table view.
• Materialised view, meaning that the first time we run the table view query, it will run the query like a normal 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.
• Incrementally updating, meaning that for any subsequent run, it will listen for the event trigger and perform the most efficient operation possible to always keep the result up to date, instead of just running the 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.

• First, while subsequent runs are very efficient, the initial run of large analytical queries can be slow and use a lot of resources, because it’s just a normal SELECT statement. Therefore indexing and query optimisation are still very important.
• Second, while both graph relations and record links are supported, the table view update event, only gets triggered based on the table we have in our 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.

Change Feeds

-- 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.

Table permissions

-- 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.email
		FOR create, update
			-- A user can create or update their own orders
			WHERE in.email = $auth.email
		FOR delete
			-- A user can delete their own order
			WHERE in.email = $auth.email;

-- Can also be specified all at once
DEFINE TABLE IF NOT EXISTS order
	PERMISSIONS
		FOR select, update, delete WHERE in.email = $auth.email;

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.

Summary

Let’s just briefly summarise what we’ve learned

The DEFINE TABLE statement allows us to define:

• SCHEMALESS or SCHEMAFULL tables
• The three table types, RELATION , NORMAL and ANY
• Pre-computed table views using the AS clause followed by the SELECT statement
• Change Feeds, using the CHANGEFEED clause
• Table level PERMISSIONS , which can be done independently for each CRUD operation or all in one group.