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)
   

   View Slide

2. Table of contents
   What is ScalikeJDBC
   Getting Started
   Query DSL
   More Features
   What is Skinny ORM
   Skinny ORM By Examples
   

   View Slide

3. What is
   ScalikeJDBC
   

   View Slide

4. ScalikeJDBC
   http://scalikejdbc.org/
   A tidy SQL-based DB access library for
   Scala developers.
   …So, what does “SQL-based” mean?
   

   View Slide

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.
   

   View Slide

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
   

   View Slide

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
   

   View Slide

8. Getting Started
   

   View Slide

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

   

   View Slide

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.
    

    View Slide

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
    }
    

    View Slide

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.
    

    View Slide

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
    

    View Slide

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)

    }
    

    View Slide

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()

    }
    

    View Slide

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
    

    View Slide

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()

    }
    :28: error: Member#fullNamee not found. Expected fields are #id,
    #fullName, #createdAt, #deletedAt.
    m.fullNamee
    ^
    

    View Slide

18. Query DSL
    

    View Slide

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!
    

    View Slide

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.
    

    View Slide

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
    

    View Slide

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
    

    View Slide

23. Other Supported DSLs
    - insert select
    - groupBy, having
    - in, exists, like, between
    - union, unionAll
    - withRoundBracket { ... }
    - dynamic(And|Or)Conditions { ... }
    

    View Slide

24. More Features
    

    View Slide

25. Code Generator
    - sbt plugin
    - Code from existing tables
    - project/scalikejdbc.properties
    - models and tests
    sbt “scalikejdbc-gen [table-name]”
    

    View Slide

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
    

    View Slide

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.
    

    View Slide

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

    View Slide

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.
    

    View Slide

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.
    

    View Slide

31. What is
    Skinny ORM
    

    View Slide

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
    

    View Slide

33. Skinny ORM
    By Examples
    

    View Slide

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.
    

    View Slide

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

    View Slide

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]
    

    View Slide

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.
    

    View Slide

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)
    

    View Slide

39. Recap
    What is ScalikeJDBC
    Getting Started
    Query DSL
    More Features
    What is Skinny ORM
    Skinny ORM By Examples
    

    View Slide