Get started
Get up an running with smart contracts and Tableland.
Tableland is live on a number of EVM chains and allows you to write SQL directly onchain. If you're looking for what the addresses of these contracts are, head over to the deployed contracts section of the docs.
Is it possible to read data in a smart contract? Short answer: no. Table reads are entirely offchain and interact directly with a Tableland validator node. A smart contract can only create tables or write to them. In order to read table data in a smart contract, you would have to implement custom offchain logic or use oracles to then write the offchain materialized data back onchain.
If you need to read data in the contract, it's best to store it in a mapping or other data structure while also writing to a Tableland table.
Installation
Install evm-tableland
.
- npm
- Yarn
- pnpm
npm install --save @tableland/evm
yarn add @tableland/evm
pnpm add @tableland/evm
We'll use the TablelandDeployments
and SQLHelpers
contracts to help us interact with the registry contract and form SQL statements, respectively.
OpenZeppelin has a useful Strings
library that makes it easier to work with variables in string templating (i.e., a SQL statement is passed to the registry as a string). So, it's best to also import @openzeppelin/contracts
and use Strings
.
- npm
- Yarn
- pnpm
npm install --save @openzeppelin/contracts
yarn add @openzeppelin/contracts
pnpm add @openzeppelin/contracts
Thus, your imports should resemble the following:
import {Strings} from "@openzeppelin/contracts/utils/Strings.sol";
import {TablelandDeployments} from "@tableland/evm/contracts/utils/TablelandDeployments.sol";
import {SQLHelpers} from "@tableland/evm/contracts/utils/SQLHelpers.sol";
It's prudent to store a reference to your table ID and prefix within your contract; being unable to refer to them within various methods makes things more difficult.
uint256 public _tableId;
string private constant _TABLE_PREFIX = "my_table";
Create a table
Creating a table (calling create()
on the Tableland registry) requires the following parameters:
owner
: Theaddress
that should own the table (i.e., ERC721 TABLE token is minted here).statement
: Astring
that defines aCREATE TABLE
statement.
You can use the SQLHelpers
's toCreateFromSchema()
method to generate a CREATE TABLE
statement for you:
schema
: A singlestring
for the table's schema definition (e.g.,"id integer primary key, name text"
).prefix
: Astring
that's the table's custom prefix.
Note that in the statement
, the chain ID is expected as part of the definition, so if you choose to write a raw SQL statement without SQLHelpers
, the statement should use CREATE TABLE my_table_<chain_id> ...
. That is, the <chain_id>
should be replaced entirely with the chain ID number (e.g., my_table_1
for Ethereum mainnet). It's recommended to always use the SQLHelpers
library to avoid any issues.
With TablelandDeployments
, it makes it easy to set up an interface with the correct TablelandTables
deployed contract address with the get()
method.
Table owned by msg.sender
For example, place the following in your constructor
method to create a table with the combination of these two libraries. This will create a table that's owned by the contract's owner, i.e., msg.sender
upon deployment.
constructor() {
_tableId = TablelandDeployments.get().create(
msg.sender,
SQLHelpers.toCreateFromSchema(
"id integer primary key," // Notice the trailing comma
"val text", // Separate lines for readability—but it's a single string
_TABLE_PREFIX
)
);
}
Contract table ownership
Ideally, we should have our contract own the table so that it can execute SQL statements directly from methods. For the contract to own the table (instead of some EOA), the contract must be able to own an ERC721 token. We'll need to import ERC721Holder
from OpenZeppelin—more on this in the docs here. If you were to, instead, mint to msg.sender
(as in the example above), the contract wouldn't have the default permissions to also write to the table; contract ownership helps solve this issue.
Then, you can then use address(this)
within the create()
and other registry method, which will send the ERC721 TABLE to the contract itself. Alternatively, you could implement onERC721Received
on your own vs. inheriting from ERC721Holder
.
// Existing imports
// Needed if the contract must own the table
import "@openzeppelin/contracts/token/ERC721/utils/ERC721Holder.sol";
contract Example is ERC721Holder {
// Existing code
}
Yu can now use address(this)
in your function.
constructor() {
_tableId = TablelandDeployments.get().create(
address(this),
SQLHelpers.toCreateFromSchema(
"id integer primary key," // Notice the trailing comma
"val text", // Separate lines for readability—but it's a single string
_TABLE_PREFIX
)
);
}
Additionally, developers can choose to set up and configure their own controller contract. In the contract-owned table setup, the contract acts as a "forwarder" for any method calls by an external account, i.e., the msg.sender
—we'll set this up below. But, if you want more flexible controls where someone could bypass the your contract and directly hit the Tableland registry, a controller allows this to be flexible and, for example, allow both the contract's owner and the contract itself to have "allow all" admin permissions.
Write table data
You can insert, update, or delete data using TablelandDeployments.get().mutate()
. The mutate
method takes the following:
caller
: Theaddress
of what is calling the registry contract.tableId
: Uniqueuint256
ID of the Tableland table.statement
: Astring
for the SQLINSERT
statement.
Be sure to always wrap strings (i.e., if a table's column has a text
type) in single quotes when writing SQL statements! The SQLHelpers.quote()
method makes this easy by taking a string
and returning it with '
wrapped around it.
Insert values
The SQLHelpers
contract has various method to help format the mutate
's input properly. For example, toInsert()
expects the following:
prefix
: Astring
that's the table's custom prefix.tableId
: Uniqueuint256
ID of the Tableland table.columns
: Astring
encoded ordered list of columns that will be updated (e.g.,"name, val"
)values
: Astring
encoded ordered list of values that will be inserted (e.g.,"'jerry', 24"
).
This will produce an INSERT
statement with its VALUES
.
// Insert data into a table
function insertIntoTable(uint256 id, string memory val) external {
/* Under the hood, SQL helpers formulates:
*
* INSERT INTO {prefix}_{chainId}_{tableId} (id,val) VALUES(
* <id>,
* '<val>'
* );
*/
TablelandDeployments.get().mutate(
address(this), // Table owner, i.e., this contract
_tableId,
SQLHelpers.toInsert(
_TABLE_PREFIX,
_tableId,
"id,val",
string.concat(
Strings.toString(id), // Convert to a string
",",
SQLHelpers.quote(val) // Wrap strings in single quotes with the `quote` method
)
)
);
};
For strings, the Strings.toString()
can be used for numbers. Although we're not using it here, the Strings.toHexString()
method converts a hexadecimal value (e.g., an address
) to a normal string.
Update values
If you want to update table values, it technically goes through the same mutate
method in the TablelandTables
registry smart contract, but a different SQLHelpers
method is used—toUpdate()
—which takes:
prefix
: Astring
that's the table's custom prefix.tableId
: Uniqueuint256
ID of the Tableland table.setters
: Astring
encoded set of updates (e.g.,"name='tom', age=26"
).filters
: Astring
encoded list of filters (e.g.,"id<2 and name!='jerry'"
) or""
for no filters.
This will produce an UPDATE
statement with a SET
and WHERE
clause.
// Update data in the table
function updateTable(uint256 id, string memory val) external {
/* Under the hood, SQL helpers formulates:
*
* UPDATE {prefix}_{chainId}_{tableId} SET val=<val> WHERE id=<id>
*/
// Set the values to update
string memory setters = string.concat("val=", SQLHelpers.quote(val));
// Specify filters for which row to update
string memory filters = string.concat(
"id=",
Strings.toString(id)
);
// Mutate a row at `address` with a new `val`
TablelandDeployments.get().mutate(
address(this),
_tableId,
SQLHelpers.toUpdate(_TABLE_PREFIX, _tableId, setters, filters)
);
}
Delete data
Lastly, you can delete table data with the same mutate
method but a different one from SQLHelpers
. Use toDelete()
to perform this action, which takes:
prefix
: Astring
that's the table's custom prefix.tableId
: Uniqueuint256
ID of the Tableland table.filters
: A string encoded list of filters (e.g.,"id<2 and name!='jerry'"
).
This will produce a DELETE FROM
statement with an attached WHERE
clause
// Delete a row from the table by ID
function deleteFromTable(uint256 id) external {
/* Under the hood, SQL helpers formulates:
*
* DELETE FROM {prefix}_{chainId}_{tableId} WHERE id=<id>
*/
// Specify filters for which row to delete
string memory filters = string.concat(
"id=",
Strings.toString(id)
);
// Mutate a row at `id`
TablelandDeployments.get().mutate(
address(this),
_tableId,
SQLHelpers.toDelete(_TABLE_PREFIX, _tableId, filters)
);
}
Setting access control
Lastly, here's an example of how you could have the contract set an access controller for the table. This assumes that you've already deployed a controller contract and have its address, which will change who and how accounts can mutate data.
// Set the ACL controller to enable row-level writes with dynamic policies
function setAccessControl(address controller) external {
TablelandDeployments.get().setController(
address(this), // Table owner, i.e., this contract
_tableId,
controller // Set the controller address—a separate controller contract
);
}
Full example
Below combines all of the steps above into a single contract. Note that the contract is the owner of the table, so it can execute SQL statements directly from methods. A couple of helpers for getting the table name and ID are also included.
// SPDX-License-Identifier: MIT
pragma solidity >=0.8.10 <0.9.0;
import {Strings} from "@openzeppelin/contracts/utils/Strings.sol";
import {ERC721Holder} from "@openzeppelin/contracts/token/ERC721/utils/ERC721Holder.sol";
import {TablelandDeployments} from "@tableland/evm/contracts/utils/TablelandDeployments.sol";
import {SQLHelpers} from "@tableland/evm/contracts/utils/SQLHelpers.sol";
contract Example is ERC721Holder {
// Store relevant table info
uint256 private _tableId; // Unique table ID
string private constant _TABLE_PREFIX = "my_table"; // Custom table prefix
// Constructor that creates a simple table with a`val` column
constructor() {
// Create a table
_tableId = TablelandDeployments.get().create(
address(this),
SQLHelpers.toCreateFromSchema(
"id integer primary key,"
"val text",
_TABLE_PREFIX
)
);
}
// Let anyone insert into the table
function insertIntoTable(uint256 id, string memory val) external {
TablelandDeployments.get().mutate(
address(this), // Table owner, i.e., this contract
_tableId,
SQLHelpers.toInsert(
_TABLE_PREFIX,
_tableId,
"id,val",
string.concat(
Strings.toString(id), // Convert to a string
",",
SQLHelpers.quote(val) // Wrap strings in single quotes with the `quote` method
)
)
);
}
// Update only the row that the caller inserted
function updateTable(uint256 id, string memory val) external {
// Set the values to update
string memory setters = string.concat("val=", SQLHelpers.quote(val));
// Specify filters for which row to update
string memory filters = string.concat(
"id=",
Strings.toString(id)
);
// Mutate a row at `id` with a new `val`
TablelandDeployments.get().mutate(
address(this),
_tableId,
SQLHelpers.toUpdate(_TABLE_PREFIX, _tableId, setters, filters)
);
}
// Delete a row from the table by ID
function deleteFromTable(uint256 id) external {
// Specify filters for which row to delete
string memory filters = string.concat(
"id=",
Strings.toString(id)
);
// Mutate a row at `id`
TablelandDeployments.get().mutate(
address(this),
_tableId,
SQLHelpers.toDelete(_TABLE_PREFIX, _tableId, filters)
);
}
// Set the ACL controller to enable row-level writes with dynamic policies
function setAccessControl(address controller) external {
TablelandDeployments.get().setController(
address(this), // Table owner, i.e., this contract
_tableId,
controller // Set the controller address—a separate controller contract
);
}
// Return the table ID
function getTableId() external view returns (uint256) {
return _tableId;
}
// Return the table name
function getTableName() external view returns (string memory) {
return SQLHelpers.toNameFromId(_TABLE_PREFIX, _tableId);
}
}