June 2, 2023

Kwil June 2023 Release: The SQL Syntax Expansion

Luke Lamey

Building upon the features included in the previous Kuneiform release, we are thrilled to announce the next major upgrade for Kwil: The SQL Syntax Expansion. This release incorporates new functionality into our supported syntax to greatly enhance our SQL support. In addition to adding new features, this release contains entirely new documentation, including full documentation on our supported SQL abstract syntax tree. This upgrade represents a leap forward in our commitment to providing robust, decentralized, permissionless SQL databases for the Web3-development stack.

The Challenge: Decentralizing SQL

There are two main challenges with designing a SQL syntax that can operate in a decentralized system: 1) non-determinism and 2) overly complex query operations.

A deterministic system is one in which, given an initial state or condition, will always produce the same results. There is no randomness or variation in how inputs are delivered as outputs.

This issue with SQL in a decentralized system is that many operations are non-deterministic. For example, if a user calls a TIME(now) function, what one node may assign as the current time may differ from what another node assigns as the time. This discrepancy creates a challenge for reconciling the precise data point that should be executed on the decentralized database.

In decentralized systems, even floating point numbers are sources of non-determinism (this is why Ethereum does not allow floating point). With SQLite being the underlying database engine in Kwil, there is a massive risk of creating non-determinism using SQLite’s dynamic typing. Kwil has implemented the proper safeguards to ensure strong typing in SQLite and prevent the use of floating point, both directly and indirectly.

Similarly, overly complex query operations threaten to slow down or even crash public decentralized networks. In a decentralized system where any SQL operation is allowed, there is little stopping a bad or unknowing actor from executing a computationally-intensive query, such as a cartesian join, thereby throttling the network and making it unusable for all.

These two challenges make decentralizing SQL a delicate task. We must strike a fine balance between supporting SQL syntax that allows developers to use the range of features that make SQL a powerful database language and restricting / modifying the syntax to make it compatible with a decentralized system.

The Kwil SQL Syntax Tree

In an attempt to strike that fine balance, we are excited to announce the full Kwil SQL syntax tree. This tree covers in detail the range of queries, operations, and functions that can be used in Kwil.

Prior to this release, our approach to solving the aforementioned problems was to restrict much of the SQL functionality the Kwil network could support. Although this solved the determinism and query complexity problems, it left developers with very simple schemas and use cases for Kwil. This release dramatically expands the SQL functionality we can confidently support, allowing developers to create more complex applications with Kwil, with little ambiguity on what is and is not supported.

Some highlighted new features in this release include subqueries, join clauses, common table expressions, upsert clauses, and much more. You can review the entire syntax tree and supported functions and operators here.

Next Steps

From this release, we are excited to continue working alongside our partners and users to expand decentralized SQL and introduce new features to allow developers and projects to manage decentralized relational data. Many thanks to our current partners for their feedback and guidance in this release.

If you have a project that you could use Kwil and would like to be the first to leverage new features over the coming months, please drop a note in our Discord or reach out to us at help@kwil.com. We cannot wait to support you.