Ledger Databases and Immudb

Description

While studying AWS and the possible datastores you can create in the cloud I found the concept of a ledger database. It looks like this is fairly newer but has a great application. This post discusses ledger databases and installing/using one called Immudb.

Ledger Databases Use Cases

Ledger databases are great for applications that require trust in the data. Some examples of applications are:

  • Finance transactions
  • Shipping Logs
  • Bank Accounts

How it Works

With every transaction a signature is used to validate the data. If anything is tampered with there is a full history of what/who/when/where that transaction happened. Immudb actually has a feature called time machine to go to a previous state in the database at any point in time.

Immudb

Immudb can be found here. In this post we will setup a working database and then run through some transactions. In our example we will be a bank call Dan’s Bank with a few clients. We will be processing transactions from Online retailers. Then we will audit what occurred in the past month for one of our clients.

Downloading Immudb

For this demonstration we will use docker for ease of setup.

dan@ubuntu:~$ docker pull codenotary/immudb
Using default tag: latest
latest: Pulling from codenotary/immudb
4bb8ce72b80c: Pull complete 
6e338cb7a9ba: Pull complete 
ca1f450ca571: Pull complete 
27c3e0168202: Pull complete 
878daed47f9c: Pull complete 
Digest: sha256:144a8b56c1edd37b4b909cbe6c47f3f240bf4e778807da80c8abfcc5720d1149
Status: Downloaded newer image for codenotary/immudb:latest
docker.io/codenotary/immudb:latest

Starting Immudb

Now run the start command to get the container running.

dan@ubuntu:~$ docker run -d --net host -it --rm --name immudb codenotary/immudb:latest
6a67adeb86e60872543b2ab91f509921816d98762ce9d9a72f71107af259ef1b

Connecting to the Container

To connect to the container codenotary actually made an image to run their client program as well.

docker run -it --rm --net host --name immuclient codenotary/immuclient:latest
immuclient>login immudb
Password: << use the default password 'immudb'
Successfully logged in
immudb user has the default password: please change it to ensure proper security 
immuclient>

Create Schema

Now let’s create that schema. We will create two tables. One to track our customers and another to track any transactions.

immuclient>exec CREATE TABLE IF NOT EXISTS customers (id INTEGER AUTO_INCREMENT, customer_name VARCHAR[60], balance INTEGER, PRIMARY KEY (id));
Updated rows: 0 
immuclient>exec CREATE TABLE IF NOT EXISTS transactions (customerid  INTEGER, memo VARCHAR[60], created_at  TIMESTAMP, amount INTEGER, PRIMARY KEY (customerid));
Updated rows: 0 

Create Customers

To create the customers run the following SQL.

immuclient>exec UPSERT INTO customers(id, customer_name, balance) VALUES (1, 'Dan', 0), (2, 'Joe', 0), (3, 'Adam', 0), (4, 'Jess', 0), (5, 'Claire', 0);
Updated rows: 5

Add Some Transactions

Let’s add some initial deposits on those accounts next.

exec INSERT INTO transactions (customerid, memo, created_at, amount) values (1, 'Gasoline', NOW(), 50)
exec UPDATE customers set balance = -50 where id = 1;
exec INSERT INTO transactions (customerid, memo, created_at, amount) values (2, 'Dance Class', NOW(), 25)
exec UPDATE customers set balance = -25 where id = 2;
exec INSERT INTO transactions (customerid, memo, created_at, amount) values (3, 'Dinner', NOW(), 10)
exec UPDATE customers set balance = -10 where id = 3;

Ignoring that each customer now has a negative balance, it’s time to see what Immudb can do with what we’ve made.

Verifying Data

With every transaction we have a new hash generated with a transaction number. To view what the current number is check the current command:

immuclient>current
txID:		10
hash:		c7421bc17fedd18aaa757fc7faa5142c4c919c20700bd511fa42ee071e354803

Whenever a new transaction happens this hash will change. You can even get the previous hashes from before the current transaction using gettx:

immuclient>gettx 5
tx:		5
time:		2022-03-23 11:31:41 +0000 UTC
entries:	1
hash:		6612516192d1bb912ae2ea9607296f35dd3d3059977090527fc8a41260e0d18c

Looking at Historical Data

To look at a table before a certain transaction you can specify that in the query. Advantages of this are to see what exactly was added or how the accounts looked at any point in time.

immuclient>query select customer_name,balance from customers before tx 8;
+-------------------------------------+-------------------------------+
| (DEFAULTDB CUSTOMERS CUSTOMER NAME) | (DEFAULTDB CUSTOMERS BALANCE) |
+-------------------------------------+-------------------------------+
| "Dan"                               |                           -50 |
| "Joe"                               |                             0 |
| "Adam"                              |                             0 |
| "Jess"                              |                             0 |
| "Claire"                            |                             0 |
+-------------------------------------+-------------------------------+

Auditors

You can setup auditors using the immuclient that will allow you to audit the data being added. You can even use signatures on the state to verify the state as the database gets updated. That is out of scope for this post but would be cool to try.

Final Thoughts

Using a ledger database is definitely interesting and different. It looks like Immudb is easy to setup as a test/learning ledger database but is really new and doesn’t support a lot of features you would find in a database. One notable difference is the lack of drop table. I had to wipe the database to get back to where I wanted to be. Anway, there are a lot of applications here for this kind of database. In the future I may do a post on AWS Quantum Ledger database.