How I can insert case object as JSONB format via Doobie?

Question

I use scala 2.13 and doobie 0.12.1

For example, I have case class

case class UserInfo(name: String, age: Int, hobbies: Vector[String])

I want insert user info in column info as jsonb

sql"""
        INSERT INTO users(
            id,
            info
            created_at,
        ) values (
            ${id},
            ${userInfo},
            ${createdAt},
        )
      """.update.run.transact(t)

In my DAO I have implicit val

implicit val JsonbMeta: Meta[Json] = Meta
.Advanced.other[PGobject]("jsonb")
.timap[Json](jsonStr => parser.parse(jsonStr.getValue).leftMap[Json](err => throw err).merge)(json => {
  val o = new PGobject
  o.setType("jsonb")
  o.setValue(json.noSpaces)
  o
})

But I have compile exception

found   : ***.****.UserInfo
   [error]  required: doobie.syntax.SqlInterpolator.SingleFragment[_]; incompatible interpolation method sql
    [error]       sql"""
    [error]       ^

score 6 · Accepted Answer · answered Jun 02 '21 at 19:46

The doobie-postgres-circe module provides pgEncoderPut and pgDecoderGet. With these, and an implicit circe Encoder and Decoder in scope, you can create a Meta[UserInfo]. Then your example insert should work.

Example usage:

// Given encoder & decoder (or you could import io.circe.generic.auto._)
implicit encoder: io.circe.Encoder[UserInfo] = ???
implicit decoder: io.circe.Decoder[UserInfo] = ???

import doobie.postgres.circe.jsonb.implicits.{pgDecoderGet, pgEncoderPut}

implicit val meta: Meta[UserInfo] = new Meta(pgDecoderGet, pgEncoderPut)

Thank you! This is the most correct solution for me! – John Jun 03 '21 at 08:16 — John, Jun 03 '21 at 08:16

Michał Pawlicki · Answer 2 · 2021-07-01T17:37:41.923

2

You have defined a Meta for type Json, but it looks like you're using an instance of UserInfo in the interpolated string. Try converting the object to Json and passing it to sql:

// This assumes you're using Circe as your JSON library
import io.circe._, io.circe.generic.semiauto._, io.circe.syntax._

implicit val userInfoEncoder: Encoder[UserInfo] = deriveEncoder[UserInfo]

val userInfo: UserInfo = UserInfo("John", 50, Vector("Scala"))
val userInfoJson: Json = userInfo.asJson // requires Encoder[UserInfo]

// and then, assuming that an implicit Meta[Json] is in scope
sql"""INSERT INTO users(
            id,
            info
            created_at,
        ) values (
            ${id},
            ${userInfoJson}, -- instance of Json here
            ${createdAt},
        )"""

edited Jul 01 '21 at 17:37

answered Jun 01 '21 at 20:00

Michał Pawlicki

371
2
6

I am getting below error when trying to insert circe `Json` type to postgres `json` column. Any ideas? ```[error] found : io.circe.Json [error] required: doobie.syntax.SqlInterpolator.SingleFragment[_]``` – Jeet Banerjee Jul 01 '21 at 16:52
My code snippet is incomplete, it assumes that an implicit instance of `Meta[Json]` is in scope. Does it help if you define an implicit `Meta[Json]` like in the question? Also, you may consider the approach described in the accepted answer, it requires less boilerplate than mine. – Michał Pawlicki Jul 01 '21 at 17:30
I do have a `Meta[Json]` in implicit scope. I tried the accepted answer but still getting the same error. – Jeet Banerjee Jul 02 '21 at 10:01
Ok, I got it working. It needed both implicit `Meta[Json]` and `Meta[UserInfo]` in scope for doobie to recognize the json mapping. – Jeet Banerjee Jul 02 '21 at 10:31

How I can insert case object as JSONB format via Doobie?

2 Answers2