Slick: All Things to All People?

class: center, middle

# Slick
## .emph[All Things to All People?]

<BR />
<BR />
Nicholas McAvoy

October 23, 2012

---

# Agenda

1. What we want
2. Brief Survey of the Field
3. Slic.emph[k]: The .emph[k] is for .emph[kit]
4. How's it look?

---

# What we want
### Seven little things

1. Statically-checked queries

2. Freedom not to write SQL

3. Many backends

4. Plug into my existing database

5. Freedom to write SQL

6. At least 23 columns

7. NoSQL?

---
layout: true

# A Brief Survey of the Field

---

## Squeryl

- Presents statically-checked, concise, composable queries.
- Is a full ORM, so your data must be mapped to objects.

## Anorm (from Play)

- Lets you write raw sql
- No in-code representation of database required
- No static checking
- Questionable future

## Querulous

- Lots of configuration for performance etc
- Plain sql, not statically checked
- MySQL only

---
layout: true

# Slic.emph[k]

---

## When Their Powers Combine

Slick is a joint effort:

- .emph[Stefan Zeiger] wrote ScalaQuery, one of the forerunning Scala database connection options already available.
- .emph[Jan Christopher Vogt] wrote Scala Integrated Query, an early prototoype for improving Scala-database relations based on recent relational algebra work.

These two men, and these two projects, have come together in Slick.

.ct[ .sz[ ![Stefan Zeiger](szeiger.jpg)] .cv[ ![Christopher Vogt](cvogt.jpg)] ]

---

## Core value: Different Tools for Different Jobs

My inference from the creators' words:

Database access is a varied beast. There may be no one Right Way to Do It.

Provide a suite of tools (a kit, if you will) suitable for different scenarios.

---
layout: false
name: arch

.arch_img[ ![Slick architecture](arch_slide.png)]

.bottom[ From: [ScalaDays 2012 Slick talk](http://slick.typesafe.com/docs/)]

---
layout: true

# Three APIs

1. "Lifted" embedding (ScalaQuery)

- Uses implicit conversions and operator overloading for monadic query definitions
  - Operates on Table objects defined for every table you want to query
  - for the present more type-safe and composable

2. "Direct" embedding (Scala Integrated Query)
  - Draws more inspiration from LINQ
  - Uses macros to enforce query type constraints
  - Operates on case classes with compiler annotations
  - Queryable will implement much of the Collections API
  - Gives better error messages due to using normal Scala types

3. Plain SQL
  - "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." -Slick docs
  - Runs directly on top of JDBC.

---
layout: true

# Lifted Embedding

---

## Schema definition
```scala
// Represents one player's batting statistics on one team in one year
object Batting extends Table[(String, Int, String, Int)]("Batting") {
  def playerId = column[String]("playerID")
  def year     = column[Int]   ("yearID")
  def teamId   = column[String]("teamID")
  def homeRuns = column[Int]   ("HR")
  def *        = playerId ~ year ~ teamId ~ homeRuns
  def playerInfo = foreignKey("Master", playerId, Master){ _.playerId }
}
  
// Information for players, managers, etc
object Master extends Table[(String, String, String)]("Master") {
  def playerId  = column[String]("playerID")
  def nameLast  = column[String]("nameLast")
  def nameFirst = column[String]("nameFirst")
  def *         = playerId ~ nameLast ~ nameFirst
}
```

---

## How to query
 
```scala
def lifted = db withSession {

//It's a Query, not an Iterable[(Int, String, String)]
  val q1 = for {
    b <- Batting if b.homeRuns >= 50
    // Foreign key lookup!
    i <- b.playerInfo
  } yield (b.year, i.nameLast, i.nameFirst)

println(q1.selectStatement)

//Work with Scala types inside the Query monad
   q1 sortBy { _._1 } foreach {
     case (year, last, first) =>
       println(year + ": " + first + " " + last)
   } 
  
  // ...or don't. 
  val returnFromMonad: List[(Int, String, String)] = q1.elements.toList

}    
```

---

## Output

```sql
select x2.`yearID`, x3.`nameLast`, x3.`nameFirst` from `Batting` x2, 
`Master` x3 where (x2.`HR` >= 50) and (x3.`playerID` = x2.`playerID`)
```
```
1920: Babe Ruth
1921: Babe Ruth
1927: Babe Ruth
1928: Babe Ruth
1930: Hack Wilson
1932: Jimmie Foxx
1938: Hank Greenberg
1938: Jimmie Foxx
1947: Ralph Kiner
1947: Johnny Mize
1949: Ralph Kiner
1955: Willie Mays
1956: Mickey Mantle
1956: Mickey Mantle
1961: Mickey Mantle
1961: Roger Maris
1965: Willie Mays
1977: George Foster
...(a bunch of steroid users follow)
```
---

### Features new over last ScalaQuery version

- New `Shape` type class allows the bypassing of the dreaded 22-column limit imposed by the use of tuples.

- Implementing calls along the lines of the Collections API

- So far just sortBy and groupBy

- Query return type is encoded in query

---
layout: true

# Direct Embedding

---

## Schema definition

```scala
// Represents one player's batting statistics on one team in one year
@table(name="Batting") // for macros!
case class BattingDirect(
   @column(name="playerID") playerId: String,
   @column(name="yearID")   year:     Int,
   @column(name="teamID")   team:     String,
   @column(name="HR")       homeRuns: Int
)

// Information for players, managers, etc
@table(name="Master")
case class MasterDirect(
   @column(name="playerID")  playerId:  String,
   @column(name="nameLast")  nameLast:  Int,
   @column(name="nameFirst") nameFirst: String
)
```
---

## How to query

```scala
def direct = db withSession {
  val backend = new SlickBackend(MySQLDriver, AnnotationMapper)

val battingYears = Queryable[BattingDirect]
  // Macros!
    // > 49 because >= 50 will break at -runtime-!
  val q2 = battingYears filter { _.homeRuns > 49 } map
    { b: BattingDirect => (b.year, b.playerId) }

//I get the sense the call on `backend` is a concession.
  //In an earlier talk the backend could be applied to the Queryable. 
  backend.result(q2, session) sortBy { _._1 } foreach {
    case (year, playerId) => println(year + ": " + playerId)
  }

}
```
Notes:
 
- No lookup to the Master table. I couldn't figure out how to do this in a way that wasn't (n+1) queries.
- The docs don't do this operation either, so I think it may not be ready yet.

---

## Result

```
1920: ruthba01
1921: ruthba01
1927: ruthba01
1928: ruthba01
1930: wilsoha01
1932: foxxji01
1938: foxxji01
1938: greenha01
1947: kinerra01
1947: mizejo01
1949: kinerra01
1955: mayswi01
1956: mantlmi01
1961: mantlmi01
1961: marisro01
1965: mayswi01
1977: fostege01
etc etc
```

---
layout: false

# As it stands

- The lifted embedding API was already a mature option as ScalaQuery, and it is even better now. With official support it should continue to improve.

- Direct embedding is not ready for primetime, but as much is said by the Slick docs. That said, its promised features are ... promising.

- The Plain SQL Api looks like a great plain-SQL option.

---

# Forthcoming

## As proposed at Scala Days 2012:

- Type-generating macros (like type providers in .NET) to obviate the need for schema generation.

```scala
object Coffees extends DBTable(
  "jdbc:h2:tcp://localhost/~/coffeeShop",
  "COFFEES"
)
```

- Nested collections

```scala
for {
  s <- Suppliers.sortBy(_.id)
  val cs = s.coffees.filter(_.price < 9.0)
  } yield ((s.id, s.name), cs)
}
```

- More databases (MongoDB!)

Roadmap available on  [Slick's assembla space](http://www.assembla.com/spaces/typesafe-slick/)'

---

# Questions?

---

# Stuff I used

- Presentation: [remark.js](http://gnab.github.com/remark/)

- CSS: [LESS](http://lesscss.org/)

- Color scheme: [Color Scheme Designer](http://colorschemedesigner.com/)

---

# Thank you!
 
Hire me.