Documentation
WebsiteDiscordBlog

Create Your Own Table

Most applications require data management - and SXT has you covered!

Creating your own custom tables in the Space and Time network is very similar to any other database, with only minor differences to account for SXT's decentralized nature - the biggest is the need for keypairs that govern authorization of tables.

Let's Get Started!

To be able to validate your script, we'd ask that you generate a new file called Create_Your_Own_Table.sh that you can commit to your branch of the SXT-Community/SXTAccreditation repo at the end of the activity.


Create a Table Keypair

All resources (tables, views, etc.) on the Space and Time network must be created with a public/private keypair. The public key is added to the CREATE TABLE statement and saved on the network. The private key is used to sign self-describing authorization keys (aka biscuits) which authorize any request to that table.

This allows authorization to be decentralized, while allowing the owner of the table (aka the person holding the private key) to define and share signed biscuits that the table can verify as authentic. The self-describing nature of biscuits also allow that authorization to be limited to a particular table, person, time period, or subscription.

To get started quickly, you can use the SXTCLI to generate a new table keypair AND an admin-level biscuit, all with one command: Open up a terminal window and enter the command:

sxtcli biscuit generate wildcard

The above command will return:

  1. An ED25519 keypair (hex encoded) that can be used for creating tables, views, etc.
  2. A description of the biscuit capabilities, in this case:
    1. for ANY resource ("*") with a matching public key,
    2. allow ANY command ("*") - hence the name "wildcard" or admin biscuit
  3. The actual biscuit key itself

This output will be displayed as:

Biscuit KeyPair (hex)
Private key: D4EAC895937773E3047FE2E9AFA7004B80601BA0F687511BC23658D366FFB51B
Public key:  51297647296389ADABBB6E62230203C620DF47BF8DCAEEF1B1EC35507FBA48E0
Biscuit content:
 Facts:
  sxt:capability("*","*");
Biscuit:
EpABCiYKDnN4dDpjYXBhYmlsaXR5CgEqGAMiDwoNCIAIEgMYgQgSAxiBCBIkCAASIJK...

Note, the below key was modified to be invalid -- please generate your own fresh key!

📘

Both Users and Tables / Views use the ED25519 keypair for security. However, there is a difference in encoding between the two:

  • User authentication will expect Base64 encoding
  • Tables and Views, which need it embedded in SQL, will expect Hex encoding

The CLI will return the correct encoding when generating new keys.

🚧

Store the private key somewhere safe! If you lose the private key, you lose control of the table, view, or resource!

Multiple tables or views can share a single keypair - for example, a User table and a User_Settings table may share the same keypair (and so wildcard biscuit), since they are related and often operated on together.

That said, it is NOT recommended to reuse keys too widely, as it broadens the exposure of any stolen or lost keys.

👍

Congrats! You have created a new resource public/private keypair!

Save Your Keys

Before we go any farther, let's take a moment to save your keys and other variables to your project file. It's highly recommended you save your keys in a format that works well with dotenv or shell files. Specifically:

API_URL="https://api.spaceandtime.dev"
API_KEY="sxt_b24iOiIzMWNiMGI0Yi0xMjZlLTRl2MtYTd"

RESOURCE_PRIVATE_KEY="D4EAC895937773E3047FE2E9AFA7004B80601BA0F687511BC23658D366FFB51B"  
RESOURCE_PUBLIC_KEY="51297647296389ADABBB6E62230203C620DF47BF8DCAEEF1B1EC35507FBA48E0"
ADMIN_BISCUIT="EpABCiYKDnN4dDpjYXBhYmlsaXR5CgEqGAMiDwoNCIAIEgMYgQgSAxiBCBIkCAASIJK..."

By convention, Keys for tables, views, etc. are typically labeled as "Resources" to differentiate them from "User" keys, hence the the RESOURCE_ prefix above. The above examples are NOT real keys, and have been purposely modified to be invalid - please generate your own unique keys.

❗️

Do not push an active API_KEY to github! It would be possible for someone to gain full control of your account / table and lock you out! Save keys to an dotenv file, or to an alternate location entirely.


Create Your Table

Once you have an ED25519 keypair, it's time to create your table!

When creating a new table, the first thing you'll need is a good ol' fashioned CREATE TABLE statement!
We'll put this into a shell script heredoc, to keep it easy to read.

🚧

Be sure the $USERID is replaced with your actual Space and Time UserName before executing any example SQL - either manually, or by setting an environment variable or dotenv file.

CREATE_SQL=$(cat << EOM

  CREATE TABLE SXTTemp.Test_$USERID
  ( MyID              INTEGER
  , MyBigint          BIGINT
  , MyText            VARCHAR
  , MyDate            DATE
  , MyBoolean         BOOLEAN
  , MyDecimal         DECIMAL(18,2)
  , MyFloat           FLOAT
  , MyJson            JSONB
  , MyTimestamp       TIMESTAMP
  , primary key(MyID)
  )
  with "public_key=$RESOURCE_PUBLIC_KEY,access_type=permissioned"

EOM
)

The table above is obviously designed to demonstrate the major data types available. You may also notice that after the standard CREATE TABLE... statement is a with command, containing two parameters:

  • public_key: the public key created above, and allows your table object on the network to verify biscuit authorizations signed by the private key. It's best practice to save your PUBLIC_KEY to an environment variable or dotenv file, rather than hard-code.
  • access_type: the default behavior for the table, when no biscuit is supplied. In this case, we're setting permissioned which is the most restrictive, allowing no permissions that are not specifically set by a biscuit.

For more options and types of tables, check out this page.

Finally, let's execute the above CREATE TABLE statement and create the table! The above CREATE_SQL can be executed as-is using the CLI:

sxtcli sql --url=$API_URL --accessToken=$ACCESS_TOKEN --sqlText="$CREATE_SQL" --biscuits=$ADMIN_BISCUIT

OR

If you would like to create the table using APIs directly, you'll have to remove newlines and tabs from the CREATE_SQL statement, as well as escape the double-quotes, so the string becomes JSON safe:

# replace newlines and tabs, and escape double quotes:
CREATE_SQL="$(echo $CREATE_SQL | tr -d '\n\t\r' | sed 's/"/\\"/g' ) " 

curl --request POST \
     --url https://api.spaceandtime.dev/v1/sql \
     --header 'accept: application/json' \
     --header 'authorization: Bearer '$ACCESS_TOKEN \
     --header 'content-type: application/json' \
     --data '{ "sqlText": "'$CREATE_SQL'", 
               "biscuits": [ "'$ADMIN_BISCUIT'" ] } '

The API will return a 200 code, but nothing is returned from the curl command. AKA, a lack of an error means success.

👍

Congrats! You've created your first table!

Again, sure to replace $USERID with your actual Space and Time UserName. This will allow us to validate and give you credit for your table!


Get Credit:

You'll get credit for this activity if you:

  • Create a new table in the schema: SXTTemp.Test_<your UserID>
    If your UserID has special characters that aren't accepted, simply replace them with underscores ().
  • Commit your work file to the SXT-Community/SXTAccreditation repo
How should I commit my branch changes?

When you're complete with the activity, simply commit your changes to your branch of work, then push to the SXT-Community/SXTAccreditation repo. Many IDEs have automated processes to manage this for you, but to do this manually by opening a temrinal / WSL window, and typing:

  • Line 1: Change to your work folder in your SXT-Community/SXTAccreditation directory
  • Line 2: Make sure your branch is active
  • Line 3: Add all changed files to your local stage
  • Line 4: Commit your changes
  • Line 5: Push your commited changes to github!
cd ./your/path/SXTAccreditation/<your_folder>
git checkout <your_branch>
git add .  
git commit -m "added my new working file"
git push

If you get an error on line 5 saying, The current branch stephens_work has no upstream branch, this is because Github doesn't know about your branch yet. Simply run the command the git tool recommends - it will look something like:
git push --set-upstream origin <your_branch>

When you're done for the day, head over to SXT-Community/SXTAccreditation/Pulls page on Github, and (if you're logged in) you'll see a message like:

Click on the button above to create a Pull Request, give us some feedback in the description, and click "Create Pull Request". Done!

Updated about 2 months ago

What’s Next