Support Json & XML Column Deserialization

[AugustNagro]

No description provided.

[guizmaii]

What is missing for JSON support?

[AugustNagro]

Hey, sorry I missed your response @guizmaii.

What is missing for JSON support?

I hope we can discover that as part of this issue. I haven't had the chance to use this library yet with jsonb / xml columns.

Ideally, we'd add some new test cases with Json / XML columns, and see what the limitations are.

Maybe we'll end up with the ability to write a table like

create table my_table (
  id bigint primary key,
  content jsonb not null
);

And entity class something like

import com.augustnagro.magnum.pg.json.circe.CirceDbCodec

case class MyContent(a: A, b: B) derives CirceDbCodec
object MyContent:
  given circeCodec = ...

@Table(PostgresDbType, SqlNameMapper.CamelToSnakeCase)
case class MyTable(
  @Id id: Long,
  content: MyContent
) derives DbCodec

The idea being that when you call myTableRepo.findById(x), it deserializes content into a PgObject, get's the value, deserializes it with the user's Circe codec, and puts it into the MyTable result.

[jivanic-demystdata]

@AugustNagro What do you think of these implementation to support JSON and JSONB, it's inspired by how Quill is doing it.

(I'm using zio-json JsonCodec)

/**
 * Adds support to JSON columns type to Magnum
 */
final case class Json[A](content: A)
object Json {
  given [A: JsonCodec]: DbCodec[Json[A]] =
    new DbCodec[Json[A]] {
      override val queryRepr: String = "?::json"
      override val cols: IArray[Int] = IArray(Types.JAVA_OBJECT)

      override def readSingle(resultSet: ResultSet, pos: Int): Json[A] = {
        val rawJson: String = resultSet.getString(pos)

        if (rawJson eq null) null
        else {
          val decoded: A =
            JsonCodec[A].decoder
              .decodeJson(rawJson)
              .fold(e => throw ShouldNeverHappenException(s"Failed to decode JSON.\n\tError: '$e'.\n\tJson:$rawJson"), identity)

          Json(content = decoded)
        }
      }

      def writeSingle(entity: Json[A], ps: PreparedStatement, pos: Int): Unit = {
        val jsonObject = PGobject()
        jsonObject.setType("json")
        jsonObject.setValue(JsonCodec[A].encoder.encodeJson(entity.content).toString)
        ps.setObject(pos, jsonObject)
      }
    }
}

/**
 * Adds support to JSONB columns type to Magnum
 */
final case class JsonB[A](content: A)
object JsonB {
  given [A: JsonCodec]: DbCodec[JsonB[A]] =
    new DbCodec[JsonB[A]] {
      override val queryRepr: String = "?::jsonb"
      override val cols: IArray[Int] = IArray(Types.JAVA_OBJECT)

      override def readSingle(resultSet: ResultSet, pos: Int): JsonB[A] = {
        val rawJson: String = resultSet.getString(pos)

        if (rawJson eq null) null
        else {
          val decoded: A =
            JsonCodec[A].decoder
              .decodeJson(rawJson)
              .fold(e => throw ShouldNeverHappenException(s"Failed to decode JSON.\n\tError: '$e'.\n\tJson:$rawJson"), identity)

          JsonB(content = decoded)
        }
      }

      def writeSingle(entity: JsonB[A], ps: PreparedStatement, pos: Int): Unit = {
        val jsonObject = PGobject()
        jsonObject.setType("jsonb")
        jsonObject.setValue(JsonCodec[A].encoder.encodeJson(entity.content).toString)
        ps.setObject(pos, jsonObject)
      }
    }
}

Usage:

@Table(PostgresDbType, SqlNameMapper.CamelToSnakeCase)
case class MyTable(
  @Id id: Long,
  content: JsonB[MyContent]
) derives DbCodec

FYI, I tried to make the Json and JsonB case classes extends AnyVal but it generates this compilation error:

bridge generated for member method readSingle(resultSet: java.sql.ResultSet, pos: Int):
  com.myorg.myapp.db.domain.types.JsonB[A] in anonymous class Object with com.augustnagro.magnum.DbCodec {...}
which overrides method readSingle(resultSet: java.sql.ResultSet, pos: Int): E in trait DbCodec
clashes with definition of the member itself; both have erased type (resultSet: java.sql.ResultSet, pos: Int): Object." [41:13]

Asked the question in the Scala discord and it seems Scala struggles to know something: See https://discord.com/channels/632150470000902164/632150470000902166/1277810459776385118

[AugustNagro]

Yes looks excellent, thanks for sharing.

I think with a few changes we can make it fully generic, and maybe remove the need for the Json[T] and JsonB[T] marker types. Let me take a stab and I'll share a wip MR.

[AugustNagro]

Hey @jivanic-demystdata please review when you get a chance:

#49

I updated the readme with an example using Play Json:

trait PlayJsonDbCodec[A] extends JsonDbCodec[A]
object PlayJsonDbCodec:
  def derived[A](using jsonCodec: OFormat[A]): PlayJsonDbCodec[A] = new:
    def encode(a: A): String = jsonCodec.writes(a).toString
    def decode(json: String): A = jsonCodec.reads(Json.parse(json)).get

case class LastService(mechanic: String, date: LocalDate) derives PlayJsonDbCodec
object LastService:
  given OFormat[LastService] = Json.format[LastService]

@Table(PostgresDbType, SqlNameMapper.CamelToSnakeCase)
case class Car(
    @Id id: Long,
    lastService: LastService
) derives DbCodec

Lmk what you think of the approach. I still need to add some tests / resolve todos.

Also, I'm curious why your codec uses the queryExprs of ?::json and ?::jsonb since it seemed to work fine in the simple insert/read tests with just ?.

[guizmaii]

Also, I'm curious why your codec uses the queryExprs of ?::json and ?::jsonb since it seemed to work fine in the simple insert/read tests with just ?.

I have no idea myself. I tried to follow what I've read on internet, I guess. If it works without that's great :)