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
