Quill JSON Play support

codecs.scala

package app.db

import java.sql.Types
import java.time.LocalDate
import java.util.TimeZone

import app.config.{ABTestSegmentNumber, ConcreteShopOffers, LevelRewardId, LevelRewardIds, MaxChestKeys, PickedDailyQuests, PromoCode}
import app.data._
import app.db.Table.HardCurrencyPurchaseTarget
import app.db.Table.Users.{MatchDataDB, SpecialOfferIds, WheelOfFortuneLastWins}
import app.utils.{ServerDate, ServerDateTime}
import app.verticles.ReplayRecorder.ReplayId
import app.verticles.http.Routes.CollectFeedback.Rating
import app.verticles.http.Routes.MatchFinished.MatchResults
import discord4j.core.`object`.util.Snowflake
import org.postgresql.util.{PGInterval, PGobject}

import scala.concurrent.duration._

// Sometimes quill (or scala compiler more precisely) fails to derive encoders and decoders
// of more complex data structures in usage site. This trait provides instances for such codecs.
trait Codecs { self: MyDBContext =>
  implicit val MatchDataDBEncoder: Encoder[MatchDataDB] = jsEncoder
  implicit val MatchDataDBDecoder: Decoder[MatchDataDB] = jsDecoder

  implicit val WheelOfFortuneLastWinsEncoder: Encoder[WheelOfFortuneLastWins] = jsEncoder
  implicit val WheelOfFortuneLastWinsDecoder: Decoder[WheelOfFortuneLastWins] = jsDecoder

  implicit val MatchDataDBOptionEncoder: Encoder[Option[MatchDataDB]] = jsOptEncoder
  implicit val MatchDataDBOptionDecoder: Decoder[Option[MatchDataDB]] = jsOptDecoder
}

jsonb.scala

package app.db

import app.OptionExts
import app.ResultSetExts
import org.postgresql.util.PGobject
import play.api.libs.json._

trait JsonB { self: MyDBContext =>
  def jsEncoder[A](implicit w: Writes[A]): Encoder[A] = encoder[A](
    java.sql.Types.OTHER,
    (index: Index, value: A, row: PrepareRow) =>
      row.setObject(index, writeJS(value), java.sql.Types.OTHER)
  )

  def jsOptEncoder[A](implicit w: Writes[A]): Encoder[Option[A]] = encoder[Option[A]](
    java.sql.Types.OTHER,
    (index: Index, maybeValue: Option[A], row: PrepareRow) => {
      val json = maybeValue.fold2(null, a => writeJS(a)(w))
      row.setObject(index, json, java.sql.Types.OTHER)
    }
  )

  def jsDecoder[A](implicit r: Reads[A]): Decoder[A] =
    decoder[A] { row: ResultRow => index: Index =>
      val pgObj = row.getObject(index).asInstanceOf[PGobject]
      if (pgObj == null) throw new Exception(s"JSON is null for column $index in ${row.asString}")
      parseJS(pgObj)
    }

  def jsOptDecoder[A](implicit r: Reads[A]): Decoder[Option[A]] =
    decoder[Option[A]] { row: ResultRow => index: Index =>
      val pgObj = row.getObject(index).asInstanceOf[PGobject]
      if (pgObj == null) None
      else Some(parseJS(pgObj))
    }

  private[this] def writeJS[A](a: A)(implicit w: Writes[A])= Json.stringify(w.writes(a))

  private[this] def parseJS[A](pgObj: PGobject)(implicit r: Reads[A]) = {
    val js = Json.parse(pgObj.getValue)
    r.reads(js) match {
      case JsSuccess(value, _) => value
      case err: JsError => throw new Exception(s"Can't parse JSON: ${JsError.toJson(err)}")
    }
  }
}

MyDBContext.scala

class MyDBContext(
  dataSource: DataSource with Closeable,
  ec: ExecutionContextWithExecutor
) extends PostgresJdbcContext(SnakeCase, dataSource)
  with Queries with Quotes with JsonB with Codecs