slick

Scala Language-Integrated Connection Kit

Created by Oliver Schrenk / @oschrenk

Setup

scalaVersion := "2.10.0"

libraryDependencies ++= List(
  "com.typesafe.slick" %% "slick" % "1.0.0",
  "org.slf4j" % "slf4j-nop" % "1.6.4",
  "mysql" % "mysql-connector-java" % "5.1.25"
)

Imports

import scala.slick.session.Database._
import scala.slick.driver.MySQLDriver.simple._

Connection

Database.forURL(
  "jdbc:mysql://host:3306/elmar",
  driver = "com.mysql.jdbc.Driver"
) withSession {...}

val ds = new MysqlDataSource
    ds.setUrl("jdbc:mysql://host:3306/elmar")
    ds.setUser("...")
    ds.setPassword( "...")
Database.forDataSource(ds)

APIs

• Plain SQL
• Lifted Embedding
• Direct Embedding (experimental, no support for inserting)

Plain SQL

Data Definition

(Q.updateNA + "insert into companies values(101, 'Acme, Inc.'").execute

Plain SQL

Queries

import scala.slick.jdbc.{GetResult, StaticQuery => Q}
import Q.interpolation
case class AvailabilityRecord(
   brand: String, city: String, dateOfQuery: Timestamp
)
implicit val getAvailabilityRecordResult =
   GetResult(r => AvailabilityRecord(
   r.nextString, // r.<<
   r.nextString, // r.<<
   r.nextTimestamp()) // r.<<
)
val myBrand = "OA"
val q =
   sql"select brand, city, dateOfQuery
   from availabilitylog
   where brand = $myBrand limit 10".as[AvailabilityRecord]
println(q.list())

Lifted Embedding

Table with default projection

object AvailabilityRecords
   extends Table[(String, String, Timestamp)]
   ("availabilitylog") {
      def brand = column[String]("brand")
      def city = column[String]("city")
      def dateOfQuery = column[Timestamp]("dateOfQuery")

      def * = brand ~ city ~ dateOfQuery
}

Lifted Embedding

Table with mapped projection

case class AvailabilityRecord(
   brand: String, city: String, dateOfQuery: Timestamp
)
object AvailabilityRecords
   extends Table[AvailabilityRecord]("availabilitylog") {
      def brand = column[String]("brand")
      def city = column[String]("city")
      def dateOfQuery = column[Timestamp]("dateOfQuery")

      def * = brand ~ city ~ dateOfQuery
              <>(AvailabilityRecord, AvailabilityRecord.unapply _)
}

Inserting

AvailabilityRecords.ddl.create
AvailabilityRecords.insert("TUI", "Hanoi", "2013-10-02")

Updating

val q = for {
   c <- AvailabilityRecords if c.name === "TUI"
} yield c.city
q.update("New York")

Sorting and Paging

Query(AvailabilityRecords).
sortBy(_.city.desc.nullsFirst).
drop(5)take(10)

Filtering

Query(AvailabilityRecords).
take(30).
filter(_.brand === "OA")

Implicit Joining

object Persons extends Table[Person]("person") {
def id = column[String]("id")
def firstName = column[String]("firstName")
def lastName = column[String]("lastName")
def * = id ~ firstName ~ lastName <> (Person, Person.unapply _)
}
object Bookings extends Table[Booking ]("booking") {
def city = column[String]("cityName")
def bookingDate = column[Timestamp]("bookingDate")
def bookerId = column[String]("booker_id")
def * = city ~ bookingDate ~ bookerId <> (Booking, Booking.unapply _)
}
val implicitInnerJoin = for {
   b <- Bookings
   p <- Persons if b.bookerId === p.id
} yield (b.city, p.firstName, p.lastName)
println(implicitInnerJoin.take(5).list)

Explicit Joining

object Persons extends Table[Person]("person") {
def id = column[String]("id")
def firstName = column[String]("firstName")
def lastName = column[String]("lastName")
def * = id ~ firstName ~ lastName <> (Person, Person.unapply _)
}
object Bookings extends Table[Booking ]("booking") {
def city = column[String]("cityName")
def bookingDate = column[Timestamp]("bookingDate")
def bookerId = column[String]("booker_id")
def * = city ~ bookingDate ~ bookerId <> (Booking, Booking.unapply _)
}
val explicitInnerJoin = for {
  (b, p) <- Bookings innerJoin Persons on (_.bookerId === _.id)
} yield (b.city, p.firstName, p.lastName)
println(explicitInnerJoin.take(5).list)