An extension to slick.jdbc
, Slick's plain SQL queries,
including the following features.
- More types in SQL interpolation
- Literal parameters
- Non-empty lists
- Case classes (products)
- Extensible raw query translator
- Embedding caller information as a comment
- Stripping margin
- Result mapper by column name
These features can be selectively enabled.
Add dependency in your build.sbt
as the following.
libraryDependencies ++= Seq(
...
"com.github.tarao" %% "slick-jdbc-extension" % "0.1.0"
)
The library is available on Maven Central. Currently, supported Scala versions are 2.12, and 2.13.
Slick supports plain SQL queries but it lacks some essential features. For example, there is no way to bind a list parameter to a prepared statement.
This extension provides additional features to Slick-style plain SQL queries. The extension consists of a family of traits and each of them can be enabled selectively. The easiest way to use the extension is to define a class with the traits.
Let's see an example of the usage of the extension. To start with, we assume to have an abstract class to run a DB query something like this.
abstract class DBHandler {
import scala.concurrent.{Future, Await}
import scala.concurrent.duration.Duration
import slick.dbio.{DBIOAction, NoStream, Effect}
import slick.backend.DatabaseComponent
type Database = DatabaseComponent#DatabaseDef
def run[R](a: DBIOAction[R, NoStream, Nothing])(implicit db: Database): R =
Await.result(db.run(a), Duration.Inf)
}
Then a repository class to use plain SQL queries with the extension will look like this. (We are not interested in how the concrete DB object is supplied.)
import com.github.tarao.slickjdbc._
import interpolation.{SQLInterpolation, CompoundParameter, TableName}
import getresult.{GetResult, AutoUnwrapOption}
class SampleRepository extends DBHandler
with SQLInterpolation with CompoundParameter
with GetResult with AutoUnwrapOption {
implicit def db: Database = ???
...
}
SQLInterpolation
, CompoundParameter
, GetResult
and
AutoUnwrapOption
are feature selectors of the extension.
For the rest of the example, we use Entry
model class defined as the
following.
case class Entry(id: Long, url: String)
To define a mapping from a column name to a field of the model in the
repository class, use getResult
method defined in GetResult
trait.
implicit val getEntryResult = getResult { Entry(
column("entry_id"),
column("url")
) }
This will map entry_id
column of a query result to the first field
of Entry
and url
column to the second. This is very similar to
Slick's GetResult.apply
. The type of the column is inferred by the
field type of Entry.id
(it is Long
in this case). Note that this
example also uses AutoUnwrapOption
to convert from Option[Long]
to
Long
. The default column type is Option[_]
ed since the resulting
column value may be NULL
.
Let's see an example of SQL interpolation. In addition to Slick's SQL interpolation, we can specify a structured value directly as a parameter.
val table = TableName("entry")
def add(entry: Entry) =
run { sqlu"""
| INSERT INTO ${table} (entry_id, url)
| VALUES ${entry}
"""
}
This actually illustrates three things.
- You can specify a table name by a value of
TableName
. This value will be embedded literally in the raw query. - A value of case class (
entry
in this case) is expanded to placeholders like(?, ?)
and the fields of the value are bound to them. - Characters before
|
is stripped in the raw query.
You can also specify a list as a parameter. But in this case, you must say that it is not empty.
import eu.timepit.refined.api.Refined
import eu.timepit.refined.collection.NonEmpty
def findAll(entryIds: Seq[Long] Refined NonEmpty): Seq[Entry] = run {
sql"""
| SELECT * FROM ${table}
| WHERE entry_id IN $ids
""".as[Entry]
}
$ids
must be refined by NonEmpty
and it expands to (?, ?, ..., ?)
. You can use refineV
to refine values.
import eu.timepit.refined.collection.NonEmpty
import eu.timepit.refined.refineV
val idsOrError = refineV[NonEmpty](Seq(101L, 102L, 103L))
val entries = ids match {
case Right(ids) => repository.findAll(ids)
case Left(_) => sys.error("Never happen in this case")
}
See the documentation of refined for the detail about refined types.
If you were using nonempty from older versions, it still works as
expected because nonempty.NonEmpty
is a kind of refined collection
type.
import com.github.tarao.nonempty.NonEmpty
def findAll(entryIds: Option[NonEmpty[Long]]): Seq[Entry] = entryIds match {
case Some(ids) => run {
sql"""
| SELECT * FROM ${table}
| WHERE entry_id IN $ids
""".as[Entry]
}
case None => Seq.empty
}
In this case, you call findAll
method like the following since there is an implicit conversion from Seq[Long]
to Option[NonEmpty[Long]]
.
repository.findAll(Seq(101L, 102L, 103L))
See the documentation of nonempty for the detail.
To use the extended SQL interpolation, you have to first enable it.
To enable the interpolation inside a particular trait or class, add
SQLInterpolation
trait to the trait or class.
import com.github.tarao.slickjdbc._
class YourRepositoryClass extends interpolation.SQLInterpolation
If you want to explicitly import
the feature, then import
methods
in SQLInterpolation
object.
import com.github.tarao.slickjdbc._
import interpolation.SQLInterpolation._
If you are import
ing a Slick driver API, doing that by import slick.driver.SomeDriver.api._
causes a conflict with the extended SQL
interpolation since the API also defines a SQL interpolation of Slick.
In this case, import
only desired features to avoid the conflict.
For example, if you are using Database
from the API, import it as
import slick.driver.SomeDriver.api.Database
.
When a value with Literal
is passed as a parameter of the
interpolation, a string returned by toString
on the value is
embedded literally in the raw query as if it was embedded by #${}
.
There are two predefined Literal
s.
SimpleString
- takes a string as a parameter of the constructor and embed that string.
TableName
- takes a name as a parameter of the constructor and embed that name.
Note that a Literal
value is not expanded to a placeholder ?
.
If the value needs to be escaped as a part of a SQL string, then you
should do it by yourself.
This feature requires interpolation.ComboundParameter
trait or
import interpolation.ComboundParameter._
.
When a non-empty list is passed as a parameter of the interpolation,
the value expands to ?
s in the prepared statement, and items in the
value are bound to the statement.
For example, if you have val Right(ne) = refineV[NonEmpty](Seq(1, 2, 3))
and embed it as sql"... ($ne) ..."
, the raw query will be
... (?, ?, ?) ...
and 1
, 2
, 3
are bound to the placeholders.
The resulting raw query will be the same if you omit parentheses like
sql"... $ne ..."
.
If you pass a non-empty list of tuples, for example pairs, it expands
to (?, ?), (?, ?), ..., (?, ?)
. This is useful to INSERT
multiple
values by INSERT INTO table (col1, col2) VALUES $aListOfPairs
.
This feature requires interpolation.ComboundParameter
trait or
import interpolation.ComboundParameter._
.
When a product especially an instance of a case class is passed as a
parameter of the interpolation, the value expands to ?
s in the
prepared statement, and fields in the value are bound to the
statement.
For example, if you have val p = SomeCaseClass(1, "foo")
and embed
it as sql"... ($p) ..."
, the raw query will be ... (?, ?) ...
and
1
, "foo"
are bound to the placeholders. The resulting raw query
will be the same if you omit parentheses like sql"... $p ..."
.
If you have another product value in a field of the passed value, it also be expanded and the placeholders are flattened.
You can also put a product value in a non-empty list and it has the
same effect as putting tuples except when the arity of the product
is 1. When the product arity is exactly 1, the value is treated as
if it is a primitive value. In other words, a value of Seq[[Single] Refined NonEmpty
(where Single
is a Product
of arity 1) expands
to (?, ?, ..., ?)
not to (?), (?), ..., (?)
. In contrast, a value
of Seq[Tuple1[_]] Refined NonEmpty
expands to (?), (?), ..., (?)
not to (?, ?, ..., ?)
.
Note that passing Option[_]
as an interpolation parameter is
statically rejected and passing a product of arity zero (this can be
made by for example case class Empty()
) causes
java.sql.SQLException
at runtime.
To use this feature, you have to enable the extended SQL interpolation. Follow the instruction above to enable it.
The query translator translates a raw query generated by the interpolation. A translation is simply from string to string and you can stack multiple translators.
MarginStripper
- Strip characters before
|
from each line of a query string. This makes a query log a bit more readable.
- Strip characters before
CallerCommenter
- Record the code position that the interpolation is invoked as a comment of SQL query. You can trace a line number, a file name and a method name in a class from an ordinary query log on the DB server.
You can use a custom translator stack by defining an implicit value of
Iterable[query.Translator]
. For example, if you want to add a
translator after the default translators, define an implicit value in
a static scope where you invoke the SQL interpolation.
import com.github.tarao.slickjdbc._
implicit val translators: Iterable[query.Translator] = Seq(
query.MarginStripper,
query.CallerCommenter,
new query.Translator {
def apply(query: String, context: query.Context) =
??? /* return a translated string */
}
)
This feature requires getresult.GetResult
trait or object.
With the trait, you can use getResult
method and can use column
method in the block of getResult
without a receiver like in the
example in Overview section.
If you prefer the object, you just need to import
it.
import com.github.tarao.slickjdbc._
import getresult.GetResult
The usage of GetResult
is quite similar to that of Slick's
GetResult
. You will receive an object to extract a result value in
a block passed to GetResult.apply
.
implicit val getEntryResult = GetResult { r => Entry(
r.column("entry_id"),
r.column("url")
) }
It also requires getresult.AutoUnwrapOption
to extract a column
value of non-Option[_]
type. This also available both as a trait
and an object.
You can use this feature without the extended SQL interpolation.
As you have already seen, a column value can be extracted by column
method. If you want to explicitly specify a return type or the return
type somehow cannot be inferred, you can pass a type parameter to
column
to specify a return type.
implicit val getEntryResult = getResult { Entry(
column[Long]("entry_id"),
column[String]("url")
) }
You can also extract the result by the index of a column.
implicit val getEntryResult = getResult { Entry(
column(1),
column(2)
) }
If you prefer the way of <<
method in Slick's GetResult
, it is
also available in our GetResult
.
implicit val getEntryResult = getResult { Entry(<<, <<) }
<<?
and skip
are also available.
getresult.AutoUnwrapOption
makes it possible to get non-Option[_]
value directly. Note that this may cause NoSuchElementException
thrown if the value in the DB record is NULL
.
If you want to specify a default value instead of throwing an
exception, then retrieve the result as an Option[_]
and give a
default value.
implicit val getEntryResult = getResult { Entry(
column[Option[Long]]("entry_id").getOrElse(0),
column[Option[String]]("url").getOrElse("http://example.com/default/page")
) }
The way of extracting a typed value from a raw query result is
different from that of Slick's GetResult
. We provide a
TypeBinder[_]
which is similar to TypeBinder[_]
in
ScalikeJDBC to specify extractor methods for both
indexed and named results.
Let's take url
field of Entry
as an example. Assume that you want
to use your custom case class URL
as a url
field instead of
String
.
case class URL(url: String)
case class Entry(id: Long, url: URL)
Then you need to define a custom TypeBinder[_]
for URL
. A good
way to define a custom type binder for a user-defined type is to
extend a type binder for a primitive type.
implicit def urlBinder(implicit
binder: TypeBinder[Option[String]]
): TypeBinder[Option[URL]] = binder.map(_.map(URL(_)))
This binder uses a TypeBinder[Option[String]]
as a parent binder,
which is defined by default, and convert the string retrieved by the
parent to a URL
. (The first map
is to convert retrieved value,
and the second one is to take care of the Option[_]
type.)
Note that you should define a TypeBinder[_]
for Option[URL]
not
for URL
since unwrapping Option[_]
will be done by general
unwrapping conversion by AutoUnwrapOption
.
With this binder, you can define a result mapper for Entry
.
implicit val getEntryResult = getResult { Entry(
column("entry_id"),
column("url")
) }
Notice that there is no difference in the code compared to the one
defined before for case class Entry(id: Long, url: String)
but the
(inferred) return type of column("url")
is now URL
.
- Copyright (C) INA Lintaro
- MIT License