Parameterized Queries
In this chapter we learn how to construct parameterized queries, and introduce the Put and Write typeclasses.
Setting Up
Same as last chapter, so if you’re still set up you can skip this section. Otherwise let’s set up a Transactor and YOLO mode.
import doobie._
import doobie.implicits._
import doobie.util.ExecutionContexts
import cats._
import cats.data._
import cats.effect._
import cats.implicits._
// This is just for testing. Consider using cats.effect.IOApp instead of calling
// unsafe methods directly.
import cats.effect.unsafe.implicits.global
// A transactor that gets connections from java.sql.DriverManager and executes blocking operations
// on an our synchronous EC. See the chapter on connection handling for more info.
val xa = Transactor.fromDriverManager[IO](
driver = "org.postgresql.Driver", // JDBC driver classname
url = "jdbc:postgresql:world", // Connect URL
user = "postgres", // Database user name
password = "password", // Database password
logHandler = None // Don't setup logging for now. See Logging page for how to log events in detail
)
val y = xa.yolo
import y._
We’re still playing with the country table, shown here for reference.
CREATE TABLE country (
code character(3) NOT NULL,
name text NOT NULL,
population integer NOT NULL,
gnp numeric(10,2)
-- more columns, but we won't use them here
)
Adding a Parameter
Let’s set up our Country class and re-run last chapter’s query just to review.
case class Country(code: String, name: String, pop: Int, gnp: Option[Double])
{
sql"select code, name, population, gnp from country"
.query[Country]
.stream
.take(5)
.quick
.unsafeRunSync()
}
Still works. Ok.
So let’s factor our query into a method and add a parameter that selects only the countries with a population larger than some value the user will provide. We insert the minPop argument into our SQL statement as $minPop, just as if we were doing string interpolation.
def biggerThan(minPop: Int) = sql"""
select code, name, population, gnp
from country
where population > $minPop
""".query[Country]
And when we run the query … surprise, it works!
biggerThan(150_000_000).quick.unsafeRunSync() // Let's see them all
So what’s going on? It looks like we’re just dropping a string literal into our SQL string, but actually we’re constructing a PreparedStatement, and the minPop value is ultimately set via a call to setInt (see “Diving Deeper” below).
doobie allows you to interpolate values of any type (and options thereof) with a Put instance, which includes
- any JVM type that has a target mapping defined by the JDBC specification,
- vendor-specific types defined by extension packages,
- custom column types that you define, and
- single-member products (case classes, typically) of any of the above.
We will discuss custom type mappings in a later chapter.
Multiple Parameters
Multiple parameters work the same way. No surprises here.
def populationIn(range: Range) = sql"""
select code, name, population, gnp
from country
where population > ${range.min}
and population < ${range.max}
""".query[Country]
populationIn(150_000_000 to 200_000_000).quick.unsafeRunSync()
Dealing with IN Clauses
A common irritant when dealing with SQL literals is the desire to inline a sequence of arguments into an IN clause, but SQL does not support this notion (nor does JDBC do anything to assist). doobie supports this via statement fragments (see Chapter 8).
def populationIn(range: Range, codes: NonEmptyList[String]) = {
val q = fr"""
select code, name, population, gnp
from country
where population > ${range.min}
and population < ${range.max}
and """ ++ Fragments.in(fr"code", codes) // code IN (...)
q.query[Country]
}
Note that the IN clause must be non-empty, so codes is a NonEmptyList.
Running this query gives us the desired result.
populationIn(100_000_000 to 300_000_000, NonEmptyList.of("USA", "BRA", "PAK", "GBR")).quick.unsafeRunSync()
Diving Deeper
In the previous chapter’s Diving Deeper we saw how a query constructed with the sql interpolator is just sugar for the stream constructor defined in the doobie.hi.connection module (aliased as HC). Here we see that the second parameter, a PreparedStatementIO program, is used to set the query parameters. The third parameter specifies a chunking factor; rows are buffered in chunks of the specified size.
import fs2.Stream
val q = """
select code, name, population, gnp
from country
where population > ?
and population < ?
"""
def proc(range: Range): Stream[ConnectionIO, Country] =
HC.stream[Country](q, HPS.set((range.min, range.max)), 512)
Which produces the same output.
proc(150_000_000 to 200_000_000).quick.unsafeRunSync()
But how does the set constructor work?
When setting parameters in the high-level API, we require an instance of Write[A] for the input type. It is not immediately obvious when using the sql interpolator, but the parameters (each of which require a Put instance, to be discussed in a later chapter) are gathered into an HList and treated as a single writable parameter.
Write instances are derived automatically for column types (and options thereof) that have Put instances, and for products of other writable types. We can summon their instances thus:
Write[(String, Boolean)]
// res5: Write[(String, Boolean)] = doobie.util.Write@7b42fe98
Write[Country]
// res6: Write[Country] = doobie.util.Write@668b3143
The set constructor takes an argument of any type with a Write instance and returns a program that sets the unrolled sequence of values starting at parameter index 1 by default. Some other variations are shown here.
// Set parameters as (String, Boolean) starting at index 1 (default)
HPS.set(("foo", true))
// Set parameters as (String, Boolean) starting at index 1 (explicit)
HPS.set(1, ("foo", true))
// Set parameters individually
HPS.set(1, "foo") *> HPS.set(2, true)
// Or out of order, who cares?
HPS.set(2, true) *> HPS.set(1, "foo")
Using the low level doobie.free constructors there is no typeclass-driven type mapping, so each parameter type requires a distinct method, exactly as in the underlying JDBC API. The purpose of the Put typeclass (discussed in a later chapter) is to abstract away these differences.
FPS.setString(1, "foo") *> FPS.setBoolean(2, true)