https://qiita.com/iisaka51/items/45f23df873ea5be433c7
@iisaka51(Goichi (Iisaka) Yukawa)
Rust
Database
RocksDB
TiKV
surrealdb
Last updated on May 15, 2023
Posted on January 21, 2023
Introduction.
This document summarizes the SurrealDB, which was released in July 2022.
History of SurrealDB
Development began in 2016, although it has been publicly available for only a short period of time
Feb 2016 Development started in GoLang
2017 Jul Began operation as a SaaS backend DB
2021 Oct Decided to be released as Open Source, rebuilt in Rust
2022 Jul Beta.1 is released
2022 Aug Beta.5 is released
2022 Oct Beta.8 release
SurrealDB, Inc.
Nov 2021 SurrealDB Ltd. established in London
Jan 2023 $6M raised for DBaaS
SurrealDB's Background
Major Trends
Database abstraction, cloud, serverless
More and more companies are adopting DBaaS
61% of developers/operators surveyed by MariaDB have completed or are about to complete a full > migration to DBaaS
DBaaS' Growing Market Size
To $24.8 Billion by 2025
Rich investment environment for DBaaS
SingleStore raises $30M (2022/Oct)
EdgeDB raises $15M (2022/Nov)
SurrealDB raises $6M (Jan 2023)
SurrealDB in the Spotlight
Hacke's News Top Page
No. 4 2022/Aug
No. 2 2022/Sep
SurrealDB is ranked in GitHub Featured Repositories
2022/Aug
2022/Dec
GitHub Star
From 180 star to 1500 star in 48 hours
5,000 stars in 3 weeks after publishing
10,000 stars in 4 weeks after publishing
No. 1 on Reddit's Hot List for Proggramming and Rust Sections
SurrealDB License
SurrealDB source code is licensed under Business Software License 1.1
SDK and libraries/drivers are MIT
SurrealDB BSL allows you to use SurrealDB on an unlimited number of nodes as long as you do not offer it as a commercial DBaaS
Can be integrated into your product
SurrealDB BSL is valid for 4 years
January 1, 2026, this restriction expires and the code becomes open source under the current Apache License 2.0
Free to use for any purpose
SurrealDB Features
Implemented in Rust
Segmentation Fault resistant
Cross-compilation
Relatively fast compared to other languages
Lightweight: Binary size: Linux: 24MB, macOS: 44MB
One binary can be used as both server and REPL client
Easy to install
Supports HTTP/Restful APIs
Supports WebSockets
Backend DB: EchoDB, RocksDB, TiKV, FoundationDB, IndexedDB
Features as database
Schema-less: No problem to define a schema
Various storage formats: tables, documents, graphs, etc.
Multi-row, multi-table ACID transactions
Record link and directed graph connections
No JOIN required, smartly avoids N+1 problem
Predefined analytical queries
Select, aggregate, group, and order data as it is written
Can extend queries with embedded JavaScript
Can write regular expressions in queries (/regex/)
Supports GeoJSON
Can perform CRUD operations in parallel
SurrealDB Strengths
Differentiating features at the same time
Users can access directly from the frontend
Database can be properly configured for authentication and authorization
Real-time data synchronization
Fewer competitors, such as Google Firestore
On-premise use is a requirement for a one-of-a-kind DB
Authentication and authorization on the database side...
Can provide role-based access control
Access control based on defined roles such as administrator, editor, viewer, etc.
Weaknesses of SurrealDB
Not yet released to the public (Jul 2022)
Potential security vulnerabilities and bugs may exist
PostgreSQL first published in 1997, predecessor Postgress in 1989)
MySQL first published in 1995
MariaDB first published in 2009
MongoDB first published in 2009
Little information available
Official documentation is under construction
If in doubt, read the source code
Not all features have been implemented
SurrealDB is not profitable at this time: DBaaS service will be available in 2023
Features currently under development (not completed even in beta.9)
Multi-node support in distributed mode
Replication
Health checks
GraphQL
FULLTEXT - full text indexing
LEARN fields
Automatic configuration based on machine learning analysis of specified fields
Versioned temporary tables
Ability to "go back in time" when referencing data
IDE code highlighting (Atom, VSCode, Vim...)
User I/F app release is in the plans as 1.x
SurrealDB 2-Tier architecture overview
surrealdb_arch.png
SurrealDB Operational Overview
surrealdb_dataflow.png
Source Code
Numbers are rows including comments (SurrealDB 1.0.0-beta.8)
Incidentally, MariaDB 10.9 has over 1.3 million rows (over 12,000 rows for the client alone)
|-- src // api layer 4172
|-- net 1981
|-- cli 895
|-- rpc 174
...
|-- lib/src // BL Layer 32261
|-- sql 19030
|-- fnc 3524
...
|-- kvs 3286
|-- indexdb 220
|-- rocksdb 316
|-- tikv 269
...
SurrealDB implements surprisingly few features
Use nom for query parsing
Can build parsers incrementally by concatenating functions
Use echodb for memory storage (Tobie)
In-Memory KVS DB with multi-version concurrency control
Use storekey to store to KVS (Tobie)
Preserves dictionary order and binary encoding
Useful for creating sorted KVS keys
Use MsgPack and serde for serialization/deserialization (rmp-serde)
Use geo for GeoJSON parsing
Use RocksDB to use local files as datastore
TiKV and FoundationDB are used for distributed DB functionality
SurrealDB stores the following items in KVS
Metadata
Tables, indexes, scopes, and other structures
Data
Values of objects held by SurrealDB
How SurrealDB stores to KVS
Map Rust structures and MsgPack with serde bindings
Storage size savings
Efficiency of serialization/deserialization
Two ways to access data in KVS
Key-based: specify a specific key and get the value (fast)
Scan: specify a range of keys and retrieve all values (slow)
Hierarchical structure is kept in the key
Namespace -> Database -> Table -> ID
SurrealDB converts keys into ranges to be scanned by building hierarchies
Installation (easy & fast)
Linux
$ curl -sSf https://install.surrealdb.com | sh
macOS
$ brew install surrealdb/tap/surreal
Windows
PS C:\> iwr https://windows.surrealdb.com -useb | iex
Docker/Podmandocker
$ docker run --rm -p 8000:8000 surrealdb/surrealb:latest start
$ podman run --rm -p 8000:8000 surrealdb/surrealdb:latest start
Starting SurrealDB
The first argument of the start subcommand is the destination to which data will be written.
Default is memory
$ surreal start --user root --pass root memory
Other destinations
file:///path/to/data.db file system (RocksDB)
rocksdb:///path/to/data.db RocksDB
tikv://endpoiint TiKV TiKV
fdb:[///path/to/clusterfile] FoundationDB (rebuild required)
STRICT mode
--strict Start by giving options
NAMESPACE,, DATABASE must be defined or error will occur
Error if TABLE is not defined
Dump/Restore
Dump to file with export subcommand
Restore from file with import subcommand
$ surreal export --conn http://dev00:8000 --ns test --db test dump.db
$ surreal import --conn http://dev00:8000 --ns test --db test dump.db
Connect from CLI client
Execute the sql subcommand
$ surreal sql --conn http://dev00:8000 --ns test --db test --pretty
--user and --pass ROOT authentication user/password
--pretty to format and display JSON output
--ns NAMESPACE --db instruct DATABASE
When SurrealDB is running in STRICT mode, the -ns and -db options are ignored
HTTP RESTful API
PATH TYPE Description
/key/:table GET Retrieve all records in a table from a database
/key/:table/:id GET Retrieve a specific record from the database
/key/:table POST Create a record in a table in the database
/key/:table/:id POST Create a specific record in a table in the database
/key/:table/:table DELETE Delete all records of a table from the database
/key/:table/:id PUT Update a specified record in the database
/key/:table/:id PATCH Modifies a given record in the database
/key/:table/:id DELETE deletes a given record in the database
HTTP RESTful API (cont.)
PATH TYPE Description
/version GET return SurrealDB version
/signup POST Register for SCOPE authentication
/signin POST Log in with SCOPE authentication
/rpc POST Request WebSocket with JON-RPC
/sql POST Allow SurQL queries
/export GET Dump database contents
/import POST Apply query contents to database (restore)
HTTP RESTful API (incomplete)
PATH TYPE Description
/sync GET replication
/health GET database health check
/status GET return status
Table definitions in general SQL
CREATE TABLE human (
id int, nickname text, nickname
nickname text, age int, age
age int, nickname
PRIMARY KEY(id)
);
SurealDB is schema-less
No need to define tables or fields
No need to change when adding fields
If the server is started in STRICT mode, table definitions are required first
CREATE human:freddie SET nickname="freddie", age=99 ;
CREATE human:brian SET nickname="brian", age=75, sex=true ;
ID == TableName:UniqID Notice that the ID contains the table name.
Specify field type without schema
CREATE human:freddie SET
nickname = <string> "freddie",.
age = <int> 99 ;
Types and casts
bool, int, float, string, number, decimal, datetime, duration
Date/time strings are converted to ISO8601: same as casting with <datetime>.
If you want to treat a datetime string as a string, cast with <string>.
SELECT * FROM "2023-01-01";
SELECT * FROM <datetime> "2023-01-01";
SELECT * FROM <string> "2023-01-01T02:03:00Z" + "-test";
Define Schema
DEFINE TABLE human SCHEMAFULL;
DEFINE FIELD nickname ON human TYPE string;
DEFINE FIELD age ON human TYPE int;
Re]define table as SCHMALESS
DEFINE TABLE human SCHEMALESS ;
DEFINE TABLE human SCHMAFULL; [re]define table as SCHMAFULL
DEFINE TABLE human SCHEMAFULL ;
SCHEMAFULL
Only data allowed in the defined fields will be stored.
Can be restricted to specific data types
DEFINE FIELD can set a default value if no data is entered
The value to be set is set to $value
DEFINE TABLE person SCHEMAFULL;.
DEFINE FIELD name ON person TYPE string VALUE $value OR 'guest';
Adding Data
If the -id field is omitted, the ID is automatically set
INSERT INTO human (nickname, age)
VALUES ('brian', 75);
INSERT INTO human (id, nickname, age)
VALUES ('human:freddie', 'freddie', -1);
CREATE human:robert SET nickname='robert', age=30;
CREATE human SET
id = human:jack, nickname='jack', age=30;
CREATE human CONTENT
{ id: 'human:john', nickname: 'john', age: 99 };
INSERT
Can UPDATE when there are duplicate IDs
INSERT INTO test (id, test, something)
VALUES ('tester', true, 'other') ;
INSERT INTO test (id, test, something)
VALLUES ('tester', true, 'other' )
ON DUPLICATE KEY UPDATE something = 'else' ;
Nesting Fields
Fields in a table can be nested.
Nested fields can be referenced by dot notation
UPDATE person:test CONTENT {
settings: {
nested: {
object: {
thing: 'test'
}
}
}
}; }
SELECT settings.nested.object FROM person ;
NONE and NULL
Field values can have NONE and NULL
NONE: no value is set
NULL: empty value is set
CREATE person:test1 SET email = 'info@example.com';
CREATE person:test2 SET email = NONE;
CREATE person:test3 SET email = NULL;
USE
Specify NAMESPACE and DATABASE to be used
Effective when accessing with ROOT authentication
USE NAMESPACE test ;
USE NAMESPACE test DATABASE db1 ;
USE NS test DB db1 ;
Relation in general SQL
CREATE TABLE armor (
id int, name text, name
name text,
resistance text,
PRIMARY KEY(id)
);
INSERT INTO armor VALUES
(0, "leather", 3);
(1, "platemail", 30), (2, "chainmail", 20), (3, "chainmail", 5), (4, "chainmail", 5)
(2, "chainmail", 20), (2, "chainmail", 20), (2, "chainmail", 20)
CREATE TABLE player (
name text,
strength int, armor_id int, armor_id int, armor_id int, armor_id int
armor_id int,
PRIMARY KEY((name),
CONSTRAIN fk_armor
FOREIGN KEY(armor_id)
REFERENCECS armor(id)
);
Relation in SurrealQL (SurQL)
CREATE armor:leather SET registance = 3;
CREATE armor:chainmail SET registance = 20;
CREATE armor:platemail SET registance = 30;
CREATE player:jack SET strength = 22, armor = armor:platemail;
CREATE player:brian SET strength = 20, armor = armor:leather;
Use that the ID contains the table name
SurQL: Relation with defined schema
DEFINE TABLE armor SCHEMAFULL;
DEFINE FIELD resistance ON armor TYPE int;
CREATE armor:leather SET resistance = 3;
CREATE armor:chainmail SET resistance = 20;
CREATE armor:platemail SET resistance = 30;
DEFINE TABLE player SCHEMAFULL;
DEFINE FIELD strength ON player TYPE int;
DEFINE FIELD armor ON player TYPE record(armor);
CREATE player:jack SET strength = 22, armor = armor:platemail;
CREATE player:brian SET strength = 20, armor = armor:leather;
Common SQL relations: JOIN
SELECT
player.name,
player.strength, armor.name AS armor_name, armor_name AS armor:leather
armor.name AS armor_name, armor.
armor.resistance AS armor_resistance
FROM player
JOIN armor
ON armor.id = player.armor_id
SurQL relationship: no JOIN required
FETCH expands the specified field
SELECT * FROM player FETCH armor;.
Record link
CREATE armor:leather SET registance = 3;
CREATE armor:chainmail SET registance = 20;
CREATE armor:platemail SET registance = 30;
CREATE player:jack SET strength = 22, armor = armor:platemail;
CREATE player:brian SET strength = 20, armor = armor:leather;
Foreign Key == Record Link
Relation: ONE-TO-ONE
CREATE human:freddie SET nickname="freddie", age=99 ;
CREATE human:brian SET nickname="brian", age=75
UPDATE human:brian SET bff = human:freddie;
SELECT bff.nickname, bff.age FROM human:brian
Indicate fields in the outer table by connecting them with dots
Relation: ONE-TO-MANY
CREATE car:tesla SET model='Model S', ev=True, price=99000;
CREATE car:mustang SET model='Mustang Cobra', ev=False, price=60000;
UPDATE human:brian SET cars=["car:tesla"];
UPDATE human:freddie SET cars=["car:mustang"];
UPDATE car:tesla SET owner = human:brian;
UPDATE car:mustang SET owner = human:freddie;
CREATE parts:tire SET brand='Michelin', size=5;
CREATE parts:gastank SET brand='Tanksy', size=10;
CREATE parts:battery SET brand='Xi Ping', size=20;
UPDATE car:mustang SET parts = ['parts:tire', 'parts:gastank'];
UPDATE car:tesla SET parts = ['parts:tire', 'parts:battery'];
Relation: ONE-TO-MANY
SELECT parts FROM car:mustang
SELECT cars.parts.brand FROM human:brian ;
Indicate fields in outer table by connecting them with dots
Graph Connections
RELATE player:jack -> wants_to_buy -> armor:dragon;
RELATE player:jack -> wants_to_buy -> armor:platemail;
SELECT * FROM wants_to_buy;
SELECT id, -> wants_to_buy -> armor AS wtb FROM player;
SELECT id, <- wants_to_buy <- player AS players FROM armor:dragon
Indicate fields in an outer table by connecting them with "->" or "<-".
LIMIT
Specifies the number of results returned by the SELECT
At this time, FETCH does not work well
CREATE tag:rs SET name = 'Rust';
CREATE tag:go SET name = 'Golang';
CREATE tag:js SET name = 'JavaScript';
CREATE person:tobie SET tags = [tag:rs, tag:go, tag:js];
CREATE person:jaime SET tags = [tag:js];
SELECT * FROM person LIMIT 1;
SELECT * FROM (SELECT * FROM person FETCH tags) LIMIT 1;
// SELECT * FROM person LIMIT 1 FETCH tags;
START
Specify the starting position of the result returned by SELECT (zero-based)
Directing FETCH works well
SELECT * FROM person START AT 1;
SELECT * FROM (SELECT * FROM person FETCH tags) START 1;
SELECT * FROM person START 1 FETCH tags;
SurQL: WHERE, ORDER BY, GROUP BY
SELECT * FROM armor ;
SELECT * armor WHERE resistance >= 30 ;
SELECT math::sum(strength) FROM player GROUP BY ALL ;
SELECT * FROM armor ORDER BY RAND();
SELECT * FROM armor ORDER RAND();
SELECT * FROM armor ORDER resistance NUMERIC ASC ;
SELECT * FROM armor ORDER resistance NUMERIC DESC ;
SurQL: BEFORE, AFTER, DIFF
CREATE, UPDATE, and DELETE can return before, after, and difference of queries
UPDATE human:freddie SET email = 'freddie@example.com';
UPDATE human:freddie SET email = 'freddie@dummy.com' RETURN DIFF ;
Predefining an analytical query
Once data is written, select, aggregate, group, order, etc.
DEFINE TABLE person SCHEMALESS;.
DEFINE TABLE person_by_age AS
SELECT
count(), age, age, age_by_age, age_by_age, age_by_age
age,.
math::sum(age) AS total, math::mean(age) AS average
math::mean(age) AS average
FROM person
GROUP BY age ;
EVENT
Contents of table specified by ON TABLE: $before, $after
ID where event occurred: $this
Event that occurred: $event
UPDATE human:freddie SET email = 'freddie@example.com';
UPDATE human:brian SET email = 'brian@example.com';
DEFINE EVENT changelog ON TABLE human
WHEN $before.email ! = $after.email
THEN ( CREATE changelog SET
time = time::now(),
email = $after.email );
RANGE Repeat as many times as specified by colon
> CREATE |test:10| SET time = time::now();
[
{
"result": [
{
"time": "2022-12-20T04:10:19.282031670Z"
},
{
"id": "test:nk45tn46dy2bn1hd6zj9", {
"time": "2022-12-20T04:10:19.282450969Z"
},.
RANGE Repeat with initial and end values indicated
> CREATE |test:1..10| SET time = time::now();
[
{
"result": [
{
"id": "test:1", "time": "2022-12-20T04:12:21.47766
"time": "2022-12-20T04:12:21.477667592Z"
},
{
"id": "test:2", "time": "2022-12-20T04:12:21.477667592Z" }, { "id": "test:2", "time".
"time": "2022-12-20T04:12:21.478289880Z"
}, }
Regular expression
> SELECT * FROM test WHERE id = /. *[24]. */
[
{
"result": [
{
"id": "test:2", "time": "2022-12-20T04:12:21.478289880Z
"time": "2022-12-20T04:12:21.478289880Z"
},
{
"id": "test:4", "time".
"time": "2022-12-20T04:12:21.478332436Z"
}
],.
"status": "OK",.
IF THEN ELSE
UPDATE person SET classtype =
IF age <= 10 THEN
'junior'
ELSE IF age <= 21 THEN
'student'
ELSE IF age >= 65 THEN
'senior'
ELSE
NULL
END ;
MERGE
Merge table fields: add, delete
UPDATE person:test SET
name.initials = 'TMH', name.first = 'Tobie', name.
name.first = 'Tobie', name.
name.last = 'Morgan Hitchcock';
UPDATE person:test MERGE {
name: {
title: 'Mr', initials: NONE, initials: NONE, initials: NONE, initials: NONE
initials: NONE,.
suffix: ['BSc', 'MSc'], }
}
}; }
ASSERT Table Constraints
Each field defined allows ASSERT to define constraints on the data
DEFINE FIELD countrycode ON user TYPE string
// Ensure country code is ISO-3166
ASSERT $value ! = NONE AND $value = /[A-Z]{3}/
// Set a default value if empty
VALUE $value OR 'GBR'; ; Ensure country code is ISO-3166
; ;
FUTURE Function
- Define table fields by values to be set later
UPDATE person:test SET
can_drive = <future> {
birthday && time::now() > birthday + 18y };
UPDATE person:test SET birthday = <datetime> '2007-06-22';
UPDATE person:test SET birthday = <datetime> '2001-06-22';
PERMISSIONS
Restrict CRUD operations on TABLE and FIELD
DEFINE TABLE user SCHEMALESS
PERMISSIONS
FOR select, create, update
WHERE id = $auth.id
FOR delete
WHERE id = $auth.id OR $auth.admin = true ;
ACID Transaction
BEGIN TRANSACTION;.
UPDATE coin:one SET balance += -23.00 ;
UPDATE coin:two SET balance -= 23.00 ;
COMMIT TRANSACTION;
or
CANCEL TRANSACTION; or
When a new transaction is specified for a table with DROP set, transactions that have not been
When a new transaction is specified for a table with DROP set, any transactions that have not yet been completed are discarded.
The default is for new transactions to be read-only (if possible).
WebSocket
Specify URL like ws://dev00:8000/rpc (ws://, wss://)
Send POST with the following JSON set as the message body
{
"id": <ID to identify>, "method": <command>, "command": <message body
"method": <command>, <message body>, { "params": <command request
"params": <array of parameters requested by the command>, "method": <command>, "id": <ID to identify
}
SurrealDB Authentication
ROOT authentication
User/password specified at server startup
-pass option disables ROOT authentication if omitted
User Authentication
User/password created with DEFINE LOGIN
Token Authentication
Authentication by JSON Web Token (JWT) (RFC7519, RFC8725)
3rd party OAuth authentication
SCOPE authentication
SIGNUP and SIGNIN pre-defined
Access period can be limited
LOGIN
Access can be restricted to NAMESPACE and DATABASE
Users without NAMESPACE privileges cannot create/delete DBs
DEFINE LOGIN admin ON NAMESPACE PASSWORD "admin.admin";
DEFINE LOGIN guest ON DATABASE PASSWORD "guest.guest";
LOGIN image
surrealdb_login.png
TOKEN
Only allow access to requests with a specific token in the header
Can be set for NAMESPACE, DATABASE, and SCOPE
DEFINE TOKEN my_token ON DATABASE
TYPE HS512 VALUE '1234567890';
SCOPE
All JSON-RPC fields are set to variables
SCOPE gives the ability to access the database
Access to tables and fields is subject to PERMISSIONS
SCOPE with TOKEN cannot create/modify/delete tables or display information
DEFINE FIELD email ON TABLE user TYPE string ASSERT is::email($value);
DEFINE INDEX email ON TABLE user COLUMNS email UNIQUE;
DEFINE SCOPE account SESSION 24h
SIGNUP ( CREATE user SET
email = $email, pass = crypto::argon
SIGNUP ( CREATE user SET email = $email, pass = crypto::argon2::generate($pass) )
SIGNIN ( SELECT * FROM user
WHERE email = $email
AND crypto::argon2::compare(pass, $pass) );
SCOPE image
surrealdb_scope.png
SIGNUP for SCOPE authentication
let jwt = fetch('https://api.surrealdb.com/signup', {
method: 'POST', {
headers: {
'Accept': 'application/json', headers: {
'NS': 'google', // Specify the namespace
'DB': 'gmail', // Specify the database
}, }
body: JSON.stringify({
'NS': 'google', // Specify the namespace
'DB': 'gmail', // Specify the database }, body: JSON.stringify({ 'NS': 'google', 'gmail', // Specify the database
'SC': 'account',
email: 'tobie@surrealdb.com',
pass: 'a85b19*1@jnta0$b&!'
}),.
});
Referencing tokens and authentication data from a query
$session, $scope, $token and $auth are set to special information relevant to the client
While using NAMESPACE, DATABASE, or TOKEN, $session and $token are set
$toekn is set to all fields of the JWT token
$scope is set to the SCOPE name for SCOPE authentication
$auth is set when the JWT has an id field for SCOPE authentication and the data specified by the id exists in the table
SELECT * FROM $session;.
SELECT * FROM $token;
SELECT * FROM $scope; SELECT * FROM $author
SELECT * FROM $auth;
LIVE Queries
Valid when accessed via WebScoket
Data changes are pushed in real-time to client, application, end-user device, and server
Data changes are pushed in real-time to client, application, end-user devices, and server-side libraries
All client devices are kept in sync
KILL to terminate a LIVE query
LIVE SELECT * FROM user WHERE age > 18 ;
PARALLEL
Append PARALLEL to CREATE, DELETE, UPDATE, and SELECT
Query execution is processed in parallel
SELECT * FROM test PARALLEL ;
TIMEOUT
Append TIMEOUT to CREATE, DELETE, UPDATE, and SELECT
Wait for the time specified in query execution
SELECT * FROM
http::get('https://ipinfo.io')
TIMEOUT 10s;.
GeoJSON
Pont, Line, Polygon, MultiPoint, MultiLine, MultiPolygon, Collection
UPDATE university:oxford SET area = {
[[ [9.0, 11.2], [10.5, 11.9],[10.3, 13.0], [9.0, 11.2]]
]
}; }
SELECT * FROM university:oxford;
SurQL built-in functions
array::xxxx()
combine, complement, concat, difference, disinc, intersect, len, sort::asc, sort::desc, sort, union, all, any, add, append, insert, prepend, remove add, append, insert, prepend, remove, reverse, group, push, pop
count()
crypto::xxxx()
argon2::compare, argon2::generate, bcrypt::compare, bcrypt::generate, md5, pdkdf2::compare, pdkdf2::generate, scrypt::compare, scrypt:: generate, sha1, sha25, sha512
SurQL built-in functions (cont.)
duration::xxxx()
days, hours, mins, secs, weeks, years
geo::xxxx()
area, bearing, centroid, distance, hash::decode, hash::encode
http::xxxx()
head, get, put, post, patch, delete
is::xxxx()
alphanum, alpha, domain, email, hexadecimal, latitude, longitude, numeric, semver, url, uuid, url, datetime
SurQL built-in functions (cont.)
math::xxxx()
abs, bottom, ceil, fixed, floor, interquartile, max, mean, midhinge, min, mode, nearestrank, percentile, round, spread, sqrt, stddev, sum, top, trimean, variance, pow
not()
parse::email()
host, user
parse::url::xxxx)
domain, fragment, host, port, path, query, scheme
SurQL built-in functions (cont.)
rand()
rand::xxxx()
bool, enum, float, guid, int, string, time, uuuid:v4, uuid:v7, uuid
session::xxxxxx()
db, id, ip, ns, origin, sc, sd, token,
SurQL built-in functions (cont.)
string::xxxx()
concat, endsWith, join, length, lowercase, repeat, replace, reverse, slice, slug, split, startsWith, trim, uppercase, words
time::xxxx()
day, floor, format, group, hour, minute, month, nano, now, round, second, unix, wday, yday, year, timezone
type::xxxxxx()
bool, datetime, decimal, duration, float, int, number, point, regex, table, thin
SurQL built-in ordinary numbers
math::xxxxxx
E, FRAC_1_PI, FRAC_1_SQORT_2, FRAC_2_PI, FRAC_2_SQRT_PI, FRAC_PI_2, FRAC_PI_3, FRAC_PI_4, FRAC_PI_6, FRAC_PI_8, LN_10, LN_2, LOGO10_2, LOG10_E LOG2_10, LOG2_E, PI, SQRT_2, TAU
LET Parameter Setting
Set variables to numbers, strings, and other objects
Can be referenced as $variable in a query
LET $test = { some: 'thing', other: true };
SELECT * FROM $test WHERE some = 'thing';
Extending SurQL with JavaScript
All values from SurrealDB are automatically converted to JavaScript types
Return values from JavaScript functions are automatically converted to SurrealDB values
Booleans, integers, floats, strings, arrays, objects, and date objects are all automatically converted to
All are automatically converted to or from SurrealDB values
CREATE user:test SET created_at = function() {
return new Date(); }
};
JavaScript extension sample 1
CREATE platform:test SET version = function() {
const { platform } = await import('os');
return platform();
};
JavaScript extension sample 2
LET $value = 'SurrealDB';
LET $words = ['awesome', 'advanced', 'cool'];
CREATE article:test SET summary = function($value, $words) {
return `${arguments[0]} is ${arguments[1].join(', ')}`;
};
Sample JavaScript Extension 3
CREATE film:test SET
ratings = [
{ ratings: 6.3 }
{ rating: 8.7 }, [ { rating: 6.3 }
], }
display = function() {
return this.ratings.filter(r => {
return r.rating >= 7; }
}).map(r => {
return { ... .r,.
rating: Math.round(r.rating * 10) }; }
});
}; }
A simple example implementation of the RETful API in Python
from urllib.request import Request, urlopen
import base64
import json
from pprint import pprint as pp
_BASE_URL = "http://dev00:8000"
_NAMESPACE = "test"
_DATABASE = "test"
_USER = "root"
_PASS = "root"
auth_str = f"{_USER}:{_PASS}".encode("utf-8")
credential = base64.b64encode(auth_str)
auth = "Basic " + credential.decode("utf-8")
headers = {
"Accept": "application/json",.
"Authorization": auth, auth, auth = "Basic " + credential.decode("utf-8")
"NS": _NAMESPACE, _NAMESPACE
"DB": _DATABASE,
}
url = _BASE_URL + "/key/human"
request = Request(url, headers=headers)
with urlopen(request) as res:)
data = res.read()
pp(json.loads(data)[0]['result'])
Example of querying via the RETful API in Python
import requests
from requests.auth import HTTPBasicAuth
from pprint import pprint as pp
_URL = "http://dev00:8000/sql"
_NAMESPACE = "test"
_DATABASE = "test"
_USER = "root"
_PASS = "root"
_HEADERS = {
'Content-Type': 'application/json',.
'Accept':'application/json',.
'ns': _NAMESPACE,.
'db': _DATABASE
}
_auth = HTTPBasicAuth(_USER, _PASS)
def db(query):.
res = requests.post( _URL,
headers=_headers,
auth = _auth,
data=query )
if "code" in res.json():
raise Exception(res.json())
return res.json()
if __name__ == '__main__': if __name__ == '__main__': if __name__ == '__main__'
while True:.
sql = input('SQL> ')
if sql.upper() == 'Q': if sql.upper() == 'Q': if True: if sql = input('SQL> ')
break
val = db(sql)
pp(val)
Example of querying via WebSocket in Python
import asyncio
from surrealdb import WebsocketClient
from pprint import pprint as pp
_URL = "ws://dev00:8000/rpc"
_NAMESPACE = "test"
_DATABASE = "test"
_USER = "root"
_PASS = "root"
async def main():.
async with WebsocketClient( url=_URL,
namespace=_NAMESPACE, database=_DATABASE,
username=_USER, password=_PASS, )
) as session: async with WebsocketClient( url=_URL
while True: sql = input('SQL>', 'SQL>', 'sql', 'SQL>')
sql = input('SQL> ')
if sql.upper() == 'Q': break
res = await session.query(sql)
pp(res)
PyPI stuff doesn't connect well with WebSocket
Pull request in progress
This repository works for now.
GitHub - iisaka51/surrealdb.py at develop
Other implementation examples
Flask
GitHub - santiagodonoso/python_flask_surrealdb
FastAPI
GitHub - santiagodonoso/fastapi_surrealdb_v_1
PHP
GitHub - santiagodonoso/php_surrealdb
React
GitHub - rvdende/surrealreact: SurrealDB explorer UI written in react.
Deno
https://github.com/officialrajdeepsingh/surrealDb-deno
Typescript
SurrealDB Explained With Express.js, Node.js, and TypeScript
Items to keep in mind
Backend DBs available in surrealdb-1.0.0-beat8
RocksDB, TiKV
FoundationDB, IndexedDB requires rebuild
IndexedDB may be originally used for embedded applications
NAMESPACE is required to access DATABASE
Different NAMESPACE makes different DATABASE with the same name
Any number of DATABASE can be created in one NAMESPACE.
However, you must have permission to access NAMESPACE.
This is probably a bug!
Some SurQL does not work well in CLI
SurQL subcommand is HTTP RestfulAPI access by pressing return key
Semicolons are not recognized as a single query
Import subcommands are OK in files given by import subcommand.
Copy&Paste is OK if return is pressed.
Comments are accepted, but only comments are an error.
Empty character ('') is considered to be executed.
Variables defined in LET are lost when Return key is pressed.
Transaction is NG if Return key is pressed in the middle of a transaction.
Declaration of USE is also lost when the Return key is pressed.
Summary
Surprisingly easy to install and configure
Very, very, very compatible with web apps
Powerful queries extensible with JavaScript
Business logic and user authentication can be handled directly in the database
Simplified back-end technology stack → shorter development time → lower costs
Still a work in progress, but very promising
Reference materials
SurrealDB Official Site
GitHub - surrealdb/surrealdb: A scalable, distributed, collaborative, document-graph database, for the realtime web
StackOverflow
Authentication Failure when using external JWT token in SurrealDB
Traverse relations upwards in SurrealDB
How can I log into a scope with SurrealDB?
How to specify a namespace when using websockets with SurrealDB
Recommended REST Clients
Surrealist
Splits the results of multiple queries into tabs
Excellent example implementation of SurrealDB
GitHub - StarlaneStudios/Surrealist: ⚡ Lightning fast graphical SurrealDB query playground for Desktop
Tabbed Postman - REST Client
Chrome Extension
Queries can be saved as collections
Collections can be exported/imported
Tabbed Postman - REST Client
Thunder Client
Extension for VSCode
Thunder Client - Rest API Client Extension for VS Code
Appendix
Server Options and Environment Variables
DB_PATH: Data storage location (memory)
USER User name for ROOT authentication (root), --user/-u
PASS Password for ROOT authentication user, --pass/-p
ADDR Subnet to allow ROOT authentication (127.0.0.1/32), --addr
BIND hostname/IP address to listen for connections (0.0.0.0:8000), --bind/-b
KEY Secret key for ON-DISK encryption, --key/-k
KVS_CA CA file for KVS connection, --kvs-ca
KVS_CRT CERT file for KVS connection, --kvs-crt
KVS_KEY Private key for KVS connection, --kvs-key
WEB_CRT CERT file for listening on SSL connections, --web-crt
WEB_KEY private key to listen for in SSL connections, --web-key
STRICT Start in STRICT mode if set, --strict
LOG log level "warn", ["info"], "debug", "trace", "full", --log
ACID: Four characteristics that define a transaction
Atomicity.
Each statement in a transaction is treated as a unit
Consistency
Guarantees that a transaction modifies a table only in a predefined and predictable manner
table in a predefined and predictable manner.
Isolation
Even if multiple users read and write to the same table at the same time, each request is treated as if it originated independently.
Even if multiple users read/write the same table at the same time, each request is treated as if it originated independently.
Durability
Guarantees that data changes from successfully executed transactions are preserved in the event of a system failure.
Ensures that data changes from successfully executed transactions are preserved even in the event of a system failure
N+1 Problem
A problem where database accesses cause queries to be executed a total of N+1 times.
Execute SELECT once to retrieve N records
Execute SELECT N times to retrieve data associated with N records
Prone to occur behind the scenes when using ORMs
Tends to cause heavy (slow) operation of the application
RocksDB
Popular and high-performance embedded KVS database
Fork of LevelDB developed by Meta (Facebook)
Used in production by various web services such as Facebook, Yahoo!
Provides data persistence while increasing performance and security
Performance increases linearly with the number of CPUs
RocksDB performance is strongly influenced by platform tuning
Not easy due to the complexity of many configurable parameters
TiKV
KVS database running on TiDB backend
Data persistence (with RocksDB)
Data integrity assurance for distributed databases
MVCC (Multi-Version Concurrency Control)
Distributed transaction implementation
Google Parcorator / 2PC(2 Phase Commit)
Coprocessor
Note that TiKV is configured as a 3-node cluster by default.
Until 3 nodes are configured, temporary files are stored.
Temporary files are automatically deleted at startup
Reference: https://docs.pingcap.com/tidb/dev/tikv-configuration-file
Cluster configuration: https://tikv.org/docs/5.1/deploy/install/production/
TiKV Architecture
tikv_stack.png
IndexedDB
Browser-based embedded KVS database
Stores data persistently within the user's browser
Allows creation of web apps with advanced query capabilities regardless of network conditions
Schema-less
Supports ACID transactions
Asynchronous processing
Multi-version concurrency
Allows users to delete data as easily as deleting a cookie
Browser selection
Data cannot be registered in IndexedDB if it exceeds a certain capacity.
The amount of data that can be registered in IndexedDB varies depending on the environment.
FoundationDB
NoSQL supporting ACID transactions
Operable by SQL
Keys are sorted.
Throughput of 20,000 writes/sec on one core when using SSDs
Scales linearly to 500 cores
Reads in 1 ms, writes in 5 ms
Can be distributed/redundant in a cluster configuration
Can be configured with at least 1 node (no redundancy/nodes can be added later)
FDB's default configuration does not work well, so configuration is required
$ fdbcli --exec 'configure ssd'
$ fdbcli --exec 'writemode on' $ fdbcli --exec 'configure ssd
$ fdbcli --exec 'getrangekeys \x00 \xff'
Reference: https://apple.github.io/foundationdb/command-line-interface.html
Binary releases are invalid for FoundationDB
Depends on FoundationDB version specified in features
$ cargo feature surreal
Avaliable features for `surreal
default = ["storage-rocksdb", "scripting", "http"].
http = ["surrealdb/http"].
scripting = ["surrealdb/scripting"]]
storage-fdb = ["surrealdb/kv-fdb-6_3"]]
storage-rocksdb = ["surrealdb/kv-rocksdb"]]
storage-tikv = ["surrealdb/kv-tikv"]]
$ grep kv-fdb- lib/Cargo.toml
kv-fdb-5_1 = ["foundationdb/fdb-5_1", "kv-fdb"] $ grep kv-fdb- lib/Cargo.toml
kv-fdb-5_2 = ["foundationdb/fdb-5_2", "kv-fdb"].
kv-fdb-6_0 = ["foundationdb/fdb-6_0", "kv-fdb"]]
kv-fdb-6_1 = ["foundationdb/fdb-6_1", "kv-fdb"]]
kv-fdb-6_2 = ["foundationdb/fdb-6_2", "kv-fdb"].
kv-fdb-6_3 = ["foundationdb/fdb-6_3", "kv-fdb"]]
kv-fdb-7_0 = ["foundationdb/fdb-7_0", "kv-fdb"]]
kv-fdb-7_1 = ["foundationdb/fdb-7_1", "kv-fdb"].
Avaliable notation is a bug in cargo-feature. I've issued a pull request.
SurrealDB rebuild
Set up a rust development environment (if necessary)
$ curl -sSf https://sh.rustup.rs | sh
$ source $HOME/.cargo/env
$ rustup install stable
Clone SurrealDB repository
$ git clone https://github.com/surrealdb/surrealdb.git
$ cd surrealdb
Rebuild
$ carrgo build -release -all-features # 2GB memory will fail
# or
$ cargo build -release -features storage-fdb # TiKV will be disabled
TiKV vs FoundationDB
FDB monitoring is weak
$ fdbcli --exec 'status json'
TiKV has Prometheus for data lookup and Grafana for status monitoring
FDB is version-sensitive
FDB stores data in memory by default, so configuration changes are required
FDB is implemented in C++, TiKV in Rust
FDB can be serviced with at least one node, while TiKV requires at least three nodes
Replication of RocksDB
Real-time replication is possible with rocksplicator
https://github.com/pinterest/rocksplicator
Note, however, that Rocksplicator is an archived project that is not actively maintained or supported by Pinterest
About storing binary files
SurrealDB is not designed for such use
Object storage should be considered
GitHub - minio/minio: Multi-Cloud Object Storage
GitHub - scality/Zenko: Zenko is the open source multi-cloud data controller: own and keep control of your data on any cloud.
SurQL Syntax
INFO
INFO FOR [
KV
| NS | NAMESPACE
| DB | DATABASE
DATABASE | SCOPE @scope
| TABLE @table
DEFINE NAMESPACE | DATABASE ...]
DEFINE NAMESPACE | DATABASE ...
DEFINE [
NAMESPACE @name
DEFINE [ NAMESPACE | DATABASE @name
| LOGIN @name ON [ NAMESPACE | DATABASE ]
[ PASSWORD @pass | PASSHASH @hash ]
| TOKEN @name ON [ NAMESPACE | DATABASE | SCOPE ]
TYPE @algorithm VALUE @value
| SCOPE @name [ SESSION @duration ]
[ SIGNUP @expression ] [ SIGNIN @expression ]
| EVENT @name ON [ TABLE ] @table
WHEN @expression THEN @expression
@algorithm
EDDSA, ES256, ES384, ES512, HS256, HS384, HS512, PS256, PS384, HS512
PS256, PS384, PS512, RS256, RS384, RS51
DEFINE TABLE
DEFINE [
| TABLE @name
[ DROP ]
[ SCHEMAFULL | SCHEMALESS ]
[ AS SELECT @projections
FROM @tables
[ WHERE @condition ]
[ GROUP [ BY ] @groups ]
]
[ PERMISSIONS [ NONE | FULL
| FOR select @expression
| FOR create @expression
| FOR update @expression
| FOR delete @expression
] ]
; ;DEFINE FIELD | INDEX
DEFINE FIELD | INDEX
DEFINE [
| FIELD @name ON [ TABLE ] @table
[ TYPE @type ]
[ VALUE @expression ]
[ ASSERT @expression ]
[ PERMISSIONS [ NONE | FULL
| FOR select @expression
| FOR create @expression
| FOR update @expression
| FOR delete @expression
]
| INDEX @name ON [ TABLE ] @table [ FIELDS | COLUMNS ] @fields [ UNIQUE ]
] ;
CREATE
CREATE @targets
[ CONTENT @value
| SET @field = @value ...
]
[ RETURN [ NONE | BEFORE | AFTER | DIFF | @projections ... ]
[ TIMEOUT @duration ]
[ PARALLEL ]
; ;
REMOVE
REMOVE [
NAMESPACE @name
| DATABASE @name
LOGIN @name ON [ NAMESPACE | DATABASE ] ; TOKEN @name ON [ NAMESPACE | DATABASE ] ; REMOVE
| TOKEN @name ON [ NAMESPACE | DATABASE ]
| SCOPE @name
| TABLE @name
TABLE @name | EVENT @name ON [ TABLE ] @table
| FIELD @name ON [ TABLE ] @table
| INDEX @name ON [ TABLE ] @table
] ;
INSERT
INSERT [ IGNORE ] INTO @what
[ @value
| (@fields) VALUES (@values)
[ ON DUPLICATE KEY UPDATE @field = @value ... ]
]
;
UPDATE
UPDATE @targets
[ CONTENT @value
| MERGE @value
| PATCH @value
| SET @field = @value ...
]
[ WHERE @condition ]
[ RETURN [ NONE | BEFORE | AFTER | DIFF | @projections ... ]
[ TIMEOUT @duration ]
[ PARALLEL ]
; ;-[ DELETE ] [ RETURN
DELETE
DELETE @targets
[ WHERE @condition ]
[ RETURN [ NONE | BEFORE | AFTER | DIFF | @projections ... ]
[ TIMEOUT @duration ]
[ PARALLEL ]
; ;
SELECT
SELECT @projections
FROM @targets
[ WHERE @condition ]
[ SPLIT [ AT ] @field ... ]
[ GROUP [ BY ] @field ... ]
[ ORDER [ BY ]
@field [RAND()| COLLATE| NUMERIC ] [ ASC | DESC ] ...
]
[ LIMIT [ BY ] @limit ]
[ START [ AT ] @start ]
[ FETCH @field ... ]
[ TIMEOUT @duration ]
[ PARALLEL ]
;
USE
USE
[ NAMESPACE | NS ] @namespace
[[ DATABASE | DB ] @database ] ;
share
Register now and start using Qiita more conveniently!
We'll send you articles that match your needs.
You can efficiently read back useful information later!
About functions you can use when you log in
iisaka51
@iisaka51(Goichi (Iisaka) Yukawa)
https://github.com/iisaka51
0 件のコメント:
コメントを投稿