ClickHouse with PlayFramework, Slick, and Evolutions

I wanted to show you how to add another database in PlayFramework that uses Slick and Evolutions. That database is ClickHouse, which also requires a little bit of special handling. In the end, it works out quite cleanly.

If you haven't set up ClickHouse locally, you can follow the instructions here: https://tanin.nanakorn.com/set-up-local-clickhouse-on-mac-for-development/

First of all, you will need to include the ClickHouse JDBC in your dependencies:

libraryDependencies += "com.clickhouse" % "jdbc-v2" % "0.9.3"

Next, you'll set up another database in your application.conf first:

slick.dbs {
  default {
    profile= "slick.jdbc.PostgresProfile$"
    db.dataSourceClass = "slick.jdbc.DatabaseUrlDataSource"
    db.properties.driver="org.postgresql.Driver"
    db.properties.url="postgres://test_user:dev@localhost:5432/test_dev"
  }

  clickHouse {
    profile= "framework.ClickHouseJdbcProfile$"
    db.dataSourceClass = "slick.jdbc.DatabaseUrlDataSource"
    db.properties.driver="com.clickhouse.jdbc.Driver"
    db.properties {
      url="jdbc:ch://localhost:8123"
      user="test_user"
      password="dev"
    }
  }
}

play.evolutions.enabled = true
play.evolutions.useLocks = true
play.evolutions.autocommit = false
play.evolutions.db.clickHouse.enabled = true
# ClickHouse doesn't support setting autocommit to false.
play.evolutions.db.clickHouse.autocommit = true
# ClickHouse doesn't support transaction. Locks use transaction.
play.evolutions.db.clickHouse.useLocks = false

You will have to enable autoCommit and disable useLocks as shown above because ClickHouse doesn't support the concept of transaction.

Notice how we use profile= "framework.ClickHouseJdbcProfile$". Therefore, we should make a basic JDBC profile for ClickHouse:

package framework

import slick.jdbc.JdbcActionComponent

object ClickHouseJdbcProfile extends slick.jdbc.JdbcProfile with JdbcActionComponent.MultipleRowsPerStatementSupport

For now, a basic JDBC profile seems to work. In the future, we can expand it to support more data types that are unique to ClickHouse.

Then, you need to set up Evolutions folders for both default and clickHouse as shown below:

./conf/evoluations/default/1.sql
./conf/evoluations/clickHouse/1.sql

Then, in your ./conf/evoluations/clickHouse/1.sql, you will need to add these 2 lines:

-- !Ups

-- Enable update for the play_evolutions table.
ALTER TABLE play_evolutions MODIFY SETTING enable_block_number_column = 1;
ALTER TABLE play_evolutions MODIFY SETTING enable_block_offset_column = 1;

CREATE TABLE some_object
(
    id       String,
    raw_json String
)
ENGINE = ReplacingMergeTree()
ORDER BY id
PRIMARY KEY id

-- !Downs

DROP TABLE some_object;

This is because, by default, ClickHouse doesn't support "light update". Enabling enable_block_number_column and enable_block_offset_column enables "light update". Play Evolutions needs to update the table play_evolutions when running an evolution.

ClickHouse is now ready to use with Slick. You can do things as usual but remember: when injecting DatabaseConfigProvider, you will have to annotate it with: @NamedDatabase("clickHouse").

Here's an example:

package clickhouse.models

import framework.Jsonable
import framework.PostgresProfile.api.*
import play.api.libs.json.{JsObject, Json}
import slick.lifted.{ProvenShape, Rep}

case class SomeObject(
  id: String,
  rawJson: String
)

class SomeObjectTable(tag: Tag) extends Table[SomeObject](tag, "some_object") {
  def id: Rep[String] = column[String]("id")
  def rawJson: Rep[String] = column[String]("raw_json")

  def * : ProvenShape[RawStripeObject] = (
    id,
    rawJson
  ).<>((RawStripeObject.apply _).tupled, RawStripeObject.unapply)
}

@Singleton
class RawStripeObjectService @Inject() (
  @NamedDatabase("clickHouse")
  val dbConfigProvider: DatabaseConfigProvider,
  config: PlayConfig
)(implicit ec: ExecutionContext) extends HasDatabaseConfigProvider[JdbcProfile] {
  import framework.ClickHouseJdbcProfile.api.*

  val query = TableQuery[RawStripeObjectTable]

  def getAll(): Future[Seq[SomeObject]] = {
    db.run {
      query.result
    }
  }
}

And that's it!