sqlite

SQLite DApp

This example shows how to build and interact with a Cartesi Rollups application that internally runs an SQLite database. You can send any valid SQL command as input and if it produces results you get those back as a notice. You can also directly retrieve information from the database by sending SQL queries in the form of inspect requests.

The example highlights how common mainstream technologies such as an SQL database can be easily used in a Cartesi DApp. It also introduces how errors should be handled by an application, in the case that invalid SQL statements are submitted or if a critical error occurs (in this case, failure to communicate with the database).

Interacting with the application

We can use the frontend-console application to interact with the DApp. Ensure that the application has already been built before using it.

First, go to a separate terminal window and switch to the frontend-console directory:

cd frontend-console

Then, send an input to create a table as follows:

yarn start input send --payload "CREATE TABLE Persons (name text, age int)"

Next, add an entry to the newly created table by submitting an SQL INSERT statement as an input:

yarn start input send --payload "INSERT INTO Persons VALUES ('Peter', 32)"

Once data has been inserted into the database, it can be queried by sending an inspect request with a regular SQL SELECT statement, such as the following:

yarn start inspect --payload "SELECT * FROM Persons"

Alternatively, the same information can also be retrieved in the form of a notice, so that it can be independently verified by third-parties and used in smart contracts. In order to do that, simply send the same SQL query as an input's payload:

yarn start input send --payload "SELECT * FROM Persons"

Note that in this case the query's results will not be retrieved immediately. Notices will be generated asynchronously by the DApp whenever a submitted input corresponds to a valid SQL query.

In order to verify the notices generated by your inputs, run the command:

yarn start notice list

The payload of the notice should be something like this:

"[[\"Peter\", 32]]"

Running the environment in host mode

When developing an application, it is often important to easily test and debug it. For that matter, it is possible to run the Cartesi Rollups environment in host mode, so that the DApp's back-end can be executed directly on the host machine, allowing it to be debugged using regular development tools such as an IDE.

This DApp's back-end is written in Python, so to run it in your machine you need to have python3 installed.

In order to start the back-end, run the following commands in a dedicated terminal:

cd sqlite/
python3 -m venv .venv
. .venv/bin/activate
pip install -r requirements.txt
ROLLUP_HTTP_SERVER_URL="http://127.0.0.1:5004" python3 sqlite.py

The final command will effectively run the back-end and send corresponding outputs to port 5004. It can optionally be configured in an IDE to allow interactive debugging using features like breakpoints.

You can also use a tool like entr to restart the back-end automatically when the code changes. For example:

ls *.py | ROLLUP_HTTP_SERVER_URL="http://127.0.0.1:5004" entr -r python3 sqlite.py

After the back-end successfully starts, it should print an output like the following:

INFO:__main__:HTTP rollup_server url is http://127.0.0.1:5004
INFO:__main__:Sending finish

After that, you can interact with the application normally as explained above.