Patterns for slick database applications

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

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]
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.

Auto joins
implicit class QueryExtensions2[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
val supplier
= Supplier( 0, "Arabian Coffees Inc.", ... )
// now ignores auto-increment column
suppliers.insert( supplier )
// includes auto-increment column
suppliers.forceInsert( supplier )

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 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
.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
.map{ case(s,c) => (s,c.?) }
.run

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

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]]) = ???
}


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
}
}
}

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

More Related Content

What's hot

Viewers also liked

Similar to Patterns for slick database applications

More from Skills Matter

Recently uploaded

Patterns for slick database applications

Editor's Notes