JDBC is back, Doobie is gone - Feb 2021

TL;DR

Pending Scala 3 release, the choice for the next service is:

Http4s, Circe, jdbc.

Dropping doobie

Given my one year dive into Cats and Doobie and Http4s, and that it is all in production and works… time to change my choices.

IO{} is simply a bad name for a wrapper around a callback. Much like Monad is a bad name for convertElements (or something similar). Not only that but you lose the stack and have to train the entire team up to understand non software terminology.

Basically, it isn’t worth it. The code is not simpler, it’s harder to understand.

Dropping cats

If we drop Doobie do we drop Cats - well as much as possible. But Circe is great. So we are keeping Cats in the project. Just not using it unless it’s used by a library.

Dropping Http4s?

This is tricky. Akka actors are overkill and have a large learning curve as well. Given microservices or Lambdas we do not need to do any of that stuff, it is far too easy to write your own without learning Akka.

If you remove Actors from Akka you are left with a smallish rest server, but the spray-json is rubbish compared with http4s json. Also Rho swagger support in http4s is better than akka swagger support - less boiler plate.

Keeping Http4s!

So, we like http4s, we like circe, and we like Rho. In the past we tried Slick and we looked at Quill, but basic JDBC with a few helper classes remains the most transparent code - and it’s easy to get to the Oracle batch and transactions if we need to do things that the Database frameworks don’t provide.

2021 Scala microservices

Pending Scala 3 release, the choice for the next service is:

Http4s, Circe, jdbc.

What does the DAO look like

Well, I don’t need a factory of factories or any of that 1998 overkill.

So my database helper could be:

case class DbCon(hikaryConfig:HikariConfig) extends LazyLogging {
  val ds = new HikariDataSource(hikaryConfig)
  sys.addShutdownHook(ds.close())

  def open(): Try[Connection] = Try( ds.getConnection())
  def close(con:Connection) :Unit = con.close()

  def using[A](debugStr:String, f:Connection=>A):Try[A] = {
    Try{
      open() match {
        case Success(con) =>
          try {
            val t0 = System.currentTimeMillis()
            val ret = f(con)
            logger.debug(s"$debugStr completed ${System.currentTimeMillis() - t0} ms")
            ret
          } catch {
            case e: Exception =>
              logger.error(s"$debugStr Failed to execute DB operation", e)
              throw e
          } finally {
            close(con)
          }
        case Failure(e) =>
          logger.error(s"$debugStr Could not get db connection, please abort", e)
          throw e
      }
    }
  }
}

and then I can bootstrap the Hikari pool by having a section of application.conf containing string properties, which I can change to HikariConfig like this:

def toProperties(config: Config): HikariConfig = {
  val properties = new Properties()
  config.entrySet.forEach((e) => properties.setProperty(e.getKey, config.getString(e.getKey)))
  new HikariConfig(properties)
}

ie application.conf is

// db is done as Hikari properies - which is why they are all string
db {
    hikariProperties {
          jdbcUrl : "jdbc:postgresql://localhost:5432/kow"
          username: "postgres"
          password: "mypassword"
    }
}

Then the DAO can have a case class which has DbCon but a companion which takes a Connection parameter - this allows my functions in the companion to be composable within my own transaction boundaries, much like a ConnectionIO in Doobie, but simpler, and I keep the stack trace.

case class GameMapDesignDao(dbCon: DbCon) {
  def findById(id: Long): Try[Option[GameMapDesignDbDto]] =
    dbCon.using(s"Find by id [$id]", con => GameMapDesignDao.findById(con, id))
}

object GameMapDesignDao extends LazyLogging {
  val CommonColumns = "MAPNAME, JSON, STATUS, VERSION, CREATED_AT, CREATED_BY, UPDATED_AT, UPDATED_BY"

  def findById(con: Connection, id: Long): Option[GameMapDesignDbDto] = {
    val ps = con.prepareStatement(s"SELECT ID, $CommonColumns FROM GAME_MAP_DESIGN WHERE ID=?")
    ps.setLong(1, id)
    executeQuery(ps)
  }

  // We will NOT write loads of functional helpers, as we prefer easy to read
  // and exposing JDBC to writing our own in house framework which new devs have to learn.
  private def executeQuery(ps: PreparedStatement): Option[GameMapDesignDbDto] = {
    val rs = ps.executeQuery()
    val ret = if (rs.next) Some(GameMapDesignDbDto(rs)) else None
    rs.close()
    ps.close()
    logPs(ps, s", returned $ret")
    ret
  }
}

You will notice that I wanted statement logging with some helpers - logPs, and because I am using postgres I am lucky the prepared statement will render to a string, if you are using Oracle you have to write a helper to generate the sql, or use another library.

The closing of result sets and prepared statements is really optional as the connection close will close them down. But since we want our DAO functions to be composable it is probably worth closing them on the green path. Exceptions can still close them right away - Which is done in the using clause.

import java.sql.PreparedStatement
import com.typesafe.scalalogging.LazyLogging

package object db extends LazyLogging {
  def logPs(ps: PreparedStatement): Unit = logPs("", ps, "")

  def logPs(ps: PreparedStatement, postFix: String): Unit = logPs("", ps, postFix)

  def logPs(prefix: String, ps: PreparedStatement, postfix: String): Unit =
    logger.debug(s"Query $prefix: ${ps.toString.replace("\r\n", "").replace("\n","")}$postfix")
}

Whenever you blog with code it looks like loads of code, but it isn’t. The Doobie code is about the same size as the JDBC code, with no need for IO or ConnectionIO stuff.

Obviously if you are writing CRUD operations for tens of tables this will create tens of DAOs which means lots of typing - or you write a code generator from the table definitions. Either way, because the code is SIMPLE, after a couple of days of typing you are done, and the new team members can easily understand the DB layer.

But how?

So the missing part, is how to invoke non cats code from the RhoRoutes? Well, say each route delegates to a handler function, and those now are just Scala which return Try[T]. You can do this IO.fromTry, so your RhoRoutes could be:

class KowRhoRoutes(handler: KowHandler) extends RhoRoutes[IO] {
  private def internalServerError(e: Throwable, uri: String) = {
    val tstamp = System.currentTimeMillis()
    logger.error(e)(s"[ID: $tstamp] FAILED $uri")
    InternalServerError("Internal Server Error, please try again later. [ID: $tstamp]")
  }

  private def logReq(req: Request[IO], debugStr: String = ""): String = {
    val uri = s"${req.method} - ${req.uri} $debugStr"
    logger.info(s"Received $uri")
    uri
  }

  private [kowms]def postMap(req: Request[IO], map: ClientMapDesignDto) = {
    val uri = logReq(req)
    IO.fromTry(handler.createMapDesign(map))
      .flatMap(n => Ok(GameMapDesignCodec.convDbToClient(n)))
      .handleErrorWith (internalServerError(_, uri))
  }
}

Two helper functions and then a post handler, taking in client facing JSON Dto’s which are converted to database Dtos. I always separate the client Json from the Database objects, because if you change the database you do not want to have to change all the clients.

This class is where cats ends and the sever side code begins. The server side is now simple scala with normal spec tests and so on, no cats to be seen anywhere.