WebsiteDiscordBlog

Using the Space and Time CLI

End-to-end steps for registering and interacting with Space and Time using the SxT CLI.

Welcome to Space and Time, the decentralized data warehouse built for smart contracts. This guide provides step-by-step instructions to get started with Space and Time.

1. Prerequisites and setup

There are three primary ways to connect to Space and Time:

  1. Space and Time dapp
  2. Space and Time API
  3. Space and Time JDBC

In this guide, we're going to use the SxTCLI which is an application/CLI wrapper around the API.

A) SxT prerequisites

To connect to Space and Time using the SxT CLI we'll need a few items.

  1. API URL - This will be provided in your Welcome to Space and Time email. You can find more info here if you want to sign up for beta access.
  2. JoinCode - While you can sign up and register without a JoinCode, you will be limited to trial access, and certain features will be unavailable. You will receive your JoinCode in your Welcome to Space and Time email.

B) SxT CLI setup

These instructions will vary slightly depending on your OS and shell.

1. Download and install the latest JavaJDK.

  • Select your OS / Chip Architecture
  • Download the file named: jdk-<latest_version>_*
  • Confirm Java is installed with java --version, which should return something like:
└─[$] java --version                                                                                            
java 20.0.1 2023-04-18
Java(TM) SE Runtime Environment (build 20.0.1+9-29)
Java HotSpot(TM) 64-Bit Server VM (build 20.0.1+9-29, mixed mode, sharing)

2. Download and run SxT CLI jar

Download the latest version of the SxT CLI here. This link will take you to a top-level directory with all CLI versions. Ensure you choose the latest version (i.e., 0.0.2 is more recent than 0.0.1). When you click on the latest version, you'll see two files: .jar and .pom. For this tutorial, you only need to download the .jar file, not the .pom file.

  • From within the directory in which you downloaded the Jar, confirm the execution of the SxT CLI with the following: 
└─[$] java -jar sxtcli-<version>.jar help                                       
Usage: <main class> [COMMAND]
Commands:
  help          Display help information about the specified command.
  authenticate  Perform platform authentication commands.
  biscuit       Perform biscuit commands.
  discover      Perform resource discovery commands.
  sql           Perform SQL commands.
  sql-support   Perform supporting SQL commands.

3. Add the CLI to your shell environment

Mac OSX & Linux

Creating a shell alias for running the SxT CLI is recommended for ease of use.

Using your editor of choice, edit your shell profile:

Mac OSXLinux
~/.zshrc~/.bash_profile

Add this line:

alias sxtcli='java -jar <PathToJar>/sxtcli-<LATEST_VERSION>.jar'

🚧

You must reload your shell or open a new terminal to properly load the alias. Also, if you're on OSX and you want to level up your shell game, check out OhMyZsh.

Windows

For instructions on adding the CLI to your PATH environment variable on Windows, see this resource.

Test CLI execution

To test that your installation was successful, run thesxtcli help command:

└─[$] sxtcli help
Usage: <main class> [COMMAND]
Commands:
  help          Display help information about the specified command.
  authenticate  Perform platform authentication commands.
  biscuit       Perform biscuit commands.
  discover      Perform resource discovery commands.
  sql           Perform SQL commands.
  sql-support   Perform supporting SQL commands.

4. Environment variables

Throughout the Space and Time docs, you will see references to variables like $API_URL and $AT. Any variable prefaced by a $ indicates that we've created an environment variable in our shell to represent the variable.

This helps us make our example code clearer and allows you to save time as you walk through the guide. In some cases, security advantages exist, such as when the variable represents a private key. A complete discussion of how environment variables work is beyond the scope of this guide.

How you set an environment variable will depend on your OS and shell, but for OSX or Linux, it's usually as simple as setting them from your shell/terminal like:

export API_URL="https://<SxT-API-URI-HERE>"

Or if you're setting an environment variable for your SxT accessToken:

export AT="your-long-JWT-like-SxT-Access-Token-Here"

Now you've got the SxT CLI installed, so let us connect to Space and Time!

2. Register and authenticate

Registering and authenticating a new user with Space and Time with CLI is as easy as:

sxtcli authenticate register --url=$API_URL --userId=$USERID --code="<joinCode>"

📘

Please take note of the public and private key and store your private key somewhere safe, like a password manager. This private key is what you will use to authenticate to the SxT network. Also, please take note of your accessToken. This is behaves as a session token, similar to a JWT. It is only valid for 30 minutes.

We've introduced two environment variables here, so let us define those:

  • $API_URL is the root/base Space and Time API URL prefaced with https://
  • $USERID is your desired username for SxT. Usernames are unique, so if you try registering a name already taken, you'll get an error. You can check if a userId exists in SxT with the following command: sxtcli authenticate check-id --url=$API_URL --userId=$USERID

Let's go ahead and create three more environment variables right now as well: 

export AT="<your-access-token>"
export USER_PRIVATE_KEY="<your-private-key>"
export USER_PUBLIC_KEY="<your-public-key>"

📘

If your access token expires, you can now easily login and get a new one with the following command: sxtcli authenticate login --privateKey=$USER_PRIVATE_KEY --publicKey=$USER_PUBLIC_KEY --url=$API_URL --userId=$USERID

3. Run a query

Now that you've connected to the platform, it's time to run your first query! Space and Time indexes blockchain data from many EVM-compatible chains. For our first query, we will look at a block from Ethereum:

sxtcli sql --accessToken=$AT --url=$API_URL dql \
--resourceId="ETHEREUM.BLOCKS" --sqlText="SELECT * FROM ETHEREUM.BLOCKS LIMIT 1" 

[ {
  "MINER" : "0x4C10a52647e8Ee35A166CeF8aa5B01E3C4E267B0",
  "PARENT_HASH" : "0x1595d3d29c3547d143cf4ab985ee70737f75fe31aaf5c4eeca38d5c633557eac",
  "BLOCK_NUMBER" : 2038655,
  "BLOCK_HASH" : "0x628d9b7ba35977fd701ef6bbf9b77a1bfcf0eef6598fdb5b9501abc6a8673d40",
  "SIZE_" : 541,
  "TIME_STAMP" : "2016-08-09T06:44:10.000+00:00",
  "REWARD" : "5000000000000000000",
  "BASE_FEE_PER_GAS" : "",
  "GAS_USED" : 0,
  "GAS_LIMIT" : 4712388,
  "TRANSACTION_COUNT" : 0
} ]

As you can see, the following SQL query is being run against Space and Time:

SELECT * FROM ETHEREUM.BLOCKS LIMIT 1

Also, take note of the --resourceId parameter in the command. A resourceId in Space and Time is the SCHEMA.TABLE and are used for query routing at the SxT gateway level to ensure optimal response times.

📘

Like any good API, we have explicit documentation around our endpoints, and you can find the Data Query Language (DQL) endpoint documentation here.

Speaking of schemas and tables, why don't we create a set now?

4. Create schema and table

1. Create schema

Before we create a table, it's a good idea to create a schema for that table to live in. Creating a schema in Space and Time is easy:

sxtcli sql --accessToken=$AT --url=$API_URL \
ddl --resourceId="<SCHEMA>" --sqlText="CREATE SCHEMA <SCHEMA>"

2. Create a biscuit

Space and Time uses biscuits to handle authorization for data on the network. When we create a table, we send a biscuit with the request to establish authorization facts about the table. Biscuits are generated using a public/private key pair, similar but distinct from the key pair we use to authenticate. 

sxtcli biscuit generate table --resources="<SCHEMA>.<TABLE>" \
  --operations="CREATE,DROP,INSERT,UPDATE,MERGE,DELETE,SELECT"

This will return a public key, a private key, and the biscuit token generated using the key pair.

🚧

Your biscuit private key, and the biscuit token are secrets and should be treated as such. Someone who gets your private key can generate biscuits and take over your tables. Similarly, anyone with your biscuit can undertake any of the --operations associated with the biscuit.

Let us load these up into environment variables:

export BISCUIT_PRIVATE_KEY="63564B7C9E..."
export BISCUIT_PUBLIC_KEY="A18461FB1C33C..."
export BISCUIT="EtUCCuoBCg5ze..."

A complete discussion on biscuit tokens is beyond the scope of this guide. For a deeper dive, please see Biscuit Authorization and BiscuitSec.org.

📘

You can find full specs for the SxT DDL API endpoint here.

3. Create a table

Let us take a look at the SQL that we will execute first:

CREATE TABLE <SCHEMA>.<TABLE>
(
    ID INT,
    NAME VARCHAR,
    PRIMARY KEY (ID)
) 
WITH 
(
    "public_key" = <your-biscuit-pulic-key-here-no-quotes>,
    "access_type" = 'public_read'
);

From the SxT CLI we can execute table creation like this:

sxtcli sql --accessToken=$AT --url=$API_URL ddl --resourceId="<SCHEMA>.<TABLE>" --biscuits=$BISCUIT --sqlText="CREATE TABLE <SCHEMA>.<TABLE> (ID INT, NAME VARCHAR, PRIMARY KEY (ID)) WITH \"public_key=<biscuit-public-key-here>,access_type=public_read\""

Success! You've now created your first table in Space and Time!

Please note, in the command above, you'll need to enter your biscuit public key where it stays <biscuit-public-key-here>without quotes. So it will look like this: public_key=A18461FB1C33C...

📘

You can learn about access_type and how to secure your tables here.

5. Load data

1. Craft the SQL

INSERT INTO <SCHEMA>.<TABLE> (ID, NAME) 
VALUES (1, 'm0thra')

2. INSERT SxT CLI command

sxtcli sql --accessToken=$AT --url=$API_URL dml --resourceId="<SCHEMA>.<TABLE>" --biscuits=$BISCUIT --sqlText="INSERT INTO <SCHEMA>.<TABLE> (ID, NAME) VALUES (1, 'm0thra')"

Next, to view your data, run this command: 

 sxtcli sql --accessToken=$AT --url=$API_URL dql \ 
 --resourceId="<SCHEMA>.<TABLE>" --sqlText="SELECT * FROM <SCHEMA>.<TABLE>"

📘

Note the use of dmlin the INSERT command. You can find more information about the SxT Data Modification Language (DML) endpoint here.

6. Join on-chain and off-chain data

This is where Space and Time shines! The ability to join your data to our indexed blockchain data in a single request opens you up to endless powerful possibilities.

In this example, we'll show you how to join data from the SE_PLAYGROUND.DAPP_USER_WALLETS (user created) with ETHEREUM.FUNGIBLETOKEN_WALLET (SxT created) to answer the following question:

How many users (by address) held our ERC20 token before a given block?

1. First, the SQL

SELECT DISTINCT 
    eth_erc_20.WALLET_ADDRESS 
FROM 
    ETHEREUM.FUNGIBLETOKEN_WALLET AS eth_erc_20 
JOIN 
    SE_PLAYGROUND.DAPP_USER_WALLETS_1 AS dapp_users 
ON 
    eth_erc_20.WALLET_ADDRESS = dapp_users.USER_WALLET_ADDRESS
WHERE 
    eth_erc_20.TOKEN_ADDRESS = '0x48C3399719B582dD63eB5AADf12A40B4C3f52FA2' --SWISE Token
AND 
    eth_erc_20.BLOCK_NUMBER > '15480960' 
LIMIT 10;

2. The SxT CLI command

sxtcli sql --accessToken=$AT --url=$API_URL dql --resourceId="SE_PLAYGROUND.DAPP_USER_WALLETS_1" --sqlText="SELECT DISTINCT WALLET_ADDRESS FROM ETHEREUM.FUNGIBLETOKEN_WALLET AS eth_erc_20 JOIN SE_PLAYGROUND.DAPP_USER_WALLETS_1 AS dapp_users ON eth_erc_20.WALLET_ADDRESS = dapp_users.USER_WALLET_ADDRESS WHERE eth_erc_20.TOKEN_ADDRESS = '0x48C3399719B582dD63eB5AADf12A40B4C3f52FA2' AND eth_erc_20.BLOCK_NUMBER > '15480960' LIMIT 100"

With that query, we can see that we have three users in our table that held SWISE token before block 15480960!

[
 {"WALLET_ADDRESS":"0x992f534fcc87864875224d142d6Bf054B1882160"},
 {"WALLET_ADDRESS":"0x1231DEB6f5749EF6cE6943a275A1D3E7486F4EaE"},
 {"WALLET_ADDRESS":"0xB825779da5f2D625540e04bf53015CD6fe38F969"}
]

And now you're ready to start using Space and Time for your use case!

Updated 5 months ago

What’s Next