SQL Support

SpacetimeDB supports a subset of SQL as a query language. Developers can evaluate SQL queries against a Spacetime database via the spacetime sql command-line tool and the /database/sql/:name_or_address POST HTTP endpoint. Client developers also write SQL queries when subscribing to events in the WebSocket API or via an SDK subscribe function.

SpacetimeDB aims to support much of the SQL 2016 standard, and in particular aims to be compatible with PostgreSQL.

SpacetimeDB 0.6 implements a relatively small subset of SQL. Future SpacetimeDB versions will implement additional SQL features.

Types

Definition statements

Query statements

Data types

SpacetimeDB is built on the Spacetime Algebraic Type System, or SATS. SATS is a richer, more expressive type system than the one included in the SQL language.

Because SATS is a richer type system than SQL, some SATS types cannot cleanly correspond to SQL types. In particular, the SpacetimeDB SQL interface is unable to construct or compare instances of product and sum types. As such, SpacetimeDB SQL must largely restrict themselves to interacting with columns of builtin types.

Most SATS builtin types map cleanly to SQL types.

Nullable types

SpacetimeDB types, by default, do not permit NULL as a value. Nullable types are encoded in SATS using a sum type which corresponds to Rust's Option. In SQL, such types can be written by adding the constraint NULL, like INT NULL.

Logic types

Numeric types

Integer types

An integer is a number without a fractional component.

Adding the UNSIGNED constraint to an integer type allows only positive values. This allows representing a larger positive range without increasing the width of the integer.

Floating-point types

SpacetimeDB supports single- and double-precision binary IEEE-754 floats.

Text types

SpacetimeDB supports a single string type, String. SpacetimeDB strings are UTF-8 encoded.

Note

SpacetimeDB SQL currently does not support length contraints like CHAR(10).

Syntax

Comments

SQL line comments begin with --.

-- This is a comment 
    

Expressions

We can express different, composable, values that are universally called expressions.

An expression is one of the following:

Literals

Binary operators

Logical expressions

Any expression which returns a boolean, i.e. true or false, is a logical expression.

Function calls

Table identifiers

Column references

Wildcards

Special "star" expressions which select all the columns of a table.

Parenthesized expressions

Sub-expressions can be enclosed in parentheses for grouping and to override operator precedence.

CREATE TABLE

A CREATE TABLE statement creates a new, initially empty table in the database.

The syntax of the CREATE TABLE statement is:

Note

CREATE TABLE table_name (column_name data_type, ...);

Examples

Create a table inventory with two columns, an integer inventory_id and a string name:

CREATE TABLE inventory (inventory_id INTEGER, name TEXT); 
    

Create a table player with two integer columns, an entity_id and an inventory_id:

CREATE TABLE player (entity_id INTEGER, inventory_id INTEGER); 
    

Create a table location with three columns, an integer entity_id and floats x and z:

CREATE TABLE location (entity_id INTEGER, x REAL, z REAL); 
    

DROP TABLE

A DROP TABLE statement removes a table from the database, deleting all its associated rows, indexes, constraints and sequences.

To empty a table of rows without destroying the table, use DELETE.

The syntax of the DROP TABLE statement is:

Note

DROP TABLE table_name;

Examples:

DROP TABLE inventory; 
    

Queries

FROM

A FROM clause derives a data source from a table name.

The syntax of the FROM clause is:

Note

FROM table_name join_clause?;

Examples

Select all rows from the inventory table:

SELECT * FROM inventory; 
    

JOIN

A JOIN clause combines two data sources into a new data source.

Currently, SpacetimeDB SQL supports only inner joins, which return rows from two data sources where the values of two columns match.

The syntax of the JOIN clause is:

Note

JOIN table_name ON expr = expr;

Examples

Select all players rows who have a corresponding location:

SELECT player.* FROM player
 JOIN location
 ON location.entity_id = player.entity_id; 
    

Select all inventories which have a corresponding player, and where that player has a corresponding location:

SELECT inventory.* FROM inventory
 JOIN player
 ON inventory.inventory_id = player.inventory_id
 JOIN location
 ON player.entity_id = location.entity_id; 
    

SELECT

A SELECT statement returns values of particular columns from a data source, optionally filtering the data source to include only rows which satisfy a WHERE predicate.

The syntax of the SELECT command is:

SELECT column_expr > FROM from_expr {WHERE expr}?

Examples

Select all columns of all rows from the inventory table:

SELECT * FROM inventory;
SELECT inventory.* FROM inventory; 
    

Select only the inventory_id column of all rows from the inventory table:

SELECT inventory_id FROM inventory;
SELECT inventory.inventory_id FROM inventory; 
    

An optional WHERE clause can be added to filter the data source using a logical expression. The SELECT will return only the rows from the data source for which the expression returns true.

Examples

Select all columns of all rows from the inventory table, with a filter that is always true:

SELECT * FROM inventory WHERE 1 = 1; 
    

Select all columns of all rows from the inventory table with the inventory_id 1:

SELECT * FROM inventory WHERE inventory_id = 1; 
    

Select only the name column of all rows from the inventory table with the inventory_id 1:

SELECT name FROM inventory WHERE inventory_id = 1; 
    

Select all columns of all rows from the inventory table where the inventory_id is 2 or greater:

SELECT * FROM inventory WHERE inventory_id > 1; 
    

INSERT

An INSERT INTO statement inserts new rows into a table.

One can insert one or more rows specified by value expressions.

The syntax of the INSERT INTO statement is:

Note

INSERT INTO table_name (column_name, ...) VALUES (expr, ...), ...;

Examples

Insert a single row:

INSERT INTO inventory (inventory_id, name) VALUES (1, 'health1'); 
    

Insert two rows:

INSERT INTO inventory (inventory_id, name) VALUES (1, 'health1'), (2, 'health2'); 
    

UPDATE

An UPDATE statement changes the values of a set of specified columns in all rows of a table, optionally filtering the table to update only rows which satisfy a WHERE predicate.

Columns not explicitly modified with the SET clause retain their previous values.

If the WHERE clause is absent, the effect is to update all rows in the table.

The syntax of the UPDATE statement is

UPDATE table_name SET > column_name = expr, ... {WHERE expr}?;

Examples

Set the name column of all rows from the inventory table with the inventory_id 1 to 'new name':

UPDATE inventory
  SET name = 'new name'
  WHERE inventory_id = 1; 
    

DELETE

A DELETE statement deletes rows that satisfy the WHERE clause from the specified table.

If the WHERE clause is absent, the effect is to delete all rows in the table. In that case, the result is a valid empty table.

The syntax of the DELETE statement is

DELETE table_name {WHERE expr}?;

Examples

Delete all the rows from the inventory table with the inventory_id 1:

DELETE FROM inventory WHERE inventory_id = 1; 
    

Delete all rows from the inventory table, leaving it empty:

DELETE FROM inventory;