Presentation Slick vs. ORM

Slick is not an object-relational mapper, but a functional-relational mapper leading to unique benefits. Using Slick efficiently may be counter-intuitive to people familiar with ORM systems. This talk explains how common ORM use cases should me implemented differently using Slick and what the benefits are. In short, ORM systems suffer from the consequences of the object-relational impedance mismatch, where in contrast Slick's mapping to functional programming constructs is rather straight forward. This allows for tight integration with Scala and highly re-useable code for composing queries.

Speakers


PDF: slides.pdf

Slides

Slick vs ORM

Slick vs ORM Jan Christopher Vogt, EPFL Stefan Zeiger, Typesafe

Object-Orientation + Relational

Object-Orientation + Relational

Functional + Relational

Functional + Relational

Functional + Relational

Functional + Relational

• Slick is a Functional-Relational Mapper

• Slick is a Functional-Relational Mapper • embraces relational (not hidden) • natural fit (no impedance mismatch) • stateless (not stateful) • Slick is to ORM what Scala is to Java

8 Reasons for using Slick

8 Reasons for using Slick

Sample App Data Model

Sample App Data Model Device id:  Long price:  Double acquisi5on:  Date

Scala collection-like API

Scala collection-like API for ( d <- Devices; if d.price > 1000.0 ) yield d.acquisition Query(Devices) .filter(_.price > 1000.0) .map(_.acquisition) Device id:  Long price:  Double acquisi5on:  Date

Configuration

Configuration • Do mappings in Scala • No XML, no "magic" behind the scenes • Connect to a JDBC URL or DataSource – Use an external connection pool • Wrap raw JDBC connections for use with DI containers that handle the transaction management

Connect

Connect import scala.slick.driver.H2Driver.simple._ val db = Database.forURL( "jdbc:h2:mem:test1", "org.h2.Driver") db.withSession { implicit s: Session => … } db.withTransaction { implicit s: Session => … }

Keep Your Data Model Clean

Keep Your Data Model Clean case class Device(id: Long, price: Double, acquisition: Date) class Devices extends Table[Device]("DEVICE") { def id = column[Long]("ID", O.PrimaryKey) def price = column[String]("PRICE") def acquisition = column[Date]("ACQUISITION") def * = id ~ price ~ acquisition <> (Device.apply _, Device.unapply _) } val Devices = new Devices

Keep Your Data Model Clean

Keep Your Data Model Clean ...or  omit  it class Devices extends Table[(Long, String, Date)]("DEVICE") { def id = column[Long]("ID", O.PrimaryKey) def price = column[String]("PRICE") def acquisition = column[Date]("ACQUISITION") def * = id ~ price ~ acquisition } val Devices = new Devices

Keep Your Data Model Clean

Keep Your Data Model Clean case class Device(id: Long, price: Double, acquisition: Date) …but  keep  iden5ty  explicit

Custom Column Types

Custom Column Types case class Device(id: DeviceId, …) class DeviceId(val id: Long) extends AnyVal

Custom Column Types

Custom Column Types case class Device(id: DeviceId, …) class DeviceId(val id: Long) extends AnyVal implicit val deviceIdType = MappedTypeMapper.base [DeviceId, Long](_.id, new DeviceId(_))

Custom Column Types

Custom Column Types case class Device(id: DeviceId, …) class DeviceId(val id: Long) extends AnyVal implicit val deviceIdType = MappedTypeMapper.base [DeviceId, Long](_.id, new DeviceId(_)) class Devices extends Table[Device]("DEVICE") { def id = column[DeviceId]("ID", O.PrimaryKey)

Custom Functions

Custom Functions

Custom Functions

Custom Functions def dayOfWeek(c: Column[Date]) = SimpleFunction[Int]("DAY_OF_WEEK").apply(Seq(c))

Custom Functions

Custom Functions def dayOfWeek(c: Column[Date]) = SimpleFunction[Int]("DAY_OF_WEEK").apply(Seq(c)) val dows = Query(Devices).map { d => (d.id, dayOfWeek(d.acquisition)) }.run

Work With Any DB Schema

Work With Any DB Schema • You define the schema: class Devices extends Table[Device]("DEVICE") { def id = column[Long]("ID", O.PrimaryKey) def price = column[String]("PRICE") def acquisition = column[Date]("ACQUISITION") def * = id ~ price ~ acquisition <> (Device.apply _, Device.unapply _) } val Devices = new Devices

Work With Any DB Schema

Work With Any DB Schema • You define the schema: class Devices extends Table[Device]("DEVICE") { def id = column[Long]("ID", O.PrimaryKey) def price = column[String]("PRICE") def acquisition = column[Date]("ACQUISITION") def * = id ~ price ~ acquisition <> (Device.apply _, Device.unapply _) } val Devices = new Devices Map  to  anything

Work With Any DB Schema

Work With Any DB Schema • You define the schema: class Devices extends Table[Device]("DEVICE") { def id = column[Long]("ID", O.PrimaryKey) def price = column[String]("PRICE") def acquisition = column[Date]("ACQUISITION") def * = id ~ price ~ acquisition <> (Device.apply _, Device.unapply _) } val Devices = new Devices Use  any  func5on Map  to  anything

Execution is always explicit

Execution is always explicit Device val query = for { d <- Devices if d.price > 1000.0 } yield d.acquisition val results = query.run id:  Long price:  Double acquisi5on:  Date

Execution is always explicit

Execution is always explicit Device val query = for { d <- Devices if d.price > 1000.0 } yield d.acquisition val results = query.run id:  Long price:  Double acquisi5on:  Date (session)

Execution is always explicit

Execution is always explicit Device val query = for { d <- Devices if d.price > 1000.0 } yield d.acquisition val results = query.run id:  Long price:  Double acquisi5on:  Date

Example Data Model

Example Data Model Device id:  Long price:  Double acquisi5on:  Date

Example Data Model

Example Data Model Device id:  Long price:  Double acquisi5on:  Date Site n 1 id:  Long name:  String

Transferred data

Transferred data ORM 3  statements, complete  Device  object  loaded val device = Device.byId(123L) : Device val site = Site("New York") Device device.site = site ORM.save id:  Long price:  Double acquisi5on:  Date Site n 1 id:  Long name:  String Slick val device = Queries(Devices).byId(123L) : Query[Devices,Device] val site = Site(None,"New York") val siteId = Sites.autoInc.insert( site ) device.map(_.siteId).update(siteId)

Transferred data

Transferred data ORM 3  statements, complete  Device  object  loaded val device = Device.byId(123L) : Device val site = Site("New York") Device device.site = site ORM.save id:  Long 2  statements, price:  Double nothing  loaded, acquisi5on:  Date Slick Site n 1 id:  Long name:  String device  re-­‐usable  as  query  components val device = Queries(Devices).byId(123L) : Query[Devices,Device] val site = Site(None,"New York") val siteId = Sites.autoInc.insert( site ) device.map(_.siteId).update(siteId)

Predictable SQL structure

Predictable SQL structure Query(Devices) .filter(_.price > 1000.0) .map(_.acquisition) .selectStatement select x2."aquisition" from "DEVICE" x2 where x2."price" > 1000.0

Plain SQL support

Plain SQL support val price = 1000.0 val expensiveDevices: List[Device] = sql"select * from device where price > $price" .as[Device].list Device id:  Long price:  Double acquisi5on:  Date

Plain SQL support

Plain SQL support val price = 1000.0 val expensiveDevices: List[Device] = sql"select * from device where price > $price" .as[Device].list Device id:  Long price:  Double acquisi5on:  Date implicit val getDeviceResult = GetResult(r => Device(r.<<, r.<<, r.<<))

Plain SQL support

Plain SQL support val price = 1000.0 val expensiveDevices: List[Device] = sql"select * from device where price > $price" .as[Device].list Device id:  Long price:  Double acquisi5on:  Date implicit val getDeviceResult = GetResult(r => Device(r.<<, r.<<, r.<<))

Enforce schema consistency

Enforce schema consistency • Generate DDL from table objects • Slick 2.0: Generate table objects and mapped classes from database

Compile-Time Safety

Compile-Time Safety • Spelling mistake in column name? • Wrong column type? • Mapped to the wrong class?

Compile-Time Safety

Compile-Time Safety • Spelling mistake in column name? • Wrong column type? • Mapped to the wrong class? scalac  sees  it  all!

Compile-Time Safety

Compile-Time Safety • Error messages can destroy the illusion

Compile-Time Safety

Compile-Time Safety • Error messages can destroy the illusion

Relationships

Relationships • ORM – device.getSite : Site – site.getDevices : List[Device]

Relationships

Relationships • ORM – device.getSite : Site – site.getDevices : List[Device] • Slick – use relational queries with joins Device id:  Long price:  Double acquisi5on:  Date Site n 1 id:  Long name:  String

Joins

Joins val sitesToDevices = (s:Sites,i:Devices) => s.id === i.siteId val sites = Query(Sites) .filter(_.id === 1L) val devices = Query(Devices).filter(_.price > 1000.0) sites.join( devices ).on( sitesToDevices ) sites join devices on sitesToDevices Device id:  Long price:  Double acquisi5on:  Date Site n 1 id:  Long name:  String

Auto joins (1-n)

Auto joins (1-n) implicit def autojoin1 = joinCondition [Sites,Devices] (_.id === _.siteId) sites autoJoin devices devices autoJoin sites sites.autoJoin( devices, JoinType.Left ) Device id:  Long price:  Double acquisi5on:  Date Site n 1 id:  Long name:  String

Join types

Join types sites leftJoin devices on sitesToDevices sites rightJoin devices on sitesToDevices sites outerJoin devices on sitesToDevices sites.autoJoin( devices, JoinType.Left ) sites.autoJoin( devices, JoinType.Right ) sites.autoJoin( devices, JoinType.Outer ) Device id:  Long price:  Double acquisi5on:  Date Site n 1 id:  Long name:  String

Example Data Model

Example Data Model Device Computer Id:  Long Name:  String companyId:  Int 1 n id:  Long price:  Double acquisi5on:  Date Site n 1 id:  Long name:  String

Modifying relationships

Modifying relationships • query for what you want to modify • insert / update that query val device = Queries(Devices).byId(123L) val site = Site(None,"New York") val siteId = Sites.autoInc.insert( site ) device.map(_.siteId).update(siteId) Site id:  Long name:  String

What about Inheritance?

What about Inheritance?

Inheritance

Inheritance • Relational does not support inheritance • Use relationships instead, you won’t loose (think “has role” instead of “is a”)

Example Data Model

Example Data Model Site id:  Long name:  String 1 n ResearchSite id:  Long siteId:  Long size:  Size 1 n Produc5onSite id:  Long siteId:  Long volume:  Int

Joining all “sub-classes”

Joining all “sub-classes” • a re-usable query that joins Sites with ResearchSites and ProductionSites sites .autoJoin( researchSites, JoinType.Left ) .autoJoinVia( productionSites, JoinType.Left )(_._1) : Query[_,((Site,ResearchSite),ProductionSite)]

You can do inheritance

You can do inheritance Site id:  Long name:  String size:  OpCon[Size] class Sites extends Table[Site]{ ... def * = ... <> ( {case (id,name,Some(size),_) => ResearchSite(id,name,size) case (id,name,_,Some(volume)) => ProductionSite(id,name,volume) }, {case ResearchSite(id,name,size) => (id,name,Some(size),None) case ProductionSite(id,name,volume) => (id,name,None,Some(volume)) } ) }

Mental paradigm shift

Mental paradigm shift The ORM Way: Executor APIs (DAOs) DevicesDAO .inPriceRange( 500.0, 2000.0 ) : List[Device]

Mental paradigm shift

Mental paradigm shift The ORM Way: Executor APIs (DAOs) DevicesDAO .inPriceRange( 500.0, 2000.0 ) : List[Device] The Slick Way: Query libraries ( devices : Query[_,Device] ) .inPriceRange( 500.0, 2000.0 ) : Query[_,Device]

Mental paradigm shift

Mental paradigm shift The ORM Way: Executor APIs (DAOs) DevicesDAO .inPriceRange( 500.0, 2000.0 ) : List[Device] The Slick Way: Query libraries ( devices : Query[_,Device] ) .inPriceRange( 500.0, 2000.0 ) : Query[_,Device] some  stuff  with  criteria  queries,  but  Slick  for  everything  including  joins,  groupBy

Write query libraries

Write query libraries

Row functions

Row functions class Sites extends Table[Site]{ def name = column( ... ) ... def nameLike( pattern:Column[String] ) : Column[Boolean] = name.toLowerCase like pattern.toLowerCase } Query(Sites).filter( _.nameLike(“EPFL”) )

Row functions

Row functions trait HasName{ this:Table[_] => def name = column( ... ) ... def nameLike( pattern:Column[String] ) : Column[Boolean] = this.name.toLowerCase like pattern.toLowerCase } class Sites extends Table[Site] with HasName class Computers extends Table[Computer] with HasName

Query functions

Query functions def byName[E,T <: Table[E] with HasName] ( q:Query[T,E], pattern:Column[String] ) = q.filter( _nameLike.(pattern) ) byName( Query(Sites), “EPFL” )

Refinements

Refinements • method extensions (implicit conversions) • Option support using Slick’s OptionMapper

Suggested Slick app architecture

Suggested Slick app architecture View Controller Database  Session

Suggested Slick app architecture

Suggested Slick app architecture View Table  objects HasName{    def  nameLike(  Column[…]  )        :  Column[  …  ] }, Devices, Sites … Controller Database  Session

Suggested Slick app architecture

Suggested Slick app architecture View Controller Table  objects Query  Library HasName{    def  nameLike(  Column[…]  )        :  Column[  …  ] }, Devices, Sites … def  byName(    Query[…],    Column[…] )  :  Query[  …  ] def  byId(  Column[…]  )  :  Query[  …  ] … Database  Session

Suggested Slick app architecture

Suggested Slick app architecture View Controller Database  Session Table  objects Query  Library Executor  API  /  DAO HasName{    def  nameLike(  Column[…]  )        :  Column[  …  ] }, Devices, Sites … def  byName(    Query[…],    Column[…] )  :  Query[  …  ] def  byName(  String  )  :  List[…] def  byId(  id:Long  )  :  Site def  byId(  Column[…]  )  :  Query[  …  ] …

Outlook

Outlook

Slick 2.0

Slick 2.0 • Coming Q3 / 2013 • Query scheduling • Improved driver architecture – BasicProfile >: RelationalProfile >: SqlProfile >: JdbcProfile • Generate Slick code from database schemas – Proper "type providers" need new Scala • More to come

slick.typesafe.com

slick.typesafe.com @cvogt @StefanZeiger hYp://slick.typesafe.com/talks/2013_scaladays/2013_scaladays.pdf hYps://github.com/slick/play-­‐slick/tree/scaladays2013