Scala Functional Database Libraries
slick, quill, doobie, skunk = sqds
Background
In this post, I will be discussing the main features and implementation examples of functional database libraries for Scala. Specifically, I will be focusing on four popular libraries: Slick, Quill, Doobie, and Skunk. The database functions implemented with using the Cats-Effects IO monad stack, which allows for safe and efficient interaction with databases. The implementations can be extended with ZIO/ZLayers as well. To follow along with the examples in this post, all related source code is available on gitlab. You can clone the repository and continue reading the post. The feature comparison of the Slick, Quill, Doobie, and Skunk libraries presented in the following table.
Scenario
I am working with a model called Toggle, and I need to perform CRUD operations on this model using four different Scala database libraries: Slick, Quill, Doobie, and Skunk.
// case class of model
import java.util.{Date, UUID}
case class Toggle(id: Option[UUID] = Option(UUID.randomUUID()),
service: String,
name: String,
value: String,
timestamp: Date = new Date())I have defined an interface ToggleRepo to facilitate CURD operations with Toggle models. This interface implemented for Slick, Quill, Doobie and Skunk libraries.
import cats.effect.IO
import com.pagero.labs.sqds.model.Toggle
/**
* define curd operations of toggles table
* return values wrapped with IO monad
*/
trait ToggleRepo {
def createToggle(toggle: Toggle): IO[Unit]
def updateToggle(name: String, value: String): IO[Unit]
def deleteToggle(name: String): IO[Unit]
def getToggle(name: String): IO[Option[Toggle]]
def getServiceToggles(service: String): IO[List[Toggle]]
def getAllToggles: IO[List[Toggle]]
}To run the databases, I am using Postgres with Docker, specifically the Postgres 12 (postgres:12-alpine Docker image). This version was selected because Quill library has issues with sha-256 password_encryption, and Postgres 12 uses md5 password_encryption instead. If you are using a newer version of Postgres, you may be able to update the password_encryption settings in Postgres. For more information on this, please refer to this post.
# run postgres docker
❯❯ docker run --name postgres -e POSTGRES_PASSWORD=admin -p 5432:5432 -d postgres:12-alpine
# postgres container
❯❯ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
b45003569b40 docker.io/library/postgres:12-alpine postgres 39 hours ago Up 3 seconds 0.0.0.0:5432->5432/tcp postgres
# connect to postgres container
❯❯ docker exec -it postgres bash
# connect to postgres
# dev.localhost points to machine ip or 127.0.0.1 in /etc/hosts
psql -h dev.localhost -p 5432 -U postgres
# and create database
create database sqds;
# list databases
\list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
rahasak | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
sqds | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)1. Slick
Slick introduced as a Functional Relational Mapping library for Scala that allows to query and access a database like other Scala collections. Slick library supports different databases including Postgres, Mysql, Oracle etc. With Slick, we can write database queries in Scala instead of SQL, thus providing typesafe queries. Slick is the most popular library for database access in the Scala ecosystem. Slick provides compile-time safety and composability to our queries. Following is the way to implement ToggleRepo functions with Slick3.
1.1 Sbt dependency
Following the build.sbt file with required dependencies to integrate with Slick3. It contains dependencies of cats-effect, circe , slick and slick-hikaricp.
name := "sqds"
description := "skunk, quill, doobie, slick"
libraryDependencies ++= {
lazy val catsVersion = "3.4.8"
lazy val circeVersion = "0.14.1"
Seq(
"org.typelevel" %% "cats-effect" % catsVersion,
"io.circe" %% "circe-core" % circeVersion,
"io.circe" %% "circe-generic" % circeVersion,
"io.circe" %% "circe-config" % "0.8.0",
"com.typesafe.slick" %% "slick" % "3.4.1",
"com.typesafe.slick" %% "slick-hikaricp" % "3.4.1",
"com.github.tminglei" %% "slick-pg" % "0.21.1"
)
}1.2. Database config
I have defined the database configs in application.conf and load them using circe-config library and cats-effects IO monad.
db {
host = "dev.localhost"
port = 5432
database = "sqds"
username = "postgres"
password = "admin"
poolSize = 10
}import cats.effect.IO
import io.circe.config.parser
import io.circe.generic.auto._
case class HttpConfig(port: Int, host: String)
case class DbConfig(host: String, port: Int, database: String, username: String, password: String, poolSize: Int)
case class Config(httpConfig: HttpConfig, dbConfig: DbConfig)
object Config {
def load(): IO[Config] = {
for {
httpConf <- parser.decodePathF[IO, HttpConfig]("http")
dbConf <- parser.decodePathF[IO, DbConfig]("db")
} yield Config(httpConf, dbConf)
}
}1.3. Database connection pool
I have created a database connection pool for Slick using HikariCP. connection pooling library which available with slick-hikaricp. The connection pool wrapped with cats-effect Resource.
import cats.effect.IO
import cats.effect.kernel.Resource
import com.pagero.labs.sqds.config.DbConfig
import com.zaxxer.hikari.{HikariConfig, HikariDataSource}
import slick.jdbc.PostgresProfile
import slick.jdbc.PostgresProfile.api._
object ConnectionPoolSlick {
def pool(dbConfig: DbConfig): Resource[IO, PostgresProfile.backend.DatabaseDef] = {
lazy val url = s"jdbc:postgresql://${dbConfig.host}:${dbConfig.port}/${dbConfig.database}"
lazy val driver = "org.postgresql.Driver"
val hikariConfig = new HikariConfig
hikariConfig.setDriverClassName(driver)
hikariConfig.setUsername(dbConfig.username)
hikariConfig.setPassword(dbConfig.password)
hikariConfig.setJdbcUrl(url)
hikariConfig.setConnectionTestQuery("SELECT 1")
val ds = new HikariDataSource(hikariConfig)
Resource.make(IO(Database.forDataSource(ds, Option(dbConfig.poolSize))))(db => IO.unit)
}
}1.4. Slick implementation of ToggleRepo
Following is the way I have implemented the ToggleRepo trait functions with Slick. The Toggles class defined the Slick table mapping for Toggle case class. ToggleRepoImplSlick implemented the ToggleRepo trait functions. In here the connection pool Resource[IO, PostgresProfile.backend.DatabaseDef] passed as constructor argument. The connections in the pool obtained with connectionPool.use.
import cats.effect.IO
import cats.effect.kernel.Resource
import com.pagero.labs.sqds.repo.ToggleRepo
import com.pagero.labs.sqds.model.Toggle
import slick.ast.BaseTypedType
import slick.jdbc.PostgresProfile.api._
import slick.jdbc.{JdbcType, PostgresProfile}
import java.sql.Timestamp
import java.util.{Date, UUID}
class Toggles(tag: Tag) extends Table[Toggle](tag, "toggles") {
implicit val dateColumnType: JdbcType[Date] with BaseTypedType[Date] = MappedColumnType.base[Date, Timestamp](
d => new Timestamp(d.getTime),
d => new Date(d.getTime)
)
def id = column[UUID]("id", O.PrimaryKey)
def service = column[String]("service")
def name = column[String]("name")
def value = column[String]("value")
def timestamp = column[Date]("timestamp")
def * = (id.?, service, name, value, timestamp).<>(Toggle.tupled, Toggle.unapply)
}
class ToggleRepoImplSlick(connectionPool: Resource[IO, PostgresProfile.backend.DatabaseDef]) extends ToggleRepo {
val toggles = TableQuery[Toggles]
def init(): IO[Unit] = {
connectionPool.use { session =>
cats.effect.IO.fromFuture(cats.effect.IO(session.run(toggles.schema.createIfNotExists)))
}
}
def createToggle(toggle: Toggle): IO[Unit] = {
connectionPool.use { session =>
cats.effect.IO.fromFuture(cats.effect.IO(session.run(toggles += toggle))).map(_ => ())
}
}
def updateToggle(name: String, value: String): IO[Unit] = {
connectionPool.use { session =>
val q = for {
t <- toggles
if t.name === name
} yield t.value
cats.effect.IO.fromFuture(cats.effect.IO(session.run(q.update(value)))).map(_ => ())
}
}
def getToggle(name: String): IO[Option[Toggle]] = {
connectionPool.use { session =>
cats.effect.IO.fromFuture(cats.effect.IO(session.run(toggles.filter(_.name === name).result.headOption)))
}
}
def getServiceToggles(service: String): IO[List[Toggle]] = {
connectionPool.use { session =>
cats.effect.IO.fromFuture(cats.effect.IO(session.run(toggles.filter(_.service === service).result))).map(_.toList)
}
}
def getAllToggles: IO[List[Toggle]] = {
connectionPool.use { session =>
cats.effect.IO.fromFuture(cats.effect.IO(session.run(toggles.result))).map(_.toList)
}
}
def deleteToggle(name: String): IO[Unit] = {
connectionPool.use { session =>
val q = for {
t <- toggles
if t.name === name
} yield t
cats.effect.IO.fromFuture(cats.effect.IO(session.run(q.delete))).map(_ => ())
}
}
}1.5. Invoke database functions
Following is the way to invoke the functions implemented in the ToggleRepoImplSlick. The SlickApp which runs as cats-effects IOApp executes the database functions with cats-effects IO.
import cats.effect.{ExitCode, IO, IOApp}
import com.pagero.labs.sqds.config.Config
import com.pagero.labs.sqds.repo.live.ToggleRepoImplSlick
import com.pagero.labs.sqds.model.Toggle
import com.pagero.labs.sqds.conn.ConnectionPoolSlick
import com.typesafe.scalalogging.LazyLogging
object SlickApp extends IOApp with LazyLogging {
override def run(args: List[String]): IO[ExitCode] = {
val program: IO[Unit] = for {
config <- Config.load()
connectionPool <- IO(ConnectionPoolSlick.pool(config.dbConfig))
repo = new ToggleRepoImplSlick(connectionPool)
_ <- repo.init()
toggle = Toggle(service = "slick", name = "rahasak", value = "kooooo")
_ <- repo.createToggle(toggle)
//search <- repo.getToggles("slick")
_ <- repo.updateToggle("rahasak", "ops")
search <- repo.getToggle("rahasak")
_ <- IO(logger.info(s"toggles $search"))
_ <- repo.deleteToggle("rahasak")
} yield {
()
}
program.as(ExitCode.Success)
}
}2. Quill
According to the documentation, Quill provides a Quoted Domain Specific Language(QDSL) to express queries in Scala and execute them in a target language. The library’s core is designed to support multiple target languages, currently featuring specializations for Structured Query Language(SQL) and Cassandra Query Language (CQL). Quill supports a wide range of databases, including Postgres, MySQL, and Cassandra. Following the implementation of ToggleRepo functions with Quill.
2.1. Sbt dependency
Following the build.sbt file with required dependencies for Quill. It contains dependencies of cats-effect, circe and quill. In here I have used quill asynchronous API which available in quill-async-postgres library.
name := "sqds"
description := "skunk, quill, doobie, slick"
libraryDependencies ++= {
lazy val catsVersion = "3.4.8"
lazy val circeVersion = "0.14.1"
Seq(
"org.typelevel" %% "cats-effect" % catsVersion,
"io.circe" %% "circe-core" % circeVersion,
"io.circe" %% "circe-generic" % circeVersion,
"io.circe" %% "circe-config" % "0.8.0",
"io.getquill" %% "quill-async-postgres" % "3.12.0",
"org.postgresql" % "postgresql" % "42.5.4"
)
}2.2. Database connection pool
The Quill asynchronous APIs’ PostgresAsyncContext provides its own connection pooling interface. I have created connection pool with PostgresAsyncContext and wrapped with cats-effects Resource here.
import cats.effect.{IO, Resource}
import com.pagero.labs.sqds.config.DbConfig
import com.typesafe.config.{ConfigFactory, ConfigValueFactory}
import io.getquill.{PostgresAsyncContext, SnakeCase}
object ConnectionPoolQuill {
def pool(dbConfig: DbConfig): Resource[IO, PostgresAsyncContext[SnakeCase.type]] = {
lazy val url = s"postgresql://${dbConfig.host}:${dbConfig.port}/${dbConfig.database}?user=${dbConfig.username}&password=${dbConfig.password}"
val config = ConfigFactory.empty()
.withValue("url", ConfigValueFactory.fromAnyRef(url))
Resource.make(IO(new PostgresAsyncContext(SnakeCase, config)))(db => IO.unit)
}
}2.3. Quill implementation of ToggleRepo
The ToggleRepoImplQuill implemented the ToggleRepo trait functions. In here the connection pool Resource[IO, PostgresAsyncContext[SnakeCase.type]] passed as constructor argument. The connections in the pool obtained with connectionPool.use.
import cats.effect.{IO, Resource}
import com.pagero.labs.sqds.repo.ToggleRepo
import com.pagero.labs.sqds.model.Toggle
import io.getquill.{PostgresAsyncContext, SnakeCase}
class ToggleRepoLiveQuill(connectionPool: Resource[IO, PostgresAsyncContext[SnakeCase.type]]) extends ToggleRepo {
implicit val ec: scala.concurrent.ExecutionContext = scala.concurrent.ExecutionContext.global
def createToggle(toggle: Toggle): IO[Unit] = {
connectionPool.use { ctx =>
import ctx._
val q = quote {
querySchema[Toggle]("toggles")
.insert(lift(toggle))
}
cats.effect.IO.fromFuture(cats.effect.IO(ctx.run(q))).map(_ => ())
}
}
def updateToggle(name: String, value: String): IO[Unit] = {
connectionPool.use { ctx =>
import ctx._
val q = quote {
querySchema[Toggle]("toggles")
.filter(t => t.name == lift(name))
.update(_.value -> lift(value))
}
cats.effect.IO.fromFuture(cats.effect.IO(ctx.run(q))).map(_ => ())
}
}
def getToggle(name: String): IO[Option[Toggle]] = {
connectionPool.use { ctx: PostgresAsyncContext[SnakeCase.type] =>
import ctx._
val a = quote {
querySchema[Toggle]("toggles")
.filter(t => t.name == lift(name))
}
cats.effect.IO.fromFuture(cats.effect.IO(ctx.run(a).map(_.headOption)))
}
}
def getServiceToggles(service: String): IO[List[Toggle]] = {
connectionPool.use { ctx: PostgresAsyncContext[SnakeCase.type] =>
import ctx._
val a = quote {
querySchema[Toggle]("toggles")
.filter(t => t.service == lift(service))
}
cats.effect.IO.fromFuture(cats.effect.IO(ctx.run(a).map(_.asInstanceOf[List[Toggle]])))
}
}
def getAllToggles: IO[List[Toggle]] = {
connectionPool.use { ctx: PostgresAsyncContext[SnakeCase.type] =>
import ctx._
val a = quote {
querySchema[Toggle]("toggles")
}
cats.effect.IO.fromFuture(cats.effect.IO(ctx.run(a).map(_.asInstanceOf[List[Toggle]])))
}
}
def deleteToggle(name: String): IO[Unit] = {
connectionPool.use { ctx =>
import ctx._
val q = quote {
querySchema[Toggle]("toggles")
.filter(t => t.name == lift(name))
.delete
}
cats.effect.IO.fromFuture(cats.effect.IO(ctx.run(q))).map(_ => ())
}
}
}2.4. Invoke database functions
Following is the way to invoke the functions implemented in the ToggleRepoImplQuill. The QuillApp which runs as cats-effects IOApp executes the database functions with cats-effects IO.
import cats.effect.{ExitCode, IO, IOApp}
import com.pagero.labs.sqds.config.Config
import com.pagero.labs.sqds.repo.live.ToggleRepoLiveQuill
import com.pagero.labs.sqds.model.Toggle
import com.pagero.labs.sqds.conn.ConnectionPoolQuill
import com.typesafe.scalalogging.LazyLogging
object QuillApp extends IOApp with LazyLogging {
override def run(args: List[String]): IO[ExitCode] = {
val program: IO[Unit] = for {
config <- Config.load()
connectionPool <- IO(ConnectionPoolQuill.pool(config.dbConfig))
repo = new ToggleRepoLiveQuill(connectionPool)
//_ <- repo.init()
toggle = Toggle(service = "quill", name = "rahasak", value = "labs")
_ <- repo.createToggle(toggle)
//search <- repo.getServiceToggles("quill")
search <- repo.getAllToggles
_ <- IO(logger.info(s"toggles $search"))
_ <- repo.deleteToggle("rahasak")
} yield {
()
}
program.as(ExitCode.Success)
}
}3. Doobie
Doobie intreoduced as a purely functional JDBC layer for Scala which implemented with Cats and Cats Effect libraries. According to the documentation Doobie is not an ORM, nor is it a relational algebra. It simply provides a functional way to construct programs (and higher-level libraries) that use JDBC. Following is the way I have implemented ToggleRepo functions with Doobie.
3.1. Sbt dependency
Following the build.sbt file with the required dependencies of Doobie. It contains dependencies of cats-effect, circe, doobie-core, doobie-hikari and doobie-postgres libraries.
name := "sqds"
description := "skunk, quill, doobie, slick"
libraryDependencies ++= {
lazy val catsVersion = "3.4.8"
lazy val circeVersion = "0.14.1"
Seq(
"org.typelevel" %% "cats-effect" % catsVersion,
"io.circe" %% "circe-core" % circeVersion,
"io.circe" %% "circe-generic" % circeVersion,
"io.circe" %% "circe-config" % "0.8.0",
"org.tpolecat" %% "doobie-core" % "1.0.0-RC1",
"org.tpolecat" %% "doobie-hikari" % "1.0.0-RC1",
"org.tpolecat" %% "doobie-postgres" % "1.0.0-RC1"
)
}3.2. Database connection pool
I have created a database connection pool for Doobie using HikariCP connection pooling library which available with doobie-hikaricp. The connection pool wrapped with cats-effect Resource.
import cats.effect.IO
import cats.effect.kernel.Resource
import com.pagero.labs.sqds.config.DbConfig
import doobie._
import doobie.hikari._
object ConnectionPoolDoobie {
def pool(dbConfig: DbConfig): Resource[IO, HikariTransactor[IO]] = for {
ce <- ExecutionContexts.fixedThreadPool[IO](32) // our connect EC
xa <- HikariTransactor.newHikariTransactor[IO](
"org.postgresql.Driver",
s"jdbc:postgresql://${dbConfig.host}:${dbConfig.port}/${dbConfig.database}",
dbConfig.username,
dbConfig.password,
ce
)
} yield xa
}3.3. Doobie implementation of ToggleRepo
Following is the Doobie implementation of ToggleRepo trait functions. DoobieQueries object defined the different Doobie queries. The ToggleRepoImplDoobie implemented the ToggleRepo trait functions. In here the connection pool connectionPool: Resource[IO, HikariTransactor[IO]] passed as constructor argument. The connections in the pool obtained with connectionPool.use.
import cats.effect.kernel.Resource
import cats.effect.IO
import com.pagero.labs.sqds.repo.ToggleRepo
import com.pagero.labs.sqds.model.Toggle
import com.typesafe.scalalogging.LazyLogging
import doobie.hikari._
import doobie.implicits._
import doobie.postgres.implicits._
/**
* Define doobie queries
* - table create
* - insert
* - update
* - select
* - delete
*/
object DoobieQueries {
def tableQuery: doobie.Update0 = {
sql"""
|CREATE TABLE IF NOT EXISTS toggles (
| id UUID PRIMARY KEY,
| name VARCHAR(100),
| service TEXT,
| value TEXT,
| timestamp TIMESTAMP
|)
""".stripMargin
.update
}
def insertQuery(toggle: Toggle): doobie.Update0 = {
sql"""
|INSERT INTO toggles (
| id,
| service,
| name,
| value,
| timestamp
|)
|VALUES (
| ${toggle.id},
| ${toggle.service},
| ${toggle.name},
| ${toggle.value},
| ${toggle.timestamp}
|)
""".stripMargin
.update
}
def updateQuery(name: String, value: String): doobie.Update0 = {
sql"""
|UPDATE toggles
|SET value = $value
|WHERE name = $name
""".stripMargin
.update
}
def selectByNameQuery(name: String): doobie.Query0[Toggle] = {
sql"""
|SELECT id, service, name, value, timestamp
|FROM toggles
|WHERE name = $name
|LIMIT 1
""".stripMargin
.query[Toggle]
}
def selectByServiceQuery(service: String): doobie.Query0[Toggle] = {
sql"""
|SELECT * FROM toggles
|WHERE service = $service
""".stripMargin
.query[Toggle]
}
def selectAllQuery: doobie.Query0[Toggle] = {
sql"""
|SELECT * FROM toggles
""".stripMargin
.query[Toggle]
}
def deleteQuery(name: String): doobie.Update0 = {
sql"""
|DELETE FROM toggles
|WHERE name=$name
""".stripMargin
.update
}
}
class ToggleExecImplDoobie(connectionPool: Resource[IO, HikariTransactor[IO]]) extends ToggleRepo with LazyLogging {
def init(): IO[Int] = {
connectionPool.use { session =>
DoobieQueries.tableQuery.run.transact(session)
}
}
def createToggle(toggle: Toggle): IO[Unit] = {
connectionPool.use { session =>
DoobieQueries.insertQuery(toggle).run.transact(session).map(_ => ())
}
}
def updateToggle(name: String, value: String): IO[Unit] = {
connectionPool.use { session =>
DoobieQueries.updateQuery(name, value).run.transact(session).map(_ => ())
}
}
def getToggle(name: String): IO[Option[Toggle]] = {
connectionPool.use { session =>
DoobieQueries.selectByNameQuery(name).option.transact(session)
}
}
def getServiceToggles(service: String): IO[List[Toggle]] = {
connectionPool.use { session =>
DoobieQueries.selectByServiceQuery(service).to[List].transact(session)
}
}
def getAllToggles: IO[List[Toggle]] = {
connectionPool.use { session =>
DoobieQueries.selectAllQuery.to[List].transact(session)
}
}
def deleteToggle(name: String): IO[Unit] = {
connectionPool.use { session =>
DoobieQueries.deleteQuery(name).run.transact(session).map(_ => ())
}
}
}3.4. Invoke database functions
Following is the way to invoke the functions implemented in the ToggleRepoImplDoobie. The DoobieApp which runs as cats-effects IOApp executes the database functions with cats-effects IO.
import cats.effect.{ExitCode, IO, IOApp}
import com.pagero.labs.sqds.config.Config
import com.pagero.labs.sqds.repo.live.ToggleExecImplDoobie
import com.pagero.labs.sqds.model.Toggle
import com.pagero.labs.sqds.conn.ConnectionPoolDoobie
import com.typesafe.scalalogging.LazyLogging
object DoobieApp extends IOApp with LazyLogging {
override def run(args: List[String]): IO[ExitCode] = {
val program: IO[Unit] = for {
config <- Config.load()
connectionPool <- IO(ConnectionPoolDoobie.pool(config.dbConfig))
repo = new ToggleExecImplDoobie(connectionPool)
_ <- repo.init()
toggle = Toggle(service = "doobie", name = "rahasak", value = "labs")
_ <- repo.createToggle(toggle)
search <- repo.getAllToggles
_ <- IO(logger.info(s"toggles $search"))
//_ <- repo.updateToggle("rahasak", "ops")
_ <- repo.deleteToggle("rahasak")
} yield {
()
}
program.as(ExitCode.Success)
}
}4. Skunk
Skunk is purely functional, non-blocking Postgres library for Scala which designed with cats, cats-effect, scodec, and fs2 libraries. Skunk built with native Postgres API without using JDBC. So it has really powerful features set. Since Skunk built with native Postgres APIs, it would be an ideal library to build GraalVM like native compiler-based applications(you can get rid of lot of compiler issues and ignore reflection configs with Skunk on GraalVM).
4.1. Sbt dependency
Following the build.sbt file with the required dependencies of Skunk. It contains dependencies of cats-effect, circe, and skunk-core libraries.
name := "sqds"
description := "skunk, quill, doobie, slick"
libraryDependencies ++= {
lazy val catsVersion = "3.4.8"
lazy val circeVersion = "0.14.1"
Seq(
"org.typelevel" %% "cats-effect" % catsVersion,
"io.circe" %% "circe-core" % circeVersion,
"io.circe" %% "circe-generic" % circeVersion,
"io.circe" %% "circe-config" % "0.8.0",
"org.tpolecat" %% "skunk-core" % "0.5.1"
)
}4.2. Database connection pool
The database connection pool created with Skunk connection pool API(Session.pooled). It returns connection pool wrapped with cats-effects Resource.
import cats.effect.IO
import cats.effect.kernel.Resource
import com.pagero.labs.sqds.config.DbConfig
import natchez.Trace.Implicits.noop
import skunk.Session
object ConnectionPoolSkunk {
def pool(dbConfig: DbConfig): Resource[IO, Resource[IO, Session[IO]]] = {
Session.pooled[IO](
host = dbConfig.host,
port = dbConfig.port,
user = dbConfig.username,
database = dbConfig.database,
password = Option(dbConfig.password),
max = dbConfig.poolSize
)
}
}4.3. Skunk implementation of ToggleRepo
Following is the Skunk implementation of ToggleRepo trait functions. It defines the Postgres table mappings, custom type encoder/decoder functions etc. The connection pool connectionPool: Resource[IO, HikariTransactor[IO]] passed as constructor argument. The connections in the pool obtained with connectionPool.use.
import cats.effect.kernel.Resource
import cats.effect.IO
import com.pagero.labs.sqds.repo.ToggleRepo
import com.pagero.labs.sqds.model
import com.pagero.labs.sqds.model.Toggle
import skunk.codec.all._
import skunk.implicits._
import skunk.{Session, _}
import java.time.LocalDateTime
import java.util.Date
/**
* implement toggles repository functions
* @param connectionPool skunk postgres session pool
*/
class ToggleRepoLiveSkunk(connectionPool: Resource[IO, Resource[IO, Session[IO]]]) extends ToggleRepo {
/**
* toggles timestamp filed in database stored as java.time.LocalDateTime,
* toggles case class timestamp field defined as java.util.Data
* so need function to convert java.util.Date into java.time.LocalDateTime when saving data
* @param date util date
* @return local date
*/
def toLocalDateTime(date: Date) = {
import java.time.{Instant, LocalDateTime, ZoneOffset}
val instant = Instant.ofEpochMilli(date.getTime)
val ldt = LocalDateTime.ofInstant(instant, ZoneOffset.UTC)
ldt
}
/**
* toggles timestamp filed in database stored as java.time.LocalDateTime,
* toggles case class timestamp field defined as java.util.Data
* so need function to convert java.time.LocalDateTime into java.util.Date when quering the data
* @param ldt local date
* @return util date
*/
def toDate(ldt: LocalDateTime) = {
import java.time.ZoneOffset
val instant = ldt.toInstant(ZoneOffset.UTC)
val date = Date.from(instant)
date
}
/**
* decode postgres data types into toggles case class
* convert postgres timestamp into util.date
*/
val toggleDecoder: Decoder[Toggle] =
(uuid ~ varchar(50) ~ text ~ text ~ timestamp).map {
case i ~ n ~ s ~ v ~ t => model.Toggle(Option(i), n, s, v, toDate(t))
}
/**
* encode toggles case class data into postgres data types
* convert toggle case class util.Date into postgres timestamp
*/
val toggleEncoder = (uuid.opt ~ varchar(50) ~ text ~ text ~ timestamp)
.values.contramap((t: Toggle) => t.id ~ t.name ~ t.service ~ t.value ~ toLocalDateTime(t.timestamp))
def init() = {
// define query as skunk command
// query parameters passed as postgres types/codecs
val q: Command[Void] =
sql"""
CREATE TABLE IF NOT EXISTS toggles(
id UUID PRIMARY KEY,
name VARCHAR(50),
service TEXT,
value TEXT,
timestamp TIMESTAMP
);
"""
.command
// execute command directly since nothing returned
connectionPool.use { session =>
session.use(s => s.execute(q))
}
}
override def createToggle(toggle: Toggle): IO[Unit] = {
// define query as skunk command
// use toggle encoder to map toggle case class fields into postgres types
val q: Command[Toggle] =
sql"""
INSERT INTO toggles(id, name, service, value, timestamp)
VALUES $toggleEncoder
"""
.command
// execute command directly since nothing returned
connectionPool.use { session =>
session.use(_.prepare(q).flatMap(_.execute(toggle)).void)
}
}
override def updateToggle(name: String, value: String): IO[Unit] = {
// define query as skunk command
// query parameters passed as postgres types/codecs
val q: Command[String ~ String] =
sql"""
UPDATE toggles
SET value = $text
WHERE name = $text
"""
.command
// execute command directly since nothing returned
connectionPool.use { session =>
session.use(_.prepare(q).flatMap(_.execute(value ~ name)).void)
}
}
override def deleteToggle(name: String): IO[Unit] = {
// define query as skunk command
// query parameters passed as postgres types/codecs
val q: Command[String] =
sql"""
DELETE FROM toggles
WHERE name = $text
"""
.command
// execute command directly since nothing returned
connectionPool.use { session =>
session.use(_.prepare(q).flatMap(_.execute(name)).void)
}
}
override def getToggle(name: String): IO[Option[Toggle]] = {
val q: Query[String, Toggle] = {
// define query as skunk query
// used toggle decoder to decode postgres types into toggle case class
sql"""
SELECT id, name, service, value, timestamp
FROM toggles
WHERE name = $text
LIMIT 1
"""
.query(toggleDecoder)
}
// create prepared statement with binding query parameters
// execute query as fs2 stream
// chunk size defines number of rows need to be fetched at once
// getting a single row or throw error if not exists
connectionPool.use { session =>
session.use(_.prepare(q).flatMap { ps =>
ps.stream(name, 64)
.compile
.last
})
}
}
override def getServiceToggles(service: String): IO[List[Toggle]] = {
// define query as skunk query
// used toggle decoder to decode postgres types into toggle case class
val q: Query[String, Toggle] =
sql"""
SELECT id, name, service, value, timestamp
FROM toggles
WHERE service = $text
"""
.query(toggleDecoder)
// create prepared statement with binding query parameters
// execute query as fs2 stream
// chunk size defines number of rows need to be fetched at once
// getting list rows
connectionPool.use { session =>
session.use(_.prepare(q).flatMap { ps =>
ps.stream(service, 32)
.compile
.toList
})
}
}
override def getAllToggles: IO[List[Toggle]] = {
// define query as skunk query
// used toggle decoder to decode postgres types into toggle case class
val q: Query[Void, Toggle] =
sql"""
SELECT id, name, service, value, timestamp
FROM toggles
"""
.query(toggleDecoder)
// execute query directly with session
connectionPool.use { session =>
session.use(_.execute(q))
}
}
}4.4 Invoke database functions
Following is the way to invoke the functions implemented in the ToggleRepoImplSkunk. The SkunkApp which runs as cats-effects IOApp executes the database functions with cats-effects IO.
import cats.effect.{ExitCode, IO, IOApp}
import com.pagero.labs.sqds.config.Config
import com.pagero.labs.sqds.repo.live.ToggleRepoLiveSkunk
import com.pagero.labs.sqds.model.Toggle
import com.pagero.labs.sqds.conn.ConnectionPoolSkunk
import com.typesafe.scalalogging.LazyLogging
object SkunkApp extends IOApp with LazyLogging {
override def run(args: List[String]): IO[ExitCode] = {
val program: IO[Unit] = for {
config <- Config.load()
connectionPool <- IO(ConnectionPoolSkunk.pool(config.dbConfig))
repo = new ToggleRepoLiveSkunk(connectionPool)
_ <- repo.init()
toggle = Toggle(service = "skunk1", name = "lambda", value = "labs")
_ <- repo.createToggle(toggle)
//_ <- repo.updateToggle("rahasak", "ops")
search <- repo.getServiceToggles("skunk1")
//search <- repo.getAllToggles
_ <- IO(logger.info(s"toggles $search"))
_ <- repo.deleteToggle("lambda")
} yield {
()
}
program.as(ExitCode.Success)
}
}Reference
- https://softwaremill.com/comparing-scala-relational-database-access-libraries/
- https://blog.rockthejvm.com/doobie/
- https://medium.com/rahasak/scala-cockroachdb-postgres-client-with-skunk-622423a29840
- https://medium.com/rahasak/hacking-with-http4s-doobie-and-cats-effects-4fc54068ea10
- https://medium.com/rahasak/ssl-enabled-cockroachdb-scala-client-with-slick-63715072310
