Ledger Databases and Immudb
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.