1 / 22

What is Slick and why we use it?

What is Slick and why we use it?. Mark van der Tol. Why we moved to SQL. MongoDB has No joins or referential integrity No transactions Max element size for index A dvantages PostgreSQL has Better indexes Better tooling Strict schema. Problem faced with MongoDB.

lexine
Download Presentation

What is Slick and why we use it?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related