Let's explore the behavior of the Slick framework through code examples to see how we can enhance and replace more traditional ORM solutions.

The first example we can study is part of the test-patterns-scala activator template project that we have analyzed in Chapter 4, Testing Tools. The scalatest/Test012.scala file found inside the project exhibits a typical usage of Slick as follows:

package scalatest

import org.scalatest._
import scala.slick.driver.H2Driver.simple._
import Database.threadLocalSession

object Contacts extends Table[(Long, String)]("CONTACTS") {
  def id = column[Long]("CONTACT_ID", O.PrimaryKey)
  def name = column[String]("CONTACT_NAME")
  def gender = column[String]("GENDER")
  def * = id ~ name
}

class Test12 extends FunSuite {
  val dbUrl = "jdbc:h2:mem:contacts"
  val dbDriver = "org.h2.Driver"
  
  test("Slick, H2, embedded") {
    Database.forURL(dbUrl, driver = dbDriver) withSession {
    Contacts.ddl.create
    Contacts.insertAll(
      (1, "Bob"),
      (2, "Tom"),
      (3, "Salley")
    )
  
    val nameQuery = 
      for( 
        c <- Contacts if c.name like "%o%"
      ) yield c.name 
    val names = nameQuery.list     
    names.foreach(println)
    assert(names === List("Bob","Tom"))
    }
  }
}

The most interesting part in the code has to do with the SQL query. The immutable variable names contains the result of a query to the database; instead of expressing the SQL query as a String or through the Java Criteria API, pure Scala code is used through a for comprehension, as shown in the following screenshot:

Unlike string-based SQL queries, any typo or reference to tables or fields that do not exist will be immediately pointed out by the compiler. More complex queries will be very naturally translated into for expressions in a readable manner compared to the verbose and hard-to-read output code resulting from the JPA Criteria API.

This sample only contains one table, Contacts, that we define by extending the scala.slick.driver.H2Driver.simple.Table class. The CONTACTS database table includes three columns, one primary key id defined as a Long datatype, and two other properties of type String, name, and gender respectively. The method * defined in the Contacts object specifies a default projection, that is, all the columns (or computed values) we are usually interested in. The expression id ~ name (using the ~ sequence operator) returns a Projection2[Long,String] which can be thought of as a Tuple2, but for the representation of relations. The default projection of (Int, String) leads to a List[(Int, String)] for simple queries.

Since the datatypes of columns in relational databases are not the same as Scala types, they need to be mapped (similar to the mappings needed when dealing with ORM frameworks or pure JDBC access). As stated in Slick's documentation, the primitive types supported out of the box are as follows (with a few limitations depending on the database driver used for each database type):

Once the domain entity is defined, the next steps are to create the database, insert some test data in it, and then run a query as we would do with any other persistence framework.

All of the code we run in the Test12 test is surrounded by the following block:

Database.forURL(dbUrl, driver = dbDriver) withSession {
  < code accessing the DB...>
}

The forURL method specifies a JDBC database connection, which normally consists of a driver class corresponding to the specific database to use and a connection URL defined by its host, port, database name as well as an optional username/password. In the example, a local in-memory database (H2) named contacts is used so that the connection URL is jdbc:h2:mem:contacts, exactly as we would write it in Java. Note that a Slick Database instance only encapsulates a "how-to" on how connections are created, the physical connection being created only at the withSession call.

The Contacts.ddl.create statement will create the database schema and the insertAll method will populate the Contacts table with three rows each consisting of its primary key id and name.

We can execute this test alone to verify that it runs as expected, by entering the following command in a terminal window in the root directory of the test-patterns-scala project:

> ./activator
> test-only scalatest.Test12
Bob
Tom
[info] Test12:
[info] - Slick, H2, embedded (606 milliseconds)
[info] ScalaTest
[info] Run completed in 768 milliseconds.
[info] Total number of tests run: 1
[info] Suites: completed 1, aborted 0
[info] Tests: succeeded 1, failed 0, canceled 0, ignored 0, pending 0
[info] All tests passed.
[info] Passed: Total 1, Failed 0, Errors 0, Passed 1
[success] Total time: 1 s, completed Dec 7, 2013 1:43:28 PM

Currently, the test-patterns-scala project includes a dependency to the slf4j-nop implementation of the SLF4J logging framework that disables any logging. Since it can be useful to visualize the exact SQL statement produced by Scala for comprehension statements, let's replace sl4j-nop with a logback implementation. In your build.sbt build file, replace the line "org.slf4j" % "slf4j-nop" % "1.6.4" with a reference to logback, for example, "ch.qos.logback" % "logback-classic" % "0.9.28" % "test".

Now, if you rerun the test, you will probably see much more logging info than you actually want. We can therefore add a logback.xml file to the project (in the src/test/resources/ folder) as follows:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>

    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n</pattern>
        </encoder>
    </appender>

    <root level="debug">
        <appender-ref ref="STDOUT" />
    </root>

    <logger name="scala.slick.compiler"	level="${log.qcomp:-warn}" />
    <logger name="scala.slick.compiler.QueryCompiler" level="${log.qcomp.phases:-inherited}" />
    
…
    <logger name="scala.slick.compiler.CodeGen"                   level="${log.qcomp.codeGen:-inherited}" />
    <logger name="scala.slick.compiler.InsertCompiler"            level="${log.qcomp.insertCompiler:-inherited}" />
    <logger name="scala.slick.jdbc.JdbcBackend.statement"         level="${log.session:-info}" />

    <logger name="scala.slick.ast.Node$"                          level="${log.qcomp.assignTypes:-inherited}" />
    <logger name="scala.slick.memory.HeapBackend$"                level="${log.heap:-inherited}" />
    <logger name="scala.slick.memory.QueryInterpreter"            level="${log.interpreter:-inherited}" />
</configuration>

This time if we enable only the "scala.slick.jdbc.JdbcBackend.statement" logger, the output from the test will show all the SQL queries, similar to the following output:

> test-only scalatest.Test12
19:00:37.470 [ScalaTest-running-Test12] DEBUG scala.slick.session.BaseSession - Preparing statement: create table "CONTACTS" ("CONTACT_ID" BIGINT NOT NULL PRIMARY KEY,"CONTACT_NAME" VARCHAR NOT NULL)
19:00:37.484 [ScalaTest-running-Test12] DEBUG scala.slick.session.BaseSession - Preparing statement: INSERT INTO "CONTACTS" ("CONTACT_ID","CONTACT_NAME") VALUES (?,?)
19:00:37.589 [ScalaTest-running-Test12] DEBUG scala.slick.session.BaseSession - Preparing statement: select x2."CONTACT_NAME" from "CONTACTS" x2 where x2."CONTACT_NAME" like '%o%'
Bob
Tom
[info] Test12:
[info] - Slick, H2, embedded (833 milliseconds)
[info] ScalaTest
[info] Run completed in 952 milliseconds.
[info] Total number of tests run: 1
[info] Suites: completed 1, aborted 0
[info] Tests: succeeded 1, failed 0, canceled 0, ignored 0, pending 0
[info] All tests passed.
[info] Passed: Total 1, Failed 0, Errors 0, Passed 1
[success] Total time: 1 s, completed Dec 10, 2013 7:00:37 PM
> 

Finally, to verify whether database schema validation has been enforced, let's try to modify one of the keys of the inserted data so that we have duplicate keys, as shown in the following lines of code:

Contacts.insertAll(
      (1, "Bob"),
      (2, "Tom"),
      (2, "Salley")
    )

If we run the test again, it fails with a message similar to the following:

[info] Test12:
[info] - Slick, H2, embedded *** FAILED *** (566 milliseconds)
[info]   org.h2.jdbc.JdbcBatchUpdateException: Unique index or primary key violation: "PRIMARY_KEY_C ON PUBLIC.CONTACTS(CONTACT_ID)"; SQL statement:
[info] INSERT INTO "CONTACTS" ("CONTACT_ID","CONTACT_NAME") VALUES (?,?) [23505-166]…