Database-like Tables in Blockchain

Angel Java Lopez
3 min readDec 15, 2020

Based on an idea by bguiz published in RSK reseach forum: Crazy Idea: New opcodes for relational DB like data reads and writes, I was writing Solidity code to explore an alternative implementation in an Ethereum-like blockchain. I don’t add opcodes because these are scarce, and adding them could broke compability with Ethereum. My project is BDTables.

The base idea is to have an smart contract representing a database-like table with rows and columns:

Table smart contract storage

That is, a Table smart contract has: no of rows, no of columns, and a data offset where the data START to reside. The data is dynamically used, it’s not a fixed side array, and each field contains a bytes32.

A fragment of the current Table.sol code:

Table smart contract constructor

To manage the data cells (in consecutive storage cells starting at data offset) I’m using assembly tricks, ie:

Adding a row of bytes32 using assembly

I have functions to add, get and delete rows. Also to get an specific field (by row and column). And a function to return the first row in a range that satisfies an equality match over a column.

But, what if you want to manage integers, strings? Well, I implemented an ExtendedTable.sol:

An extended table could manage strings and define comparisonoperators

Using an extended table, I can define a table with other fields and types, ie ExpensesTable.sol:

Notice that the string is saved as a bytes32 hash, and the mapping to the original string is kept by the extended table. In the current code, I have another extended sample TransfersTable.sol.

As usual, I wrote all the code following the TDD (Test-Driven Development) workflow, so the tests are a guide to the use of these contracts.

In these tables, I have ad-hoc query functions, by string value or by unsigned integer comparison. Notice that in the query functions I’m providing a range of rows ids to process in the query, so the calling transaction or smart contract could limit the gas to use in the operation.

Next steps

There are many ideas to add:

  • A database smart contract, that contains the names and address of tables
  • Code generation of extended tables, based on columns definitions (types), so the query functions are generated
  • Write a precompiled contract to manage any Table smart contract storage (the recommendation is that this contract has only the cells I described at the beginning of this post: no of rows, no of columns, data offset and field cells)
  • Add to that precompiled contract some capacity to parse and execute a simplified SQL statement
  • Write a “to do dapp” but I’m not yet confident writing such project

I could write the precompiled contract into the RSK implementation, but I think the RSK storage management is not decoupled from inner storage in tries, so, to avoid such complications, I could start using my personal blockchain project.

I have a lot of fun with this project, thanks for the idea bguiz!

Angel “Java” Lopez