The @tableland/sdk (v4) library provides a minimal client and SDK that implements the Cloudflare D1Database interface on top of the Tableland network. It can be used as a drop-in replacement to work with many community-created D1 tools and libraries. It also comes with a set of helper utilities for working with Tableland.

The SDK comes with a few APIs: a “primary” Cloudflare D1 client compatible database API, and two additional "secondary" APIs (Validator, Registry) that are unique to Tableland operations. Generally, users will leverage the primary database API for typical SQL operations, and the secondary APIs are useful when needing to interact directly with a Tableland Validator node or directly calling Tableland’s smart contracts (on-chain access control, direct contract calls, etc.).

See the Quick Start page if you’re looking to get up and running without all of the details Quick Start

Installation

From the command line, cd to the project’s directory and install the SDK via npm:

npm install --save @tableland/sdk@next

Or yarn:

yarn add @tableland/sdk@next

Table name format

Recall that table names ({prefix}_{chainId}_{tableId}) are comprised of three components:

prefix ⇒ Defined by the developer at the time of table creation.
chainId ⇒ Identifier assigned to the table under the hood, based on the chain the table was created on.
tableId ⇒ Unique identifier for a table, assigned by the Tableland “registry” contract upon table creation (a table is minted as an ERC721 token).

Chains

Note the chain naming convention (e.g., maticmum) passed into the Database connection matches that of ethersjs. All supported chains can be found here. To dictate which chain your app should connect to, you can leverage singers.

Signing transactions

The default behavior for database connections prompt a wallet to connect via the browser. If no additional logic is implemented, the connection will leverage whatever the browser connects to. In other words, you must restrict which chains the Database can connect through by creating a signer, or the app will leverage whatever the browser wallet has specified as the chain.

Third party libraries, like ethersjs and wagmi, are useful to create a signer and restrict which chains the app is using. These libraries may also be useful if passing an account private key stored locally in a .env file from a Node app.

See the Signers section for more details.

Database API

All table creates, writes, and reads fall under a single method: prepare. Start by creating an instance of a Database and then pass SQL, such as CREATE TABLE, INSERT INTO, UPDATE, DELETE, and SELECT statements.

Let’s start with a simple table read query. Every chain comes with a “healthbot” table that can be queried, which has a single counter column with integer value. For example, on Polygon Mumbai, this table is healthbot_80001_1.

Reads

Start by importing the Database and establishing a read-only connection. This allows developers to bypass any wallet connection since table reads are not an on-chain operation. You’ll notice the all method (and run in the example below) is chained to the statement—more details are provided in the query statement methods section.

import { Database } from "@tableland/sdk";

const tableName = "healthbot_80001_1"; // Our pre-defined health check table

const db = new Database();

const { results } = await db.prepare(`SELECT * FROM ${tableName};`).all();
console.log(results);

Creates

Once you are ready to create a table, you can follow rather standard SQL convention. You’ll first import the Database class, create a new instance, and then pass a CREATE TABLE statement to prepare. For readability purposes, a prefix variable is created and passed using string templating within the prepare method.

When connecting to the Database, the default will connect to Polygon Mumbai and use a browser connection (e.g., MetaMask prompt). For context, the run method returns metrics about the query, such as transaction information.

import { Database } from "@tableland/sdk";

// Default to grabbing a wallet connection in a browser
const db = new Database()

// This is the table's `prefix`--a custom table value prefixed as part of the table's name
const prefix = "my_sdk_table";

const { meta: create } = await db
	.prepare(`CREATE TABLE ${prefix} (id integer primary key, name text);`)
	.run();

// The table's `name` is in the format `{prefix}_{chainId}_{tableId}`
console.log(create.txn.name); // e.g., my_sdk_table_80001_311

At this point, the table exists, but it has no data.

Note is possible to create a table without a prefix value. In this case, do be careful with string interpolation. You’ll want to wrap the prefix passed to the prepare method in double quotes:

const prefix = "" // An empty string is a valid prefix, but make sure the CREATE TABLE statement sees it!
const { meta: create } = await db
	.prepare(`CREATE TABLE "${prefix}" (id integer primary key, name text);`)
	.run();

Double quotes around any table name is valid SQL in a CREATE statement is valid SQL.

Writes

The SDK allows for parameter binding to help simplify writing queries (includes both mutating and read queries). The ? is an anonymous parameter, which replaces the values in the statement with those in bind. Let’s extend the example for creating a table.

// Insert a row into the table
const { meta: insert } = await db
	.prepare(`INSERT INTO ${name} (id, name) VALUES (?, ?);`)
	.bind(0, "Bobby Tables")
	.run();

// Wait for transaction finality
await insert.txn.wait();

// Perform a read query, requesting all rows from the table
const { results } = await db.prepare(`SELECT * FROM ${name};`).all();

When a table is written to, it includes two steps:

On-chain interaction—this is what the wait method is waiting for (i.e., transaction finality).
Off-chain materialization—once wait is fulfilled, the mutating SQL query will have been materialized by the Tableland network and is now readable with the SELECT statement.

Prepared statements

The Tableland SDK comes with a Database API that supports both static and prepared statements. A static statement is typical SQL where the values are part of the statement; prepared statement will bind values to the statement as part of a separate method.

const preparedStmt = db.prepare("SELECT * FROM users WHERE name = ?1").bind("Bobby Tables");
const staticStmt = db.prepare('SELECT * FROM users WHERE name = "Bobby Tables"');

Parameter binding

Tableland follows the SQLite convention for prepared statement parameter binding. This includes Ordered (?NNNN) and Anonymous (?) parameters, as well as all three forms of Named parameters. To bind a parameter, simply use the method stmt.bind(). This only works for parameters; a table’s name cannot be passed and bound as a parameter (e.g., SELECT * FROM ? ...).

Syntax	Type	Description
`?NNN`	Ordered	A question mark followed by a number NNN holds a spot for the NNN-th parameter.
`?`	Anonymous	A question mark that is not followed by a number creates a parameter with a number one greater than the largest parameter number already assigned. This parameter format is provided for compatibility with other database engines. But because it is easy to miscount the question marks, the use of this parameter format is discouraged. Programmers are encouraged to use one of the symbolic formats below or the `?NNN` format above instead
`:AAAA`	Named	A colon followed by an identifier name holds a spot for a named parameter with the name :AAAA. To avoid confusion, it is best to avoid mixing named and numbered parameters.
`@AAAA`	Named	An "at" sign works exactly like a colon, except that the name of the parameter created is @AAAA.
`$AAAA`	Named	A dollar-sign followed by an identifier name also holds a spot for a named parameter with the name $AAAA.

With anonymous binding, the order in which values are passed is the order assigned within the query. The ordered style allows you to specify a number that corresponds to position of the parameter within the statement, starting at 1.

const stmt = db
  .prepare("SELECT * FROM users WHERE name = ? AND age = ?")
  .bind("John Doe", 41);

const stmt = db
  .prepare("SELECT * FROM users WHERE name = ?2 AND age = ?1")
  .bind(41, "John Doe");

More complex binding is possible using named parameters and complex datatypes, which are converted to basic types on the fly. Named parameters are prefixed with @, :, or $, and it is best to not mix named and numbered parameters, as it can get a bit confusing.

const stmt = db
  .prepare(
    "INSERT INTO people VALUES (@first, ?, :first, ?, ?4, ?3, ?, $last);"
  )
  .bind(
    45,
    { first: "Bobby", last: "Tables" },
    [54, true, Uint8Array.from([1, 2, 3])],
    null
  );

Reusing prepared statements

Prepared statements can be reused with new bindings; there is no need to redefine the prepared statement:

const stmt = db.prepare("SELECT name, age FROM users WHERE age < ?1");
const young = await stmt.bind(20).all();
console.log(young);
/*
{
  results: [...],
  success: true
  meta: {
    duration: 31,
  }
}
*/
const old = await stmt.bind(80).all();
console.log(old);
/*
{
  results: [...],
  success: true
  meta: {
    duration: 29,
  }
}
*/

Return object

In the example above, the returned object from the prepare method is assigned to the stmt variable. Using the methods stmt.run(), stmt.all() and db.batch() will return an object that contains the results (if applicable), the success status, and a meta object with the internal duration of the operation in milliseconds, and any transaction information available.

{
  results: [], // may be empty
  success: boolean, // true if the operation was successful
  error?: string,
  meta: {
    duration: number, // duration of operation in milliseconds
    txn?: {
        chainId: number,
        tableId: string,
        transactionHash: string,
        blockNumber: number,
        error?: string,
        name?: string
        wait(): Promise<{ ... }>
    }
  }
}

Recall that the Database API is compatible with Cloudflare is useful—because it matches the D1Database object, allowing any tools that are compatible with this object to also be compatiable with Tableland’s object as well.

Query statement methods

When calling the the Database API’s prepare method, it returns an object that supports a variety of query statement methods. Each one is asynchronous and should be called upon that response object—for example, with the definitions above, the stmt variable was used.

const tableName = `healthbot_80001_1`;
// Define the `Database` reponse object
const stmt = db.prepare(`SELECT * FROM ${tableName};`);
// Call a query statement method
await stmt.all();

The following defines the full set of possible methods in the scenario above:

await stmt.first( [column] )
await stmt.all( [column] )
await stmt.raw()
await stmt.run()

There also exists one method for the Database, which should only be used for maintenance and one-shot tasks:

await db.exec()

`await stmt.first([column])`

Returns the first row of the results. This does not return metadata like the other methods. Instead, it returns the object directly.

Get a specific column from the first row:

const stmt = db.prepare("SELECT COUNT(*) AS total FROM users");
const total = await stmt.first("total");
console.log(total); // 50

Get all the the columns from the first row:

const stmt = db.prepare("SELECT COUNT(*) AS total FROM users");
const values = await stmt.first();
console.log(values); // { total: 50 }

If the query returns no rows, then first() will return null.

If the query returns rows, but column does not exist, then first() will throw an exception.

`await stmt.all( [column] )`

Returns all rows and metadata. Optionally, a column can be specified.

const stmt = db.prepare("SELECT name, age FROM users LIMIT 3");
const { results } = await stmt.all();
console.log(results);
/*
[
  {
     name: "John",
     age: 42,
  },
   {
     name: "Anthony",
     age: 37,
  },
    {
     name: "Dave",
     age: 29,
  },
 ]
*/

`await stmt.raw()`

Returns all rows and metadata. Optionally, a column can be specified. This has the same functionality as all() but returns an array of rows instead of objects.

const stmt = db.prepare("SELECT name, age FROM users LIMIT 3");
const raw = await stmt.raw();
console.log(raw);
/*
[
  [ "John", 42 ],
  [ "Anthony", 37 ],
  [ "Dave", 29 ],
]
*/

`await stmt.run()`

Runs the query but returns no results. Instead, run() returns the metrics only. Useful for write operations like UPDATE, DELETE or INSERT.

const info = await db
  .prepare("INSERT INTO users (name, age) VALUES (?1, ?2)")
  .bind("John", 42)
  .run();
console.log(info);
/*
{
  success: true
  meta: {
    duration: 366.55073300004005,
    txn: {
        tableId: '5',
        transactionHash: '0x050b60bfec948c82f81528d60b3189cc00bd967b3ffcf5ac253a6a103bd2c3b7',
        blockNumber: 7710,
        chainId: 31337,
        wait: [AsyncFunction: wait],
        name: 'test_run_31337_5'
    }
  }
}
*/

On Tableland, mutating transactions such as INSERT, DELETE, and UPDATE produce a two-phase transaction. Firstly, the transaction is sent to the registry contract, and awaited. The returned txn information also contains a wait method that can be used to await finalization on the Tableland network. This method will also throw an exception if any runtime errors occur.

 const { transactionHash } = await info.txn.wait();
 console.log(transactionHash);
 /*
 0x050b60bfec948c82f81528d60b3189cc00bd967b3ffcf5ac253a6a103bd2c3b7
 */

The Database may also be run in autoWait mode, such that each mutating call will not resolve until it has finalized on the Tableland network. This is useful when working with D1 compatible libraries, or to avoid issues with nonce-reuse etc.

Additionally, all async method calls take an optional AbortSignal object, which may be used to cancel or otherwise abort an inflight query. Note that this will only abort queries (including wait status), not the actual mutation transaction itself.

const controller = new AbortController();
const signal = controller.signal;
const stmt = db.prepare("SELECT name, age FROM users WHERE age < ?1");
setTimeout(() => controller.abort(), 10);
const young = await stmt.bind(20).all({ signal });
/*
Error: The operation was aborted.
*/

`await db.exec()`

Executes one or more queries directly without prepared statements or parameters binding. This method can have poorer performance (prepared statements can be reused in some cases) and, more importantly, is less safe. Only use this method for maintenance and one-shot tasks (e.g., migration jobs). The input can be one or multiple queries separated by the standard ;. If an error occurs, an exception is thrown with the query and error messages (see below for Errors).

Currently, the entire string of statements is submitted as a single transaction. In the future, more "intelligent" transaction planning, splitting, and batching may be used.

const migration = await fetch("/migration.sql");
const out = await db.exec(migration.text());
console.log(out);
/*
{
  count: 5,
  duration: 76,
  ...
}
*/

Batch statements

Batching sends multiple SQL statements inside a single call to the network. This can have a huge performance impact as it reduces latency from network round trips to Tableland. This implementation guarantees that each statement in the list will execute and commit, sequentially, non-concurrently.

You cannot use this method across multiple tables. When batching mutating queries, they must only correspond to a single table.

Batched statements are similar to SQL transactions. If a statement in the sequence fails, then an error is returned for that specific statement, and it aborts or rolls back the entire sequence.

`db.batch()`

To send batch statements, you must feed batch() with a list of prepared statements and get back the results.

await db.batch([
  db.prepare("UPDATE users SET name = ?1 WHERE id = ?2").bind("John", 17),
  db.prepare("UPDATE users SET age = ?1 WHERE id = ?2").bind(35, 19),
]);

You can construct batches reusing the same prepared statement.

Signers

The default behavior when creating a Database instance is to trigger a browser wallet connection. Instead, you can also use third party libraries to handle this.

`ethersjs`

Similar to the default behavior, you can pass a custom signer to the Database by retrieving signer information from the browser.

import { Database } from "@tableland/sdk";
import { providers } from "ethers";

// Connect to provider from browser and get accounts
const provider = new providers.Web3Provider(window.ethereum);
await provider.send("eth_requestAccounts", []);

// Pass the signer to the Database
const signer = provider.getSigner();
const db = new Database({ signer });

Within a Node app, you might instantiate a Database using dotenv and a PRIVATE_KEY stored and read from .env file. The provider URL will point to the chain you are trying to connect to, which could be a testnet, mainnet, or local node (e.g., hardhat or local-tableland).

import { getDefaultProvider } from "@tableland/sdk";
import { Database, helpers } from "@tableland/sdk";
import { Wallet, getDefaultProvider } from "ethers";
import * as dotenv from "dotenv";
dotenv.config()

// You can also pull getDefaultProvider from the SDK's 'helpers' module

const privateKey = process.env.PRIVATE_KEY;

const wallet = new Wallet(privateKey);
// To avoid connecting to the browser wallet (locally, port 8545),
// replace the URL with Alchemy, Infura, or Etherscan provider
const provider = getDefaultProvider("http://127.0.0.1:8545"); // For example: "https://polygon-mumbai.g.alchemy.com/v2/${process.env.YOUR_ALCHEMY_KEY}"
const signer = wallet.connect(provider);
// Connect to the database
const db = new Database({ signer });

It might be useful to use the NonceManager (experimental), which will automatically manage the nonce for you so you can blast the network with as many transactions as you would like.

import { NonceManager } from "@ethersproject/experimental";
import { Database } from "@tableland/sdk";
import { Wallet, getDefaultProvider } from "ethers";
import * as dotenv from "dotenv";
dotenv.config()

const privateKey = process.env.PRIVATE_KEY;

const wallet = new Wallet(privateKey);
// Replace with Alchemy, Infura, or Etherscan provider URL for the respective chain
const provider = getDefaultProvider("http://127.0.0.1:8545"); 
const baseSigner = wallet.connect(provider);
// Also demonstrates the nonce manager usage
const signer = new NonceManager(baseSigner);
const db = new Database({ signer });

// No need to await individual transactions (due to nonce manager)!

`wagmi`

Aside from ethersjs, other third party libraries can also be used. For example, if you are writing Tableland interactions inside a React app, it could leverage a signer from wagmi (inside your components/hooks).

import { useSigner } from "wagmi";
import { Database } from "@tableland/sdk";
 
function App() {
  const { data: signer } = useSigner()
 
	const db = Database.fromSigner(signer);
	// ...
}

Type conversion

Tableland supports a subset of SQLite types. When working in JavaScript, these types are converted to the Tableland requirements. Additionally, any object that implements a toSQL method can also be used.

JavaScript	Tableland	Notes
null	`NULL`
undefined	`NULL`
Number	`INTEGER`	Tableland supports 64-bit signed INTEGERs internally, and we mostly support BigInts on the client side. These values will be converted to INTEGERs. Note that Javascript integer's are safe up to Number.MAX_SAFE_INTEGER.
String	`TEXT`
ArrayBuffer	`BLOB`
Booleans	`INTEGER`	Booleans will be turned into integers where 1 is `TRUE` and 0 is `FALSE`.
Date	`INTEGER`	Dates are converted to their UNIX timestamp representation.
Object/JSON	`TEXT`	Plain old objects that can be converted to JSON strings will be converted to JSON and inserted as TEXT.

Errors

The stmt. and db. methods will throw an Error object whenever an error occurs. Database Errors use cause property for details.

new Error("ERROR", { cause: new Error("Error detail") });

To capture exceptions:

try {
    await db.exec("INSERTZ INTO my_table (name, employees) VALUES ()");
} catch (e: any) {
    console.log({
        message: e.message,
        cause: e.cause.message,
    });
}
/*
{
  "message": "EXEC_ERROR",
  "cause": "Error in line 1: INSERTZ INTO my_table (name, employees) VALUES (): sql error: near \\"INSERTZ\\": syntax error in INSERTZ INTO my_table (name, employees) VALUES () at offset 0"
}
*/

Registry API

The Database API provides a surface area that is familiar to SQL developers. However, Tableland is a web3 database, which means it comes with some blockchain-specific nuances. In order to finely tune access control, a developer must be able to directly interact with the Tableland registry contract; this holds methods that are abstracted in the Database API but also a few new ones.

import { Registry, helpers } from "@tableland/sdk";

const { getContractReceipt } = helpers;

// Pull info from an existing Database instance
const reg = await new Registry(db.config); // Must have a signer

const tx = await reg.createTable({
	chainId: 31337,
	statement: "create table test_ownership_31337 (id int, name text)",
});
// Helper function to extract table name event information
const receipt = await getContractReceipt(tx);

// List my tables
const results = await reg.listTables(/* default to connected wallet address */);

// Transfer the above table to my friend!
const tx = await reg.safeTransferFrom({
	to: friendAddress,
	tableName: receipt, // Also accepts name as string
});
// Tableland adopts this "wait" style pattern from ethers!
await tx.wait();

Validator API

The Validator API allows for direct access to a Tableland Validator node. This does not involve any on-chain interactions.

import { Validator } from "@tableland/sdk";

// Pull info from an existing Database instance
const obj = await new Validator(db.config);

const isHealthy = await obj.health();
console.log(isHealthy) // true

const { name, schema } = await obj.getTableById({ chainId: 80001, tableId: "1" });
console.log(name) // healthbot_31337_1
console.log(schema)
/*
{
	columns: [
		{
			name: "counter",
			type: "integer",
		},
	],
}
*/

ORM

Since Tableland supports Cloudflare’s D1Database interface, this means there is also support for an ORM is possible via d1-orm. Here’s a quick example of creating, updating, and querying a table via a Model object:

import {
   D1Orm,
   DataTypes,
   Model,
   GenerateQuery,
   QueryType,
   type Infer,
 } from "d1-orm";
import { Database } from "@tableland/sdk";

const db = new Database({ autoWait: true });
const orm = new D1Orm(db);

const users = new Model(
 {
   D1Orm: orm,
   tableName: "users",
   primaryKeys: "id",
   uniqueKeys: [["email"]],
 },
 {
   id: {
     type: DataTypes.INTEGER,
     notNull: true,
   },
   name: {
     type: DataTypes.STRING,
     notNull: true,
   },
   email: {
     type: DataTypes.STRING,
   },
 }
);
type User = Infer<typeof users>;

const { meta: create } = await users.CreateTable({
	strategy: "default",
});

// Slight temporary hack
(users.tableName as any) = create.txn.name;

await users.InsertOne({
	name: "Bobby Tables",
	email: "bobby-tab@gmail.com",
});

const { results } = await users.All({
	where: { name: "Bobby Tables" },
	limit: 1,
	offset: 0,
	orderBy: ["id"],
});

Additional integrations provide some client-side safety for injecting table names, query parameters, and more via prepared statement syntax. While you don’t need @databases/sql to leverage prepared statements with the Tableland SDK, it does provide some nice methods for working with raw SQL strings.

import sql, { FormatConfig } from "@databases/sql";
import { escapeSQLiteIdentifier } from "@databases/escape-identifier";
import { Database } from "@tableland/sdk";

// See https://www.atdatabases.org/docs/sqlite
const sqliteFormat: FormatConfig = {
	escapeIdentifier: (str) => escapeSQLiteIdentifier(str),
	formatValue: (value) => ({ placeholder: "?", value }),
};

// First, we'll test out using sql identifiers
const primaryKey = sql.ident("id");
const query = sql`CREATE TABLE test_sql (${primaryKey} integer primary key, counter integer, info text);`;
const { text, values } = query.format(sqliteFormat);
const { meta } = await db.prepare(text).bind(values).run();
const { name } = await meta.txn.wait();
console.log(`Created table ${name}`);

Using Typescript

The Database API and all related classes and modules are written in Typescript, and provide a generic interface to fully typed queries and responses (if you want). Currently, if you do not provide types, it will default to unknown. This is probably not what you want, so passing in any is fine, but you can do a whole lot more if you provide a concrete type.

Types can be provided on the Database constructor, on the Statement constructor (prepare), or callers can override them on any of the query/execution APIs directly (i.e., run, all, first, or raw).

For example, start by defining the type associated with the table’s schema and use this type in the database constructor.

import { Database } from "@tableland/sdk";

// This table has schema: `counter INTEGER PRIMARY KEY`
const tableName: string = "healthbot_80001_1"; // Our pre-defined health check table

interface HealthBot {
  counter: number;
}

const db = new Database<HealthBot>();

// Type is inferred due to `Database` instance definition
const { results } = await db.prepare(`SELECT * FROM ${tableName};`).all();
console.log(results);

You can then use this pattern for table creates and writes:

import { Database } from "@tableland/sdk";

interface Schema {
	id: number;
	name: string;
}

// Default to grabbing a wallet connection in a browser
const db = new Database<Schema>()

// This is the table's `prefix`; a custom table value prefixed as part of the table's name
const prefix: string = "my_sdk_table";

const { meta: create } = await db
	.prepare(`CREATE TABLE ${prefix} (id integer primary key, name text);`)
	.run();

// The table's `name` is in the format `{prefix}_{chainId}_{tableId}`
console.log(create.txn.name); // e.g., my_sdk_table_80001_311

// Insert a row into the table
const { meta: insert } = await db
	.prepare(`INSERT INTO ${name} (id, name) VALUES (?, ?);`)
	.bind(0, "Bobby Tables")
	.run();

// Wait for transaction finality
await insert.txn.wait();

// Perform a read query, requesting all rows from the table
const { results } = await db.prepare(`SELECT * FROM ${name};`).all();

← Previous

Quick Start

CLI