# SurrealDB
```elixir
Mix.install([
{:surreal_ex, "~> 0.2.0"},
{:csv, "~> 3.0"},
{:ecto_sql, "~> 3.9"},
{:ecto_sqlite3, "~> 0.9.1"}
])
```
## A word on SurrealDB
When an application runs on embedded devices, with the model "off-line first", you want to use your embedded (local) database first. When the user has access to the internet, you can sync with a remote database. One can use SQLite, a single file database, but it may be hard to synchronise with a backend. Here comes SurrealDB into play. It is a lightweight cloud-native database that claims to synchronise easily to a backend.
SurrealDB is essentially a key/value ACID-compliant database. It can run in memory or with local persistence, or with a remote connection. Redis is this kind of in-memory database mainly used for caching, for PubSub and streams (queue-like). SurrealDB is different; you can run it from the backend or in the browser. It offers a DSL very close to SQL and allows you to write Javascript functions.
By default, it is schemaless; you can insert any key/value. When you want more control, you can turn a table into a schemafull table. structure is fixed and you want more control, you can turn a table into a _schemafull_ table.
If you run the server locally, you need to start a server. It can also be run "serverless", meaning that you reach a service in the cloud. By default, it points to <https://cloud.surrealdb.com>. HTTP is all you need: the server provides a REST API for CRUD queries, and a unique endpoint at "/sql" where you POST a query.
Websockets aren't documented [at the moment](https://surrealdb.com/docs/integration/websockets).
> [An example](https://developer.android.com/topic/libraries/architecture/workmanager) of the technology used with Android.
It can run the database in-memory. This configuration is used here.
Websockets aren't documented [at the moment](https://surrealdb.com/docs/integration/websockets). This would make a lot of sense combined with events.
It is also worth noting that there are some functions to deal with GeoJSON data. It can be important as many apps that deal with geolocation are embedded apps. However, spatial indexing doesn't seem to be implemented (cf PostGis [using GIST](http://postgis.net/workshops/postgis-intro/indexing.html)).
SurrealDB still seems pretty new. I still needs to be battle tested, but also how would one migrate to SurrealDB, as well as performance metrics.
We made a quick demo on how to work with SurrealDB in Elixir with the [SurrealEX](https://hexdocs.pm/surreal_ex/readme.html) package.
A quick comparison is made by serializing a CSV file into a SurrealDB table vs an `SQLite` table with Elixir, and then querying the tables. There is a clear gap in terms of performance, with insertions and aggregation function and select functions, schemaless and schemafull with index. For example:
* to insert 57000 rows, it takes approx around 6-7ss with SurrealDB (remove the `log debug` flag) vs less than 2 for SQLite.
* to count 57000 rows, it takes approx 350ms with SurrealDB vs 7ms for SQLite.
* to query on the schemaless table a given row, it takes approx 200ms and approx 150ms for the schemafull indexed table. It takes approx 10ms for SQLite without index, and 1ms when we index the column.
## Start a local SurrealDB server
You can run a Docker image to start a SurrealDB server. It is setup here to run in memory with the following credentials: "user", "pass", "location-of-the-database":
```bash
docker run --rm -p 8000:8000 surrealdb/surrealdb:latest start --log debug --user root --pass root memory
```
or if you have SurrealDB installed, run in a terminal:
```bash
surreal start --log debug --user root --pass root memory
```
You should get the following prompt:
```bash
.d8888b. 888 8888888b. 888888b.
d88P Y88b 888 888 'Y88b 888 '88b
Y88b. 888 888 888 888 .88P
'Y888b. 888 888 888d888 888d888 .d88b. 8888b. 888 888 888 8888888K.
'Y88b. 888 888 888P' 888P' d8P Y8b '88b 888 888 888 888 'Y88b
'888 888 888 888 888 88888888 .d888888 888 888 888 888 888
Y88b d88P Y88b 888 888 888 Y8b. 888 888 888 888 .d88P 888 d88P
'Y8888P' 'Y88888 888 888 'Y8888 'Y888888 888 8888888P' 8888888P'
[2023-02-18 20:12:12] INFO surrealdb::iam Root authentication is enabled
[2023-02-18 20:12:12] INFO surrealdb::iam Root username is 'root'
[2023-02-18 20:12:12] INFO surrealdb::dbs Database strict mode is disabled
[2023-02-18 20:12:12] INFO surrealdb::kvs Starting kvs store in memory
[2023-02-18 20:12:12] INFO surrealdb::kvs Started kvs store in memory
[2023-02-18 20:12:12] INFO surrealdb::net Starting web server on 0.0.0.0:8000
[2023-02-18 20:12:12] INFO surrealdb::net Started web server on 0.0.0.0:8000
```
## SurrealDB CLI
You can send POST requests via `cURL` with a payload, the two headers (NS for namespace, DB for database) and a BASIC Authentication to the endpoint:
```bash
data = "create tab:john name='john'"
curl -k -L -s --compressed POST \
--header "Accept: application/json" \
--header "NS: test" \
--header "DB: test" \
--user "root:root" \
--data "${DATA}" \
http://localhost:8000/sql
```
You can also [connect](https://surrealdb.com/docs/cli/sql) to the server with the [CLI](https://surrealdb.com/docs/cli/start), once installed. You run `surreal sql` followed by:
* the connection as the "localhost:8000" since by default it will reach the cloud (default is https://cloud.surrealdb.com),
* and the _namespace_ and the _database name_,
* the basic authentication of the super-user.
```bash
$ surreal sql --conn http://localhost:8000 --user root --pass root --ns testns --db testdb
> create tab:john name='john';
[{"time": "1.47255ms", "status": "OK", "results: "{...}}]
```
## Start a connection from Elixir
You can simply connect via an HTTP client and pass your query once you defined the correct headers and authentication:
```elixir
url = "http://localhost:8000/key/airports"
url_sql = "http://localhost:8000/sql"
headers = [
{"accept", "application/json"},
{"Content-Type", "application/json"},
{"NS", "testns"},
{"DB", "testdb"}
]
auth = [hackney: [basic_auth: {"root", "root"}]]
q = Jason.encode!([%{c: 1}, %{c: 2}])
query = "INSERT INTO airports #{q}"
HTTPoison.post!(url_sql, query, headers, auth)
# |> Map.get(:status_code)
```
You can get the response:
```elixir
HTTPoison.post!(url_sql, "select * from airports;", headers, auth)
|> Map.get(:body)
|> Jason.decode!()
```
#### SurrealEx
We will use the package [SurrealEx](https://hexdocs.pm/surreal_ex/readme.html). You have two ways to setup the package. Firstly, you setup a config file as below. For Livebook, put the file below in your root directory, and add the key `config_path: "config.exs"` in the `Mix.install` of the Livebook.
<!-- livebook:{"break_markdown":true} -->
<!-- livebook:{"force_markdown":true} -->
```elixir
#config.exs
import Config
config :surreal_ex, Conn,
interface: :http,
uri: "http://localhost:8000",
ns: "testns",
db: "testdb",
user: "root",
pass: "root"
```
The Notebook dependencies can be setup with:
<!-- livebook:{"force_markdown":true} -->
```elixir
Mix.install([{:surreal_ex, "~> 0.2.0"}], config_path: "config.exs")
```
<!-- livebook:{"break_markdown":true} -->
<hr />
* You can alternatively use the module `SurrealEx.Config`. Let's define a module `Conn` to wrap the config.
```elixir
defmodule Conn do
@config [
interface: :http,
uri: "http://localhost:8000",
ns: "test",
db: "test",
user: "root",
pass: "root"
]
use SurrealEx.Conn
def setup do
@config
|> SurrealEx.Config.for_http()
|> SurrealEx.Config.set_config_pid()
end
end
```
You describe the config: uri, (namespace, database) and basic auth, and pass it to the `Conn` module which uses the `SurrealEx.Conn` behaviour.
You run the setup:
```elixir
Conn.setup()
cfg = SurrealEx.Config.get_config(Conn)
```
We can now use this database with `Conn.sql(query)`.
If the user is registered, you get a token that you can pass `Conn.sql(query, token)`.
## Schemaless
#### CREATE
Every row is identified relatively to a table.
To **CREATE** a row with the "SurQL" DSL, we pass a table name and possibly a _unique id_ in the format `<table>:<id>` and **SET** the body with a list of `<key>=<value>`. You then pass the SurQL query to the `YOURMODULE.sql` function.
> Each individual statement within SurrealDB is run within its own transaction. When we pass an id, the transaction can be run only once.
With `SurrealEx`, we run SurQL statements with `Conn.sql` where `Conn` is the module we defined with the behaviour `SurrealEx.Conn`.
```elixir
Conn.sql("
CREATE developper:nelson SET name = 'nelson', status= 'founder';
")
```
When you don't specify the `id`, SurrealDB will assign one for you. Note that you can now run the CREATE query below _multiple times_, but you maybe don't want this.
```elixir
Conn.sql("
CREATE developper SET name = 'nelson', status='founder'
")
```
You can also use `INSERT INTO` and pass a JSON object (where you can specify the `id`), or use a more SQL like with `INSERT INTO ... VALUES`:
```elixir
Conn.sql("
INSERT INTO developper {id: 'bob_id', name: 'bob', status: 'trainee' };
")
Conn.sql("
INSERT INTO developper (name, status, id) VALUES ('mike', 'founder', 'mike');
")
```
#### Note on the `SurrealEx.HTTP` module
You can use the module [SurrealEx.HTTP](https://hexdocs.pm/surreal_ex/SurrealEx.HTTP.html#create/3): you reach the REST API provided by SurrealDB. You pass the config, the table name and a payload. Note that you cannot pass an id. SurrealDB will assign an id for you. In this case, the query below can be run multiple times. We don't use it here.
```elixir
{:create, row_id} = SurrealEx.HTTP.create(cfg, :developper, "{name: 'john', status: 'trainee'}")
[_, john_id] = String.split(row_id, ":")
```
We check the table:
```elixir
Conn.sql("SELECT * FROM developper;")
```
#### Transaction
You can use a **transaction** for a set of changes. You can use `CREATE` and pass params, or run an `INSERT INTO` and pass a JSON object.
```elixir
query = "
BEGIN TRANSACTION;
CREATE developper:lucio SET name = 'lucio', status = 'dev';
INSERT INTO developper {name: 'nd', id: 'nd', status: 'trainee'};
COMMIT TRANSACTION;"
Conn.sql(query)
```
#### Multiple inserts
<!-- livebook:{"break_markdown":true} -->
You pass an array of tuples. You can pass an id, or let Surreal do it for you if you don't need to retrieve them immediatly.
```elixir
Conn.sql("
INSERT INTO a_table [{name: 'one'}, {name: 'two', id: 'two'}];
")
Conn.sql("SELECT * FROM a_table")
```
An example of inserting a CSV file into a SurrealDB table is given further down with Elixir.
You can also use the form:
```sql
INSERT INTO users (name, location) VALUES ('La Cantine', 'Nantes'),('Work In Excess', 'Nantes'),...
```
<!-- livebook:{"break_markdown":true} -->
### Timestamps
We can use [futures](https://surrealdb.com/docs/surrealql/datamodel/futures) for this. It is a value that will be dynamically computed on every access to the record.
For example, we create a record with an id, a (fixed) "created_at" field, and a _future_ field "updated_at":
```elixir
Conn.sql("
CREATE a_table:1 SET name='one', created_at=time::now(), updated_at=<future>{time::now()}
")
```
```elixir
Conn.sql("
UPDATE a_table:1 SET name='new one';
")
```
#### Record links
You can pass a nested object with the _dot_ **"."** format. Note how we can pass **record links** of the "developper" table into the "webapp" table.
```elixir
Conn.sql("
CREATE app_table:surreal SET
app_name = 'surrealistic.com',
agency.team = [developper:nelson, developper:lucio, developper:`#{john_id}`, developper:nd],
agency.name = 'dwyl.com';
")
```
You can pass the data in JSON format:
```elixir
Conn.sql("
INSERT INTO app_table {
id: 'unreal',
app_name: 'unreal',
agency: {
name: 'dwyl.com',
team: [developper:nelson, developper:lucio]
}
};
")
```
```elixir
Conn.sql("
UPDATE app_table:unreal SET app_name='unreal.com';
")
```
#### Query nested records without "join"
We can get linked data **without joins** with the dot `.` notation:
```elixir
Conn.sql("
SELECT agency.team.*.name, agency.team.*.status FROM app_table:surreal;
")
```
We can get the name of the devs with status 'dev' and the app for a given agency:
```elixir
Conn.sql("
SELECT app_name,agency.team[WHERE status='dev'].name AS dev
FROM app_table
WHERE agency.name = 'dwyl.com';
")
```
#### Aggregation query
Return the number of developpers in the team for the webapp "unreal" with [array functions](https://surrealdb.com/docs/surrealql/functions/array). With the dot `.`, we change chain and dig in the relation:
```elixir
{:ok, %{result: result}} = Conn.sql("
SELECT * from array::len(app_table:unreal.agency.team);
")
result
```
You can also use `count()`. Return the number of developpers and their names per project:
```elixir
Conn.sql("
SELECT
app_name,
agency.name AS agency,
agency.team.*.name AS members,
count(agency.team) AS team_count
FROM app_table;
")
#
```
#### Type functions
You can use [type functions](https://surrealdb.com/docs/surrealql/functions/type) that converts a string into the desired type:
```elixir
Conn.sql("
SELECT count() AS total, app_name FROM type::table('app_table') GROUP BY app_table;
")
```
You can use the generic `type:thing` and pass the object and identifier:
```elixir
Conn.sql("
SELECT * FROM type::thing('app_table', 'unreal');
")
```
#### Parameters and subqueries
We can run queries with parameters. Given a webapp, get the names of the team members with status "dev":
```elixir
Conn.sql("
LET $status = 'dev';
LET $team = (SELECT agency.team[WHERE status=$status] FROM app_table:unreal);
SELECT name, status FROM $team;
")
```
## Graph connections
Suppose we have a 1-n relation between agencies and webapps, and a 1-n relation between agencies and devs. In a conventional DB, we would have a foreign key "agency_id" in the table "webapps" ("belongs_to" agencies), and a foreign key "agency_id" in the "devs" table ("belongs_to" agencies).
<!-- livebook:{"break_markdown":true} -->
```mermaid
erDiagram
agencies ||--|{ webapps : developped
webapps {
string app_name
string agency_id
}
agencies {
int name
}
agencies ||--|{ devs : hires
devs {
string name
string status
string agency_d
}
```
<!-- livebook:{"break_markdown":true} -->
With SurrealDB, we can use 2 approaches:
* set an array of [webapp_ids] and an array of [dev_ids] as fields of the table agencies,
* or set connections between the nodes.
<!-- livebook:{"break_markdown":true} -->
#### Array of linked records
The ERD of the first approach is shown below:
<!-- livebook:{"break_markdown":true} -->
```mermaid
erDiagram
webapps {
string name
}
agencies {
int name
array webapps
array team
}
agencies ||--|{ webapps : developped
agencies ||--|{ devs : hires
devs {
string name
string status
}
```
<!-- livebook:{"break_markdown":true} -->
We added a field "team" and webapps which respectively stores the references to devs employed and to webapps developed by an agency.
```elixir
Conn.sql("
BEGIN TRANSACTION;
create webapp:app1 set name = 'app1';
create webapp:app2 set name = 'app2';
create webapp:app3 set name = 'app3';
create developper:nelson set name = 'nelson', status = 'founder';
create developper:nd set name = 'nd', status = 'trainee';
create developper:lucio set name = 'lucio', status = 'dev';
create agency:dwyl1 set name = 'dwyl', project = [], team = [];
create agency:unreal1 set name = 'unreal', project = [], team = [];
update agency:dwyl1 set projects += [webapp:1, webapp:3];
update agency:unreal1 set projects += [webapp:2];
update agency:dwyl1 set team += [developper:nelson, developper:lucio];
update agency:unreal1 set team += [developper:nd];
COMMIT TRANSACTION;
")
```
We can get the team members name per agency (by omitting the id) since we have a relation 1-n with record links:
```elixir
Conn.sql("SELECT name AS company, team.*.name AS employees FROM agency:dwyl1")
```
Conversely, find the agency for which a dev works. We used the `CONTAINS` [operator](https://surrealdb.com/docs/surrealql/operators).
```elixir
Conn.sql("SELECT name AS company FROM agency WHERE team CONTAINS developper:nd; ")
```
#### Connections
The second approach with connections is shown below. We setup 2 connections with the 2 verbs "works_for" and "developped". This will generate 2 other tables.
* `[dev:id]->works_for->[agency:id]`
* `[agency:id]->developped->[webapp:id]`
The order is not important as in the first case, it is a 1-1, and a 1-n in the second case. It turns out that we can reverse the links are we will see. A connection is coded with `RELATE @from->verb->@to`
<!-- livebook:{"break_markdown":true} -->
```mermaid
erDiagram
webapps {
string name
}
developped {
string created_at
}
agencies {
int name
}
agencies ||--|| developped : developped
developped ||--|{ webapps : developped
agencies ||--|| works_for : works_for
works_for ||--|| devs : works_for
works_for {
boolean owner
string created_at
}
devs {
string name
string status
}
```
```elixir
Conn.sql("
BEGIN TRANSACTION;
CREATE agency:dwyl2 SET name = 'dwyl';
CREATE agency:unreal2 SET name = 'unreal';
RELATE developper:lucio->works_for->agency:dwyl2 CONTENT {owner: false, created_at: time::now()};
RELATE developper:nelson-> works_for->agency:dwyl2 CONTENT {owner: true, created_at: time::now()};
RELATE developper:nd->works_for->agency:unreal2 CONTENT {owner: true, created_at: time::now()};
RELATE agency:dwyl2->developped->webapp:app1;
RELATE agency:unreal2->developped->webapp:app2;
RELATE agency:dwyl2->developped->webapp:app3;
COMMIT TRANSACTION;
")
```
We can take a peek at the association-table "works_for":
```elixir
Conn.sql("select * from developped;")
```
We have an association `[dev:id]->works_for->[agency:id]`. We can query for the _agency.name_ given a _dev:id_:
```elixir
Conn.sql("SELECT name, ->works_for->agency.name AS employer FROM developper:nelson;")
```
We now want all the devs working for a particular agency. We just _revert_ the relation: get all _dev:id_ from _agency:id_:
```elixir
Conn.sql("
SELECT name, <-works_for<-developper.name AS employees FROM agency:dwyl2;
")
```
Similarly, we can check the webapps name developped by an agency with the association with `[agency:id]->developped->[webapp:id]`.
```elixir
Conn.sql("SELECT ->developped->webapp.name AS agency FROM agency:dwyl2;")
```
To query the agency which developped a given webapp, we reverse the query:
```elixir
Conn.sql("SELECT <-developped<-agency.name AS agency FROM webapp:app2;")
```
We can run _subqueries_ if we want the devs that worked on a given webapp:
```elixir
Conn.sql("
LET $agency=(SELECT <-developped<-agency.id AS id FROM webapp:app1);
SELECT <-works_for<-developper.name AS employees FROM $agency;
")
```
## Schemafull
If we want to enforce a fixed struct, we can define a schema with `DEFINE TABLE..SCHEMAFULL`.
We will check this enforcement below.
The ERD shows a one_to_many_through relation with 3 tables. Apps has many users, and uses has one details. We set a 1-N relation between app and users, and 1-1 between users and details. We elaborate ith 2 examples:
* one with 3 tables where we pass an array of references (apps<-[users:id]) and a reference to another table (users <- details:id),
* and one with 2 tables where we pass an array of references (apps<-[users:id]) and an object (users.details{}) that mirrors the thrid table.
**How to pass an array of references?**
Since we will have nested data in each table, we create a field that should be an _array of references_ to the other table. For this:
* create a field say `team` of type `array` in the table "apps",
* and declare the `team.*` of type `record(users)` which are references to rows of the table "users".
For the table users, we have a 1-1 relation with the table details. In the first case mentionned above, we add a field of type `record(details)`. In the second case, we declare a field of `TYPE object` and declare the nested fields in the table "users" that mirror the "details" table that is not used.
We also use `INDEX` to enforce uniqueness. We also showcased field constraints and default values (with `VALUE $value`)
<!-- livebook:{"break_markdown":true} -->
<!-- Learn more at https://mermaid-js.github.io/mermaid -->
```mermaid
erDiagram
apps ||--|{ users: used_by
apps {
string name
string agency
array team
}
users ||--o| details : details
users {
int name
array details
}
details {
string email
int age
date birthdate
}
```
<!-- livebook:{"break_markdown":true} -->
##### First case: 3 tables
```elixir
Conn.sql("
BEGIN TRANSACTION;
DEFINE TABLE details SCHEMAFULL;
DEFINE FIELD email ON details TYPE string;
DEFINE FIELD age ON details type int ASSERT is::numeric($value) and $value>18;
DEFINE FIELD birthdate ON details TYPE string;
DEFINE TABLE users SCHEMAFUL;
DEFINE FIELD name ON users type string;
DEFINE FIELD details ON users TYPE record(details);
DEFINE INDEX users ON TABLE users COLUMNS name UNIQUE;
DEFINE TABLE apps SCHEMAFULL;
DEFINE FIELD name ON apps TYPE string;
DEFINE FIELD agency ON apps TYPE string VALUE $value OR 'dwyl;
DEFINE FIELD team ON apps TYPE array;
DEFINE FIELD team.* ON apps TYPE record(users);
DEFINE INDEX name ON TABLE apps COLUMNS name UNIQUE;
COMMIT TRANSACTION;
")
```
We can `INSERT INTO` (and set the `:id`), or `CREATE SET`. We can pass nested links. SurrealDB [understands dates](https://surrealdb.com/docs/surrealql/datamodel/datetimes).
```elixir
Conn.sql("
BEGIN TRANSACTION;
INSERT INTO details {id: 'john', email: 'john@com', age: 20, birthdate: '2023-03-01'};
INSERT INTO users {name: 'john', id: 'john', details: details:john};
CREATE details:lucio SET email='lucio@com', age = 20, birthdate = '2023-03-01';
INSERT INTO users { id: 'lucio', name: 'lucio', details: details:lucio};
INSERT INTO users {name: 'nelson', id: 'nelson'};
INSERT INTO apps {id: 'test', agency: 'dwyl', team: []};
COMMIT TRANSACTION;
")
```
```elixir
Conn.sql("SELECT * FROM apps;")
```
#### Prevent bad insertions
Let's insert a "bad" record into the "details" table: we see that the field "age" has been set to 0, and the field "occupation" ignored.
```elixir
Conn.sql("
INSERT INTO details {gender: 'f', occupation: 'eng', age: 'twenty', id: 'wrong'};
")
```
#### Insert into a nested array
Let's create a new "dev" and add him as a team member: we use `array:concat` ([doc](https://surrealdb.com/docs/surrealql/functions/array)) or simply `+=`.
Before:
```elixir
Conn.sql("SELECT team from apps:test;")
```
Let's update and check if only filtered data of type "users" is accepted:
```elixir
Conn.sql("
UPDATE apps:test SET team += [users:nelson, users:lucio, 'ok'], bad = 'input';
")
```
After:
```elixir
Conn.sql("
SELECT team FROM apps:test;
")
```
### Nested query without "join"
Let's select the names of the users for all apps developped by the agency "dwyl":
```elixir
Conn.sql("
SELECT team.*.name FROM apps WHERE agency = 'dwyl'
")
```
##### Second case: 2 tables
Instead of defining a third table "details", we pass an object of `TYPE object` as a field of the table "users", and define the fields as nested attributes (`details.owner` for example). Since we defined a SCHEMAFULL table, the input is still filtered. For example, we can't pass an extra attribute in the "details" object on the talbe "users".
```elixir
Conn.sql("
BEGIN TRANSACTION;
DEFINE TABLE users1 SCHEMAFUL;
DEFINE FIELD name ON users1 type string;
DEFINE FIELD details ON users1 TYPE object;
DEFINE FIELD details.age ON users1 TYPE int;
DEFINE FIELD details.owner ON users1 TYPE bool;
DEFINE INDEX users1 ON TABLE users1 COLUMNS name UNIQUE;
DEFINE TABLE apps1 SCHEMAFULL;
DEFINE FIELD name ON apps1 TYPE string;
DEFINE FIELD agency ON apps1 TYPE string;
DEFINE FIELD team ON apps1 TYPE array;
DEFINE FIELD team.* ON apps1 TYPE record(users1);
DEFINE INDEX name ON TABLE apps1 COLUMNS name UNIQUE;
COMMIT TRANSACTION;
")
```
```elixir
Conn.sql("
DELETE apps1:app1;
DELETE users:1;
INSERT INTO users1 {
id: 1,
name: 1,
details: {
age: 20,
owner: false,
test1: 'bad'
}
};
INSERT INTO users1 {
id: 2,
name: 2,
details: {
age: 20,
owner: true
}
};
INSERT INTO apps1 {
name: 'app1',
agency: 'surreal',
team: [users1:1, users1:2, 'toto']
};
")
```
## Events
Let's create an **event** query example. When we change a field, say the birthdate of the table details, we want to create a new table that records this new date. We can do this with `DEFINE EVENT`:
```elixir
Conn.sql("
DEFINE EVENT passed_birthdates ON TABLE details
WHEN $before.birthdate < $after.birthdate
THEN (CREATE passed_birthdates SET birthdate = $after.birthdate);
")
```
We update 2 rows of the table "details":
```elixir
Conn.sql("UPDATE details:john SET birthdate='2023-03-02';")
Conn.sql("UPDATE details:lucio SET birthdate='2023-03-02';")
```
We check that the change in the field triggered the action to create a new table where the record has the field with value the new date.
```elixir
Conn.sql("SELECT * FROM passed_birthdates;")
```
## Register User
Provides a registration via JWT
[Source tutorial](https://tutorials.surrealdb.com/community/how-to-use-signin-and-signup.html)
```elixir
query = "
#---define SCHEMAFULL and PERMISSIONS, then INDEX and SCOPE
DEFINE TABLE user SCHEMAFULL
PERMISSIONS
FOR select, update WHERE id = $auth.id,
FOR create, delete NONE;
DEFINE FIELD user ON user TYPE string;
DEFINE FIELD pass ON user TYPE string;
DEFINE FIELD email ON user TYPE string;
DEFINE FIELD role ON user TYPE int;
DEFINE INDEX idx_user ON user COLUMNS user UNIQUE;
DEFINE SCOPE allusers
#-- the JWT session will be valid for 14 days
SESSION 14d
/* The optional SIGNUP clause will be run when calling the signup method for this scope
It is designed to create or add a new record to the database.
If set, it needs to return a record or a record id
The variables can be passed in to the signin method */
SIGNUP ( CREATE user SET user = $user, email = $email, role = $role, pass = crypto::argon2::generate($pass))
/* The optional SIGNIN clause will be run when calling the signin method for this scope
It is designed to check if a record exists in the database.
If set, it needs to return a record or a record id
The variables can be passed in to the signin method */
SIGNIN ( SELECT * FROM user WHERE user = $user AND crypto::argon2::compare(pass, $pass) )
#-- this optional clause will be run when calling the signup method for this scope
"
Conn.sql(query)
```
```elixir
{:ok, token} = Conn.register("dev", "4321", "hot@mail.com")
```
```elixir
Conn.sql("SELECT * FROM use WHERE email = 'hot@mail.com';")
```
```elixir
Conn.login("admin", "1234")
```
```elixir
Conn.login("dev", "supersecret")
```
## Insert Elixir maps
Use the `SurrealEx.HTTP.Table` module. It reaches the REST API provided by SurrealDB.
```elixir
defmodule FromAirportMap do
use SurrealEx.HTTP.Table,
conn: Conn,
table: "airports"
end
```
Then you can insert Elixir maps:
```elixir
Conn.sql("DELETE airports")
FromAirportMap.create(%{country: "FR", IATA: "CDG"})
Conn.sql("SELECT * FROM airports")
```
## Insert a CSV file
Let's download a CSV file, parse it into a map and insert into a SurrealDB table. We will use the unique POST "/sql" endpoint with the routine `Conn.sql`.
Firstly, since SurrealEx seems to use HTTPoison to POST to the server, we build a wrapper around it to stream down a file.
```elixir
defmodule HTTPoisonStream do
@doc """
Uses `HTTPoison` and builds a custom stream from `HTTPoison.AsyncChunk`.
"""
def download(url) do
Stream.resource(
# start_fun: stream line by line with `async: :once`
fn -> HTTPoison.get!(url, %{}, stream_to: self(), async: :once) end,
# next_fun,
fn %HTTPoison.AsyncResponse{id: id} = resp ->
receive do
%HTTPoison.AsyncStatus{id: ^id, code: _code} ->
HTTPoison.stream_next(resp)
{[], resp}
%HTTPoison.AsyncHeaders{id: ^id, headers: _headers} ->
HTTPoison.stream_next(resp)
{[], resp}
%HTTPoison.AsyncChunk{id: ^id, chunk: chunk} ->
HTTPoison.stream_next(resp)
{[chunk], resp}
%HTTPoison.AsyncEnd{id: ^id} ->
{:halt, resp}
after
5000 ->
raise "timeout"
end
end,
# end_fun
fn %HTTPoison.AsyncResponse{id: id} = _resp ->
:hackney.stop_async(id)
end
)
end
end
```
This is where we will get a downloadable CSV dataset:
```elixir
unzipped_airports =
"https://pkgstore.datahub.io/core/airport-codes/airport-codes_csv/data/e07739e49300d125989ee543d5598c4b/airport-codes_csv.csv"
```
We parse a CSV line into a map with this module:
```elixir
defmodule AirportDataset do
@moduledoc """
Provides mapping for the Airport dataset: <https://datahub.io/core/airport-codes>
"""
def map(row) do
%{
ident: Enum.at(row, 0),
type: Enum.at(row, 1),
name: Enum.at(row, 2),
elevation_ft: Enum.at(row, 3),
continent: Enum.at(row, 4),
iso_country: Enum.at(row, 5),
iso_region: Enum.at(row, 6),
municipality: Enum.at(row, 7),
gps_code: Enum.at(row, 8),
iata_code: Enum.at(row, 9),
local_code: Enum.at(row, 10),
coordinates: Enum.at(row, 11)
}
end
end
```
We can now download the file (small 6MB), CSV parse it, and parse into an Elixir map and finally, insert into a SurrealDB table with the module FromAirportMap:
```elixir
:timer.tc(fn ->
HTTPoisonStream.download(unzipped_airports)
|> Stream.map(&IO.chardata_to_string/1)
|> CSV.decode!(headers: false, separator: ?,)
|> Stream.map(&AirportDataset.map/1)
|> Stream.chunk_every(1000)
|> Stream.map(fn chunk ->
data = Jason.encode!(chunk)
Conn.sql("
BEGIN TRANSACTION;
INSERT INTO airports #{data};
COMMIT TRANSACTION;
")
end)
|> Stream.run()
end)
```
It takes around 10s to process this small file. The same operation with the relational database SQLite takes less than 1 second.
The SELECT query is still fast (2ms)
```elixir
Conn.sql("SELECT * FROM airports limit 2;")
```
```elixir
Conn.sql("SELECT count() from airports GROUP BY all ;")
```
Let's select the airports listed in Valledupar, Colombia. It takes around 200ms.
```elixir
Conn.sql("SELECT * FROM airports WHERE municipality = 'Valledupa';")
```
Let's select one, "SK-151". It takes approx 180ms.
```elixir
Conn.sql("Select * FROM airports WHERE ident = 'SK-151';")
```
## Schemaless vs indexed Schemafull
Let's build a schemafull table 'airport" from the schemaless table "airports" and add an INDEX on the column "ident" to evaluate the performance.
```elixir
Conn.sql("
BEGIN TRANSACTION;
DEFINE TABLE airport SCHEMAFULL;
DEFINE INDEX ident ON TABLE airport COLUMNS ident UNIQUE;
DEFINE FIELD ident ON TABLE airport TYPE string;
DEFINE FIELD elevation_ft ON TABLE airport TYPE string;
DEFINE FIELD name ON TABLE airport TYPE string;
DEFINE FIELD continent ON TABLE airport TYPE string;
DEFINE FIELD iso_country ON TABLE airport TYPE string;
DEFINE FIELD iso_region ON TABLE airport TYPE string;
DEFINE FIELD municipality ON TABLE airport TYPE string;
DEFINE FIELD itata_code ON TABLE airport TYPE string;
DEFINE FIELD local_code ON TABLE airport TYPE string;
DEFINE FIELD gps_code ON TABLE airport TYPE string;
DEFINE FIELD coordinates ON TABLE airport TYPE string;
COMMIT TRANSACTION;
")
```
We copy the schemaless table "airports" into the schemafull table "airport". It takes 1.2s.
```elixir
Conn.sql("
BEGIN TRANSACTION;
LET $data = (SELECT * FROM airports);
INSERT INTO airport $data;
COMMIT TRANSACTION;
")
```
Let's query on the indexed column "ident". The same query as above takes 150ms. The performance gain seems modest.
```elixir
Conn.sql("SELECT * FROM airport WHERE ident = 'SK-151';")
```
## Compare with SQLite
We setup the SQLite connection, database and create a table to mirror the CSV file we got. The setup is much heavier than SurrealDB.
Our dependencies are:
<!-- livebook:{"force_markdown":true} -->
```elixir
Mix.install([
{:surreal_ex, "~> 0.2.0"},
{:csv, "~> 3.0"},
{:ecto_sql, "~> 3.9"},
{:ecto_sqlite3, "~> 0.9.1"}
])
```
```elixir
defmodule SqilteConn do
def start do
Exqlite.Sqlite3.open(":memory")
end
def create_table(conn, table) do
Exqlite.Sqlite3.execute(conn, "DROP TABLE IF EXISTS #{table}")
Exqlite.Sqlite3.execute(
conn,
"CREATE TABLE IF NOT EXISTS #{table} (
id integer primary key,
ident text,
elevation_ft text,
type text,
name text,
continent text,
iso_country text,
iso_region text,
municipality text,
iata_code text,
local_code text,
gps_code text,
coordinates text
)"
)
end
def reset_table(conn, table) do
Exqlite.Sqlite3.execute(conn, "DROP TABLE IF EXISTS #{table}")
create_table(conn, table)
end
end
{:ok, conn} = SqilteConn.start()
SqilteConn.reset_table(conn, "csv")
SqilteConn.create_table(conn, "csv")
```
We create a Repo and a Repo since SQLite adaptor ecto_sqlite3 works best with it (as per the docs for the SQLite adaptor for Elixir).
The Repo:
```elixir
defmodule Repo do
use Ecto.Repo, adapter: Ecto.Adapters.SQLite3, otp_app: :noop
end
case Repo.start_link(database: ":memory", default_chunk_size: 100) do
{:ok, pid} -> {:ok, pid}
{:error, {_, pid}} -> {:ok, pid}
end
```
The schema:
```elixir
headers = [
:ident,
:elevation_ft,
:type,
:name,
:continent,
:iso_country,
:iso_region,
:municipality,
:iata_code,
:local_code,
:gps_code,
:coordinates
]
defmodule Airport do
use Ecto.Schema
@headers headers
schema "csv" do
Enum.each(@headers, &field(&1, :string))
end
end
```
Let's check that everything is setup:
```elixir
Airport.__schema__(:fields) |> IO.inspect()
Repo.all(Airport)
```
We can now stream down the CSV endpoint into an SQLite table:
```elixir
:timer.tc(fn ->
Repo.transaction(fn ->
HTTPoisonStream.download(unzipped_airports)
|> Stream.map(&IO.chardata_to_string/1)
|> CSV.decode!(headers: false, separator: ?,)
|> Stream.map(&AirportDataset.map/1)
|> Stream.chunk_every(1000)
|> Stream.each(&Repo.insert_all(Airport, &1))
|> Stream.run()
end)
end)
```
The aggregation query below took 5ms, much less than the SurrealDB equivalent:
```elixir
Repo.aggregate(Airport, :count)
```
Let's select a row with "ident = SK-151". It takes 10ms, much less than SurrealDB.
```elixir
Repo.get_by(Airport, ident: "SK-151")
```
Let's create an INDEX on the same column "ident":
```elixir
Exqlite.Sqlite3.execute(conn, "
CREATE INDEX ident_idx ON csv (ident);
")
```
The query takes now 1ms.
```elixir
Repo.get_by(Airport, ident: "SK-151")
```
