Skip to main content

Get started

Dive into SQL and start using it to build web3 applications.

SQL offers a way to interact with databases for extracting and mutating structured data. It is a domain-specific language that allows you to build scalable applications through relational data through organizing data into a table row and column structure. It is a rather simple yet powerful syntax.

Usage​

When you write SQL statements, they follow a format that loosely resembles that of the following in written / spoken language. Note that SQL syntax does not require all capital letters and can use lowercase (create table is the same as CREATE TABLE, int the same as INT, etc.).

Tableland currently supports the following SQL clauses:

  • CREATE TABLE: Create a table with a series of types aligned to columns and constraints.
  • INSERT: Insert data into a table's columns with certain values (with "upsert" support, too).
  • UPDATE: Update a table's values where some condition is met.
  • DELETE: Delete a value from a table where some condition is met.
  • SELECT: Select a set of data from a table(s), along with conditions.

To access the database, Tableland clients like the SDK, smart contracts, and CLI can be used to create and mutate table data. These also use the Gateway REST API to actually read data directly from the Tableland network by directly writing SELECT statement against it.

Data types​

Not all "typical" SQL data types are supported since blockchains and distributed systems can lead to non-deterministic state. The following defines what can be used in column definitions:

TypeDescription
INTSigned integer values, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
INTEGERSame as INT, except it may also be used to represent an auto-incrementing PRIMARY KEY field.
TEXTText string, stored using the database encoding (UTF-8).
BLOBA blob of data, stored exactly as it was input. Useful for byte slices etc.

Note that a common pattern for dates / times is to use INT, INTEGER, or TEXT, depending on how you design your application. For booleans, use an INT or INTEGER and a 0 or 1 value.

Table names​

Every table created will have a deterministically unique table name in the format {prefix}_{chainId}_{tableId}. If you're using clients or helpers built on top of the protocol, the chainId and tableId should automatically appended after a table is created. So, when you form create statements, they generally should only need the first segment of the table name string, {prefix}, which is also optional. If you use a prefix, it must start with a letter, followed by any combination of (zero or more) letters, numbers, and/or underscores. If you choose to forego a prefix, be sure that the "prefix"—an empty string—is wrapped in double quotes (e.g., CREATE TABLE "" (...)) for SQL parsing purposes.

Keep in mind that lower level interactions, like smart contract calls without a Tableland helper library, might expect a table name in the format {prefix}_{chainId}—it's something to keep in mind if you run into table name issues.

Callouts​

Tableland doesn't have full SQLite parity. Let's review a TL;DR of what to look out for:

  • Table names in queries should use the format {prefix}_{chainId}_{tableId}, but a create statement should not have the _{tableId} portion since this ID autogenerated.
  • There is a set of reserved keywords to be aware of.
  • Foreign keys constraints are not supported.
  • Some common SQL languages have type support for numbers with decimals (floats/reals), dates/times, and booleans, but Tableland does not support any of these (see the data type docs).
  • Subqueries ("nesting" SELECT statements within a query) are fully supported in read queries but limited to flattened subqueries or GROUP BY statements (with HAVING support) for inserts; they cannot be used in updates nor deletes.
  • Mutating queries (inserts, updates, deletes) can touch one or more tables in a single transaction, but they cannot touch multiple tables in a single string of statements.
  • Be sure to wrap any TEXT in a single quotes ('), including hexadecimals numbers (e.g., EVM account addresses).
  • Custom SQL functions (TXN_HASH() and BLOCK_NUM()) are available for accessing blockchain-related transaction hash and block numbers associated with a SQL query.
  • Not all of the SQLite functions are supported, and be aware that unsupported types impact their usage as well.