What is Slick and why we use it?

1. What is Slick and why we use it? Mark van der Tol

2. Why we moved to SQL MongoDB has • No joins or referential integrity • No transactions • Max element size for index Advantages PostgreSQL has • Better indexes • Better tooling • Strict schema

3. Problem faced with MongoDB Query unseen alerts for users Data: { "message": "Example alert", "seenBy": [...] } Query: { "seenBy": { "$ne": "user" } } Max element size for indexed elements: 1 KB

4. Trivial in SQL SELECT message FROM alerts WHERE id NOT IN ( SELECT messageId FROM views WHERE user = "user" )

5. MongoDB • We keep using Mongo for statistics data • Easier to shard/replicate • No schema enforcement

6. What is Slick • Database query library for Scala • Table mapping • Strongly typed • Collection like syntax

7. Available for • DB2* • Derby/JavaDB • H2 • HSQLDB/HyperSQL • Microsoft Access • Microsoft SQL Server • MySQL • Oracle* • PostgreSQL • SQLite *Requires subscription for production use

8. Database connection importscala.slick.driver.PostgresDriver.simple._ importDatabase.threadLocalSession Database.forURL("jdbc:postgresql://…", driver = "org.postgresql.Driver") withSession {//session is now implicitly available in thread-localstorage } Database.forURL("jdbc:postgresql://…", driver = "org.postgresql.Driver") withTransaction {//session is now implicitly available in thread-local storage }

9. Table definition objectCoffeeTable extends Table[(String, BigDecimal, Int)]("COFFEE") { def name = column[String]("NAME", O.PrimaryKey)def price = column[BigDecimal]("PRICE")def sales = column[Int]("SALES") def * = name ~ price ~ sales }

10. Simple SELECT query val minPrice: BigDecimal = 1.0 val query = for { c <- CoffeeTable if (c.price >= minPrice) } yield (c.name) val names = query.list

11. Table definition for Case class caseclassCoffee (name: String, price: BigDecimal, sales: Int) objectCoffeeTable extendsTable[Coffee]("coffee") { def name = column[String]("NAME", O.PrimaryKey)def price = column[BigDecimal]("PRICE")def sales = column[Int]("SALES")def * = name ~ price ~ sales <> (Coffee, Coffee.unapply _) }

12. Simple SELECT query val query = for { c <- CoffeeTable } yield (c) val coffees = query.list

13. SELECT query with join val query = for{ p <- PersonTable c <- CoffeeTable if(p.favoriteCoffee === c.name) } yield (p.name, c.name, c.price)

14. INSERT query CoffeeTable.insert( Coffee("Java", 2.50, 0) )

15. UPDATE/DELETE query val query = for { c <- CoffeeTable if (c.price < 1.50) } yield (c.price) query.update(1.50) query.delete

16. Plain SQL queries val query = StaticQuery .query[BigDecimal, (String, Int)]( """select c.name, c.sales from coffees c where c.price < ?""" )

17. Features not shown • Queries with parameters • Extensible: Add own types and functions • "Direct embedding"

18. Features • Query library that stays close to SQL • Hides dbms specific syntax • Prevents SQL-injections • Many checks compile time

19. Disadvantages • DSL not always intuitive • Difficult compile errors • Focus on single thread usage • Not very comprehensive documentation

20. Multi-threaded use workaround val session = Database.forURL("jdbc:…", driver = “…").createSession() session.conn.setAutoCommit(false) //prevent Slick from creating transactions itself session.asInstanceOf[BaseSession].inTransaction = true … session.conn.commit() session.close()

21. Resources • Slick: http://slick.typesafe.com/http://groups.google.com/group/scalaquery • Sheets: http://www.plotprojects.com/

22. Thank you