ScalikeJDBC / Skinny ORM Beginners' Guide

* http://scalikejdbc.org/
* http://skinny-framework.org/documentation/orm.html

Transcript

1. ScalikeJDBC / Skinny ORM Beginners’ Guide @seratch Kazuhiro Sera (Updated in June 2018)
2. Table of contents What is ScalikeJDBC Getting Started Query DSL More Features What is Skinny ORM Skinny ORM By Examples
3. What is ScalikeJDBC
4. ScalikeJDBC http://scalikejdbc.org/ A tidy SQL-based DB access library for Scala developers. …So, what does “SQL-based” mean?
5. SQL-based import scalikejdbc._ val id = 123 DB.readOnly { implicit session => // SQL Interpolation val name = sql”select name from company where id = ${id}” .map(rs => rs.string(“name”)).single.apply() // Query DSL val name = withSQL { select(c.name).from(Company as c).where.eq(c.id, id) }.map(rs => rs.string(c.resultName.name)).single.apply() } SQL Interpolation; not a simple String interpolation. sql”…” generates a PreparedStatement object internally. Thus, SQL injection vulnerability never happens as far as you use this feature. QueryDSL; Internal DSL to build SQL Interpolation Using the DSL to build SQL statements is more type-safe than using SQL Interpolation directly.
6. The Concept (1) - “Scalike”: Scala-like - Idiomatic - Least surprise - No steep learning curve - Thin wrapper of JDBC - No need to be an expert at: - Scala lang - Any other libraries - Functional programming
7. The Concept (2) - The most practical - Strict backwards compatibility - Multiple data sources support - Detailed logging for debugging - Unit testing support - Handy - Play Framework module - Source code generator
8. Getting Started
9. ConnectionPool import scalikejdbc._ Class.forName(“org.h2.Driver”) // default connection pool val (url, user, password) = (“jdbc:h2:mem:db”, “sa”, “”) ConnectionPool.singleton(url, user, password) val conn: java.sql.Connection = ConnectionPool.borrow() val names: Seq[String] = DB.readOnly { implicit session => 
 sql”select name from company”.map(_.string(“name”).list.apply()
 } // named connection pool val poolSettings = new ConnectionPoolSettings(maxSize = 50) ConnectionPool.add(‘secondary, url, user, password, poolSettings) NamedDB(‘secondary).readOnly { implicit session => Setting up the default connection pool Setting up a named connection pool For more information: http://scalikejdbc.org/documentation/ connection-pool.html

10. Implicit DBSession import scalikejdbc._ val id = 123 // Use the DBSession directly val name: Option[String] = DB readOnly { session: DBSession => session.single(“select name from company where id = ?”, id) { rs => rs.string(“name”)
 } } // Running SQL object requires implicit DBSession val name: Option[String] = DB readOnly { implicit session => sql”select name from company where id = ${id}” .map(_.string(“name”)).single.apply() } 1) SQL#single#apply() internally runs DBSession#single method. 2) If no implicit DBSession is available here, the code results in compilation error! DBSession’s APIs are inspired by github.com/twitter/querulous at the beginning.
11. Flexible Transaction import scalikejdbc._ def addCompany(name: String)(implicit s: DBSession = AutoSession) { sql”insert into company values (${name})”.update.apply()
 } def getAllNames()(implicit s: DBSession = AutoSession): List[String] = { sql”select name from company”.map(_.string(“name”)).list.apply()
 } val names: List[String] = getAllNames() // AutoSession provides a new session DB localTx { implicit session => addCompany(“Typesafe”) // within the transaction getAllNames() // within the transaction, “Typesafe” will be contained in the result }
12. SQLSyntax (sqls) // SQLSyntax = a part of SQL object, will be embedded as-is val c: SQLSyntax = sqls”count(*)” val bobby: String = “Bob%” val query = sql”select ${c} from members where name like ${bobby}” // -> “select count(*) from members where name like ?” A piece of SQL which can be embedded as-is into SQL objects.
13. SQLSyntaxSupport // Scala object case class GroupMember(id: Long, fullName: Option[String] = None) object GroupMember extends SQLSyntaxSupport[GroupMember] { def apply … } // Corresponding DDL create table group_member { id bigint not null primary key, full_name varchar(255)
 } Simply snake_case’d name
14. Configurable? Sure! // Scala object case class GroupMember(id: Long, fullName: Option[String] = None) object GroupMember extends SQLSyntaxSupport[GroupMember] { override val tableName = “group_members” override val nameConverters = Map(“fullName” -> “fullname”) def apply … } // Corresponding DDL create table group_members { id bigint not null primary key, fullname varchar(255)
 }
15. Querying + Extraction // entity class (Plain Old Scala Object) case class Member(id: Long, fullName: Option[String] = None) // companion object for entity object Member extends SQLSyntaxSupport[Member] { 
 def apply(m: ResultName[Member])(rs: WrappedResultSet) = new Member( id = rs.get(m.id), name = rs.get(m.fullName) )
 ) val m = Member.syntax(“m”) val id = 123 val member: Option[Member] = DB readOnly { implicit s => sql”select ${m.result.*} from ${Member as m} where ${m.id} = ${id}” .map(Member(m.resultName)).single.apply()
 }
16. result? resultName? val m = Member.syntax(“m”) m.fullName == “m.full_name” m.result.fullName == “m.full_name as fn_on_m” m.resultName.fullName == “fn_on_m” Scala: sql”select ${m.result.fullName} from ${Member as m} where ${m.id} = 123” SQL: “select m.full_name as fn_on_m from member m where m.id = 123” ResultSet extractor: val name = sql”...”.map(rs => rs.string(m.resultName.fullName)).single.apply() // extracting “fn_on_m” from ResultSet
17. Type-safe Dynamic! import scalikejdbc._ val m = Member.syntax(“m”) val id = 123 val memebr: Option[Member] = DB readOnly { implicit s => sql”select ${m.result.fullNamee} from ${Member as m} where ${m.id} = ${id}” .map(Member(m.resultName)).single.apply()
 } <console>:28: error: Member#fullNamee not found. Expected fields are #id, #fullName, #createdAt, #deletedAt. m.fullNamee ^
18. Query DSL
19. Query DSL import scalikejdbc._ implicit val session = AutoSession val c = Company.syntax(“c”) val id = 123 val company: Option[Company] = withSQL { select.from(Company as c).where.eq(c.id, id) }.map(Company(c.resultName)).single.apply()
 insert.into(Company).values(123, “Typesafe”) val column = Company.column update(Company).set(column.name -> “Oracle”).where.eq(column.id, 123) delete.from(Company).where.eq(column.id, 123) The DSL looks very similar to SQL. It should be intuitive for everybody!
20. Joins, one-to-x API val programmerWithSkills = withSQL { select .from(Programmer as p) .leftJoin(Company as c).on(p.companyId, c.id) .leftJoin(ProgrammerSkill as ps).on(ps.programmerId, p.id) .leftJoin(Skill as s).(ps.skillId, s.id) .where .eq(p.id, id) .and .isNull(p.deletedAt) } .one(Programmer(p, c)) .toMany(SKill.opt(s)) .map { (pg, skills) => pg.copy(skills = skills) } .single.apply() A programmer can have n skills.
21. Subqueries, Pagination val noSkillProgrammers = withSQL { select .from(Programmer as p) .leftJoin(Company as c).on(p.companyId, c.id) .where .notIn(p.id, select(sqls.distinct(ps.programmerId)).from(ProgrammerSkill as ps)) .isNull(p.deletedAt) .limit(10).offset(0) .orderBy(p.id).desc } .map(Programmer(p, c)) .list.apply() Subquery Pagination
22. Embed It Anyway! val userId = val orderCount: Long = withSQL { select(sqls.count(sqls.distinct(o.id))) // sqls object .from(Order as o) .innerJoin(Product as p).on(p.id,o.productId) .where .append(sqls”${o.userId} = ${userId}”) // direct SQL embedding } .map(rs => rs.long(1)) .single.apply().get
23. Other Supported DSLs - insert select - groupBy, having - in, exists, like, between - union, unionAll - withRoundBracket { ... } - dynamic(And|Or)Conditions { ... }
24. More Features
25. Code Generator - sbt plugin - Code from existing tables - project/scalikejdbc.properties - models and tests sbt “scalikejdbc-gen [table-name]”
26. Gentle/detailed logging [debug] s.StatementExecutor$$anon$1 - SQL execution completed [Executed SQL] select id, name from users where email = 'alice@example.com'; (3 ms) [Stack Trace] ... models.User$.findByEmail(User.scala:26) controllers.Projects$$anonfun$index$1$$anonfun$apply$1$$anonfun$apply$2.apply(Projects.scala:20) controllers.Projects$$anonfun$index$1$$anonfun$apply$1$$anonfun$apply$2.apply(Projects.scala:19) controllers.Secured$$anonfun$IsAuthenticated$3$$anonfun$apply$3.apply(Application.scala:88) Issued SQL statement Stack trace dump to figure out where the query was issued in the source code Execution time
27. Testing with ScalaTest import scalikejdbc._, scalatest.AutoRollback import org.scalatest.fixture.FlatSpec class MemberSpec extends FlatSpec with AutoRollback { override def fixture(implicit s: DBSession) { sql”delete from members”.update.apply() Member.create(1, “Alice”) } behavior of “Member” it should “create a new record” in { implicit s => val beforeCount = Member.count Member.create(123, “Brian”) Member.count should equal(before + 1)
 } } The prepared rows by the fixture are also rolled back when the tests complete. All the operations are surely rolled back when the tests completed regardless of the test results.
28. Testing with specs2 (1) import scalikejdbc._, specs2.mutable.AutoRollback import org.specs2.mutable.Specification object MemberSpec extends Specification { “Member should create a new record” in new MyAutoRollback { val beforeCount = Member.count Member.create(123, “Brian”) Member.count should equal(before + 1)
 } } trait MyAutoRollback extends AutoRollback { override def fixture(implicit s: DBSession) { sql”delete from members”.update.apply() Member.create(1, “Alice”) } }
29. Testing with specs2 (2) import scalikejdbc._, specs2.AutoRollback import org.specs2.Specification object MemberSpec extends Specification { def is = “Member should create a new record” ! autoRollback().create end } case class autoRollback() extends AutoRollback { override def fixture(implicit s: DBSession) { sql”delete from members”.update.apply() Member.create(1, “Alice”) } def create = this { val beforeCount = Member.count Member.create(123, “Brian”) Member.count should equal(before + 1)
 } } The prepared rows by the fixture are also rolled back when the tests complete.
30. Play Framework // build.sbt libraryDependencies += "org.scalikejdbc" %% "scalikejdbc-play-dbapi-adapter" % "2.6.0-scalikejdbc-3.2" // conf/application.conf play.modules.enabled += "scalikejdbc.PlayDBApiAdapterModule" scalikejdbc-play-dbapi-adapter is the easiest way to set up ScalikeJDBC in Play applications.
31. What is Skinny ORM
32. The Concept - Straight-forward ORM in Scala - Statically-typed ActiveRecord - Object-oriented > Functional - Convenient for 80% use cases - Flexible enough for the rest - e.g., Querying no-PK tables - The last resort for corner cases: Directly using ScalikeJDBC
33. Skinny ORM By Examples
34. Entity + DAO case class Member( id: Long, name: Option[String] = None, score: Double ) object Member extends SkinnyCRUDMapper[Member] { override lazy val defaultAlias = createAlias("m") override def extract(rs: WrappedResultSet, n: ResultName[Member]) = autoConstruct(rs, rn) } data class representing a table row (plain Scala class) Although defining as the companion is preferred, it works even if it’s not the companion but whatever else. Possible other base traits: SkinnyMapper (read-only), SkinnyNoIdMapper, SkinnyMapperWithId[Id, Entity], etc.
35. Finder API val m = Member.defaultAlias // full scan Member.findAll() // -> Seq[Member] Member.findAll(orderings = Seq(m.id.desc)) Member.count() // -> Long Member.findById(123) // -> Option[Member] val nameIsAlice = sqls.eq(m.name, “Alice”) Member.findBy(nameIsAlice) // -> Option[Member] Member.findAllBy(nameIsAlice) // -> Seq[Member] Member.findAllBy(where = nameIsAlice, orderings = Seq(m.id)) Member.findAllByWithPagination(where = nameIsAlice, pagination = Pagination.page(1).per(10), orderings = Seq(m.id))
36. Querying API val m = Member.defaultAlias val nameIsAlice = sqls.eq(m.name, “Alice”) Member.where(nameIsAlice).apply() // -> Seq[Member] Member.where(‘name -> “Alice”).apply() // -> Seq[Member] Member.where(clause1).where(clause2).apply() // -> Seq[Member] Member.where(‘name -> “Alice”).count() // -> Long // calculations: sum, average, min, max Member.where(‘name -> “Alice”).sum(‘score) // -> BigDecimal Member.where(nameIsAlice).orderBy(m.id.desc) .offset(0).limit(3).apply() // -> Seq[Member] Member.where(nameIsAlice).orderBy(m.id) .paginate(Pagination.page(1).per(3)).apply() // -> Seq[Member]
37. Associations case class Member( id: Long, name: String, companyId: Long, company: Option[Company] = None) object Member extends SkinnyCRUDMapper[Member] { lazy val companyRef = belongsTo[Company](Company, (m, c) => m.copy(company = c)) override lazy val defaultAlias = createAlias("m") override def extract(rs: WrappedResultSet, n: ResultName[Member]) = autoConstruct(rs, rn) } val m = Member.defaultAlias val nameIsAlice = sqls.eq(m.name, “Alice”) Member.joins(Member.companyRef).findAllBy(nameIsAlice) Member.joins(Member.companyRef).where(nameIsAlice).apply() If the field (companyId here) is class name + “Id”, that’s the simplest (just use belongsTo in mapper). If you have a different name, use belongsToWithFk/WithJoinCondition instead.
38. Insert/Update/Delete case class Member(id: Long, name: Option[String] = None, score: Double = 0.0D) object Member extends SkinnyCRUDMapper[Member] { ... } val column = Member.column Member.createWithAttributes(‘name -> “Alice”) Member.createWithNamedValues(column.name -> “Alice”) Member.updateById(123).withAttributes(‘name -> “Bob”) Member.updateBy(sqls.lt(column.id, 123)).withAttributes(‘score -> 0.0D) Member.deleteById(123) Member.deleteBy(sqls.eq(column.name, “Alice”)) #createWithNamedValues is more type- safe than #createWithAttributes. update/deleteById update/deleteBy(condition)
39. Recap What is ScalikeJDBC Getting Started Query DSL More Features What is Skinny ORM Skinny ORM By Examples