SQLite Scala Client with Slick
Functional Ops
Background
I have been designing highly scalable, lightweight blockchain storage Librumchain
. The edge nodes of the Librumchain run on Raspberry-Pi
based embedded devices. These edge nodes use SQLite
as their data storage. Basically SQLite stores accounts
, tokens
, token balances
details of the blockchain. In this post I’m gonna discuss about how I have used SQLite to handle the lightweight blockchain storage functions in Scala application with using Slick
library which is modern database query and access library for Scala. To keep the things simple I’m using minimum database table structures. All the source codes which related to this post available in gitlab. Please clone the repo and continue the post.
Install SQLite
SQLite is file based data storage. Unlike other databases, It does not support remote access. So we have to install SQLite inside the Scala application running machine. I have run this setup on macOS environment. macOS by default comes SQLite installation(sqlite3
). If you are using Docker
based setup to run the Scala application, you have install SQLite inside the Docker container which runs Scala the application.
Sbt Dependency
Following is the build.sbt
. I have used several Slick related dependencies here, 1) slick-hikaricp(Slick connection pooling library), 2) sqlite-jdbc(SQLite jdbc extension)
etc.
Database Config
Following is the SQLite related configs. These configurations defined in storage.conf
file and loaded into StorageConf
trait. Slick data source created with HikariCP
connection pooling library HikariDataSource
. The SQLite database created in a librum.db
file which resides in .storage
directory. The url
of the database file defined with jdbc:sqlite:.storage/librum.db
.
Slick SQLite Database Models
As mentioned previously, I have used three main tables in this blockchain scenario, 1) accounts, 2) tokens, 3) balances
. Following Slick models of these tables.
Slick Queries
Following are the Slick queries which related to model classes. To keep things simple I have used TikaRepo
and extend it with AccountTable
, TokenTable
and BalanceTable
traits. To properly handle dependencies you could use self typed annotations(cake pattern) or monads.
Run Queries
Following is the way to execute the Slick queries defined in the TikaRepo
. In here first I have created the SQLite database file and tables executing init()
function and then execute the queries.
SQLite Command Line
I can connect SQLite via command line client and view the data. Following is the way to connect SQLite from the command line.
Reference
- https://github.com/ludah65/Scala-Slick-SQLite-Example/blob/master/test.scala
- https://medium.com/rahasak/cockroachdb-scala-client-with-slick-12361f01afe9
- https://github.com/yashsriv/sqlite-slick
- https://medium.com/rahasak/scala-cake-pattern-e0cd894dae4e
- https://medium.com/rahasak/dependency-injection-with-reader-monad-in-scala-fe05b29e04dd