Slick overview

Overview

Slick is as convenient for Scala as linQ as for C, or for ANM systems on other platforms, and it uses an app to use a database as easily as using Scala's built-in collection types, such as lists, collections, and so on. Of course, you can query the database directly using SQL statements if you need them.

Here's a snippet of code that uses Slick:

val limit = 10.0

// Your query could look like this:
( for( c <- coffees; if c.price < limit ) yield c.name ).list

// Or using more plain SQL String Interpolation:
sql"select COF_NAME from COFFEES where PRICE < $limit".as[String].list

// Both queries result in SQL equivalent to:
// select COF_NAME from COFFEES where PRICE < 10.0

Instead of using SQL statements directly, you can use the compiler to help find some type errors, while Slick can generate queries for different background database types.

It has some of the following characteristics:

Scala

All queries, table and field maps, and types are based on the normal Scala syntax.

class Coffees(tag: Tag) extends Table[(String, Double)](tag, "COFFEES") {
    def name = column[String]("COF_NAME", O.PrimaryKey)
    def price = column[Double]("PRICE")
    def * = (name, price)
}
val coffees = TableQuery[Coffees]

The collection type of scala, the data provider type

// Query that only returns the "name" column
coffees.map(_.name)

// Query that does a "where price < 10.0"
coffees.filter(_.price < 10.0)

Type security

The IDE you use can help you write code that compiles without having to run to find some errors

// The result of "select PRICE from COFFEES" is a Seq of Double
// because of the type safe column definitions
val coffeeNames: Seq[Double] = coffees.map(_.price).list

// Query builders are type safe:
coffees.filter(_.price < 10.0)
// Using a string in the filter would result in a compilation error

Can be combined

The query interface is a function that can be combined and reused multiple times.

// Create a query for coffee names with a price less than 10, sorted by name
coffees.filter(_.price < 10.0).sortBy(_.name).map(_.name)
// The generated SQL is equivalent to:
// select name from COFFEES where PRICE < 10.0 order by NAME

Supported database systems

• DB2 (via slick-extensions)
• Derby/JavaDB
• H2
• HSQLDB/HyperSQL
• Microsoft Access
• Microsoft SQL Server (via slick-extensions)
• Mysql
• Oracle (via slick-extensions)
• PostgreSQL
• Sqlite

Limited support is also available for some other database types, Slick.

Query interface Lifted Embedding

Sclick uses Lifted Embedding as the standard database query interface, and the Direct Embedding interface is being developed for testing.

Lifted Embedding's name comes from the fact that instead of using a standard Scala data type to access the query database, you use the Rep constructor to promote the basic data type of Scala, and then use the promoted data type to access the database, such as the example of a standard Scala collection:

case class Coffee(name: String, price: Double)
val coffees: List[Coffee] = //...

val l = coffees.filter(_.price > 8.0).map(_.name)
//                       ^       ^          ^
//                       Double  Double     String

And the corresponding example after the promotion:

class Coffees(tag: Tag) extends Table[(String, Double)](tag, "COFFEES") {
    def name = column[String]("COF_NAME")
    def price = column[Double]("PRICE")
    def * = (name, price)
}
val coffees = TableQuery[Coffees]

val q = coffees.filter(_.price > 8.0).map(_.name)
//                       ^       ^          ^
//               Rep[Double]  Rep[Double]  Rep[String]

All basic Scala types are promoted to Rep. Even the 8.0 literal quantity is promoted to the Rep.Double type.

In the latest example, we'll use the Chinook database as an example.

The Chinook database, formerly known as the Northwind database, has the following data model: