Support Json & XML Column Deserialization

[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