SurrealDB cheatsheet
Let’s use the ready made database provided by SurrealDB to experiment and build some useful queries and add them here in the SurrealDB cheatsheet. One of the interesting things is getting familiar with understanding the noun-verb-noun paradigm and using it to your advantage along with “fetch” which saves having to create joins.
The RELATE is done on write, so it uses a bit more storage, but should make queries faster.
Tip: This will help you with most common queries
SurrealDB Cheatsheet
Install SurrealDB database and import the example data
Install SurrealDB and start the Database
https://surrealdb.com/docs/surrealdb/installation/linux
https://surrealdb.com/docs/surrealdb/installation/windows
https://surrealdb.com/docs/surrealdb/installation/macos
Download the dataset from SurrealDB
curl -L "https://datasets.surrealdb.com/surreal-deal-mini-v1.surql" -o surreal-deal-mini-v1.surqlThe demo tables are “schemaless” apart from “order”
Import it to your RUNNING SurrealDB using curl:
curl -v POST -u "root:root" -H "NS: test" -H "DB: test" -H "Accept: application/json" --data-binary @surreal-deal-mini-v1.surql http://localhost:8000/importUse surrealist.app to check the tables and connection (go to http://127.0.0.1:8000 in your browser)
Start using Surrealist.app to perform queries
Once the demo data has imported (we did it via the CLI above but you can also do it using the up arrow, top right hand corner -see screenshot below) – you will then be able to explore the tables.
Use the explorer to find an id for a person from “person” table – then you can search for what they bought. In this case the person is in the format “person : random id”
A quick note about graph edge queries, a good way to think of the order is “noun -> verb -> noun” you may often see “Author Wrote Article” or “Person Order(ed) Product”. The table is created and named after the “verb” that you use when you do a RELATE.
Example Advanced SurrealDB Queries
Find out what products a customer ordered (using their name rather than id)
** This might not always be a good idea, you might have 2 people called “Bob Jones” but it shows you how to filter using WHERE so you can apply this style of query to filter on other fields by replacing “name=<person’s name>” with “email=<insert email address here>” or whatever you desire.
SELECT id,->order.out as bought FROM person WHERE name="Gwenda Stokes" FETCH bought;The result will be 1 row, with 4 items (or “objects”)
[
{
"bought": [
{
"category": "acrylic paint",
"currency": "£",
"description": "allowed fuel nearest commercial suicide murder finder clearly terry remember powder",
"discount": null,
"id": "product:77i5hq577i9l9u38r80q",
"image_url": "https://source.unsplash.com/1920x1080?",
"name": "sir leadership over cafe",
"price": 4894.34,
"quantity": 18
},
{
"category": "charcoal",
"currency": "£",
"description": "describes eligible healthy meal assist dsl cassette continuing tank logged oriented senator seat world safe folk married jurisdiction tones law",
"discount": null,
"id": "product:92s9uh652s5b9s62f19f",
"image_url": "https://source.unsplash.com/1920x1080?",
"name": "amendment offline",
"price": 23360.66,
"quantity": 12
},
{
"category": "watercolor",
"currency": "£",
"description": "orders latex hence procedures basketball maintained oclc drive era letter converted setup maintain golf outline something politics attendance printable rating spread artwork",
"discount": 0.2,
"id": "product:36x6bx947h9j8n37k06j",
"image_url": "https://source.unsplash.com/1920x1080?",
"name": "assets scheme least",
"price": 14242.22,
"quantity": 9
},
{
"category": "digital art",
"currency": "£",
"description": "plc define inch easily remaining indicator machinery equity severe irish somewhere frames vacations wish interested duty generate look walter shower selling catch determine palestinian",
"discount": null,
"id": "product:19g5td222t5f3y68a46m",
"image_url": "https://source.unsplash.com/1920x1080?",
"name": "clip ieee headlines",
"price": 13512.59,
"quantity": 16
}
],
"id": "person:08m6ez414r0d4p19z09i"
}
]Find out the total value of a selected customer’s purchases
SELECT id,->order.out AS bought,
math::sum(->order.*.price) AS total
FROM person WHERE name="Gwenda Stokes" FETCH bought;[
{
"bought": [
{
"category": "acrylic paint",
"currency": "£",
"description": "allowed fuel nearest commercial suicide murder finder clearly terry remember powder",
"discount": null,
"id": "product:77i5hq577i9l9u38r80q",
"image_url": "https://source.unsplash.com/1920x1080?",
"name": "sir leadership over cafe",
"price": 4894.34,
"quantity": 18
},
{
"category": "charcoal",
"currency": "£",
"description": "describes eligible healthy meal assist dsl cassette continuing tank logged oriented senator seat world safe folk married jurisdiction tones law",
"discount": null,
"id": "product:92s9uh652s5b9s62f19f",
"image_url": "https://source.unsplash.com/1920x1080?",
"name": "amendment offline",
"price": 23360.66,
"quantity": 12
},
{
"category": "watercolor",
"currency": "£",
"description": "orders latex hence procedures basketball maintained oclc drive era letter converted setup maintain golf outline something politics attendance printable rating spread artwork",
"discount": 0.2,
"id": "product:36x6bx947h9j8n37k06j",
"image_url": "https://source.unsplash.com/1920x1080?",
"name": "assets scheme least",
"price": 14242.22,
"quantity": 9
},
{
"category": "digital art",
"currency": "£",
"description": "plc define inch easily remaining indicator machinery equity severe irish somewhere frames vacations wish interested duty generate look walter shower selling catch determine palestinian",
"discount": null,
"id": "product:19g5td222t5f3y68a46m",
"image_url": "https://source.unsplash.com/1920x1080?",
"name": "clip ieee headlines",
"price": 13512.59,
"quantity": 16
}
],
"id": "person:08m6ez414r0d4p19z09i",
"total": 56009.81
}
]Total = 56009.81
Get the product where description contains
SELECT id
FROM product
WHERE description
CONTAINS "cassette"
FETCH id;// -------- Query 1 (3ms278µs378ns) --------
[
{
"id": {
"category": "charcoal",
"currency": "£",
"description": "required broad houses ear pocket keeps pi apartments graph newspapers acres enforcement counts cassette diet export acrobat releases comfort gb produced last solo serves automotive",
"discount": null,
"id": "product:51o8dd215w0z5j54y03y",
"image_url": "https://source.unsplash.com/1920x1080?",
"name": "karen implications doors",
"price": 5152.5,
"quantity": 20
}
},
{
"id": {
"category": "charcoal",
"currency": "£",
"description": "describes eligible healthy meal assist dsl cassette continuing tank logged oriented senator seat world safe folk married jurisdiction tones law",
"discount": null,
"id": "product:92s9uh652s5b9s62f19f",
"image_url": "https://source.unsplash.com/1920x1080?",
"name": "amendment offline",
"price": 23360.66,
"quantity": 12
}
}
]Select products from product table created by a particular artist
SELECT id,->create->product.name AS prod FROM artist:19z7ve811m3q9g37i80w;[
{
"id": "artist:19z7ve811m3q9g37i80w",
"prod": [
"girl philadelphia silk william",
"maintained inquiry"
]
}
]Find products from artists who created more than n products
-- Conditional filtering based on graph edges
SELECT ->create->product.name AS prod, *
FROM artist
WHERE count(->create->product) > 4;[
{
"address": {
"address_line_1": "804 Ballywatermoy",
"address_line_2": null,
"city": "Windermere",
"coordinates": [
-20.736378,
170.989997
],
"country": "Wales",
"post_code": "EX2J 0ZS"
},
"company_name": null,
"email": "tips2091@live.com",
"first_name": "Reggie",
"id": "artist:08o3oj303m0j1n46l20i",
"last_name": "Hubbard",
"name": "Reggie Hubbard",
"phone": "016977 3262",
"prod": [
"congress bin",
"deutsch outsourcing meals",
"causing working quotes later",
"substantial quantity",
"difference madison"
]
},
{
"address": {
"address_line_1": "738 Lattone",
"address_line_2": null,
"city": "New Milton",
"coordinates": [
-30.841051,
142.029942
],
"country": "Northern Ireland",
"post_code": "LD8X 0DQ"
},
"company_name": null,
"email": "gore1988@duck.com",
"first_name": "Mirella",
"id": "artist:28e7sj511w7r4i78n95x",
"last_name": "Rivers",
"name": "Mirella Rivers",
"phone": "028 2716 6632",
"prod": [
"internal focused enterprise",
"isbn watches fast",
"jay appear aware tree",
"bet never",
"sd bet",
"parker zus meeting kick"
]
}
]Note the sample data is gobbledegook so in this example the products appear as
"prod": [ "internal focused enterprise", "isbn watches fast", "jay appear aware tree", "bet never", "sd bet", "parker zus meeting kick" ]Get email addresses of people who bought each product, by product, sort by ascending product name
SELECT name,<-order<-person.email as buyer FROM product ORDER BY name ASC fetch name;[
{
"buyer": [
"memories1850@protonmail.com",
"deutsch2031@duck.com",
"eva2055@live.com",
"exploring2047@yandex.com"
],
"name": "activity minimum bottle"
},
{
"buyer": [
"decent1902@protonmail.com",
"located1908@outlook.com"
],
"name": "additional loss"
},
{
"buyer": [
"noted1850@protonmail.com",
"vice1806@outlook.com",
"direct1834@outlook.com",
"deposit1892@example.com"
],
"name": "aim recognize"
....truncated here but you get the ideaGet a list of customer orders sorted by customer name and exclude where product qty is zero
Anthony Reilly has an order quantity of 0 and shows in our results if we don’t check the count >=1
SELECT name,->order
AS orderr
FROM person
WHERE count(->order->product) >=1
ORDER by name ASC
FETCH orderr;
[
{
"name": "Aaron Lindsay",
"orderr": [
{
"currency": "£",
"discount": null,
"id": "order:['Scotland', '2023-07-22T02:08:49.068430']",
"in": "person:88i4ax940l2g0j45k17k",
"order_date": "2023-07-22T02:08:49.068430",
"order_status": "delivered",
"out": "product:10f4vl014z8v5v68q46e",
"payment_method": "debit card",
"price": 11324.97,
"product_name": "strategy because block",
"quantity": 2,
"shipping_address": {
"address_line_1": "791 Barra",
"address_line_2": null,
"city": "Rushden",
"coordinates": [
75.131311,
-9.916224
],
"country": "Scotland",
"post_code": "FI0S 3DA"
}
},
{
"currency": "£",
"discount": null,
"id": "order:['Scotland', '2023-10-05T23:22:18.207768']",
"in": "person:88i4ax940l2g0j45k17k",
"order_date": "2023-10-05T23:22:18.207768",
"order_status": "delivered",
"out": "product:02z4oe339z5a8a85w66l",
"payment_method": "credit card",
"price": 7482.19,
"product_name": "blog amateur nevada",
"quantity": 2,
"shipping_address": {
"address_line_1": "791 Barra",
"address_line_2": null,
"city": "Rushden",
"coordinates": [
75.131311,
-9.916224
],
"country": "Scotland",
"post_code": "FI0S 3DA"
}
.....truncated for brevity, but you get the ideaSummary – SurrealDB Cheatsheet
I’ll add to this page as I build more useful / advanced queries, so be sure to pop back and check back soon!
There are some good examples on YouTube videos as well, but you will often have to pause the video and then type in the query into your editor/surrealist.app
The idea of this article was to build a list of examples that can be copy and pasted and be used by anyone who is learning via the demo dataset. If you have any examples to share please feel free to contact me via the contact link above.
These surrealDB docs are a great resource : https://surrealdb.com/docs/surrealdb/surrealql/statements/select#advanced-expressions
Check out the YouTube playlist – SurrealDB for Rust :
https://www.youtube.com/playlist?list=PL38rDfx7QwKaluI7PoOrjlmjGTgx45C2Y
