Handling Postgres json datatype in slick, scala

Question

I have a Postgres 'json' column in my schema. This is my column mapping in my code:

def my_col = column[Option[String]]("my_col")

Which doesn't work, the stack trace I get when inserting says:

column "my_col" is of type json but expression is of type character varying

I tried this too:

def my_col = column[Option[String]]("my_col", O.SqlType("json"))

Which also yields the same error.

Answer 1

So I found the the slick-pg solution to be the better way to get this to work. If all you need is JSON support in your code, i.e. you have a DB column that you simply want to represent as a Play JsValue while using Postgres' JSON column type, you just need to write a profile that mixes in the appropriate functionality from the slick-pg package.

Step 1: Add the necessary dependences

libraryDependencies += "com.github.tminglei" %% "slick-pg" % "0.18.0"
libraryDependencies += "com.github.tminglei" %% "slick-pg_play-json" % "0.18.0"

Step 2: Write a profile class

import com.github.tminglei.slickpg._
import slick.basic.Capability
import slick.jdbc.JdbcCapabilities

trait PostgresProfile extends ExPostgresProfile with PgPlayJsonSupport {
  def pgjson = "jsonb"

  override protected def computeCapabilities: Set[Capability] =
    super.computeCapabilities + JdbcCapabilities.insertOrUpdate

  override val api = PostgresJsonSupportAPI

  object PostgresJsonSupportAPI extends API with JsonImplicits
}

object PostgresProfile extends PostgresProfile

Step 3: Use your new profile class

So you'll need to extend HasDatabaseConfigProvider but parameterize it with the trait that you defined in the step #2 above, e.g.

class MyEntityRepository @Inject() (
  protected val dbConfigProvider: DatabaseConfigProvider
  ... // plus whatever other components you need, probably an ExecutionContext
) extends HasDatabaseConfigProvider[PostgresProfile] {

  import PostgresProfile.api._
...
}

Define your column

This is the easy part. In the above class, MyEntityRepository write a private or package private Slick class that extends Table and simply define your column like this:

def someColumnName = column[JsValue]("some_column_name")

And that's it!