Data Modeling - SurrealTools/Documentation GitHub Wiki

DEFINE FIELD id ON something TYPE {location: string, date: datetime};

INSERT INTO type::table($new) (SELECT *, meta::id(id) AS id FROM type::table($old));
REMOVE TABLE type::table($old);

DEFINE TABLE permission_set SCHEMAFULL;
DEFINE FIELD permissions ON permission_set TYPE array<record>;
DEFINE FIELD permissions.* ON permission_set TYPE record<permission> ASSERT !type::is::none($input.id);
OR
DEFINE FIELD permissions.* ON permission_set TYPE record<permission> ASSERT $value = (SELECT id FROM $input)[0]['id'];

---Old Systax
DEFINE FIELD author ON post TYPE record<user> ASSERT $value != NONE;

---New Syntax
DEFINE FIELD author ON post TYPE record<user>;
DEFINE FIELD author ON post TYPE option<record<user>>;

    Any,
	Array,
    Set,
	Bool,
	Bytes,
	Datetime,
	Decimal,
	Duration,
	Float,
	Int,
	Number,
	Object,
	String,
	Record(Vec<Table>),
	Geometry(Vec<String>),

DEFINE FIELD employees ON TABLE company TYPE array<record<person>>;
DEFINE FIELD age ON person TYPE int;
DEFINE FIELD enabled ON person TYPE bool | int;
DEFINE FIELD name ON person TYPE string;
DEFINE FIELD scores ON person TYPE set<float, 5>;
UPDATE person:test SET age = NONE, enabled = NONE, name = NONE, scored = [1,1,2,2,3,3,4,4,5,5];
UPDATE person:test SET age = '18', enabled = NONE, name = NONE, scored = [1,1,2,2,3,3,4,4,5,5];
UPDATE person:test SET age = '18', enabled = true, name = NONE, scored = [1,1,2,2,3,3,4,4,5,5];
UPDATE person:test SET age = '18', enabled = true, name = 'Tobie Morgan Hitchcock', scores = [1,1,2,2,3,3,4,4,5,5];

and for inline typing...

UPDATE person:test SET age = <int> NONE;
UPDATE person:test SET age = <int> '18';
UPDATE person:test SET enabled = <bool | int> NONE;
UPDATE person:test SET enabled = <bool | int> true;
UPDATE person:test SET name = <string> 'Tobie Morgan Hitchcock';
UPDATE person:test SET scores = <set<float>> [1,1,2,2,3,3,4,4,5,5];
UPDATE person:test SET scores = <array<float>> [1,1,2,2,3,3,4,4,5,5];
UPDATE person:test SET scores = <set<float, 5>> [1,1,2,2,3,3,4,4,5,5];
UPDATE person:test SET scores = <array<float, 5>> [1,1,2,2,3,3,4,4,5,5];

DEFINE TABLE person SCHEMAFULL;
DEFINE FIELD info ON person TYPE object;
DEFINE FIELD info.hello ON person TYPE number;

CREATE person:me CONTENT {
    info: {
        hello: 123
    }
};

DEFINE TABLE person SCHEMAFULL;
DEFINE FIELD info ON person TYPE object FLEXIBLE;

CREATE person:me CONTENT {
    info: {
        hello: 123
    }
};

-- Use an array as the id
UPDATE config:[$section, $key] SET value = $value;
-- Use an object as the id
UPDATE config:{ section: $section, key: $key } SET value = $value;

Then to select an item...
SELECT * FROM config:{ section: $section, key: $key };

SELECT * FROM `123`;
OR angle brackets...
SELECT * FROM ⟨123⟩;
SELECT * FROM ⟨123⟩:⟨456⟩;

SELECT * FROM person:dxbebreslfp0er668y7k

SELECT * FROM person:⟨30d5bc81-e837-4b6d-83b3-39cd1c8892b7⟩

SELECT * FROM person:⟨this is my record id⟩

CREATE person:test SET name = 'test';
CREATE person:`this is an id with spaces` SET name = 'test';
CREATE person:`e030f6a5-5b11-4cde-adfb-e81d0e12d6e0` SET name = 'test';
-- You can also use angle brackets (not found on a keyboard though)...
CREATE person:test SET name = 'test';
CREATE person:⟨this is an id with spaces⟩ SET name = 'test';
CREATE person:⟨8424486b-85b3-4448-ac8d-5d51083391c7⟩ SET name = 'test';
-- Or using a function
CREATE type::thing('person', '8424486b-85b3-4448-ac8d-5d51083391c7') SET name = 'test';

db.query("UPDATE type::thing('person', 'e030f6a5-5b11-4cde-adfb-e81d0e12d6e0') SET name = $name;", {
    name: 'test'
});

CREATE restaurant:{ country: 'GBR', hash: geo::hash::encode(51.509865, -0.118092) } SET name = "McDonalds";

SELECT * FROM restaurant:{ country: 'GBR', hash: 'abcde' }..{ country: 'GBR', hash: 'efghi' };

BEGIN;
-- Set the date
LET $date = "2022-10-05";
-- Increase the counter
LET $counter = (UPDATE counter:[$date] SET value += 1);
-- Create the event
CREATE event:[$date, $counter.value];
-- Commit all changes
COMMIT;

RELATE person:tobie->purchased->product:macbook
RELATE person:tobie->bought_product->product:macbook 

DEFINE FIELD name ON TABLE person TYPE object;
DEFINE FIELD name.first ON TABLE person TYPE string;
DEFINE FIELD name.last ON TABLE person TYPE string;

DEFINE FIELD hobbies ON TABLE person TYPE array;
DEFINE FIELD hobbies.* ON TABLE person TYPE object;
DEFINE FIELD hobbies.*.name ON TABLE person TYPE string;
  
-- You can define arrays of record IDs
DEFINE FIELD friends ON TABLE person TYPE array;
DEFINE FIELD friends.* ON TABLE person TYPE record (person);
-- This allows you to specify embedded objects too
DEFINE FIELD friends ON TABLE person TYPE array;
DEFINE FIELD friends.* ON TABLE person TYPE object;
DEFINE FIELD friends.*.relation ON TABLE person TYPE string;
DEFINE FIELD friends.*.who ON TABLE person TYPE record (person);
  
CREATE person:tobie SET friends = [
  { who: person:jaime, relation: 'brother' },
  { who: person:bob, relation: 'schoolfriend' },
];
  
SELECT friends[*].who.name FROM person:tobie;
-- or
SELECT friends.*.who.name FROM person:tobie;
SELECT friends[WHERE relation = 'brother'].who.name FROM person:tobie;

DEFINE FIELD mood ON TABLE person TYPE string ASSERT $value IN ['sad', 'ok', 'happy'];
or if you want mutliple different potential types on a field...!?
DEFINE FIELD mood ON TABLE person TYPE any ASSERT $value IN ['sad', true, 'happy'];

DEFINE FIELD author ON TABLE book TYPE record (author); -- single type
DEFINE FIELD author ON TABLE book TYPE record (author, person); -- polymorphic types
DEFINE FIELD author ON TABLE book TYPE record (author) ASSERT $value != NONE; -- ensure a value is set

DEFINE EVENT author ON TABLE book WHEN true THEN (UPDATE $after.author SET books += $this);

SELECT * FROM <float> 13.5719384719384719385639856394139476937756394756;
-- returns 13.571938471938473
SELECT * FROM <decimal> 13.5719384719384719385639856394139476937756394756;
-- returns 13.5719384719384719385639856394139476937756394756
SELECT * FROM <float> 0.3 + <float> 0.3 + <float> 0.3 + <float> 0.1;
-- returns 1.0000000000000002
SELECT * FROM 0.3 + 0.3 + 0.3 + 0.1;
-- returns 1.0 (because they are decimals by default)

create thing:float set num = <float> 4.2;
[{"time":"543.52µs","status":"OK","result":[{"id":"thing:float","num":4.2}]}]
create thing:decimal set num = <decimal> 4.2;
[{"time":"453.885µs","status":"OK","result":[{"id":"thing:decimal","num":"4.2"}]}]

DEFINE FIELD my_new_field ON TABLE person VALUE function() {
 return 'something from javascript';
};
Or you could use futures...
DEFINE FIELD releasedate ON TABLE movie VALUE <future> { releasedate - time::now() };
DEFINE FIELD age ON TABLE person VALUE <future> { duration::years(time::now() - birthday) };

DEFINE FIELD slug ON TABLE feature TYPE string
  VALUE
    IF $value THEN
      string::slug($value)
    ELSE
      string::slug(title)
    END
;

EFINE TABLE person SCHEMAFULL;
DEFINE FIELD name ON TABLE person TYPE option<string | null>;
UPDATE person:test SET name = "Tobie"; -- This works
UPDATE person:test SET name = NULL; -- This works
UPDATE person:test SET name = NONE; -- This works

DEFINE TABLE person SCHEMAFULL;
DEFINE FIELD name ON TABLE person TYPE string | null;
UPDATE person:test SET name = "Tobie"; -- This works
UPDATE person:test SET name = NULL; -- This works
UPDATE person:test SET name = NONE; -- This fails

DEFINE FIELD title ON story TYPE string ASSERT $value IS NOT NONE;

-- Re-computed every time the data is selected...
DEFINE FIELD difference ON metrics VALUE <future> { expected_sales - previous_sales };
-- Calculated only once, when the record is updated...
DEFINE FIELD difference ON metrics TYPE int VALUE $this.expected_sales - $this.previous_sales;

DEFINE FIELD difference ON metrics TYPE any VALUE <future> { expected_sales - previous_sales };
-- or just
DEFINE FIELD difference ON metrics VALUE <future> { expected_sales - previous_sales };
DEFINE FIELD difference ON metrics VALUE <future> { expected_sales - previous_sales };

DEFINE FIELD total_amount ON TABLE order VALUE net_price + shipping;

DEFINE FIELD role ON TABLE person TYPE record (role);

DEFINE FIELD created ON waitlist TYPE datetime VALUE $before OR time::now(); 

DEFINE FIELD createdAt ON waitlist TYPE datetime VALUE $before OR time::now();
DEFINE FIELD updatedAt ON waitlist TYPE datetime VALUE time::now();

-- Set a readonly field which is always a certain value
DEFINE FIELD enabled ON TABLE user VALUE true;
-- Set a field which is always a certain value and can't be updated
DEFINE FIELD created_at ON TABLE user VALUE $value OR time::now();
-- Set a field which can have a value set, but can't be changed after that
DEFINE FIELD purchased_at ON TABLE user VALUE $before OR $value;
-- Set a field which starts at 1, and allows for a new value to be set, but only if it increments
DEFINE FIELD score ON TABLE user VALUE $value OR $before OR 1 ASSERT $after >= $before;

DEFINE EVENT email ON TABLE user WHEN $before.email != $after.email THEN http::post('https://my-api.com/update', $after);
DEFINE EVENT email ON TABLE user WHEN $event = "CREATE" AND $after.email THEN http::post('https://my-api.com/created', $after.email);
DEFINE EVENT email ON TABLE user WHEN $event = "DELETE" AND $before.email THEN http::post('https://my-api.com/deleted', $before.email);
DEFINE EVENT event_for_field ON TABLE demo WHEN $before.field != $after.field THEN (...)

DEFINE EVENT my_notification ON TABLE user WHEN $event = "UPDATE" THEN http::post('https://my-remote-endpoint.com', { action: $event, data: $this });

DEFINE TABLE lifeforms AS SELECT * FROM person, dog, cat;

-- Drop all writes to the 'temperature' table
DEFINE TABLE temperature DROP;
DEFINE FIELD city ON temperature TYPE string;
DEFINE FIELD temp ON temperature TYPE float;
DEFINE FIELD time ON temperature TYPE datetime;
-- Create tables to aggregate temperature readings
DEFINE TABLE average_temperature_by_city_by_day AS
  SELECT
    math::mean(temp) AS average,
    time::group(time, 'day') AS day,
    city
  FROM
    temperature
  GROUP BY city, day
;
DEFINE TABLE average_temperature_by_city_by_hour AS
  SELECT
    math::mean(temp) AS average,
    time::group(time, 'hour') AS hour,
    city
  FROM
    temperature
  GROUP BY city, hour
;

REMOVE FIELD userid ON user TYPE string;
DEFINE FIELD username ON user TYPE string;
-- This will remove the userid field on all documents (in schemafull mode), and add a username field.
UPDATE user;
 -- This will copy the contents of the userid field into the username field, and remove the userid field (in schemafull mode).
UPDATE user SET username = userid;

DEFINE FIELD new_name ON my_table VALUE old_name;
REMOVE FIELD old_name ON my_table;
UPDATE my_table;

DEFINE FIELD id ON TABLE profile PERMISSIONS NONE;

REMOVE FIELD bar ON TABLE test;
UPDATE test; -- This will remove the 'bar' field, because it is no longer defined

REMOVE FIELD bar ON TABLE test;
UPDATE test SET bar = NONE; -- You have to specifically delete the field, because in a schemaless table the field can still exists, even though the field definition has been removed

DEFINE FIELD user ON test TYPE record(user) VALUE $session.sd;

-- Set only the VALUE clause
DEFINE FIELD timestamp ON TABLE person TYPE datetime VALUE time::now();
CREATE person;

-- Set only the DEFAULT clause
DEFINE FIELD timestamp ON TABLE person TYPE datetime DEFAULT time::now();
CREATE person;

-- Set both the DEFAULT and VALUE clauses
DEFINE FIELD timestamp ON TABLE person TYPE datetime DEFAULT time::now() VALUE time::now();
CREATE person;

CREATE post:surrealdb SET name = 'My new post about SurrealDB', comments = [];
UPDATE post:surrealDB SET comments += { created_at: time::now(), text: 'Wow this is cool' };

CREATE post:surrealdb SET name = 'My new post about SurrealDB', comments = [];
LET $comment = (CREATE comment SET created_at = time::now(), text = 'Wow this is cool');
UPDATE post:surrealDB SET comments += $comment;

CREATE post:surrealdb SET name = 'My new post about SurrealDB', comments = [];
LET $comment = (CREATE comment SET text = 'Wow this is cool');
RELATE $comment->commented_on->post:surrealdb SET time = time::now();
SELECT ->commented_on->comment.text AS all_comments FROM post:surrealdb;

DEFINE TABLE tbl SCHEMALESS;
DEFINE FIELD alpha ON tbl TYPE bool VALUE $value ?? true;
DEFINE FIELD beta ON tbl TYPE bool VALUE $value ?? false;

DELETE FROM type::table(tbl_page);

INSERT INTO recordings_san_francisco (SELECT * FROM temperature WHERE city = 'San Francisco');