Structured query language (SQL) is a programming language for storing and processing information in a relational database.

Space and Time is an ANSI-standard, ACID-compliant data warehouse, which means it accepts standard SQL, the same as any other relational database. If you need to brush-up on writing SQL, check out this resource.

You can run SQL against:

• Your own tables in Space and Time, containing off-chain data that you've ingested
• Tables containing relational, realtime blockchain data that we've indexed from major chains

📘

Try it out!

Hop over to our API Reference page to try these commands out on our SQL API.

Types of SQL commands

There are three SQL commands you'll need to know to use Space and Time:

DQL

Data query language (DQL) is a type of SQL command used to retrieve data from the database. In other words, it's used to run queries.

DQL is only associated with one command: SELECT. The syntax for SELECT commands is as follows:

SELECT column1, column2, ...  
FROM table_name;

Here's an example of a DQL command you might run in Space and Time:

SELECT EXCHANGE_NAME, COUNT(*)  
FROM ETH.DEX_TRADE  
GROUP BY EXCHANGE_NAME  
ORDER BY 2 DESC

DDL

Data definition language (DDL) changes the structure of the table. In other words, it's used to configure your table.

DDL is associated with the following commands:

• CREATE: used to create a new table in the database. The syntax for the CREATE command is as follows:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

Here's an example of a CREATE statement you might run in Space and Time:

CREATE TABLE My_User_Wallets (
  User_Wallet_Address VARCHAR PRIMARY KEY,  
  User_Subscription VARCHAR
) WITH "publicKey=4c4d31237894198ab4174f8b49f9e9dc370737bcc4e741897ba56e86ffb5fa2f,accessType=public_write"

• DROP: used to delete both the structure and record stored in the table. The syntax for the DROP command is as follows:

DROP TABLE table_name;

Here's an example of a DROP statement you might run in Space and Time:

DROP TABLE My_User_Wallets;

DML

Data manipulation language (DML) modifies the database. In other words, it's used to change the data in your table.

DML is associated with the following commands:

• INSERT: used to insert data into the row of a table. The syntax for the INSERT command is as follows:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Here's an example of an INSERT statement you might run in Space and Time:

INSERT INTO My_User_Wallets (User_Wallet_Address, User_Subscription)  
VALUES ('0x456008396BFdd64159998cE362b8D650FFd6F28b', 'premium')

• UPDATE: used to update or modify the value of a column in the table. The syntax for the UPDATE command is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Here's an example of an UPDATE statement you might run in Space and Time:

UPDATE My_User_Wallets  
SET User_Subscription = 'premium'  
WHERE User_Wallet_Address = '0x456008396BFdd64159998cE362b8D650FFd6F28b' 

• DELETE: used to delete existing records in a table. The syntax for the DELETE command is as follows:

DELETE FROM table_name WHERE condition;

Here's an example of a DELETE statement you might run in Space and Time:

DELETE FROM My_User_Wallets WHERE User_Subscription = 'expired'