How do I create a Doobie Meta instance for MySQL using Play JSON?

Question

How do I implement basic serialization/deserialization at the query level using Doobie, MySQL, and Play JSON?

Answer 1

After scouring the internet for an answer regarding JSON specific type support within MySQL and JDBC, it seems String is all we have available (please chime in and correct me if this is wrong). IMO, the Doobie doc doesn't make this answer obvious, since the example provided uses a more rigorous example via PostGRES. Unfortunately, MySQL is anything but rigorous, but that's for another conversation.

For now, here's a simple example of a solution, this also assumes Play JSON Readers/Writers are in scope:

import doobie.util.meta.Meta
import play.api.libs.json._

def playJsonMeta[A: Reads: Writes]: Meta[A] = Meta[String].xmap[A](
  Json.parse(_).as[A],
  s => Json.stringify(Json.toJson(s))
)

implicit val fooMeta: Meta[Foo] = playJsonMeta[Foo]

Do note, this will throw an exception if deserialization fails.