Tableland refactored its JS SDK to improve the developer experience and was heavily influenced by the Cloudflare D1 design.

Announcing the new Tableland SDK

A total SDK refactor should make it easier to get up and running in JavaScript applications.

By @Carson Farmer

📢

TL;DR This is the big announcement post about Tableland’s new SDK and database client. This SDK is a major breaking change from our previous offering, so developers are going to want to listen up. The changes include an entirely refactored (and simplified) API, a cleaner developer experience, and lots of fixes and improvements under the hood. So pay attention, this is a biggie!

As many Tablelanders probably know, the team behind Tableland has been around the decentralized database world for a while now. We’ve learned a lot about database design and usability along the way, and we’ve put a lot of what we’ve learned into the design and development of Tableland. One key thing that we’ve seen again and again, is that you need to provide familiar interfaces to new concepts, to keep folks moving forward with a new tool or technology. For example, there are already a ton of new things to learn in web3, so the last thing a developer new to the space wants to do, is re-learn something they already know: like database query languages.

That’s why we built Tableland in the first place: familiar SQL built natively into web3.

And that familiarity comes with a ton of advantages! Easier onboarding, reduced dev time, increased productivity, greater flexibility, and even a larger pool of existing developers to pull from! For folks interested in tinkering with web3, the more familiar interfaces they encounter, the more likely they’ll stick around and make a positive impact on the ecosystem. It’s really a win-win scenario for all of us.

So that’s why we’re particularly excited to announce this latest evolution to our Tableland SDK. We’re doubling down on familiar SQL interfaces by adopting what we think is the defacto standard in SQL APIs.

Better SQL

Perhaps you’ve seen Cloudflare’s announcement about their new D1 database offering? They provide a small and simple API that will feel very familiar to web2 database users. It includes the concept of prepared statements, SQL parameter binding, execution and query modes, and more. It is actually similar to the better-sqlite3 API in many respects. This is great, because it means developers don’t have to learn new APIs to be productive with new tools.

This is also why the core Tableland devs are so excited about our new D1-style APIs… we are benefiting from the “defacto standards” that are emerging from the SQLite ecosystem. Code that works for Cloudflare workers should work on Tableland, and the local dev implementation of D1 in wrangler, actually uses better-sqlite3 under the hood! The same APIs, here, there, and everywhere.

We think you’re going to love the simplicity of the new APIs, but we have a ton of open source software developers in the SQLite ecosystem to thank for them!

What it looks like

The "primary" API, is the D1 client compatible database API mentioned above. There are two additional "secondary" APIs. These are specific to Tableland. The first is the Validator API. This wraps the set of core APIs provided by Tableland Validators. The spec for this API comes from our spec documentation, and the underlying client is auto-generated from that. The next "secondary" API is the Registry API. This wraps interactions with the TablelandTables smart contract. It enables on-chain access control, sending create and mutate queries, and more.

In general, users will focus on the Database API, and only when fine-grained control, or specific use-cases require it, will they need to leverage the two secondary APIs. Note that there are also a ton of helper functions included to help with things like testing, accessing Tableland network defaults, and interacting with wallets, chains, and other Tabeland features.

Since the new APIs are based on the D1Database interface, the D1 docs provide pretty good coverage of the core features. Nevertheless, we have quirks and features specific to Tableland and blockchains that we document here as well. Let’s dive in!

Prepared statements

As part of our Database API, both static and prepared statements are supported. In the current implementation, the prepared statements are prepared locally, and executed remotely (on-chain). In the future, the statements will be prepared remotely.

Below is an example of a prepared statement:

const stmt = db.prepare("SELECT * FROM users WHERE name = ?1").bind("Joe");

However, if you still choose to use a static statement you can use the following as an example:

const stmt = db.prepare('SELECT * FROM users WHERE name = "John Doe"');

Parameter binding

We follow the SQLite convention for prepared statements parameter binding. Currently we support Ordered (?NNNN) and Anonymous (?) parameters, as well as all three forms of named parameters.

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.

To bind a parameter we use the method: stmt.bind()

Order and anonymous examples:

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

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");

In addition to the basic features offered by D1Ddatabase, our Database API supports a number of advanced bind features, such as named parameters, and complex data types that are converted to basic types on the fly.

const stmt = db
  .prepare(
    "INSERT INTO people VALUES (@name, ?, :name, ?, '?', ?4, ?3, ?, $blah);"
  )
  .bind(
    45,
    { name: "Hen'ry", blah: "😬" },
    [54, true, Uint8Array.from([1, 2, 3])],
    null
  );

Type conversion

Type conversion from JavaScript inputs to Tableland inputs is as follows:

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.

Additionally, any object that implements a toSQL method can also be used.

Return object

The methods stmt.run(), stmt.all() and db.batch() 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<{ ... }>
    }
  }
}

This is where compatibility to D1Database is useful, because it means that tools that expect a D1Database object can similarly use a Tableland Database. This is powerful, it means anything that works a bound D1Database, should work with Tableland out of the box:

const { duration } = (
  await db
    .prepare("INSERT INTO users (name, age) VALUES (?1, ?2)")
    .bind("John", 42)
    .run()
).meta;
console.log(duration); // 0.172

Query statement methods

The Database API supports the following query statement methods:

await stmt.first( [column] )
await stmt.all( [column] )
await stmt.raw()
await stmt.run()
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.

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()`

Same as stmt.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 ],
]
*/
console.log(raw.map((row) => row.join(",")).join("\\n"));
/*
John,42
Anthony,37
Dave,29
*/

`await stmt.run()`

Runs the query/queries, 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 (example: 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,
  ...
}
*/

Reusing prepared statements

Prepared statements can be reused with new bindings:

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,
  }
}
*/

Batch statements

Batching sends multiple SQL statements inside a single call to the network. This can have a huge performance impact, as it reduces the total number of transactions sent to the Tableland smart contract, thereby reducing gas costs. We also have some updates coming to our smart contract APIs to make these potential batching optimizations, even more optimal. In the mean time, this method will do its best to group statements into batches, and will error if it isn’t able to (for example, mixing create and write statements). Our implementation guarantees that each statement in the list will execute and commit, sequentially, non-concurrently.

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, we 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. When sending readonly queries, these are processed concurrently, and a set of results are returned.

const stmt = db.prepare("SELECT * FROM users WHERE name = ?1");
const rows = await db.batch([stmt.bind("John"), stmt.bind("Anthony")]);
console.log(rows[0].results);
/*
[
  {
     name: "John Clemente",
     age: 42,
  },
   {
     name: "John Davis",
     age: 37,
  },
 ]
*/
console.log(rows[1].results);
/*
[
  {
     name: "Anthony Hopkins",
     age: 66,
  },
 ]
*/

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).

// Define a custom type
type User {
  name: string;
  age: number;
}

const user = {
  name: "John Clemente",
  age: 42,
}

type UserInferred = typeof user

const db = new Database<User>({ ... })
const stmt = db.prepare("SELECT * FROM users WHERE name = ?1");

// From now on, query results will be fully typed
const { results } = await stmt.bind("John").all<UserInferred>();
// Assuming you have the above type correctly defined,
// you should get something like this:
console.log(results[0].name) // Fully typed
/*
"John Clemente"
*/

Note that the generic type system for Database is relatively sophisticated, so it should correctly determine the response shape of raw versus all, etc. Building on the previous example:

// Callers do not need to define these types,
// they are provided for illustrative purposes
type ValueOf<T> = T[keyof T];
type RawUser = ValueOf<User>;

// Results will be typed with the correct structure
const results = await stmt.bind("John").raw<User>();

// The results here are basically defined as
// type Array<RawUser>
console.log(results[0][0]);
/*
"John Clemente"
*/

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"
}
*/

Explore

The new SDK has lots of tests, including unit tests, end-to-end tests, and even a few anticipated uses of third party libraries. They are a great example of usage and we encourage you to take a look.

So please dive in, break things, explore the code, maybe upgrade your existing project to use the new APIs, and let us know what you think. The more feedback we get from users and developers, the better we can fine-tune the developer experience. Here’s to a whole new way to build the future of the web… familiar SQL coupled with the power of web3.

← Blog Home