CLI

CLI

Experiment with creating, editing, and querying tables from the comfort of the command line.

Introduction

The Tableland project provides an experimental command line tool that make it easy to interact with the Tableland network from the comfort of your command line. The @tableland/cli is simple and easy to use and integrates nicely with tools like jq. Additionally, when interacting with Tableland's on-chain components, you can specify your own provider endpoints for added control.

Simply install the package, and you are ready to start building on Tableland.

🚧
This is the experimental Tableland command line tool built on top of the @tableland/sdk. For any bugs or feature requests, please open an issue on GitHub. Open an Issue

Setup

Install

You can install the CLI tool via npm:

npm install -g @tableland/cli

Or yarn:

yarn global add @tableland/cli

Environment Variables

Tableland reads the following environment variables automatically instead of needing to specify the corresponding flag (i.e., TBL_ prepended to an all caps snake case of the CLI flag described in Usage below):

TBL_PRIVATE_KEY="wallet_private_key"
# optional provider(s)
TBL_ALCHEMY="alchemy_api_key"
TBL_INFURA="infura_api_key"
TBL_ETHERSCAN="etherscan_api_key"

Some of the mutating commands will need a private key string (--privateKey) for EVM account access while interacting with Tableland (e.g., signing on-chain transactions). Optionally, a custom provider can be set for connecting to the chain itself; only Alchemy, Infura, and Etherscan are currently supported (as optional flags).

⚠️
Tableland is still a testnet and will be launching a Tableland mainnet in 2023. But, smart contracts and apps deployed on testnet and mainnet chains can and should use the Tableland testnet. Developers should still proceed with caution due to the nature of testnets and ensure contracts that use Tableland are upgradable.

Usage

Overview

Most of the common Tableland usage patterns are possible via the command line. In general, you'll need to connect (to a host/provider), create a table (with a schema, using create), insert/mutate values (write), and query (read) a table. Developers can even retrieve all tables owned by a specific EVM address (using list). And for a full list of valid SQL statements used throughout these different commands, check out the SQL Specification’s statement types.

There also exists some useful commands for on-chain information. View Tableland’s live chain deployments & smart contract addresses (chains) or retrieve on-chain data from a create or write query (passing the transaction hash to receipt). Lastly, the corresponding token metadata about a table can also be requested (i.e., tables are on-chain as ERC721s — the minted token’s id can be passed to info).

Commands

There are a number of commands available for interacting with the Tableland APIs. Each one has a specific set of required and optional arguments. Read on to learn more about these different sub-commands.

tableland [command]

Commands:
  tableland chains                    List information about supported chains
  tableland create <schema> [prefix]  Create a new table
  tableland info <name>               Get info about a given table by name
  tableland list [address]            List tables by address
  tableland read <query>              Run a read-only query against a remote
                                      table
  tableland receipt <hash>            Get the receipt of a chain transaction to
                                      know if it was executed, and the execution
                                      details
  tableland token                     Create a SIWE token
  tableland write <statement>         Run a mutating SQL statement against a
                                      remote table

Options

The list of global options for the Tableland CLI include:

Options:
      --help        Show help                                          [boolean]
      --version     Show version number                                [boolean]
  -k, --privateKey  Private key string                                  [string]
  -c, --chain       The EVM compatible chain to target
                                           [string] [default: "ethereum-goerli"]
      --alchemy     Alchemy provider API key                            [string]
      --infura      Infura provider API key                             [string]
      --etherscan   Etherscan provider API key                          [string]

As mentioned, most commands will need flag a --privateKey (or set the TBL_PRIVATE_KEY environment variable). The host and chain, have default values, and the providers (alchemy, infura, etherscan) are optional flags for setting API providers to a chain.

create

Create a new, unique table

tableland create <schema> [prefix]

Positionals:
  schema      SQL table schema                                [string] [required]
  prefix      The table prefix                                [string] [optional]

Options:
      --help        Show help                                          [boolean]
      --schema      SQL table schema.                                   [string]
      --prefix      Table name prefix.                                  [string]
  -k, --privateKey  Private key string                                  [string]
      --chain       The EVM compatible chain to target
                                           [string] [default: "ethereum-goerli"]
      --alchemy     Alchemy provider API key                            [string]
      --infura      Infura provider API key                             [string]
      --etherscan   Etherscan provider API key                          [string]

Like most relational database systems, Tableland requires the user to create tables for storing, querying, and relating data. See the JavaScript SDK docs for details on create requirements.

The response from a create statement includes the created table name, which the caller can use to make subsequent queries and updates:

{
  "tableId": "237",
  "prefix": "cli_demo_table",
  "chainId": 5,
  "txnHash": "0xf14684b9ae8d4063768883f486ff5f8996393d888c63265865b6862eb34e899e",
  "blockNumber": 7291011,
  "name": "cli_demo_table_5_237"
}

Since creating a table on requires a smart contract interactions, it is recommended to provide an API key to a remote EVM provider API when using the create command. This avoids rate limiting by ethers. For context, Tableland mints a TABLE (ERC721) token using the Tableland smart contract, which is the on-chain representation of every table then created in Tableland. As noted, we currently support Alchemy, Infura, and Etherscan. Simply specify your desired API provider key (or provide all three), and create a table.

write

Run a mutating SQL statement against a remote table

tableland write <statement>

Positionals:
  statement  SQL write statement                             [string] [required]

Options:
      --help        Show help                                          [boolean]
  -k, --privateKey  Private key string                                  [string]
      --chain       The EVM compatible chain to target
                                           [string] [default: "ethereum-goerli"]

The write command allows for vanilla SQL INSERT, UPDATE, and DELETE statements. One key aspect to keep in mind when working with tables is that you must specify the table name that you get back from the create command.

Using the write command will return a value of the blockchain’s transaction hash:

{
  "hash": "0x4f655a84c69766c80faa2e9f6e712f14a244cddc59886b8d43c9894c07c91871"
}

read

Run a read-only query against a remote table

tableland read <query>

Positionals:
  query  SQL query statement                                 [string] [required]

Options:
      --help        Show help                                          [boolean]
      --format      Output format. One of 'raw', 'tabular', or 'objects'.
                                                       [string] [default: "raw"]
      --chain       The EVM compatible chain to target
                                           [string] [default: "ethereum-goerli"]

It is also easy to use vanilla SQL SELECT statements to query the whole table! See the JavaScript SDK docs for further details. As with the write command, you must specify the table name returned from the create command.

This results in a response in the following format, with columns & rows and their specified values:

{
  "columns": [
    {
      "name": "id"
    },
    {
      "name": "name"
    }
  ],
  "rows": [
    [
      0,
      "Bobby Tables"
    ]
  ]
}

list

List tables by address

tableland list [address]

Positionals:
  address  The target address                                          [string]

Options:
      --help        Show help                                          [boolean]
  -k, --privateKey  Private key string                       [string] [required]
      --chain       The EVM compatible chain to target
                                           [string] [default: "ethereum-goerli"]

Using list command provides and easy way to understand which tables are owned by a certain address.

The retrieved information about those tables includes the table’s name, structure (describing the schema), and created time:

[
  {
    "controller": "0x4D5286d81317E284Cd377cB98b478552Bbe641ae",
    "name": "cli_demo_table_5_237",
    "structure": "466dc130f3b02cf995fb66f6a0bdbadc49d2a527c26ac328daddc3f7b8ef779c",
    "created_at": "2022-07-26T00:41:43Z"
  }
]

info

Get metadata info about a given table by name

tableland info <name>

Positionals:
  name  The target table name                                 [string] [required]

Options:
      --help        Show help                                          [boolean]
      --chain       The EVM compatible chain to target
                                           [string] [default: "ethereum-goerli"]

The response includes table information in a standard ERC721 metadata format, including the table’s name, creation data, external URL (which points to the Tableland gateway), and attributes:

{
  "name": "cli_demo_table_5_237",
  "external_url": "https://testnet.tableland.network/chain/5/tables/237",
  "image": "https://bafkreifhuhrjhzbj4onqgbrmhpysk2mop2jimvdvfut6taiyzt2yqzt43a.ipfs.dweb.link",
  "attributes": [
    {
      "display_type": "date",
      "trait_type": "created",
      "value": 1658796103
    }
  ]
}

receipt

Get the receipt of a chain transaction to know if it was executed, and the execution details

tableland receipt <hash>

Positionals:
  hash  Transaction hash                                     [string] [required]

Options:
      --help        Show help                                          [boolean]
  -k, --privateKey  Private key string                       [string] [required]
      --chain       The EVM compatible chain to target
                                           [string] [default: "ethereum-goerli"]

This allows you to retrieve data like the chain id, block number, and table Id — and note that the transaction hash itself is returned when running the create and write commands:

{
  "chainId": 5,
  "txnHash": "0xf14684b9ae8d4063768883f486ff5f8996393d888c63265865b6862eb34e899e",
  "blockNumber": 7291011,
  "tableId": "237"
}

token

Create a signed SIWE token

tableland token

Options:
      --help        Show help                                          [boolean]
  -k, --privateKey  Private key string                       [string] [required]
      --chain       The EVM compatible chain to target
                                           [string] [default: "ethereum-goerli"]

Use the CLI to generate a self-signed SIWE Token. This is done automatically when using the JavaScript SDK via a browser app (thanks to MetaMask). For creating one via the command line, use this command.

Namely, with the token command, the response will simply be a long string: the SIWE token.

⚠️
It may be useful to create a local environment variable to avoid pasting the private key string (and SIWE token) in all CLI commands. Be sure to not prefix this with TBL_ as this may cause unwanted errors with the CLI. Using the TBL_ prefix for an environment variable causes the CLI to look for & apply the variable to the command. So, for any commands that do not take a --token flag, using something like TBL_TOKEN would cause the command to apply the flag, leading to an error.

chains

List information about supported chains

tableland chains

Options:
      --help        Show help                                          [boolean]

Use the chains command to retrieve which chains Tableland is deployed on, which returns information about the deployment itself, including chain name, id, and contract address:

{
  "ethereum-goerli": {
    "name": "goerli",
    "phrase": "Ethereum Goerli",
    "chainId": 5,
    "contract": "0xDA8EA22d092307874f30A1F277D1388dca0BA97a"
  },
	...
}

Example

The following is a simple connect, create, mutate, and query workflow to demonstrate interacting with Tableland from the command line.

Start with the install:

npm i -g @tableland/cli

Next, we'll create some env vars to use when interacting with the CLI:

export TBL_ALCHEMY=fblahblah-Osomethingd0MeXJ
export TBL_PRIVATE_KEY=myhexstringprivatekeystringthatissecret

Creating a table is generally the first thing we'll do, so let's start with something simple (note that this is an on-chain event, and so it can take a while to complete):

tableland create "id int, name text, primary key (id)" "cli_demo_table"
{
  "tableId": "237",
  "prefix": "cli_demo_table",
  "chainId": 5,
  "txnHash": "0xf14684b9ae8d4063768883f486ff5f8996393d888c63265865b6862eb34e899e",
  "blockNumber": 7291011,
  "name": "cli_demo_table_5_237"
}

Optionally, you could then check the table creation details using the receipt command — use the txnHash returned from your own create response (or, you can use the example below):

tableland receipt 0xf14684b9ae8d4063768883f486ff5f8996393d888c63265865b6862eb34e899e
{
  "chainId": 5,
  "txnHash": "0xf14684b9ae8d4063768883f486ff5f8996393d888c63265865b6862eb34e899e",
  "blockNumber": 7291011,
  "tableId": "237"
}

In case we forgot to note the table’s name, we can list our owned tables and retrieve it.

tableland list
[
  {
    "controller": "0x4D5286d81317E284Cd377cB98b478552Bbe641ae",
    "name": "cli_demo_table_5_237",
    "structure": "466dc130f3b02cf995fb66f6a0bdbadc49d2a527c26ac328daddc3f7b8ef779c",
    "created_at": "2022-07-26T00:41:43Z"
  }
]
🚧
Note: if you run into any issues, you might need to unset your previous TBL_ prefixed environment variable.

Make note of the tableId of the previously created table from the create, receipt, or list statements — here, we had 35, but it’s unique to every created table. We'll use that id in the next command to retrieve metadata about the table:

tableland info 35
{
  "name": "cli_demo_table_5_237",
  "external_url": "https://testnet.tableland.network/chain/5/tables/237",
  "image": "https://bafkreifhuhrjhzbj4onqgbrmhpysk2mop2jimvdvfut6taiyzt2yqzt43a.ipfs.dweb.link",
  "attributes": [
    {
      "display_type": "date",
      "trait_type": "created",
      "value": 1658796103
    }
  ]
}

Ok, now we're going to create a SIWE token. Recall that we specified our privateKey argument previously (as TBL_PRIVATE_KEY), which is what will be used to sign the SIWE token:

tableland token
# returns a long string
# "eyJtZXNz..."

If you want to hang on to the token value itself from the above command, export it for future ease of use (e.g., used in JSON RPC calls). Recall to not prefix this with TBL_ as this may cause unwanted errors while using commands that don’t take the --token flag.

export SIWE_TOKEN=$(tableland token | cut -d '"' -f2)

Finally, we can start mutating and querying our table using the write and read commands. We'll start with a basic INSERT and then SELECT to showcase that it really is working. Note that the if you used the create command to generate your own table, the appended tokenId will be unique to your table and differ from the value shown below (237):

tableland write "INSERT INTO cli_demo_table_5_237 VALUES (0, 'Bobby Tables');"

The returned value is the blockchain transaction hash (check it out on Etherscan, here):

{
  "hash": "0x4f655a84c69766c80faa2e9f6e712f14a244cddc59886b8d43c9894c07c91871"
}

Ok, let's see if little Bobby Tables made it into our table on Tableland:

tableland read "SELECT * FROM cli_demo_table_5_237;"

Regardless of who owns the table (or has the proper mutating permissions), anyone can actually read from any table. The read commands will return something like:

{
  "columns": [
    {
      "name": "id"
    },
    {
      "name": "name"
    }
  ],
  "rows": [
    [
      0,
      "Bobby Tables"
    ]
  ]
}

You should now see some a JSON output with columns and rows data. Using a tool like jq, you can pipe the JSON response to pull out what is needed:

tableland read "select * from cli_demo_table_5_237" | jq '.rows'
[
  [
    0,
    "Bobby Tables"
  ]
]

And that covers the basics of creating a table and mutating/reading from it — continue onward to learn more!

← Previous

Next →