Avoiding Boilerplate in Slick 2's Plain SQL?

Question

Using Slick 2's Plain SQL queries, I find myself writing a fair amount of:

import slick.driver.H2Driver.api._

def f(search: Option[String]) = search match {
  case Some(s) =>   sql""" SELECT a FROM foo WHERE s = $s"""
  case None    =>   sql""" SELECT a FROM foo"""
}

In the past, I've resorted to using String's to attempt SQL re-use:

def g(search: Option[String]) = {

  val query: String =   search match {
    case Some(s) =>   s"WHERE s = '$s'"
    case None     =>  ""
 }

 sql""" SELECT a FROM foo #$query"""
}

Ignoring the SQL Injection risk, I don't like this second approach since I find it harder to read, i.e. I have to keep track of String's rather than view the entire query.

However, both of these approaches smell to me. It seems ideal if I could build the base query, and then compose or add to it to add a WHERE.

Can I do better with Slick 2's Plain SQL?

Dr. Vick Dr. Vick · Accepted Answer · 2017-08-20T13:26:13

Slick v2 wasn't that production ready and had a lot of issues around performance and query creation.

All that has been taken care of in the latest versions of slick (3.2.1): http://slick.lightbend.com/doc/3.2.1/introduction.html#plain-sql-support

You also have the ability to the slick internals generate the SQL for you and start formulating queries using Slick’s Scala-based query API: http://slick.lightbend.com/doc/3.2.1/queries.html

Avoiding Boilerplate in Slick 2's Plain SQL?

1 Answers