Proof of SQL℠ tamperproof queries enable many new use-cases at the intersection of Blockchain and AI, however, each SQL operation requires a new ZK circuit, so the SQL syntax has some limitations today. This page is intended to help guide developers on writing tamperproof queries, demonstrate what is available now, what's coming soon, and how to work around certain limitations.

Proof of SQL Syntax

Feature Availability

Below is Proof of SQL feature availability - what is currently available, what is coming soon. Each and every part of SQL syntax requires a custom ZK circuit to be built and optimized, keeping the Space and Time cryptography team busy! Understand there are limitations to Tamperproof queries today, but also that we expand the feature set weekly - check back often!

Currently Supported Syntax

Broadly, we support the following with more SQL features being added quickly:

• SELECT ... WHERE
• GROUP BY
• Comparison operations: =, >=, <=.
• Logical operations: AND, OR, NOT.
• Aggregations: SUM, COUNT
• Data Types: BIGINT, VARCHAR, DECIMAL75

For a detailed list of supported functions, see the SQL specification.

Roadmap

Proof of SQL is in active development. Here are some items that we are currently working on. We are happy to recieve feedback on additional features that the community requests.

• Expanded SQL support - in particular, multi-table queries (like JOIN) and subqueries
• Cluster scale proofs - this means faster proofs over larger tables!
• Solidity verifier - this would mean that verification can occur directly on chain.
• A novel commitment scheme - while we support a variety of commitment schemes, we are developing a commitment scheme specifically for database operations.

We are also currently undergoing robust security audits. Keep this in mind as you use this code.

📘

Check back frequently, this list will update often!

CREATE TABLE Requirements

Below walks through requirements when creating a tamperproof table. Tamperproof queries are only possible on tamperproof tables, as only tamperproof tables generate cryptographic commitments for all newly ingested data - a requirement for proof generation.

Creating a tamperproof table requires a few special settings, which are marked out in the example below:

CREATE TABLE MySchema.My_TpTable_v1 (
  MyPK_ID        bigint  not null  primary key
 ,some_textData  varchar not null  
 ,some_intData   bigint  not null  
 ,PROOF_ORDER    bigint  not null  /* <-- this is required */
)  
with "tamperproof=true, immutable=true,  public_key=$RESOURCE_PUBLIC_KEY" /* <-- all required */

Tamperproof Requirements

• PROOF_ORDER - an ordered, unique identifier for the data
  • This exists for technical reasons, later versions of Proof of SQL will remove this requirement.
  • The order of this column must match the order of the data inserted, however the values need not be consecutive, only ordered.
  • The application performing the data insert will need to manage the correct ordering of values (i.e., there is no Auto-Number yet)
  • The column must be named PROOF_ORDER
  • It can optionally be the primary key for the table, but not required
• NOT NULL - any column you wish to use in proof generation cannot allow nulls
  • You can have nullable columns, however they cannot be used for proof generation (aka cannot be used in a tamperproof query)
• WITH Statement has two required entries:
  • tamperproof=true - table should allow ZK Proofs
  • immutable=true - table is append-only
  • public_key - is required for ALL tables, tamperproof or not
• Data Types have varying levels of restrictions
  • Check out this page for the latest mapping of data type / feature mapping

Create Table Best Practices

Tamperproof tables are today immutable, meaning data can be added, but not updated nor deleted. It also means the tables themselves cannot be dropped (to prevent a table-recreation attack).

🚧

Plan ahead for the immutability of tamperproof tables.

If you create a tamperproof table, it cannot be dropped, and will consume schema.table name forever on the network! This keeps the data immutable, but also makes every data model mistake or column mis-type immutable as well, which can lead to table clutter inside schemas.

To help, here are a few best-practices to help reduce the number of unused tamperproof tables:

• Iterate on your data structure using non-tamperproof tables first, then drop/recreate as tamperproof table only as the last step. The process for calling tamperproof and non-tamperproof queries is nearly identical, so iterate on the mutable table, and create your tamperproof table only once.
• That said, time changes everything - version your tamperproof table names (MySchema.TPTable_v1, _v2, etc.), making it clear which is the "correct" version of the table.
• Isolate tamperproof tables to their own schema, so they are easily identifiable and don't clutter up your 'core' mutable data workspace (MySchema vs MySchema_TP).
• Create a view to query the current version of each tamperproof table, using a more business-friendly and non-versioned alias (table MySchema_TP.Table_v3 with view MySchema.Table).
  Note, tamperproof queries can only be run on the table directly, not a view - however, non-tamperproof queries will run on the view just fine.

All of these practices are optional, but worth considering when designing an intuitive, user-friendly data ecosystem for users.