How to insert a linked object using Doobie

Question

I need to insert into the database an object of the type Entity

case class Entity(id: Long, name: String)
case class OtherEntity(id: Long, entity_id: Long, info: String)
case class AnotherEntity(other_entity_id: Long, data: String)

How can I do this if at the input I receive somewhere about

{
    "name": "string",
    "data": [
        {
            "info": "string",
            "data": [
                {
                    "data": "string"
                }       
            ]
        }
    ]
}

The main problem is that I cannot think of an analogue foreach for doobie.ConnectioIO.

sql"insert into entity (name) values('name')"
    .update.withUniqueGeneratedKeys[Long]("id")
.flatmap(entityId => 
    sql"insert into other_entity (entity_id, info) values ($entityId, 'info')"
        .update.withUniqueGeneratedKeys[Long]("id")
).flatmap(otherEntityId => 
    sql"insert into another_entity (other_entity_id, data) values ($otherEntityId, 'data')"
        .update.run
)

But this only works for one-to-one relationships. Thank you for your help.

Answer 1

You can chain together multiple inserts for the same foreign key. I.e. if you have a List of "infos" for every "name", you can traverse over that list to give you back a ConnectionIO[List[_]]. Or just a ConnectionIO[Unit] if you use traverse_.

import doobie.implicits._
import cats.implicits._

sql"insert into entity (name) values('name')"
  .update.withUniqueGeneratedKeys[Long]("id")
  .flatMap{ entityId => 
    val infos: List[String] = ???
    infos.traverse_{ info =>
      sql"insert into other_entity (entity_id, info) values ($entityId, $info)"
        .update.withUniqueGeneratedKeys[Long]("id")
        .flatMap{ otherEntityId =>
          val datas: List[String] = ???
          datas.traverse_{ data =>
            sql"insert into another_entity (other_entity_id, data) values ($otherEntityId, $data)"
              .update.run
          }
        }
    }
  }