This section explains how databases work for readers who have not used one before. It covers what a database does, the ways a database can shape data, transactions, and schemas and data types. Every example uses SurrealQL, the query language of SurrealDB, and you can paste each one into SurrealDB Studio or the surreal sql shell to run it. If you already know databases, go to Getting started instead.
What a database does
A database does two jobs. It keeps information, and it finds that information again when you ask for it. A post on the SurrealDB blog that explains databases to children compares this to a human mind:
In fact, even your own mind has its own database. Because a database is really just something that you use to do two things.
The first thing is: Keeping information. Lots of information. It's like a box that never gets full.
The second thing is: Finding the information again when you need it.
From What's a database anyway?? A blog post for kids
A mind and a database are good at different questions. A person can remember a childhood memory in under a second, or a person's name they haven't seen in over a decade, and can use all of their memories together to accomplish complex tasks at work.
However, a person cannot quickly find answers to exact queries like the third healthiest of every food they have ever eaten, or a count of how many friends prefer milkshakes to hamburgers. A database answers exact questions like these quickly, and it gives the same answer every time.
Records, fields and tables
A database keeps information as records. A record holds fields, and each field has a name and a value. Records of the same kind go in a table.
The examples on this page follow the blog post quoted above, which plans a camping trip with a group of friends. Some of the friends dislike outhouses, some get carsick on long drives, and some want to go hiking, so the questions that matter for each campsite are how many bathrooms it has, how far away it is, and how many hiking trails it has.
The statements below create three records in a table called camping_site, one for each campsite. Each record has the same four fields:
name: the name of the campsite, as text.distance: how far the campsite is from home, in kilometres.bathrooms: how many bathrooms the campsite has.trails: how many hiking trails start at the campsite.
CREATE makes a new record, and CONTENT gives it its fields as a list of names and values between { and }. Text goes between single quotes, numbers are written as they are, and a semicolon ends each statement.
CREATE camping_site:outback CONTENT {
name: 'Outback Camping',
distance: 49.7,
bathrooms: 0,
trails: 50
};
CREATE camping_site:easy CONTENT {
name: 'Easy Camping',
distance: 10.1,
bathrooms: 20,
trails: 1
};
CREATE camping_site:happy CONTENT {
name: 'Happy Trails',
distance: 30.0,
bathrooms: 3,
trails: 5
};The part before each CONTENT is the record ID. camping_site:happy names the table, camping_site, and a key that is unique in that table, happy. The record ID is how you point to one record and only that record. If you leave out the key and write CREATE camping_site CONTENT {...}, SurrealDB generates a random key for you.
Asking a question with a query
A query is a question written in a query language. Query languages are exact on purpose: a person understands "a campsite that is close", but a database needs to know how close. So before writing the query, the blog post turns each wish of the group into a number:
Clean bathrooms: more than two bathrooms, so that one still works if another is out of order.
Close to home: less than 50 kilometres away, so that nobody gets carsick.
Places to hike: more than two trails, so that there is a new one to try each day.
The post then shows how the question in plain English becomes a query, one phrase at a time. Its records also have an address field, which the records on this page leave out:
"What" is called "SELECT",
"Name and address" is called
name, address,"for camping sites" is called
FROM camping_site,"that have" is called
WHERE,
From What's a database anyway??
The query below asks for the name of every campsite that matches all three wishes. SELECT name chooses which field to return, FROM camping_site chooses the table, and WHERE keeps only the records for which every condition is true. > means "more than", < means "less than", and AND joins the conditions so that a campsite has to meet all of them.
The first three lines create the records from the example above, so that the query has data to read. The site hides them under a toggle, and the copy button includes them.
-- Setup from the example above
CREATE camping_site:outback CONTENT { name: 'Outback Camping', distance: 49.7, bathrooms: 0, trails: 50 };
CREATE camping_site:easy CONTENT { name: 'Easy Camping', distance: 10.1, bathrooms: 20, trails: 1 };
CREATE camping_site:happy CONTENT { name: 'Happy Trails', distance: 30.0, bathrooms: 3, trails: 5 };
SELECT name FROM camping_site
WHERE bathrooms > 2 AND trails > 2 AND distance < 50;[
{
name: 'Happy Trails'
}
]The result is a list with one entry for each matching record, and each entry holds only the fields that the query asked for. Outback Camping has no bathrooms, and Easy Camping has only one trail, so Happy Trails is the only match.
The query keeps working as the table grows. With a thousand campsites, the same SELECT returns every campsite that matches, and you choose from a short list.
Create, read, update and delete
Almost everything an application does with a database is one of four operations, known together as CRUD. An earlier post on the SurrealDB blog explains them with a poem written on paper:
Create = make data ("Let's write a poem on a piece of paper")
Read = retrieve data ("Find the piece of paper you wrote on and read the poem")
Update = change data ("Let's change a few lines in the poem")
Delete = remove data ("The poem wasn't good, let's throw the paper in the trash bin")
From The ultimate beginners guide to databases
Each operation has a statement in SurrealQL. SET is a second way to give a record its fields, one name = value pair at a time, and * in a SELECT means "every field":
| Operation | Statement | Example |
|---|---|---|
| Create | CREATE | CREATE camping_site:lakeside SET name = 'Lakeside'; |
| Read | SELECT | SELECT * FROM camping_site; |
| Update | UPDATE | UPDATE camping_site:easy SET trails = 2; |
| Delete | DELETE | DELETE camping_site:outback; |
The example below uses all four on one table. It creates two campsites, changes the number of trails at Easy Camping from 1 to 2, deletes Outback Camping, and then reads what is left. The output includes the id field, which every record has, because * asks for every field.
CREATE camping_site:easy SET name = 'Easy Camping', trails = 1;
CREATE camping_site:outback SET name = 'Outback Camping', trails = 50;
UPDATE camping_site:easy SET trails = 2;
DELETE camping_site:outback;
SELECT * FROM camping_site;[
{
id: camping_site:easy,
name: 'Easy Camping',
trails: 2
}
]Why a query language is exact
A query language sits between the words a person uses and the bytes a database stores. The children's post shows this with food. It gives each food a tastiness field that holds a number from 1 to 10, where 1 is the least tasty and 10 is the tastiest, in the same way as a rating for a film. Here are the human layer and the database layer side by side for one opinion about a hamburger:
Human language:
It was the best thing I ever ateDatabase language:
tastiness: 10
From What's a database anyway??
"The best thing I ever ate" becomes the highest number on the scale. The third layer is the format the database writes to disk, which only the database reads. The query language is the layer you write. It has to be exact because the database does what the query says and nothing more: trails > 2 excludes a campsite with exactly two trails, even if "more than a couple of trails" was what you meant.
Next steps
Data models explains the different shapes a database can give your data.
Transactions explains how a database keeps a group of changes together.
Schemas and data types explains how a database checks the data you give it.