❗

This page is part of the Getting Started guide. For the best experience, make sure to follow the guide step-by-step

Joining indexed blockchain data with your data is a core function of Space and Time. Before you can do that, you have to load your data into Space and Time. Fortunately, creating a custom table is super easy. In this part, we'll walk you through creating a table using our SQL Operations REST API.

How to create a table

Before we get started, make sure you have the API Reference page open for the Configure Resources (DDL) API.

1. Create a new schema

Tables live underneath a schema like this:SCHEMA.TABLE_NAME. Before creating a table, we'll need to use the DDL API to create a new schema. There is only one required parameter for creating a new schema.

• sqlText: This will be the SQL you want to execute.

❗️

Replace <NEW_SCHEMA_NAME> with a unique schema name.

This will ensure you're able to successfully create a new schema in the shared database environment. If you use a schema name that's already taken, the API will return an error.

CREATE SCHEMA <NEW_SCHEMA_NAME>

Here is an example cURL request for reference:

 curl -i --request POST \                                                                   
     --url "$SxT_DDL_API" \
     --header 'accept: application/json' \
     --header 'authorization: Bearer <YOUR_SxT_ACCESS_TOKEN>' \
     --header 'content-type: application/json' \
     --data '
{
  "sqlText": "CREATE SCHEMA <NEW_SCHEMA_NAME>"
}
'

2. Create a new public/private keypair and biscuit

Before creating a new table, we need to set up authorization parameters for the table. With Space and Time, this is done using a Biscuit token.

📘

Your keypair is for authenticating, and your table has a separate keypair for authorizing access. Your table's keypair will be used to generate a biscuit that will be used for request authorization.

Creating a biscuit with the SxT CLI is straightforward. However, if it's your first time working with biscuits, we recommend following our guide to creating biscuits with the SxT CLI. Take note of your resourceId (SCHEMA.TABLE) as you will need that to create your biscuit.

For reference, here is the command that you can use to generate a biscuit via the SxT CLI:

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

Before moving to step 3, make sure you have created a biscuit!

3. Create a table

Now we're ready to create a table! You will need three values:

• biscuit: This will be the biscuit that you generated in step 2. This will be added to the header of the POST request.
• biscuit_public_key: This is the public key for the biscuit you created in step 2 (not your public key used to register and authenticate). This value will be appended to the data payload of the POST request.
• sqlText: This will be the SQL DDL command to create your table and will also be appended to the data payload of the POST request.

Here is a sample of the sqlText. We will use this to create a simple public table for collecting user wallet addresses:

CREATE TABLE <ENTER_SCHEMA_HERE>.DAPP_USER_WALLETS(
  User_Wallet_Address  VARCHAR  PRIMARY KEY,
  User_Subscription    VARCHAR
) WITH "public_key=<biscuit_public_key>,access_type=public_write"

And the cURL request to the API:

curl --request POST \
     --url "$SxT_DDL_API" \
     --header 'accept: application/json' \
     --header 'authorization: Bearer <YOUR_SxT_ACCESS_TOKEN_HERE>' \
     --header 'biscuit: <YOUR_BISCUIT_HERE>' \
     --header 'content-type: application/json' \
     --data '
{
  "sqlText": "CREATE TABLE <ENTER_SCHEMA_HERE>.DAPP_USER_WALLETS (
  User_Wallet_Address  VARCHAR  PRIMARY KEY,
  User_Subscription    VARCHAR
) WITH "public_key=<biscuit_public_key>,access_type=public_write\"" 
}'

👍

You created your first table!

📘

The table we created has public_write access. Learn more about how table security works and the different permissions you can assign to your tables here.

Next, we'll walk through inserting data into the table you created using our REST APIs!