Doobie update and insert case class syntax

Question

Doobie can select * with a case class for convenient and correct parameter passing, but I don't see how to work in a similar way with update and insert.

For example, given a case class like this:

case class Course(
  sku: String,
  title: String,
  id: Id,
  price: Int,
  instructorid: Id,
  groupid: Id,
  shortdescription: String = "",
  transcript: String = "",
  project_home: String = "",
  repository: String = "",
  category: String = "",
  image: String = "",
  privacy: String = "",
  language: String = "",
  keywords: String = "",
  goals: String = "",
  instructionallevel: String = "",
  audience: String = "",
  studenttasks: String =  "",
  sections: String = "",
  active: Boolean = true,
  video: String = "",
  paypal_button_id: String = "",
  prerequisite_ids: String = ""
)

I can nicely select records. This nice syntax is possible because Doobie iterates through the Course case class properties and assigns values to them by matching their names to the courses database record fields:

    def find(id: Id): Option[Course] =
      sql"select * from courses where id = $id"
        .query[Course]
        .option
        .transact(SQLSupport.xa)
        .unsafeRunSync

However insert requires all of the case class properties to be manually listed, and matched up with values, which is horrible and error-prone:

    /** @return saved Course with new Id */
    def save(course: Course): Course = {
      val insert: doobie.ConnectionIO[Course] = sql"""insert into courses (
          sku,
          title,
          price,
          instructorid,
          groupid,
          shortdescription,
          transcript,
          project_home,
          repository,
          category,
          image,
          privacy,
          language,
          keywords,
          goals,
          instructionallevel,
          audience,
          studenttasks,
          sections,
          active,
          video,
          paypal_button_id,
          prerequisite_ids
        ) values (
          ${ course.sku },
          ${ course.title },
          ${ course.price },
          ${ course.instructorid },
          ${ course.groupid },
          ${ course.shortdescription },
          ${ course.transcript },
          ${ course.project_home },
          ${ course.repository },
          ${ course.category },
          ${ course.image },
          ${ course.privacy },
          ${ course.language },
          ${ course.keywords },
          ${ course.goals },
          ${ course.instructionallevel },
          ${ course.audience },
          ${ course.studenttasks },
          ${ course.sections },
          ${ course.active },
          ${ course.video },
          ${ course.paypal_button_id },
          ${ course.prerequisite_ids }
        )"""
        .update
        .withUniqueGeneratedKeys("id")
      val newCourse: Course = insert.transact(SQLSupport.xa).unsafeRunSync
      newCourse
    }

Also update is similarly horrible:

    /** @return updated Course, which should be identical to the given course */
    def update(course: Course): Course = {
      val update: doobie.ConnectionIO[Course] = sql"""update courses set
          sku = ${ course.sku },
          title = ${ course.title },
          id = ${ course.id },
          price = ${ course.price },
          instructorid = ${ course.instructorid },
          groupid = ${ course.groupid },
          shortdescription = ${ course.shortdescription },
          transcript = ${ course.transcript },
          project_home = ${ course.project_home },
          repository = ${ course.repository },
          category = ${ course.category },
          image = ${ course.image },
          privacy = ${ course.privacy },
          language = ${ course.language },
          keywords = ${ course.keywords },
          goals = ${ course.goals },
          instructionallevel = ${ course.instructionallevel },
          audience = ${ course.audience },
          studenttasks = ${ course.studenttasks },
          sections = ${ course.sections },
          active = ${ course.active },
          video = ${ course.video },
          paypal_button_id = ${ course.paypal_button_id },
          prerequisite_ids = ${ course.prerequisite_ids }
        where id = ${ course.id }"""
        .update
        .withUniqueGeneratedKeys("id")
      val modifiedCourse: Course = update.transact(SQLSupport.xa).unsafeRunSync
      modifiedCourse
    }

Is there a better way?

It seems there is a `Write` typeclass designed for this purpose: https://tpolecat.github.io/doobie/docs/12-Custom-Mappings.html#column-vector-mappings but I can't find how to use it... — M. Karassev, Sep 30 '21 at 15:47
These issues seem related: https://github.com/tpolecat/doobie/issues/1328 and https://github.com/tpolecat/doobie/issues/902 — M. Karassev, Sep 30 '21 at 15:57

David Corral · Accepted Answer · 2022-02-24T15:05:04.010

Doobie documentation is quite great, but sometimes you might find yourself in some scenarios that are not directly explained in their docs.

In order to insert directly a case class object (not their attributes), you must define a Write[A] which will tells Doobie how the data must be inserted. This is used when the mapping of the attributes, in the case class, are slightly different from the ones in the database table.

Imagine the following case class:

case class Course (id: UUID, name: String, year: Int)

In this case, we need to define a Write[Course] for doobie, which would be:

// Scala 3:
given Write[Course] = Write[(UUID, String, Int)].contramap(c => (c.id, c.name, c.year))

// Scala 2:
implicit val writer : Write[Course] = Write[(UUID, String, Int)].contramap(c => (c.id, c.name, c.year))

So now, you can run your Update and Doobie will know how to map your columns:

def insertCourse(course: Course): Update0 =
    sql"""INSERT INTO courses (id, name, year) VALUES ($course)""".update

Also, you might need these imports:

import doobie.implicits.*
import doobie.implicits.javasql.*
import doobie.postgres.implicits.*
import doobie.*

If your case class attributes and their types exactly matches with those specified in the table of the database, you don't need to manually specify the Writer[Course] because Doobie will automatically derivate it for you [1] and this should work for you:

case class Course (id: UUID, name: String, year: Int)

def insertCourse(course: Course): Update0 =
  sql"""INSERT INTO courses (id, name, year) VALUES ($course)""".update

Credits to my partner Y.C. that helped me to resolve this issue!

Thanks for helping out. This code shows the `writer`'s properties listed out manually. This gets tedious fast. Is there not a way that the public properties of a case class be persisted by default? ... even better, automagic property enumeration for Reader and Writer? — Mike Slinn, Feb 24 '22 at 12:07
@MikeSlinn I just tried again the same code, but without specifying the `Writer[Course]` and it seems that Doobie automatics derivation works well to generate it for us. I've updated my answer to be more specific. — David Corral, Feb 24 '22 at 15:05

score 1 · Answer 2 · answered Apr 13 '22 at 11:59

A little note: in case anyone also encountered

doobie.syntax.SqlInterpolator.SingleFragment[_]; incompatible interpolation method sql

while following David Corral example:

instead of

def insertCourse(course: Course): Update0 =
sql"""INSERT INTO courses (id, name, year) VALUES ($course)""".update

try wrapping course variable in Fragments

def insertCourse(course: Course): Update0 =
sql"""INSERT INTO courses (id, name, year) VALUES (${Fragments.values(course)})""".update

score 0 · Answer 3 · answered Dec 29 '19 at 09:54

If you are using Postgres, you could take a look at another library of Rob Norris - skunk.

It allows you to write custom codecs:

  case class City(id: Int, name: String, code: String, district: String, pop: Int)

  val city: Codec[City] =
    (int4 ~ varchar ~ bpchar(3) ~ varchar ~ int4).gimap[City]

  val insertCity: Command[City] =
    sql"""
         INSERT INTO city
         VALUES ($city)
       """.command

Check examples.

YisraelU · Answer 4 · 2022-03-02T02:00:38.223

0

Doobie has Quill integration from getquill.io which allows you to model sql DML's using case classes https://github.com/polyvariant/doobie-quill

edited Mar 02 '22 at 02:00

answered Jul 08 '21 at 14:48

YisraelU

352
1
8

Doobie / Quill docs are very, very thin. Not enough info to be usable in a reasonable time period. – Mike Slinn Jul 09 '21 at 15:13
I meant that the docs on the integration do not say much. In particular, no examples show how the 2 products work together to add value. – Mike Slinn Jul 10 '21 at 18:21
Doobie is no longer officially supporting Quill and the library and docs have been removed from Doobie's site. – Mark Kegel Feb 28 '22 at 21:32
It is supported externally https://github.com/polyvariant/doobie-quill – YisraelU Mar 02 '22 at 02:00

Doobie update and insert case class syntax

4 Answers4