.typesafe.com Scala Language-Integrated Connection Kit Jan - - PowerPoint PPT Presentation

▶
typesafe com
SMART_READER_LITE
LIVE PREVIEW

.typesafe.com Scala Language-Integrated Connection Kit Jan - - PowerPoint PPT Presentation

Apr 06, 2024 29 likes •531 views

.typesafe.com Scala Language-Integrated Connection Kit Jan Christopher Vogt Software Engineer, EPFL Lausanne A database query library for Scala person "select * from person" id name or 1 Martin 2 Stefan for( p <- Persons )

slide-1
SLIDE 1

.typesafe.com

Scala Language-Integrated Connection Kit

Jan Christopher Vogt

Software Engineer, EPFL Lausanne

slide-2
SLIDE 2

A database query library for Scala

"select * from person"

  • r

for( p <- Persons ) yield p id name 1 Martin 2 Stefan 3 Chris 4 Eugene … … person

including insert, update, delete, DDL

slide-3
SLIDE 3

http://foter.com/photo/cypresses/

slide-4
SLIDE 4

Slick is to Hibernate and JDBC, what Scala is to Java and Groovy

Slick

  • Easy, Concise, Scalable, Safe, Compositional

Hibernate

  • Complex
  • Scalable, if used with caution
  • HQL: unsafe, non-compositional
  • Criteria Queries: safer, compositional, verbose

JDBC/Anorm

  • SQL: unsafe, non-compositional
slide-5
SLIDE 5

ORM? No. Better Match: Functional Programming

Relational SQL rows expressions NULL … Functional comprehensions tuples / case classes lambdas Option …

slide-6
SLIDE 6

Agenda

  • Key features
  • Live demo
  • Detailed query features
  • Under the hood
  • Upcoming features
slide-7
SLIDE 7

Slick key features

  • Easy

– access stored data like collections – unified session handling

  • Concise

– Scala syntax – fetching results without pain

  • Scales naturally

– stateless – explicit control

  • Safe

– no SQL-injections – compile-time checks (names, types, typos, etc.)

  • Composable

– it‘s Scala code: abstract and re-use with ease

slide-8
SLIDE 8

Easy

  • It‘s Scala – you already know it
  • Access stored data like Scala collections

Persons.withFilter(_.id === 3).map(_.name) for(p <- Persons if p.id === 3) yield p.name identical

Persons id : Int name : String age : Int

slide-9
SLIDE 9

Unified Session Management

  • Unified: URL, DataSource, JNDI
  • Transactions

import org.slick.session._ implicit val session = Database .forURL("jdbc:h2:mem:test1", driver="org.h2.Driver") .createSession session.withTransaction { // execute queries here } session.close()

  • r

.forDataSource( dataSource )

  • r

.forName( JNDIName )

slide-10
SLIDE 10

val name = ... // <- e.g. user input session.createCriteria(Person.getClass) .add( Restrictions.and( .add( Restrictions.gt("age", 20) ) .add( Restrictions.lt("age", 25) ) )) for( p <- Persons if p.age > 20 || p.age < 25 ) yield p

Concise: queries

Hibernate Criteria Queries

slide-11
SLIDE 11

Concise: results

val name = ... // <- e.g. user input val sql = "select * from person where name = ?“ val st = conn.prepareStatement( sql ) try { st.setString(1, name) 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() ( for( p <- Persons if p.name === name ) yield p ).list

JDBC

slide-12
SLIDE 12

Scales naturally

  • Stateless

– No caches

  • Explicit control

– What is transferred – When is it transferred (execution)

( for( p <- Persons if p.name === name ) yield (p.id,p.name) ).list

slide-13
SLIDE 13

http://xkcd.com/327/

slide-14
SLIDE 14

val name = ... // <- e.g. user input "from Person where name = ' " + name + "'" "select * from person wehre name = ' " + name + "'" session.createCriteria(Person.getClass) .add( Restrictions.eq("name", name) ) for( p <- Persons if p.name === name ) yield p

Slick is Safe

Hibernate HQL SQL (JDBC/Anorm)

Fully type-checked: No SQL-injections, no typos, code completion

Hibernate Criteria Queries

slide-15
SLIDE 15

Type-safe use of stored procedures

// stored procedure declaration val dayOfWeekDynamic = SimpleFunction[Int]("day_of_week") def dayOfWeek(c: Column[Date]) = dayOfWeekDynamic(Seq(c)) // stored procedure usage for( p <- Persons ) yield dayOfWeek(p.birthdate) Person birthdate : Date

slide-16
SLIDE 16

// Interests of people between 20 and 25 // Cars of people between 55 and 65 def personByAge( from:Int, to:Int ) = Persons.filter( p => p.age >= from && p.age <= to ) for( p <- personByAge(20, 25); i <- Interests; if i.personId === p.id) yield i.text for( p <- personByAge(55, 65); c <- Cars; if c.personId === p.id) yield c.model

Composable queries

Persons Cars Interests

* *

slide-17
SLIDE 17

SQL fallback

val name = ... // <- e.g. user input ( for( p <- Persons if p.name === name ) yield p ).list val sql = "select * from person where name = ?“ query[String, (Int, String)]( sql )( name ).list

using SQL

Native SQL fallback Not type-safe, but still more convenient than JDBC

slide-18
SLIDE 18

Comparison

JDBC Anorm Slick SQueryl HQL Crit.Q. API (safe, composable)

( )

Concise Scala coll. Syntax SQL-Like Native SQL

18

Unique Slick features coming up soon

slide-19
SLIDE 19

Supported DBMS

JDBC / Anorm Slick Squeryl Hibernate

Oracle DB2 MS SQL Server Sybase MySQL PostgreSQL Derby/JavaDB H2 HSQLDB/HyperSQL MS Access SQLite ( ) ( )

19

NoSQL coming up in Slick: Summer 2013

slide-20
SLIDE 20

Slick in the ecosystem

  • Slick will be official database connector in

Play / Typesafe Stack

  • Successor of ScalaQuery
  • Inspired by LINQ
  • Currently based on JDBC
  • NoSQL coming summer 2013
  • Influenced by Scala Integrated Query
slide-21
SLIDE 21

Stable Versions

  • This talk: Slick 0.11 pre-release for Scala 2.10

– Slick 1.0 coming during Scala 2.10‘s RC period – http://slick.typesafe.com

  • Use ScalaQuery 0.10 for Scala 2.9

– http://scalaquery.org

  • License: BSD
slide-22
SLIDE 22

Live Demo

  • Setup
  • Meta data
  • Queries

– insert some data – find all people above a certain age with their tasks

  • Abstractions

Result at https://github.com/cvogt/slick-presentation

Persons id : Int name : String age : Int Tasks id : Int title : String personId : Int

*

slide-23
SLIDE 23

Sorting and Paging

Persons .sortBy(_.name) .drop(5).take(10)

slide-24
SLIDE 24

Grouping and aggregation

// Number of people per age Persons .groupBy(_.age) .map( p =>( p._1, p._2.length ) )

slide-25
SLIDE 25

First

// person 3 Persons.filter(_.id === 3).first

slide-26
SLIDE 26

Union

Persons.filter(_.age < 18) unionAll Persons.filter(_.age > 65)

slide-27
SLIDE 27

NULL support

case class Person( ..., age : Option[Int] )

  • bject Persons extends Table[Person]("person"){

def age = column[Option[Int]]("id") ... } Persons.insertAll( Person( 1, „Chris“, Some(22) ), Person( 2, „Stefan“, None ) )

slide-28
SLIDE 28

Outer Joins (left, right, full)

for ( Join(p, t) <- Tasks outerJoin Persons

  • n (_.personId === _.id)

) yield p.title.? ~ t.name.?

28

Persons id : Int name : String age : Int Tasks id : Int title : String personId : Option[Int]

*

slide-29
SLIDE 29

Relationships

  • bject Persons extends Table[Person]("person"){

def id = column[Int]("id") ... }

  • bject Tasks extends Table[Task]("task"){

def id = column[Int]("id") ... def assignees = for( pt <- PersonsTasksAssociations; p <- pt.assignee; if pt.taskId === id ) yield p }

  • bject PersonsTasksAssociations extends Table[(Int,Int)]("person_task"){

def personId = column[Int]("person_id") def taskId = column[Int]("task_id") def assignee = foreignKey( "person_fk", personId, Persons )(_.id) ... }

for( t <- Tasks; ps <- t.assignees; if t.id === 1 ) yield ps Persons id : Int …. Tasks id : Int … PersonsTasksAssociations personId : Int taskId : Int

* *

Assignees of task 1:

slide-30
SLIDE 30

Column Operators

Common: .in(Query), .notIn(Query), .count, .countDistinct, .isNull, .isNotNull, .asColumnOf, .asColumnOfType Comparison: === (.is), =!= (.isNot), <, <=, >, >=, .inSet, .inSetBind, .between, .ifNull Numeric: +, -, *, /, %, .abs, .ceil, .floor, .sign, .toDegrees, .toRadians Boolean: &&, ||, .unary_! String: .length, .like, ++, .startsWith, .endsWith, .toUpperCase, .toLowerCase, .ltrim, .rtrim, .trim

slide-31
SLIDE 31

Other features (not exhaustive)

  • auto-increment
  • sub-queries
  • CASE
  • prepared statements
  • custom data types
  • foreach-iteration
  • …
slide-32
SLIDE 32

UNDER THE HOOD

slide-33
SLIDE 33

Under the hood

Slick API Slick Query Tree SQL Native SQL

  • ptimizations

Your app

Lifting: Getting Query trees from Scala code

slide-34
SLIDE 34

How lifting works

for( p <- Persons if p.name === "Chris" ) yield p.name Column[String] String (implicitly to Column[String]) Persons.filter(p=>p.name === "Chris").map(p=>p.name) "select name from person where name = ‘Chris’" Projection( Filter( Table( Person ), Equals( ColumnRef( Person, „name“ ), Constant( name ) ) ), ColumnRef(Person,„name“) ) Scala desugaring

slide-35
SLIDE 35

UPCOMING FEATURES / SLICK MILESTONES

slide-36
SLIDE 36

2012

slide-37
SLIDE 37

Alternative Frontend

Slick „lifted embedding“ API Slick Query Tree SQL Native SQL Slick „direct embedding“ API

  • ptimizations

Scala AST

Slick macros Scala compiler

slide-38
SLIDE 38

Alternative Frontend

  • 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-when
  • …

– identical error messages

  • Compile-time optimizations
  • More compile-time checks
slide-39
SLIDE 39

SUMMER 2013

slide-40
SLIDE 40

Type providers using macros

  • schema auto-generated from database
  • compiler checks queries against real database

schema

  • bject Persons extends Table( „person“ )

A macro which connects to the db at compile time to fetch schema

slide-41
SLIDE 41

Extensible backend

Slick „lifted embedding“ API Slick Query Tree SQL Native SQL

  • ptimizations

Other backends, e.g. NoSQL like MondoDB You can hook in here

slide-42
SLIDE 42

BEGINNING OF 2014

slide-43
SLIDE 43

Scheduling over multiple backends

for( p <- Persons; t <- Tasks if p.id … && t.id … ) yield ( p, t ) Coming from datasource 1, e.g. Orcale SQL DB Coming from datasource 2, e.g. MongoDB or webservice

slide-44
SLIDE 44

Nested Results

  • As demonstrated in

Scala Integrated Query / Ferry

for( p <- Persons ) yield ( p, for( t <- Tasks; if … ) yield t ) . list : List[ ( Person, List[Task] ) ]

slide-45
SLIDE 45

MAYBE 2013

slide-46
SLIDE 46

Comprehensive Comprehensions

  • For-comprehension support for

– Sorting – Grouping – …

  • We are still thinking about it
slide-47
SLIDE 47

Summary

Slick makes database access

  • easy, concise, scalable, safe, composable

Upcoming features will make Slick

  • easier, extensible, faster, more powerful
slide-48
SLIDE 48

Thank you! Questions?

.typesafe.com

Jan Christopher Vogt Stefan Zeiger Martin Odersky Eugene Burmako

slide-49
SLIDE 49

EXTRA SLIDES

slide-50
SLIDE 50

Direct Embedding

== (no need for ===) Person.filter(p=>p.name == name).map(p=>p) String String macro (Scala 2.10) Macro works on this expression‘s Scala AST at compile time Projection( Filter( Table( Person ), Equals( ColumnRef( Person, „name“ ), Constant( name ) ) ), „*“ ) generates Arbitrary compile time checks

  • r optimizations possible