Plain SQL Queries

Sometimes you may need to write your own SQL code for an operation which is not well supported at a higher level of abstraction. Instead of falling back to the low level of JDBC, you can use Slick’s Plain SQL queries with a much nicer Scala-based API.

Scaffolding

The database connection is opened in the usual way. All Plain SQL queries result in a DBIOAction that can be composed and run like any other action.

String Interpolation

Plain SQL queries in Slick are built via string interpolation using the sql and sqlu interpolators. They are available through the standard api._ import from a Slick profile:

copysourceimport slick.cats.Database
import slick.jdbc.DatabaseConfig
import slick.jdbc.H2Profile
import slick.jdbc.H2Profile.api.*

You can see the simplest use case in the following methods where the sqlu interpolator is used with a literal SQL string:

copysourcedef createCoffees: DBIO[Int] =
  sqlu"""CREATE TABLE coffees(
  name VARCHAR NOT NULL,
  sup_id INT NOT NULL,
  price DOUBLE NOT NULL,
  sales INT NOT NULL,
  total INT NOT NULL,
  FOREIGN KEY(sup_id) REFERENCES suppliers(id))"""

def createSuppliers: DBIO[Int] =
  sqlu"""CREATE TABLE suppliers(
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR NOT NULL,
  street VARCHAR NOT NULL,
  city VARCHAR NOT NULL,
  state VARCHAR NOT NULL,
  zip VARCHAR NOT NULL)"""

def insertSuppliers: DBIO[Unit] = DBIO.seq(
  // Insert some suppliers
  sqlu"INSERT INTO suppliers VALUES(101, 'Acme, Inc.', '99 Market Street', 'Groundsville', 'CA', '95199')",
  sqlu"INSERT INTO suppliers VALUES(49, 'Superior Coffee', '1 Party Place', 'Mendocino', 'CA', '95460')",
  sqlu"INSERT INTO suppliers VALUES(150, 'The High Ground', '100 Coffee Lane', 'Meadows', 'CA', '93966')"
)

The sqlu interpolator is used for DML statements which produce a row count instead of a result set. Therefore they are of type DBIO[Int]. Use sqlu (or .asUpdate) for every statement that does not return a result set, including DDL: reading such a statement with .as[R] fails with a SlickException. On SQL Server and Sybase, a batch or stored procedure that modifies rows before its SELECT (directly or through a trigger) first returns an update count, so .as[R] fails the same way. Start the batch or procedure with SET NOCOUNT ON to suppress those update counts.

A Plain SQL action should contain a single SQL statement. Several statements separated by ; are not portable: H2, PostgreSQL and SQL Server run all of them, MySQL runs them only with the allowMultiQueries=true connection property, HSQLDB, Derby, Oracle and DB2 reject them, and SQLite silently runs only the first one. Where they do run, the action returns the result of the first statement. A later statement that fails makes the action fail only if Slick reads its result: always when the first statement returns an update count, but after a query only when its result set is read to the end. .head and .headOption, which stop after the first row, and a stream that is cancelled early can miss the error. Use DBIO.seq(sqlu"...", sqlu"...") to run several statements.

Any variable or expression injected into a query gets turned into a bind variable in the resulting query string. It is not inserted directly into a query string, so there is no danger of SQL injection attacks. You can see this used in here:

copysourcedef insert(c: Coffee): DBIO[Int] =
  sqlu"insert into coffees values (${c.name}, ${c.supID}, ${c.price}, ${c.sales}, ${c.total})"

The SQL statement produced by this method is always the same:

insert into coffees values (?, ?, ?, ?, ?)

Note the use of the DBIO.sequence combinator which is useful for this kind of code:

copysourceval inserts: Seq[DBIO[Int]] = Seq(
  Coffee("Colombian", 101, 7.99, 0, 0),
  Coffee("French_Roast", 49, 8.99, 0, 0),
  Coffee("Espresso", 150, 9.99, 0, 0),
  Coffee("Colombian_Decaf", 101, 8.99, 0, 0),
  Coffee("French_Roast_Decaf", 49, 9.99, 0, 0)
).map(insert)

val combined: DBIO[Seq[Int]] = DBIO.sequence(inserts)
combined.map(_.sum)

Unlike the simpler DBIO.seq combinator which runs a (varargs) sequence of database I/O actions in the given order and discards the return values, DBIO.sequence turns a Seq[DBIO[T]] into a DBIO[Seq[T]], thus preserving the results of all individual actions. It is used here to sum up the affected row counts of all inserts.

Result Sets

The following code uses the sql interpolator which returns a result set produced by a statement. The interpolator by itself does not produce a DBIO value. It needs to be followed by a call to .as to define the row type:

copysourcesql"""select c.name, s.name
    from coffees c, suppliers s
    where c.price < $price and s.id = c.sup_id""".as[(String, String)]

This results in a DBIO[Seq[(String, String)]]. The call to as takes an implicit GetResult parameter which extracts data of the requested type from a result set. There are predefined GetResult implicits for the standard JDBC types, for Options of those (to represent nullable columns) and for tuples of types which have a GetResult. For non-standard return types you have to define your own converters:

copysource// Case classes for our data
case class Supplier(id: Int, name: String, street: String, city: String, state: String, zip: String)
case class Coffee(name: String, supID: Int, price: Double, sales: Int, total: Int)

// Result set getters
implicit val getSupplierResult: GetResult[Supplier] = GetResult(r => Supplier(r.nextInt(), r.nextString(),
  r.nextString(), r.nextString(), r.nextString(), r.nextString()))
implicit val getCoffeeResult: GetResult[Coffee] = GetResult(r => Coffee(r.<<, r.<<, r.<<, r.<<, r.<<))

GetResult[T] is simply a wrapper for a function PositionedResult => T. The implicit val for Supplier uses the explicit PositionedResult methods getInt and getString to read the next Int or String value in the current row. The second one uses the shortcut method << which returns a value of whatever type is expected at this place. (Of course you can only use it when the type is actually known like in this constructor call.)

If the columns of a query are not known statically, use asSeqOfAny instead of as to read each row as a Seq[Any] (the values in column order), or asMapOfAny to read it as a Map[String, Any] (from column label to value, in column order):

copysourceval rows: DBIO[Seq[Seq[Any]]] =
  sql"select name, price from coffees order by name".asSeqOfAny
val maps: DBIO[Seq[Map[String, Any]]] =
  sql"select name as coffee, price from coffees order by name".asMapOfAny

Both read every column of the row with JDBC’s getObject, so the values are of whatever type the JDBC driver returns for the column (for example, a numeric column may be read as a java.lang.Integer on one database and a java.math.BigDecimal on another) and SQL NULL becomes null. The keys of the Map are the column labels reported by the driver, which take AS aliases into account but whose case depends on the database; if several columns have the same label, only the first one is included.

Splicing Literal Values

While most parameters should be inserted into SQL statements as bind variables, sometimes you need to splice literal values directly into the statement, for example to abstract over table names or to run dynamically generated SQL code. You can use #$ instead of $ in all interpolators for this purpose, as shown in the following piece of code:

copysourceval table = "coffees"
sql"select * from #$table where name = $name".as[Coffee].headOption

Concatenating SQL statements

It is possible to combine partial SQL statements using concat method:

copysource(sql"select * from coffees where ".concat(name.fold(sql"true")(name => sql"name = $name"))).as[Coffee]