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:
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 listed out, and matched up with values, which is horrible:
/** @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?