slick database access with scala · scala language integrated connection kit •database query and...
TRANSCRIPT
![Page 1: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/1.jpg)
Slick database access with Scala
Stefan Zeiger
![Page 2: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/2.jpg)
Image by Don & Tonya Christner
Your App And Your Database
![Page 4: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/4.jpg)
Idea
• Write your database code in Scala
– Instead of SQL, JPQL, Criteria API, etc.
select p.NAME from PERSON p
for { p <- Person } yield p.name
4
![Page 5: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/5.jpg)
select x2.x3, count(1) from ( select * from ( select x4."NAME" as x5, x4."AGE" as x3 from "PERSON" x4 where x4."AGE" < 20 union all select x6."NAME" as x5, x6."AGE" as x3 from "PERSON" x6 where x6."AGE" >= 50 ) x7 where x7.x5 like 'A%' escape '^' ) x2 group by x2.x3
(for { p <- Persons.filter(_.age < 20) unionAll Persons.filter(_.age >= 50) if p.name.startsWith("A") } yield p).groupBy(_.age).map { case (age, ps) => (age, ps.length) }
5
![Page 6: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/6.jpg)
Agenda
• Key Concepts
• Live Demo
• Under The Hood
• Outlook
6
![Page 7: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/7.jpg)
Slick
Scala Language Integrated Connection Kit
• Database query and access library for Scala
• Successor of ScalaQuery
• Developed at Typesafe and EPFL
• Version 0.11 launched in August
• 1.0 to be released shortly after Scala 2.10
• Use ScalaQuery 0.11-M1 for Scala 2.9 instead
7
![Page 8: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/8.jpg)
Supported Databases
• PostgreSQL
• MySQL
• H2
• Hsqldb
• Derby / JavaDB
• SQL Server
• SQLite
• Access
Closed-Source Slick Extensions (commercially supported by
Typesafe) to be released with 1.0:
• Oracle • DB/2
Next big step: NoSQL! MongoDB support coming
Q1/2013
8
![Page 9: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/9.jpg)
Why not use an ORM tool?
9
![Page 10: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/10.jpg)
“Object/Relational Mapping is The Vietnam of Computer Science”
(Ted Neward)
http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx
![Page 11: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/11.jpg)
Impedance Mismatch: Concepts
Object-Oriented:
• Identity
• State
• Behaviour
• Encapsulation
: Transactional
Relational:
• Identity
• State
• Behaviour
• Encapsulation
11
![Page 12: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/12.jpg)
Impedance Mismatch: Retrieval
Colombian French_Roast Espresso Colombian_Decaf French_Roast_Decaf
Espresso Price: 9.99 Supplier: The High Ground
select COF_NAME from COFFEES
select c.*, s.SUP_NAME from COFFEES c, SUPPLIERS s where c.COF_NAME = ? and c.SUP_ID = s.SUP_ID
12
![Page 13: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/13.jpg)
Impedance Mismatch: Retrieval
13
![Page 14: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/14.jpg)
Impedance Mismatch: Retrieval
def getAllCoffees(): Seq[Coffee] = … def printLinks(s: Seq[Coffee]) { for(c <- s) println(c.name ) } def printDetails(c: Coffee) { println(c.name) println("Price: " + c.price) println("Supplier: " + c.supplier.name) }
+ " " + c.price
Colombian French_Roast Espresso Colombian_Decaf French_Roast_Decaf
Espresso Price: 9.99 Supplier: The High Ground
14
![Page 15: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/15.jpg)
O/R Mapper
• Mapping low-level programming (OOP) to high-level concepts (relational algebra)
• Not transparent
15
![Page 16: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/16.jpg)
Better Match: Functional Programming
• Relation
• Attribute
• Tuple
• Relation Value
• Relation Variable
case class Coffee(name: String,
supplierId: Int, price: Double)
val coffees = Set(
Coffee("Colombian", 101, 7.99),
,
Coffee("Espresso", 150, 9.99)
)
Coffee("French_Roast", 49, 8.99)
- mutable state in the DB
16
![Page 17: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/17.jpg)
Compared to ORMs
• Slick is simple!
– Just write your queries in Scala
• Slick is explicit!
– No lazy loading means predictable performance
– Only read the data you need
• Slick is functional!
– No mutable state (except in the database)
17
![Page 18: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/18.jpg)
Why not write your own SQL code?
18
![Page 19: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/19.jpg)
SQL
• Non-compositional syntax
• Generating SQL via string manipulation is awkward
• Generating it from templates (e.g. MyBatis) is verbose
• Easy to make mistakes which are not caught at compile-time
19
![Page 21: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/21.jpg)
Compared to SQL
• Slick is simple!
– Just write your queries in Scala
• Slick is compositional!
– Not based on ad-hoc syntax and semantics
• Slick is safe!
– Protects against type errors, spelling mistakes, wrong composition, etc.
21
![Page 22: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/22.jpg)
Plain SQL Queries
def personsMatching(pattern: String)(conn: Connection) = { val st = conn.prepareStatement( "select id, name from person where name like ?") try { st.setString(1, pattern) val rs = st.executeQuery() try { val b = new ListBuffer[(Int, String)] while(rs.next) b.append((rs.getInt(1), rs.getString(2))) b.toList } finally rs.close() } finally st.close() }
22
![Page 23: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/23.jpg)
Plain SQL Queries
def personsMatching(pattern: String)(implicit session: Session) = sql"select id, name from person where name like $pattern") .as[(Int, String)].list
23
![Page 24: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/24.jpg)
Agenda
• Key Concepts
• Live Demo
• Under The Hood
• Outlook
24
![Page 25: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/25.jpg)
Live Demo
• Clone it from https://github.com/szeiger/slick-scalaexchange2012
• Scaffolding, tables, mapping, insert
• Query, map, getting results, printing statements
• Comprehension, implicit join, sortBy, table methods, foreign keys
• Finders, foreach, bind variables, templates
• Implicit join, pagination, outer join, Option
• groupBy
25
![Page 26: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/26.jpg)
Agenda
• Key Concepts
• Live Demo
• Under The Hood
• Outlook
26
![Page 27: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/27.jpg)
Under the hood
Slick API
Slick Query Tree
SQL
Native SQL
transformations
Your app
Lifting: Getting Query trees from Scala code
27
![Page 28: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/28.jpg)
How lifting works for( p <- Persons if p.name === "Stefan" ) yield p.name
Column[String] String (implicitly to Column[String])
Persons.withFilter(p=>p.name === "Stefan").map(p=>p.name)
"select name from person where name = 'Stefan'"
Projection("p", Filter("p", Table( Person ), Equals( ColumnRef( "p", "name" ), Constant( name ) ) ), ColumnRef( "p", "name" ) )
Scala desugaring
28
![Page 29: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/29.jpg)
Agenda
• Key Concepts
• Live Demo
• Under The Hood
• Outlook
29
![Page 30: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/30.jpg)
Beyond JDBC
• New back-end architecture
• MongoDB support
• Other NoSQL databases
• Enabling SQL-based non-JDBC drivers (e.g. SQLite on Android)
• Other data sources (e.g. Web Services)
30
![Page 31: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/31.jpg)
Direct Embedding
Slick „lifted embedding“ API
Slick Query Tree
SQL
Native SQL
Slick „direct embedding“ API
transformations Scala AST
Slick macros
Scala compiler
31
![Page 32: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/32.jpg)
Direct Embedding
• Real Scala (types, methods) using macros instead of emulation using lifting – no need to think about differences anymore
– identical syntax • == instead of ===
• if-else instead of Case.If-Else
• …
– identical error messages
• Compile-time optimizations
• More compile-time checks
32
![Page 33: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/33.jpg)
Type Providers
• Based on type macros
object Coffees extends Table[(String, Int, Double)]("COFFEES") { def name = column[String]("NAME") def supID = column[Int ]("SUP_ID") def price = column[Double]("PRICE") def * = name ~ supID ~ price }
33
![Page 34: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/34.jpg)
Type Providers
• Based on type macros
object Coffees extends DBTable( "jdbc:h2:tcp://localhost/~/coffeeShop", "COFFEES")
type DBTable = macro ...
34
![Page 35: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/35.jpg)
Nested Collections
• As seen in the Scala Integrated Query research prototype
for { s <- Suppliers c <- s.coffees } yield (s, c)
Flat result set
35
![Page 36: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/36.jpg)
Nested Collections
• As seen in the Scala Integrated Query research prototype
• Multiple execution strategies are possible
for { s <- Suppliers val cs = s.coffees } yield (s, cs)
Nested collection
36
![Page 37: Slick database access with Scala · Scala Language Integrated Connection Kit •Database query and access library for Scala •Successor of ScalaQuery •Developed at Typesafe and](https://reader035.vdocuments.us/reader035/viewer/2022062506/5f01dadd7e708231d4015cd6/html5/thumbnails/37.jpg)
.typesafe.com
@StefanZeiger
@typesafe