SQLite Migration & Parser

SQLite Migration & Parser

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

SQLite Migration & Parser

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

Tableland ❤️s SQLite

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…

  • Running a validator is a single process, they don’t need to run multiple services to participate in the network.
  • There’s not extra monitoring required for the database since it’s part of the main validator process!
  • It requires fewer resources compared to a non-embedded database, making it cheaper and easier to run.
  • There is a ton of tooling out there to help validators fine tune their setup, without complex code or SQL migration steps.

Users can also take advantage of the SQLite choice…

  • They can exploit a rich ecosystem of tools (UIs, CLIs, libraries) that exist for SQLite databases since tables in Tableland can be exported to SQLite databases with less friction.
  • There are libraries that can allow interacting with an SQLite database inside the browser, which is very convenient for dApps.
  • SQLite is commonly used in small or medium-sized applications that rely on relational databases, thus integrating existing tables and queries with Tableland can be low friction.

Tableland SQL Spec Parser

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: