KEMBAR78
Patterns for slick database applications | ODP
Patterns for Slick database
applications
Jan Christopher Vogt, EPFL
Slick Team

#scalax
Recap: What is Slick?
(for ( c <- coffees;
if c.sales > 999
) yield c.name).run

select "COF_NAME"
from
"COFFEES"
where "SALES" > 999
Agenda
• Query composition and re-use
• Getting rid of boiler plate in Slick 2.0
–
–
–

outer joins / auto joins
auto increment
code generation

• Dynamic Slick queries
Query composition and reuse
For-expression desugaring in Scala
for ( c <- coffees;
if c.sales > 999
) yield c.name

coffees
.withFilter(_.sales > 999)
.map(_.name)
Types in Slick
class Coffees(tag: Tag) extends Table[C](tag,"COFFEES”) {
def * = (name, supId, price, sales, total) <> ...
val name = column[String]("COF_NAME", O.PrimaryKey)
val supId = column[Int]("SUP_ID")
val price = column[BigDecimal]("PRICE")
val sales = column[Int]("SALES")
val total = column[Int]("TOTAL")
}
lazy val coffees = TableQuery[Coffees]

<: Query[Coffees,C]

coffees.map(c => c.name)
(coffees:TableQuery[Coffees,_]).map(
(coffees
).map(
(c:Coffees) => (c.name Column[String])
(c.name:
(c
)
)
): Query[Column[String],String]
)
Table extensions
class Coffees(tag: Tag) extends Table[C](tag,"COFFEES”) {
…
val price = column[BigDecimal]("PRICE")
val sales = column[Int]("SALES")
def

revenue = price.asColumnOf[Double] *
sales.asColumnOf[Double]

}

coffees.map(c => c.revenue)
Query extensions
implicit class QueryExtensions[T,E]
( val q: Query[T,E] ){
def page(no: Int, pageSize: Int = 10)
: Query[T,E]
= q.drop( (no-1)*pageSize ).take(pageSize)
}
suppliers.page(5)
coffees.sortBy(_.name).page(5)
Query extensions by Table
implicit class CoffeesExtensions
( val q: Query[Coffees,C] ){
def byName( name: Column[String] )
: Query[Coffees,C]
= q.filter(_.name === name).sortBy(_.name)
}
coffees.byName("ColumbianDecaf").page(5)
Query extensions for joins
implicit class CoffeesExtensions2( val q: Query[Coffees,C] ){
def withSuppliers
(s: Query[Suppliers,S] = Tables.suppliers)
: Query[(Coffees,Suppliers),(C,S)]
= q.join(s).on(_.supId===_.id)
def suppliers
(s: Query[Suppliers, S] = Tables.suppliers)
: Query[Suppliers, S]
= q.withSuppliers(s).map(_._2)
}
coffees.withSuppliers() : Query[(Coffees,Suppliers),(C,S)
coffees.withSuppliers( suppliers.filter(_.city === "Henderson") )
// buyable coffees
coffeeShops.coffees().suppliers().withCoffees()
Query extensions by Interface
trait HasSuppliers{ def supId: Column[Int] }
class Coffees(…)
extends Table... with HasSuppliers {…}
class CofInventory(…) extends Table... with HasSuppliers {…}
implicit class HasSuppliersExtensions[T <: HasSupplier,E]
( val q: Query[T,E] ){
def bySupId(id: Column[Int]): Query[T,E]
= q.filter( _.supId === id )
def withSuppliers
(s: Query[Suppliers,S] = Tables.suppliers)
: Query[(T,Suppliers),(E,S)]
= q.join(s).on(_.supId===_.id)
def suppliers ...
}
// available quantities of coffees
cofInventory.withSuppliers()
.map{ case (i,s) =>
i.quantity.asColumnOf[String] ++ " of " ++ i.cofName ++ " at " ++ s.name
}
Query extensions summary
• Mindshift required!
Think code, not monolithic query strings.
• Stay completely lazy!
Keep Query[…]s as long as you can.
• Re-use!
Write query functions or extensions
methods for shorter, better and DRY code.
Getting rid of boilerplate
Auto joins
Auto joins
implicit class QueryExtensions2[T,E]
( val q: Query[T,E] ){
def autoJoin[T2,E2]
( q2:Query[T2,E2] )
( implicit condition: (T,T2) => Column[Boolean] )
: Query[(T,T2),(E,E2)]
= q.join(q2).on(condition)
}
implicit def joinCondition1
= (c:Coffees,s:Suppliers) => c.supId === s.id
coffees.autoJoin( suppliers ) : Query[(Coffees,Suppliers),(C,S)]
coffees.autoJoin( suppliers ).map(_._2).autoJoin(cofInventory)
Auto incrementing inserts
Auto incrementing inserts
val supplier
= Supplier( 0, "Arabian Coffees Inc.", ... )
// now ignores auto-increment column
suppliers.insert( supplier )
// includes auto-increment column
suppliers.forceInsert( supplier )
Code generatION
Code generator for Slick code
// runner for default config
import scala.slick.meta.codegen.SourceCodeGenerator
SourceCodeGenerator.main(
Array(
"scala.slick.driver.H2Driver",
"org.h2.Driver",
"jdbc:h2:mem:test",
"src/main/scala/", // base src folder
"demo" // package
)
)
Generated code
package demo
object Tables extends {
val profile = scala.slick.driver.H2Driver
} with Tables
trait Tables {
val profile: scala.slick.driver.JdbcProfile
import profile.simple._
case class CoffeeRow(name: String, supId: Int, ...)
implicit def GetCoffees
= GetResult{r => CoffeeRow.tupled((r.<<, ... )) }
class Coffees(tag: Tag) extends Table[CoffeeRow](…){…}
...
OUTER JOINS
Outer join limitation in Slick
suppliers.leftJoin(coffees)
.on(_.id === _.supId)
.run // SlickException: Read NULL value ...
id

name

name

supId

1

Superior Coffee

NULL

NULL

2

Acme, Inc.

Colombian

2

2

Acme, Inc.

French_Roast

2

LEFT JOIN
id

name

name

supId

1

Superior Coffee

Colombian

2

2

Acme, Inc.

French_Roast

2
Outer join pattern
suppliers.leftJoin(coffees)
.on(_.id === _.supId)
.map{ case(s,c) => (s,(c.name.?,c.supId.?,…)) }
.run
.map{ case (s,c) =>
(s,c._1.map(_ => Coffee(c._1.get,c._2.get,…))) }
// Generated outer join helper
suppliers.leftJoin(coffees)
.on(_.id === _.supId)
.map{ case(s,c) => (s,c.?) }
.run
CUSTOMIZABLE Code
generatION
Using code generator as a library
val metaModel = db.withSession{ implicit session =>
profile.metaModel // e.g. H2Driver.metaModel
}
import scala.slick.meta.codegen.SourceCodeGenerator
val codegen = new SourceCodeGenerator(metaModel){
// <- customize here
}
codegen.writeToFile(
profile = "scala.slick.driver.H2Driver”,
folder = "src/main/scala/",
pkg = "demo",
container = "Tables",
fileName="Tables.scala" )
Adjust name mapping
import scala.slick.util.StringExtensions._
val codegen =
new SourceCodeGenerator(metaModel){
override def tableName
= _.toLowerCase.toCamelCase
override def entityName
= tableName(_).dropRight(1)
}
Generate auto-join conditions 1
class CustomizedCodeGenerator(metaModel: Model)
extends SourceCodeGenerator(metaModel){
override def code = {
super.code + "nn" + s"""
/** implicit join conditions for auto joins */
object AutoJoins{
${indent(joins.mkString("n"))}
}
""".trim()
}
…
Generate auto-join conditions 2
…
val joins = tables.flatMap( _.foreignKeys.map{ foreignKey =>
import foreignKey._
val fkt = referencingTable.tableClassName
val pkt = referencedTable.tableClassName
val columns = referencingColumns.map(_.name) zip
referencedColumns.map(_.name)
s"implicit def autojoin${name.capitalize} "+
" = (left:${fkt},right:${pkt}) => " +
columns.map{
case (lcol,rcol) =>
"left."+lcol + " === " + "right."+rcol
}.mkString(" && ")
}
Other uses of Slick code generation
• Glue code (Play, etc.)
• n-n join code
• Migrating databases (warning: types
change)
(generate from MySQL, create in
Postgres)
• Generate repetitive regarding data model
(aka model driven software engineering)
• Generate DDL for external model
Use code generation wisely
• Don’t loose language-level abstraction
• Add your generator and data model to
version control
• Complete but new and therefor
experimental in Slick
Dynamic Queries
Common use case for web apps
Dynamically decide
• displayed columns
• filter conditions
• sort columns / order
Dynamic column
class Coffees(tag: Tag)
extends Table[CoffeeRow](…){
val name = column[String]("COF_NAME",…)
}

coffees.map(c => c.name)
coffees.map(c =>
c.column[String]("COF_NAME")
)

Be careful about security!
Example: sortDynamic

suppliers.sortDynamic("street.desc,city.desc")
sortDynamic 1
implicit class QueryExtensions3[E,T<: Table[E]]
( val query: Query[T,E] ){
def sortDynamic(sortString: String) : Query[T,E] = {
// split string into useful pieces
val sortKeys = sortString.split(',').toList.map(
_.split('.').map(_.toUpperCase).toList )
sortDynamicImpl(sortKeys)
}
private def sortDynamicImpl(sortKeys: List[Seq[String]]) = ???
}

suppliers.sortDynamic("street.desc,city.desc")
sortDynamic 2
...
private def sortDynamicImpl(sortKeys: List[Seq[String]]) : Query[T,E] = {
sortKeys match {
case key :: tail =>
sortDynamicImpl( tail ).sortBy( table =>
key match {
case name :: Nil =>
table.column[String](name).asc
case name :: "ASC" :: Nil => table.column[String](name).asc
case name :: "DESC" :: Nil => table.column[String](name).desc
case o => throw new Exception("invalid sorting key: "+o)
}
)
case Nil => query
}
}
}
suppliers.sortDynamic("street.desc,city.desc")
Summary
• Query composition and re-use
• Getting rid of boiler plate in Slick 2.0
–
–
–

outer joins / auto joins
auto increment
code generation

• Dynamic Slick queries
Thank you

#scalax
slick.typesafe.com
@cvogt
http://slick.typesafe.com/talks/
https://github.com/cvogt/slick-presentation/tree/scala-exchange-2013
filterDynamic
coffees.filterDynamic("COF_NAME like Decaf")

Patterns for slick database applications

  • 1.
    Patterns for Slickdatabase applications Jan Christopher Vogt, EPFL Slick Team #scalax
  • 2.
    Recap: What isSlick? (for ( c <- coffees; if c.sales > 999 ) yield c.name).run select "COF_NAME" from "COFFEES" where "SALES" > 999
  • 3.
    Agenda • Query compositionand re-use • Getting rid of boiler plate in Slick 2.0 – – – outer joins / auto joins auto increment code generation • Dynamic Slick queries
  • 4.
  • 5.
    For-expression desugaring inScala for ( c <- coffees; if c.sales > 999 ) yield c.name coffees .withFilter(_.sales > 999) .map(_.name)
  • 6.
    Types in Slick classCoffees(tag: Tag) extends Table[C](tag,"COFFEES”) { def * = (name, supId, price, sales, total) <> ... val name = column[String]("COF_NAME", O.PrimaryKey) val supId = column[Int]("SUP_ID") val price = column[BigDecimal]("PRICE") val sales = column[Int]("SALES") val total = column[Int]("TOTAL") } lazy val coffees = TableQuery[Coffees] <: Query[Coffees,C] coffees.map(c => c.name) (coffees:TableQuery[Coffees,_]).map( (coffees ).map( (c:Coffees) => (c.name Column[String]) (c.name: (c ) ) ): Query[Column[String],String] )
  • 7.
    Table extensions class Coffees(tag:Tag) extends Table[C](tag,"COFFEES”) { … val price = column[BigDecimal]("PRICE") val sales = column[Int]("SALES") def revenue = price.asColumnOf[Double] * sales.asColumnOf[Double] } coffees.map(c => c.revenue)
  • 8.
    Query extensions implicit classQueryExtensions[T,E] ( val q: Query[T,E] ){ def page(no: Int, pageSize: Int = 10) : Query[T,E] = q.drop( (no-1)*pageSize ).take(pageSize) } suppliers.page(5) coffees.sortBy(_.name).page(5)
  • 9.
    Query extensions byTable implicit class CoffeesExtensions ( val q: Query[Coffees,C] ){ def byName( name: Column[String] ) : Query[Coffees,C] = q.filter(_.name === name).sortBy(_.name) } coffees.byName("ColumbianDecaf").page(5)
  • 10.
    Query extensions forjoins implicit class CoffeesExtensions2( val q: Query[Coffees,C] ){ def withSuppliers (s: Query[Suppliers,S] = Tables.suppliers) : Query[(Coffees,Suppliers),(C,S)] = q.join(s).on(_.supId===_.id) def suppliers (s: Query[Suppliers, S] = Tables.suppliers) : Query[Suppliers, S] = q.withSuppliers(s).map(_._2) } coffees.withSuppliers() : Query[(Coffees,Suppliers),(C,S) coffees.withSuppliers( suppliers.filter(_.city === "Henderson") ) // buyable coffees coffeeShops.coffees().suppliers().withCoffees()
  • 11.
    Query extensions byInterface trait HasSuppliers{ def supId: Column[Int] } class Coffees(…) extends Table... with HasSuppliers {…} class CofInventory(…) extends Table... with HasSuppliers {…} implicit class HasSuppliersExtensions[T <: HasSupplier,E] ( val q: Query[T,E] ){ def bySupId(id: Column[Int]): Query[T,E] = q.filter( _.supId === id ) def withSuppliers (s: Query[Suppliers,S] = Tables.suppliers) : Query[(T,Suppliers),(E,S)] = q.join(s).on(_.supId===_.id) def suppliers ... } // available quantities of coffees cofInventory.withSuppliers() .map{ case (i,s) => i.quantity.asColumnOf[String] ++ " of " ++ i.cofName ++ " at " ++ s.name }
  • 12.
    Query extensions summary •Mindshift required! Think code, not monolithic query strings. • Stay completely lazy! Keep Query[…]s as long as you can. • Re-use! Write query functions or extensions methods for shorter, better and DRY code.
  • 13.
    Getting rid ofboilerplate
  • 14.
  • 15.
    Auto joins implicit classQueryExtensions2[T,E] ( val q: Query[T,E] ){ def autoJoin[T2,E2] ( q2:Query[T2,E2] ) ( implicit condition: (T,T2) => Column[Boolean] ) : Query[(T,T2),(E,E2)] = q.join(q2).on(condition) } implicit def joinCondition1 = (c:Coffees,s:Suppliers) => c.supId === s.id coffees.autoJoin( suppliers ) : Query[(Coffees,Suppliers),(C,S)] coffees.autoJoin( suppliers ).map(_._2).autoJoin(cofInventory)
  • 16.
  • 17.
    Auto incrementing inserts valsupplier = Supplier( 0, "Arabian Coffees Inc.", ... ) // now ignores auto-increment column suppliers.insert( supplier ) // includes auto-increment column suppliers.forceInsert( supplier )
  • 18.
  • 19.
    Code generator forSlick code // runner for default config import scala.slick.meta.codegen.SourceCodeGenerator SourceCodeGenerator.main( Array( "scala.slick.driver.H2Driver", "org.h2.Driver", "jdbc:h2:mem:test", "src/main/scala/", // base src folder "demo" // package ) )
  • 20.
    Generated code package demo objectTables extends { val profile = scala.slick.driver.H2Driver } with Tables trait Tables { val profile: scala.slick.driver.JdbcProfile import profile.simple._ case class CoffeeRow(name: String, supId: Int, ...) implicit def GetCoffees = GetResult{r => CoffeeRow.tupled((r.<<, ... )) } class Coffees(tag: Tag) extends Table[CoffeeRow](…){…} ...
  • 21.
  • 22.
    Outer join limitationin Slick suppliers.leftJoin(coffees) .on(_.id === _.supId) .run // SlickException: Read NULL value ... id name name supId 1 Superior Coffee NULL NULL 2 Acme, Inc. Colombian 2 2 Acme, Inc. French_Roast 2 LEFT JOIN id name name supId 1 Superior Coffee Colombian 2 2 Acme, Inc. French_Roast 2
  • 23.
    Outer join pattern suppliers.leftJoin(coffees) .on(_.id=== _.supId) .map{ case(s,c) => (s,(c.name.?,c.supId.?,…)) } .run .map{ case (s,c) => (s,c._1.map(_ => Coffee(c._1.get,c._2.get,…))) } // Generated outer join helper suppliers.leftJoin(coffees) .on(_.id === _.supId) .map{ case(s,c) => (s,c.?) } .run
  • 24.
  • 25.
    Using code generatoras a library val metaModel = db.withSession{ implicit session => profile.metaModel // e.g. H2Driver.metaModel } import scala.slick.meta.codegen.SourceCodeGenerator val codegen = new SourceCodeGenerator(metaModel){ // <- customize here } codegen.writeToFile( profile = "scala.slick.driver.H2Driver”, folder = "src/main/scala/", pkg = "demo", container = "Tables", fileName="Tables.scala" )
  • 26.
    Adjust name mapping importscala.slick.util.StringExtensions._ val codegen = new SourceCodeGenerator(metaModel){ override def tableName = _.toLowerCase.toCamelCase override def entityName = tableName(_).dropRight(1) }
  • 27.
    Generate auto-join conditions1 class CustomizedCodeGenerator(metaModel: Model) extends SourceCodeGenerator(metaModel){ override def code = { super.code + "nn" + s""" /** implicit join conditions for auto joins */ object AutoJoins{ ${indent(joins.mkString("n"))} } """.trim() } …
  • 28.
    Generate auto-join conditions2 … val joins = tables.flatMap( _.foreignKeys.map{ foreignKey => import foreignKey._ val fkt = referencingTable.tableClassName val pkt = referencedTable.tableClassName val columns = referencingColumns.map(_.name) zip referencedColumns.map(_.name) s"implicit def autojoin${name.capitalize} "+ " = (left:${fkt},right:${pkt}) => " + columns.map{ case (lcol,rcol) => "left."+lcol + " === " + "right."+rcol }.mkString(" && ") }
  • 29.
    Other uses ofSlick code generation • Glue code (Play, etc.) • n-n join code • Migrating databases (warning: types change) (generate from MySQL, create in Postgres) • Generate repetitive regarding data model (aka model driven software engineering) • Generate DDL for external model
  • 30.
    Use code generationwisely • Don’t loose language-level abstraction • Add your generator and data model to version control • Complete but new and therefor experimental in Slick
  • 31.
  • 32.
    Common use casefor web apps Dynamically decide • displayed columns • filter conditions • sort columns / order
  • 33.
    Dynamic column class Coffees(tag:Tag) extends Table[CoffeeRow](…){ val name = column[String]("COF_NAME",…) } coffees.map(c => c.name) coffees.map(c => c.column[String]("COF_NAME") ) Be careful about security!
  • 34.
  • 35.
    sortDynamic 1 implicit classQueryExtensions3[E,T<: Table[E]] ( val query: Query[T,E] ){ def sortDynamic(sortString: String) : Query[T,E] = { // split string into useful pieces val sortKeys = sortString.split(',').toList.map( _.split('.').map(_.toUpperCase).toList ) sortDynamicImpl(sortKeys) } private def sortDynamicImpl(sortKeys: List[Seq[String]]) = ??? } suppliers.sortDynamic("street.desc,city.desc")
  • 36.
    sortDynamic 2 ... private defsortDynamicImpl(sortKeys: List[Seq[String]]) : Query[T,E] = { sortKeys match { case key :: tail => sortDynamicImpl( tail ).sortBy( table => key match { case name :: Nil => table.column[String](name).asc case name :: "ASC" :: Nil => table.column[String](name).asc case name :: "DESC" :: Nil => table.column[String](name).desc case o => throw new Exception("invalid sorting key: "+o) } ) case Nil => query } } } suppliers.sortDynamic("street.desc,city.desc")
  • 37.
    Summary • Query compositionand re-use • Getting rid of boiler plate in Slick 2.0 – – – outer joins / auto joins auto increment code generation • Dynamic Slick queries
  • 38.
  • 39.

Editor's Notes

  • #2 Not an introductory talk, but with some Scala knowledge you should be able to follow even if you don’t know Slick &lt;number&gt;
  • #3 underlines -&gt; implicit conversions lazy query builder, explicit execution &lt;number&gt;
  • #4 &lt;number&gt;
  • #5 Embrace! Slick’s single coolest features! &lt;number&gt;
  • #7 C is element type alias, can be tuple or case class Table is row prototype &lt;number&gt;
  • #8 asColumn &lt;number&gt;
  • #10 C is element type of Coffees here, can be Coffee case class or Tuple &lt;number&gt;
  • #11 that’s the way to do association in slick, because it is lazy, composes val can put into method &lt;number&gt;
  • #13 string computed server side, which means still lazy and composable &lt;number&gt;
  • #14 none of what we have seen actually executed the query. just composed. call .run for ONE roundtrip &lt;number&gt;
  • #21 Run from shell or sbt as sourceGenerator or using separate &lt;number&gt;
  • #22 // not tied to a driver // entity class // table class // GetResult // Slick Hlists for &gt; 22 columns. &lt;number&gt;
  • #25 not big problem, when select exact &lt;number&gt;
  • #27 needs staged sbt build or manual execution &lt;number&gt;
  • #32 interfaces still useful &lt;number&gt;
  • #38 Limitations: - type is string - using db name Can use reflection instead &lt;number&gt;
  • #39 &lt;number&gt;
  • #40 https://github.com/cvogt/slick-presentation/tree/scala-exchange-2013 &lt;number&gt;