Enable returning a single field from SELECT statements

SurrealDB now allows you to fetch a single field from a SELECT statement or from a subquery, returning an array of values, rather than an array of objects.

// We can now select an array of just a single field
SELECT VALUE name FROM person;

Add support for FETCH clauses at the end of RETURN statements

SurrealDB now allows fetching remote fields in RETURN statements, in addition to SELECT statements.

// We can now fetch fields from RETURN statements
RETURN person:tobie FETCH account, connections;

Add Null Coalescing operator and Ternary Conditional operator

SurrealDB now supports the Null Coalescing operator (??), and the Ternary Conditional operator (?:) for simpler logic calculations.

// Check whether either of two values are truthy
SELECT * FROM NULL ?: 0 ?: false ?: 10; -- Will return 10
// Check whether either of two values are truthy and not NULL.
SELECT * FROM NULL ?? 0 ?? false ?? 10; -- Will return 0

Allow fetching fields from within complex Record IDs

Fields within complex Record IDs can now be retrieved and used within SELECT queries.

// Create a record with a complex Record ID
CREATE recording:{ location: 'London', date: time::now() } SET temperature = 23.7;
// Select a field from within the Record ID
SELECT id.location, temperature FROM recording;

Support for FLEXIBLE fields on SCHEMAFULL tables

SCHEMAFULL and SCHEMALESS functionality can now be used together, suitable for capturing schema-free log data.

// We want to define a SCHEMAFULL table
DEFINE TABLE person SCHEMAFULL;
// But we want one field to allow any content
DEFINE FIELD settings on person FLEXIBLE TYPE object;
// We can then set the field value without a schema
CREATE person:test CONTENT {
	settings: {
		nested: {
			object: {
				thing: 'test'
			}
		}
	}
};

Support code blocks and advanced expressions

It is now possible to run blocks of code, with support for an arbitrary number of statements, including LET and RETURN statements. This allows for writing advanced custom logic, and allowing for more complicated handling of data operations.

DEFINE FIELD average_sales ON metrics VALUE {
	LET $sales = (SELECT VALUE quantity FROM sales);
	LET $total = math::sum($sales);
	LET $count = count($sales);
	RETURN ($total / $count);
};

Enable access to the user input in DEFINE FIELD statements

DEFINE FIELD statements now have access to an $input variable in the VALUE and ASSERT clauses.

DEFINE FIELD name ON TABLE person
	-- Prevent any changes, if the input value is not title-case
	ASSERT IF $input THEN $input = /^[A-Z]{1}[a-z]+$/ ELSE true END
	-- If the input value is title-case, then add a prefix to the field
	VALUE IF $input THEN 'Name: ' + $input ELSE $value END
;
-- This record update will fail
UPDATE person:test CONTENT { name: 'jaime' };
-- This record update will succeed
UPDATE person:test REPLACE { name: 'Tobie' };

Define global parameters with DEFINE PARAM statements

SurrealDB now supports the ability to define global database-wide parameters, which can be useful to store certain commonly-used variables.

-- Define a global parameter which will be accessible to all queries.
DEFINE PARAM $STRIPE VALUE "https://api.stripe.com/payments/new";
-- Use the defined global parameter in all queries on the database.
DEFINE EVENT payment ON TABLE order WHEN $event = 'CREATE' THEN http::post($STRIPE, $value);
// We can also override the parameter for certain queries if we want
LET $STRIPE = "https://api-dev.stripe.com/payments/new";
// And fetching this parameter will fetch the locally defined parameter
RETURN http::post($STRIPE, $value);

Define custom functions with DEFINE FUNCTION statements

SurrealDB now supports the ability to define global database-wide custom functions, which allow for complicated or repeated user-defined code, to be run seamlessly within any query across the database. Custom functions support typed arguments, and multiple nested queries with custom logic.

-- Define a global function which can be used in any query
DEFINE FUNCTION fn::get_person($first: string, $last: string, $birthday: string) {

	LET $person = SELECT * FROM person WHERE [first, last, birthday] = [$first, $last, $birthday];

	RETURN IF $person[0].id THEN
		$person[0]
	ELSE
		CREATE person SET first = $first, last = $last, birthday = $birthday
	END;

};

-- Call the global custom function, receiving the returned result
LET $person = fn::get_person('Tobie', 'Morgan Hitchcock', '2022-09-21');

Add support for selecting ranges of records

SurrealDB supports the ability to query a range of records, using the record ID. The record ID ranges, retrieve records using the natural sorting order of the record IDs. These range queries can be used to query a range of records in a timeseries context.

-- Select all person records with IDs between the given range
SELECT * FROM person:1..1000;
-- Select all records for a particular location, inclusive
SELECT * FROM temperature:['London', NONE]..=['London', time::now()];
-- Select all temperature records with IDs less than a maximum value
SELECT * FROM temperature:..['London', '2022-08-29T08:09:31'];
-- Select all temperature records with IDs greater than a minimum value
SELECT * FROM temperature:['London', '2022-08-29T08:03:39']..;
-- Select all temperature records with IDs between the specified range
SELECT * FROM temperature:['London', '2022-08-29T08:03:39']..['London', '2022-08-29T08:09:31'];

Rust API with embedded and remote connections over HTTP and WebSockets

SurrealDB now has a native Rust API for connecting to local database instances, and remote database servers over both HTTP and WebSockets with a binary protocol and a custom serialization format.

static DB: Surreal<Client> = Surreal::init();

#[tokio::main]
async fn main() -> surrealdb::Result<()> {
    // Connect to the database
    DB.connect::<Ws>("cloud.surrealdb.com").await?;
    // Select a namespace + database
    DB.use_ns("test").use_db("test").await?;
    // Create or update a specific record
    let tobie: Record = DB
        .update(("person", "tobie"))
        .content(Person { name: "Tobie" })
        .await?;
    Ok(())
}

Embedding the results of a subquery

SurrealDB now allows you to use the resulting Record IDs of a subquery, and embed them within another record, without having to specify the id field specifically.

// It is now possible to define a new Record ID as normal
LET $friend = (CREATE person SET name = 'Jaime');
// There is no need to select the `id` field using `$friend.id`
CREATE person SET name = "Tobie", friend = $friend;

Embedded Record IDs in CREATE and RELATE statements

SurrealDB now supports the ability to set a specific reocrd ID in the record content itself when creating records using CREATE and RELATE statements.

// It is now possible to define a new Record ID as normal
CREATE person:test SET name = "Tobie";
// Or by specifying the Record ID within the record data part of the query
CREATE person SET id = person:test, name = "Tobie";
// In addition the ID can be specified in a CONTENT object
CREATE person CONTENT { id: person:test, name: "Tobie" };
// Numbers can be used, and will be converted into a Record ID
CREATE user CONTENT { id: 1, name: "Robert" };
// Strings can be used, and will be converted into a Record ID
CREATE city CONTENT { id: "London", name: "London" };
// UUIDs can be used, and will be converted into a Record ID
CREATE city CONTENT { id: "8e60244d-95f6-4f95-9e30-09a98977efb0", name: "London" };
// Complex values can be used, and will be converted into a Record ID
CREATE temperature CONTENT { id: ["London", time::now()], name: "London" };
// Complex values can be used, and will be converted into a Record ID
RELATE person:tobie->knows->person:jaime SET id = knows:cofounder;

Support for deep-merge record updates

SurrealDB now supports deep-merging record changes, ensuring that only specified object fields are modified and overwritten.

// Create a record
CREATE person:test SET name.initials = 'TMH', name.first = 'Tobie', name.last = 'Morgan Hitchcock';
// Use the MERGE clause to deep merge objects, without overwriting any fields
UPDATE person:test MERGE {
	name: {
		title: 'Mr',
		initials: NONE,
	},
	tags: ['Rust', 'Golang', 'JavaScript'],
};

Dynamic expressions in complex Record IDs

Complex Record IDs now support dynamic expressions, allowing parameters, and function expressions to be used as values within the IDs. This is useful in a timeseries context, or for ensuring locality between specific records in a table.

// Set a new parameter
LET $now = time::now();
// Create a record with a complex ID using an array
CREATE temperature:['London', $now] SET
	location = 'London',
	date = $now,
	temperature = 23.7
;
// Create a record with a complex ID using an object
CREATE temperature:{ location: 'London', date: $now } SET
	location = 'London',
	date = $now,
	temperature = 23.7
;
// Select a range of records up to the current time
SELECT * FROM temperature:['London', '2022-08-29T08:03:39']..['London', time::now()];

Complex Record IDs

SurrealDB now supports the ability to define record IDs using arrays and objects. These values sort correctly, and can be used to store values or recordings in a timeseries context.

// Create a record with a complex ID using an array
CREATE temperature:['London', '2022-08-29T08:03:39'] SET
	location = 'London',
	date = '2022-08-29T08:03:39',
	temperature = 23.7
;
// Create a record with a complex ID using an object
CREATE temperature:{ location: 'London', date: '2022-08-29T08:03:39' } SET
	location = 'London',
	date = '2022-08-29T08:03:39',
	temperature = 23.7
;
// Select a specific record using a complex ID
SELECT * FROM temperature:['London', '2022-08-29T08:03:39'];

Record ID ranges

SurrealDB now supports the ability to query a range of records, using the record ID. The record ID ranges, retrieve records using the natural sorting order of the record IDs. With the addition of complex record IDs above, this can be used to query a range of records in a timeseries context.

// Select all person records with IDs between the given range
SELECT * FROM person:1..1000;
// Select all temperature records with IDs between the given range
SELECT * FROM temperature:['London', '2022-08-29T08:03:39']..['London', '2022-08-29T08:09:31'];

SurrealDB REPL

SurrealDB now supports the ability to start a command-line REPL to query a local or remote database from the terminal.

user@localhost % surreal sql --conn http://localhost:8000 --user root --pass root --ns test --db test

Strict mode

SurrealDB now supports the ability to startup in strict mode. When running in strict mode, all NAMESPACE, DATABASE, and TABLE definitions will not be created automatically when data is inserted. Instead if the selected namespace, database, or table has not been specifically defined, then the query will return an error.

user@localhost % surreal start --strict --log debug --user root --pass root memory

Futures

Values which should be computed only when outputting data, can be stored as futures. These values are stored in SurrealDB as SurrealQL code, and are calculated only when output as part of a SELECT clause.

Casting

In SurrealDB, all data values are strongly typed. Values can be cast and converted to other types using specific casting operators. These include bool, int, float, string, number, decimal, datetime, and duration casts.

UPDATE product SET
	name = "SurrealDB",
	launch_at = "2021-11-01",
	countdown = <future> { launch_at - time::now() }
;

UPDATE person SET
	waist = <int> "34.59",
	height = <float> 201,
	score = <decimal> 0.3 + 0.3 + 0.3 + 0.1
;

RELATE statements

The RELATE statement adds graph edges between records in SurrealDB. It follows the convention of vertex -> edge -> vertex or noun -> verb -> noun, enabling the addition of metadata to the edge record.

INSERT statements

The INSERT statement resembles the traditional SQL statement, enabling users to get started quickly. It supports the creation of records using a VALUES clause, or by specifying the record data as an object.

-- Add a graph edge between user:tobie and article:surreal
RELATE user:tobie->write->article:surreal
	SET time.written = time::now()
;

-- Add a graph edge between specific users and developers
LET $from = (SELECT users FROM company:surrealdb);
LET $devs = (SELECT * FROM user WHERE tags CONTAINS 'developer');
RELATE $from->like->$devs UNIQUE
	SET time.connected = time::now()
;

INSERT INTO company {
	name: 'SurrealDB',
	founded: "2021-09-10",
	founders: [person:tobie, person:jaime],
	tags: ['big data', 'database']
};

INSERT IGNORE INTO company (name, founded)
	VALUES ('SurrealDB', '2021-09-10')
	ON DUPLICATE KEY UPDATE tags += 'developer tools'
;

Expressions

SurrealQL supports fetching data using dot notation ., array notation [], and graph semantics ->. SurrealQL enables records to link to other records and traverses all embedded links or graph connections as desired. When traversing and fetching remote records SurrealQL enables advanced filtering using traditional WHERE clauses.

-- Select a nested array, and filter based on an attribute
SELECT emails[WHERE active = true] FROM person;

-- Select all 1st, 2nd, and 3rd level people who this specific person record knows, or likes, as separate outputs
SELECT ->knows->(? AS f1)->knows->(? AS f2)->(knows, likes AS e3 WHERE influencer = true)->(? AS f3) FROM person:tobie;

-- Select all person records (and their recipients), who have sent more than 5 emails
SELECT *, ->sent->email->to->person FROM person WHERE count(->sent->email) > 5;

-- Select other products purchased by people who purchased this laptop
SELECT <-purchased<-person->purchased->product FROM product:laptop;

-- Select products purchased by people in the last 3 weeks who have purchased the same products that we purchased
SELECT ->purchased->product<-purchased<-person->(purchased WHERE created_at > time::now() - 3w)->product FROM person:tobie;

Embedded JavaScript functions

Javascript functions can be used for more complex functions and triggers. Each Javascript function iteration runs with its own context isolation - with the current record data passed in as the execution context or this value.

CREATE film SET
	ratings = [
		{ rating: 6, user: user:bt8e39uh1ouhfm8ko8s0 },
		{ rating: 8, user: user:bsilfhu88j04rgs0ga70 },
	],
	featured = function() {
		return this.ratings.filter(r => {
			return r.rating >= 7;
		}).map(r => {
			return { ...r, rating: r.rating * 10 };
		});
	}
;

Pre-defined aggregate analytics views

Aggregate views let you pre-compute analytics queries as data is written to SurrealDB. Similarly to an index, a table view lets you select, aggregate, group, and order data, with support for moving averages, time-based windowing, and attribute-based counting. Pre-defined aggregate views are efficient and performant, with only a single record modification being made for every write.

-- Drop all writes to the reading table. We don't need every reading.
DEFINE TABLE reading DROP;

-- Define a table as a view which aggregates data from the reading table
DEFINE TABLE temperatures_by_month AS
	SELECT
		count() AS total,
		time::month(recorded_at) AS month,
		math::mean(temperature) AS average_temp
	FROM reading
	GROUP BY city
;

-- Add a new temperature reading with some basic attributes
CREATE reading SET
	temperature = 27.4,
	recorded_at = time::now(),
	city = 'London',
	location = (-0.118092, 51.509865)
;