Tableland previously moved to SQLite — and also released a parser for the custom Tableland SQL specification.

You probably didn’t notice, but Tableland recently switched our validator database implementation from PostgreSQL to SQLite. For some, this might sound like a wild and crazy thing to do — and maybe it is?! But we’re doubling down on SQLite (and we’re not the only ones) as the core SQL database implementation for Tableland, and we have some pretty good reasons for doing this.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. We’re talking about a complete SQL database with all the normal bells and whistles, contained within a single disk file 🤯. Turns out it’s also the most widely deployed database in the world. Plus it is free and open source, tiny, full-featured, cheap to run, easy to deploy, fun to use, and is supported by a huge range of user and developer tooling.

Our biggest motivation for the switch is for our downstream users and future validators. For validators, the an embedded database like SQLite makes their life easier because…

Users can also take advantage of the SQLite choice…

One thing you hopefully did notice, is that Tableland recently shipped our full preliminary SQL specification. The spec is geared toward web3 native SQL operations and the highly specific SQL features that we need to support Tableland’s primary use-cases (NFTs, Games, and more). It borrows heavily from the SQLite SQL specification (which is an additional motivation for the migration to SQLite), but differs in some key ways, including scope, syntax, and some core limitations from running on a decentralized network.

In order to decouple the SQL spec (Tableland) from the SQL implementation (SQLite), we created our own custom SQL parser.

Why did we do this? Well, it’s hard to find a SQLite parser decoupled from the full SQLite source code that would allow us to customize what we support. The SQLite parser does not produce an intermediary AST for INSERT, DELETE, UPDATE and CREATE, and so most of the default SQLIte parser logic is coupled with the execution logic. This makes customization hard. Additionally, the Tableland SQL spec accepts custom statements like GRANT/REVOKE that are not part of the SQLite syntax, so having a custom parser allows offering that syntax plus leaves room for future additions to the spec.

Perhaps most importantly, the Tableland SQL spec is much shorter than the SQLite SQL spec, and the broader SQL language spec in general. So why have a parser that parses things we don't need (or aren’t going to allow)?

There are some technical reasons for having our own parser as well, including the ability to parse SQL statements in pure Go (our validator is implemented in Go), and to actually interact with, and create custom functions or even new SQL language features using Go. Imagine a brand-new NFT operator built right into the SQL spec?! In some sense, Tableland become database implementation agnostic. This allows multiple implementations of Tableland protocol to exist, potentially with different databases.

Plus, for the developers in the room, here’s a super cool feature of having a custom parser: We can compile the parser to JS/WebAssembly and use it directly in the browser! That means developers can parse and validate SQL statements on the client, before sending them off to the smart contract, avoiding a ton of potentially costly mistakes.

To play around with the SQL parser via WASM, check out this handy little Codepen project and take a look at the tiny bit of Javascript required to integrate this into your own application or scripts:

← Back