--- title: "4: Adding a schema | SurrealDB University" description: "Adding a schema. A step in the SurrealDB movie database tutorial, with queries you can run." url: https://surrealdb.com/learn/movies/page-04 --- ![Course content preview](https://surrealdb.com/assets/static/header.C1-dPXT9.avif) [Back to Courses](https://surrealdb.com/learn) Course chapters [Movie database tutorial](https://surrealdb.com/learn/movies) [1: Getting started](https://surrealdb.com/learn/movies/page-01) [2: Improving the existing data](https://surrealdb.com/learn/movies/page-02) [3: SurrealQL data types](https://surrealdb.com/learn/movies/page-03) [4: Adding a schema](https://surrealdb.com/learn/movies/page-04) [5: Adding and querying relations](https://surrealdb.com/learn/movies/page-05) [6: Defining users and permissions](https://surrealdb.com/learn/movies/page-06) [All the queries from the tutorial](https://surrealdb.com/learn/movies/page-07) # 4: Adding a schema Now that we have changed the fields from the original `naive_movie` data into something more useful, it's time to start thinking about our database schema. Adding a schema will allow us to start adding other `movie` records as new movies are released, without allowing any unexpected data to get added. Note: in real life it is preferable to define a schema before inserting any data, so that whatever data is inserted will be validated at exactly that point. But since this tutorial is focused on learning SurrealDB by first experimenting with a lot of data, we are only now moving on to schema definitions. We definitely want to add some other tables besides `movie` to our database schema, but we'll focus on the fields of our `movie` records first. We can give each field a type, while some will also have an assertion or two. Finally, while the `plot` field is just a string, it has a ton of useful content that we will be able to make best use of by defining a search analyzer and an index so that full text search will work on it. We'll start with some basic `DEFINE` statements. Right now our `movie` table has been defined as a `SCHEMALESS` table of `TYPE ANY`, as the output from an `INFO FOR DB` command shows. ```surql tables: { movie: 'DEFINE TABLE movie TYPE ANY SCHEMALESS PERMISSIONS NONE', naive_movie: 'DEFINE TABLE naive_movie TYPE ANY SCHEMALESS PERMISSIONS NONE' } ``` So our first task will be to define `movie` as a schemafull table that is `TYPE NORMAL`, which will ensure that nobody can use it in a `RELATE` statement. The rest of the statements will each specify the type of some of the fields, many of which are optional. That should be enough strictness for these fields. ```surql DEFINE TABLE OVERWRITE movie SCHEMAFULL TYPE NORMAL; DEFINE FIELD actors ON TABLE movie TYPE array; DEFINE FIELD awards ON TABLE movie TYPE option; DEFINE FIELD box_office ON TABLE movie TYPE option; DEFINE FIELD directors ON TABLE movie TYPE array; DEFINE FIELD dvd_released ON TABLE movie TYPE option; DEFINE FIELD genres ON TABLE movie TYPE array; DEFINE FIELD imdb_rating ON TABLE movie TYPE option; DEFINE FIELD languages ON TABLE movie TYPE array; DEFINE FIELD metacritic_rating ON TABLE movie TYPE option; DEFINE FIELD oscars_won ON TABLE movie TYPE option; DEFINE FIELD plot ON TABLE movie TYPE string; DEFINE FIELD released ON TABLE movie TYPE datetime; DEFINE FIELD rt_rating ON TABLE movie TYPE option; DEFINE FIELD runtime ON TABLE movie TYPE duration; DEFINE FIELD title ON TABLE movie TYPE string; DEFINE FIELD writers ON TABLE movie TYPE array; ``` It will also be useful to add a field that computes the average rating of a movie, so let's add that. The `math::mean()` function will compute the average, but we will first need to filter out any values that are not `NONE`. This field has a clause called `COMPUTED`, meaning that it will be computed each time it is accessed instead of stored as a permanent value. This will allow a movie's average rating to be updated as more ratings from these three sites come in. ```surql DEFINE FIELD average_rating ON TABLE movie COMPUTED math::mean([imdb_rating, metacritic_rating, rt_rating][WHERE $this IS NOT NONE]); ``` Following this, we have two fields that we could add some assertions to: `poster`, and `rated`. The `poster` field holds a url that contains the movie's poster, so it should be a string that passes the `string::is_url()` test. Before we define this field, let's first make sure that every `poster` field we have passes the test. An `array::distinct()` function should do the trick. If every value passes `string::is_url()`, we should only see the output `true`. And that is what we see! ```surql (SELECT VALUE poster.is_url() FROM movie).distinct(); ``` Response ```surql [ true ] ``` Since all of the original data passes this condition, we can define the field with the assertion. Let's make it an `option` just in case we have any movies to add later on that don't have a poster url that we can refer to. The assertion uses the syntax `ASSERT $value.is_url()`. The `$value` parameter inside a `DEFINE` statement represents the actual value of the field. ```surql DEFINE FIELD poster ON TABLE movie TYPE option ASSERT $value.is_url(); ``` The second to last field to define is `rated`, which represents how appropriate the movie is for certain age groups. Since we have the `$RATINGS` param defined already, we can put this into the definition to ensure that any new movie has a suitability rating that equals one of those values. ```surql DEFINE FIELD rated ON TABLE movie TYPE option ASSERT $value IN $RATINGS; ``` Let's get to the next feature that we'll add to our movie database: full text search to make it as easy as possible to find just the right movie. ## Adding full text search Now it's time to set up our database to use [full-text search](https://surrealdb.com/docs/learn/data-models/full-text-search/overview). This will allow search terms like "ital" to match on a string like "A working-class Italian-American bouncer", which is part of one of the plot summaries for the movies. Our `movie` table has three fields that are freeform strings. Let's take a look at a small sample of a few of them. ```surql SELECT awards, plot, title FROM movie LIMIT 3; ``` Response ```surql [ { awards: 'Won 1 Oscar. 15 wins & 15 nominations total', plot: 'After two male musicians witness a mob hit, they flee the state in an all-female band disguised as women, but further complications set in.', title: 'Some Like It Hot' }, { awards: 'Won 3 Oscars. 59 wins & 124 nominations total', plot: 'A working-class Italian-American bouncer becomes the driver of an African-American classical pianist on a tour of venues through the 1960s American South.', title: 'Green Book' }, { awards: 'Won 2 BAFTA Film 12 wins & 12 nominations total', plot: 'A former Prohibition-era Jewish gangster returns to the Lower East Side of Manhattan 35 years later, where he must once again confront the ghosts and regrets of his old life.', title: 'Once Upon a Time in America' } ] ``` Of these, `title` and `plot` are important enough that adding a full-text search index makes sense. The first step is to define an analyzer, inside which we choose which tokenisers to use to split up the text, and which filters to use to modify it. An analyzer is a customisable menu of full-text search capabilities that depends on how you intend to search. They can take tokenisers to split text, and filters to modify the tokens. We'll go with the `class` tokeniser, which detects changes between digit, letter, punctuation, and blank. After that come the filters ascii (`résumé` to `resume`) and lowercase (`America` to `america`). One more filter to add is called an `edgengram`, which is perfect for apps that return search results as a user starts typing. We will use `edgengram(3,10)` which will allow movies like "Casino" and "Casablanca" to show up even when a user only types "cas" (or "CAS", "cAS" and so on, thanks to the `lowercase` filter). ```surql DEFINE ANALYZER movie_fts TOKENIZERS class FILTERS ascii, lowercase, edgengram(3,10); ``` There is a function called [`search::analyze()`](https://surrealdb.com/docs/reference/query-language/functions/database-functions/search#searchanalyze) that lets us pass in a string to a certain analyzer to see what the final tokens look like. Let's give this a try with two movies! ```surql SELECT search::analyze("movie_fts", title) AS tokens, title FROM movie LIMIT 2; ``` As the output shows, now a user that types something like "SHIN" or "spO" will still match on one of the titles. ```surql [ { title: 'The Shining', tokens: [ 'the', 'shi', 'shin', 'shini', 'shinin', 'shining' ] }, { title: 'Eternal Sunshine of the Spotless Mind', tokens: [ 'ete', 'eter', 'etern', 'eterna', 'eternal', 'sun', 'suns', 'sunsh', 'sunshi', 'sunshin', 'sunshine', 'the', 'spo', 'spot', 'spotl', 'spotle', 'spotles', 'spotless', 'min', 'mind' ] } ]; ``` The next step is to apply this search analyzer to the fields that we want to use it on. This is done by defining an index that contains a `FULLTEXT ANALYZER` clause. We will also include a clause called `BM25` which shows the closeness of a search, and `HIGHLIGHTS` for the plot which allows us to highlight matches in a certain manner, such as by adding `**` or `--` or anything else to make it clear which part of the text is the matching one. ```surql DEFINE INDEX plot_index ON TABLE movie FIELDS plot FULLTEXT ANALYZER movie_fts BM25 HIGHLIGHTS; DEFINE INDEX title_index ON TABLE movie FIELDS title FULLTEXT ANALYZER movie_fts BM25; ``` Having a full-text index defined lets us use the `@@` operator, known as the "matches" operator. It's like using `=` except that it uses the full-text index instead of an exact match. As this query shows, you can't use it unless an index is present. ```surql SELECT rated FROM movie WHERE rated @@ "rat"; ``` Output ```surql "There was no suitable index supporting the expression: rated @@ 'rat'" ``` First, a query to see what movies have the word "time" in the title or plot. ```surql SELECT title, plot FROM movie WHERE plot @@ "time" OR title @@ "time"; ``` Response ```surql [ { plot: 'April 6th, 1917. As an infantry battalion assembles to wage war deep in enemy territory, two soldiers are assigned to race against time and deliver a message that will stop 1,600 men from walking straight into a deadly trap.', title: '1917' }, { plot: 'Marty McFly, a 17-year-old high school student, is accidentally sent thirty years into the past in a time-traveling DeLorean invented by his close friend, the eccentric scientist Doc Brown.', title: 'Back to the Future' }, { plot: 'A mysterious stranger with a harmonica joins forces with a notorious desperado to protect a beautiful widow from a ruthless assassin working for the railroad.', title: 'Once Upon a Time in the West' }, { plot: 'The Tramp struggles to live in modern industrial society with the help of a young homeless woman.', title: 'Modern Times' }, { plot: 'A former Prohibition-era Jewish gangster returns to the Lower East Side of Manhattan 35 years later, where he must once again confront the ghosts and regrets of his old life.', title: 'Once Upon a Time in America' } ] ``` Next, a query using the `search::score()` function to see which three movies have the greatest relation with the search term "time". This function takes a number which is used to match the output of the score with the matches operator below. For example, `search::score(0)` knows to get the score from the `plot` field because of the `WHERE plot @0@ "time"` clause that follows. ```surql SELECT title, plot, search::score(0) + search::score(1) AS score FROM movie WHERE plot @0@ "time" OR title @1@ "time" ORDER BY score DESC LIMIT 3; ``` Response ```surql [ { plot: 'The Tramp struggles to live in modern industrial society with the help of a young homeless woman.', score: 3.9507575035095215f, title: 'Modern Times' }, { plot: 'Marty McFly, a 17-year-old high school student, is accidentally sent thirty years into the past in a time-traveling DeLorean invented by his close friend, the eccentric scientist Doc Brown.', score: 3.735989570617676f, title: 'Back to the Future' }, { plot: 'April 6th, 1917. As an infantry battalion assembles to wage war deep in enemy territory, two soldiers are assigned to race against time and deliver a message that will stop 1,600 men from walking straight into a deadly trap.', score: 3.4721930027008057f, title: '1917' } ] ``` Here is a more complex query that matches a comment theme in SurrealDB tutorials: the concept of time. Adding the words present, past, and future should do the trick. Each one of these will return a score, which added together will make up the total relevance score for each movie. ```surql SELECT title, plot, search::score(0) + search::score(1) + search::score(2) + search::score(3) + search::score(4) + search::score(5) + search::score(6) + search::score(7) AS score FROM movie WHERE plot @0@ "time" OR title @1@ "time" OR plot @2@ "future" OR title @3@ "future" OR plot @4@ "past" OR title @5@ "past" OR plot @6@ "present" OR title @7@ "present" OR plot @8@ "fate" OR title @9@ "fate" ORDER BY score DESC; ``` Doing so brings the movies Back to the Future and WALL-E straight to the top! ```surql [ { plot: 'Marty McFly, a 17-year-old high school student, is accidentally sent thirty years into the past in a time-traveling DeLorean invented by his close friend, the eccentric scientist Doc Brown.', score: 11.41375184059143f, title: 'Back to the Future' }, { plot: 'A family heads to an isolated hotel for the winter where a sinister presence influences the father into violence, while his psychic son sees horrific forebodings from both past and future.', score: 6.268502235412598f, title: 'The Shining' }, { plot: 'The Tramp struggles to live in modern industrial society with the help of a young homeless woman.', score: 3.9507575035095215f, title: 'Modern Times' }, { plot: "In the future, a sadistic gang leader is imprisoned and volunteers for a conduct-aversion experiment, but it doesn't go as planned.", score: 3.702232599258423f, title: 'A Clockwork Orange' }, { plot: 'In the distant future, a small waste-collecting robot inadvertently embarks on a space journey that will ultimately decide the fate of mankind.', score: 3.545522689819336f, title: 'WALL·E' }, { plot: 'April 6th, 1917. As an infantry battalion assembles to wage war deep in enemy territory, two soldiers are assigned to race against time and deliver a message that will stop 1,600 men from walking straight into a deadly trap.', score: 3.4721930027008057f, title: '1917' }, { plot: 'A mysterious stranger with a harmonica joins forces with a notorious desperado to protect a beautiful widow from a ruthless assassin working for the railroad.', score: 3.166771173477173f, title: 'Once Upon a Time in the West' }, { plot: 'A former Prohibition-era Jewish gangster returns to the Lower East Side of Manhattan 35 years later, where he must once again confront the ghosts and regrets of his old life.', score: 3.0653889179229736f, title: 'Once Upon a Time in America' }, { plot: 'A thief who steals corporate secrets through the use of dream-sharing technology is given the inverse task of planting an idea into the mind of a C.E.O., but his tragic past may doom the project and his team to disaster.', score: 3.0321388244628906f, title: 'Inception' }, { plot: "When a ronin requesting seppuku at a feudal lord's palace is told of the brutal suicide of another ronin who previously visited, he reveals how their pasts are intertwined - and in doing so challenges the clan's integrity.", score: 2.957204580307007f, title: 'Hara-Kiri' }, { plot: "A human soldier is sent from 2029 to 1984 to stop an almost indestructible cyborg killing machine, sent from the same year, which has been programmed to execute a young woman whose unborn son is the key to humanity's future salvation", score: 2.9160244464874268f, title: 'The Terminator' } ] ``` Next is a query to practice the `search::highlight()` function. This function is especially useful when using our edgengram filter, as it will return results even if just the first three letters match. Without highlighting where the match occurred, the reader will probably have a hard time telling exactly which word inside the plot was the matching one. Adding an asterisk to the left and right side of the matching word will make it most clear which one fits what the user was searching for. ```surql SELECT search::highlight("**", "**", 0) AS plot FROM movie WHERE plot @0@ "per" LIMIT 4; ``` As two asterisks are often used to make font bold, you can see just how clear this would be for a user who is beginning to type a word in a search for a matching plot. In the text below, "per" matches on "perceived", "persecution", "perspective", and "perfect". ```surql [ { plot: 'A mentally unstable veteran works as a nighttime taxi driver in New York City, where the **perceived** decadence and sleaze fuels his urge for violent action.' }, { plot: 'In German-occupied Poland during World War II, industrialist Oskar Schindler gradually becomes concerned for his Jewish workforce after witnessing their **persecution** by the Nazis.' }, { plot: 'The presidencies of Kennedy and Johnson, the Vietnam War, the Watergate scandal and other historical events unfold from the **perspective** of an Alabama man with an IQ of 75, whose only desire is to be reunited with his childhood sweeth' }, { plot: 'When an open-minded Jewish waiter and his son become victims of the Holocaust, he uses a **perfect** mixture of will, humor, and imagination to protect his son from the dangers around their camp.' } ]; ``` Previous 3: SurrealQL data types [Previous](https://surrealdb.com/learn/movies/page-03) Next lesson 5: Adding and querying relations [Next lesson](https://surrealdb.com/learn/movies/page-05) ```json {"@context":"https://schema.org","@type":"Course","name":"Movie database tutorial","description":"Movie database tutorial","url":"https://surrealdb.com/learn/movies","inLanguage":"en","isAccessibleForFree":true,"provider":{"@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com"},"hasPart":[{"@type":"LearningResource","name":"Movie database tutorial","url":"https://surrealdb.com/learn/movies"},{"@type":"LearningResource","name":"1: Getting started","url":"https://surrealdb.com/learn/movies/page-01"},{"@type":"LearningResource","name":"2: Improving the existing data","url":"https://surrealdb.com/learn/movies/page-02"},{"@type":"LearningResource","name":"3: SurrealQL data types","url":"https://surrealdb.com/learn/movies/page-03"},{"@type":"LearningResource","name":"4: Adding a schema","url":"https://surrealdb.com/learn/movies/page-04"},{"@type":"LearningResource","name":"5: Adding and querying relations","url":"https://surrealdb.com/learn/movies/page-05"},{"@type":"LearningResource","name":"6: Defining users and permissions","url":"https://surrealdb.com/learn/movies/page-06"},{"@type":"LearningResource","name":"All the queries from the tutorial","url":"https://surrealdb.com/learn/movies/page-07"}]} ``` ```json {"@context":"https://schema.org","@type":"LearningResource","name":"4: Adding a schema","description":"Adding a schema","url":"https://surrealdb.com/learn/movies/page-04","learningResourceType":"lesson","isPartOf":{"@type":"Course","name":"Movie database tutorial","url":"https://surrealdb.com/learn/movies"},"position":5} ``` ```json {"@context":"https://schema.org","@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com","logo":"https://surrealdb.com/assets/static/logo.BG7_TG2b.svg","description":"SurrealDB is the unified data layer for AI. A multi-model database for documents, graphs, vectors, and time-series.","foundingDate":"2022","hasCertification":[{"@type":"Certification","name":"SOC 2 Type 2"},{"@type":"Certification","name":"GDPR"},{"@type":"Certification","name":"Cyber Essentials Plus"},{"@type":"Certification","name":"ISO 27001"}],"owns":[{"@type":"SoftwareApplication","name":"SurrealDB","url":"https://surrealdb.com/surrealdb"},{"@type":"SoftwareApplication","name":"Agent Memory","url":"https://surrealdb.com/agent-memory"}],"knowsAbout":["multi-model databases","document databases","graph databases","vector search","time-series databases","SurrealQL","Agent Memory","real-time databases","embedded databases","context layer","graph ontology","distributed database","knowledge graphs","distributed transaction protocols","highly-scalable databases"],"sameAs":["https://www.wikidata.org/wiki/Q124316308","https://github.com/surrealdb/surrealdb","https://twitter.com/surrealdb","https://www.youtube.com/@surrealdb","https://www.linkedin.com/company/surrealdb","https://discord.gg/surrealdb","https://www.reddit.com/r/surrealdb","https://www.instagram.com/surrealdb","https://medium.com/surrealdb","https://dev.to/surrealdb"]} ``` ```json {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://surrealdb.com"},{"@type":"ListItem","position":2,"name":"Learn","item":"https://surrealdb.com/learn"},{"@type":"ListItem","position":3,"name":"Page 04","item":"https://surrealdb.com/learn/movies/page-04"}]} ```