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?

Answer 1

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!

Answer 2

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

Answer 3

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.

Answer 4

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