Repository

https://github.com/pdolega/slick-101

Preso

http://slides.com/pdolega/slick-unicorns

What is Slick?

Not an ORM

ORM problems

N+1 problem (lazy / eager fetching)

Session context scope

Execution under the cover (cache)

O-R impedance mismatch

False promise

Leaky abstraction

FRM

Embrace database model through a functional paradigm.

Type-Safe

Reactive (™?)

...wait a minute, JDBC is blocking anyway

Everything is async...

db_pool_connections =

(core_count * 2) + effective_spindle_count

source: postgres docs

Brilliant presentation by @StefanZeiger: Reactive Slick for Database Programming

Server handling 10 000 client connections may need not more than 10 connection

It's still viable

If you were to remember only one thing

Monadic Trio

DBIO

Query

description of a DB query

description of 1...N DB operations

Future

well... you know

Tables


  // in-application respresentation of table's tuple
  case class University(name: String,
                        id: Long = -1L)

  // definition of table
  class UniversityTable(tag: Tag) extends Table[University](
    tag, "university") {
    
    def name = column[String]("name")
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

    // default projection
    def * = (name, id) <> (University.tupled, 
                           University.unapply)
  }

  // 'table' object used for interacting with in app
  lazy val UniversityTable = TableQuery[UniversityTable]

Actions

Monadic Trio



val futureResults = memDb.run(
    UniversityTable                            
    .filter(_.name === "Hogwarth") // Query
    .result               // DBIOAction (DBIO)
)    // Future

// futureResults is of type 
// Future[Seq[University]]
futureResults.onComplete {
  case Success(unis) => 
    for (uni <- unis) println(uni)
  case Failure(e) => 
    e.printStackTrace()
}

DBIOAction / DBIO

DBIOAction[R, S, E]

R - type of result
S - streaming / not streaming
E - what kind of effect

DBIO[R] =

DBIOAction[R, NoStream, Effect.All]

R - type of result

  def executeReadOnly[R, S <: dbio.NoStream](
    readOnlyOper: DBIOAction[R, S, Effect.Read] // param decl
  ): Future[Unit] = { // result type
    db.run(readOnlyOper).map { results =>
      log.info(s"Results are: $results")
    }
  }

// this works
executeReadOnly(UniversityTable.result)

// this won't even compile
executeReadOnly(UniversityTable += University("Nice try!"))

// DBIOAction[Seq[University], Stream, Effect.Read]
db.run(
    UniversityTable.result
)
...
// DBIOAction[Option[Int], NoStream, Effect.Write]
db.run(
    UniversityTable += University("California")
)
...
// DBIOAction[Option[Int], NoStream, 
// Effect.Write with Effect.Transactional]
db.run(
    DBIO.seq(
        UniversityTable += University("Massachusetts"),
        UniversityTable += University("California")
    ).transactionally
)
...
// DBIOAction[Unit, NoStream, Effect.Schema]
db.run(
    UniversityTable.schema.create
)

Queries

Query

Query[M, U, C]

M - mixed type

U - unpacked type

C - collection type


UniversityTable                            
    .filter(_.name === "Hogwarth")

What are unpacked (U), mixed (M) and collection (C) types?


UniversityTable                            
    .filter(_.name === "Hogwarth")    
    // _ above is UniverityTable (M)


  // unpacked type
  case class University(name: String,
                        id: Long = -1L)

  // mixed type
  class UniversityTable(tag: Tag) extends Table[University](
    tag, "university") {
    
    def name = column[String]("name")
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

    // default projection
    def * = (name, id) <> (University.tupled, 
                           University.unapply)
  }








db.run(    // Future[Seq[University]]

   UniversityTable.filter(uni => // Query[UniversityTable,
                                 // University, Seq]
       uni.name === "Hogwarth"
   )
   .result    // DBIO[Seq[University]]

)

Do you speak it?!

Queries

    select  "NAME", 
            "MIDDLE_NAME", 
            "SURNAME", 
            "NATIONALITY", 
            "ID" 
    
    from    "STUDENT"




    StudentTable

// or

    for {student <- StudentTable }
        yield student

      


    StudentTable
        .map(_.name)

    

    select "NAME" 
    
    from "STUDENT"

    StudentTable
        .filterNot(student => 
            student.name === "Tom" && 
            student.surname.startsWith("Smi")
    )
    // or
    for {
        student <- StudentTable if !(
                student.name === "Tom" && 
                student.surname.startsWith("Smi"))
    } yield student


    select "NAME", 
           "MIDDLE_NAME", 
           "SURNAME", 
           "NATIONALITY", 
           "ID" 

    from "STUDENT" 
    
    where not (("NAME" = 'Tom') and 
               ("SURNAME" like 'Smi%' escape '^'))



    StudentTable
        .filter(student => student.middleName.nonEmpty)
        .sortBy(s => (s.name.desc, s.middleName.asc))
        .distinct


    select distinct "NAME", ... 

    from "STUDENT" 

    where "MIDDLE_NAME" is not null 

    order by "NAME" desc, "MIDDLE_NAME"



      StudentTable
        .map(s => (s.name, s.surname))
        .take(3)
        .drop(2)


    select ... 

    from "STUDENT" 

    limit 1 offset 3

Joins

Monadic

applicative


  // student course segment
  case class StudentCourseSegment(studentId: Long,
                                  courseId: Long,
                                  semesterId: Long,
                                  id: Long)

  class StudentCourseSegmentTable(tag: Tag) extends 
    Table[StudentCourseSegment](tag, "STUDENT_COURSE_SEGMENT") {
    def studentId = column[Long]("STUDENT_ID")
    def courseId = column[Long]("COURSE_ID")
    def semesterId = column[Long]("SEMESTER_ID")
    def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)

    def * = (studentId, courseId, semesterId, id) <> (StudentCourseSegment.tupled, 
                                                      StudentCourseSegment.unapply)

    // foreign keys
    def student = foreignKey("fk_segment_student", studentId, StudentTable)(_.id)
    def course = foreignKey("fk_segment_course", courseId, CourseTable)(_.id)
    def semester = foreignKey("fk_segment_semester", semesterId, SemesterTable)(_.id)
  }

  lazy val StudentCourseSegmentTable = TableQuery[StudentCourseSegmentTable]




def student = 
    foreignKey("fk_segment_student", 
        studentId, StudentTable)(_.id)
def course = 
    foreignKey("fk_segment_course", 
        courseId, CourseTable)(_.id)
def semester = 
    foreignKey("fk_segment_semester", 
        semesterId, SemesterTable)(_.id)

db.run((
    for {
      segment <- StudentCourseSegmentTable
      course <- segment.course // foreign key
      student <- segment.student // foreign key
    } yield (course, student)
)

db.run(
        StudentCourseSegmentTable
          join CourseTable on (_.courseId === _.id)
          join StudentTable on (_._1.studentId === _.id)
        ).map {
            case ((segment, course), student) => 
                (course, student)
        }
)

Monadic

Applicative

More complicated join

Applicative vs monadic

StudentCourseSegmentTable
    .join(StudentTable)
        .on { case (segment, student) => 
            student.id === segment.studentId }
    .join(CourseTable)
        .on { case ((segment, _), course) => 
            course.id === segment.courseId }
    .join(SemesterTable)
        .on { case (((segment, _), _), semester) => 
            semester.id === segment.semesterId }
    .filter { case (((_, student), _), _) =>
        student.name === "Tim"
    }

    select x2."NAME",
           ...
    from    "STUDENT_COURSE_SEGMENT" x2, 
            "STUDENT" x3, 
            "COURSE" x4, 
            "SEMESTER" x5 

    where   (x3."NAME" = 'Tim') and 
            (((x3."ID" = x2."STUDENT_ID") and 
            (x4."ID" = x2."COURSE_ID")) and 
            (x5."ID" = x2."SEMESTER_ID"))

    for {
      segment <- StudentCourseSegmentTable
      student <- segment.student if student.name === "Tim"
      course <- segment.course
      semester <- segment.semester
    } yield (segment, student, course, semester)

    select x2."NAME",
           ...
    from    "STUDENT_COURSE_SEGMENT" x2, 
            "STUDENT" x3, 
            "COURSE" x4, 
            "SEMESTER" x5 

    where   (x3."NAME" = 'Tim') and 
            (((x3."ID" = x2."STUDENT_ID") and 
            (x4."ID" = x2."COURSE_ID")) and 
            (x5."ID" = x2."SEMESTER_ID"))

Sometimes one form is more elegant than the other

Outer joins



        DocumentTable
          .joinLeft(StudentTable).on(_.studentId === _.id)
          .filter { case(doc, student) => 
                student.map(_.name) === "Tom" 
        }

    select x2."STUDENT_ID", ..., x3."ID", ... 

    from "DOCUMENT" x2 

    left outer join "STUDENT" x3 
        on x2."STUDENT_ID" = x3."ID"

     where x3."NAME" = 'Tom'

Composition / Transactions

DBIO.transactionally

db.run(
    (UniversityTable ++= Seq(
      University("Hogwart"),
      University("Scala University")
    )).transactionally
)

Transactions - example

OK, but how do we combine DB operations?

DBIO is a monad

db.run(
    (for {
        dbio1 <- insertUnis
        dbio2 <- ...
        dbio3 <- ...
        ...
        dbioN <- ...
    } yield {
        ...
    }).transactionally
)

def insertUnis: DBIO[Option[Int]] =
    UniversityTable ++= Seq(
      University("Hogwart"),
      University("Scala University")
    )

map / flatMap

sequence

    val combined: DBIO[Seq[T]] = DBIO.sequence(
        Seq(
            produceDbOper1, // DBIO[T]
            produceDbOper2, // DBIO[T]
            produceDbOper3  // DBIO[T]
        )
    )

  db.run( // Future[Seq[Seq[Course]]]
    for {
      students <- StudentTable.result
      courses <- DBIO.sequence(students.map(fetchMoreData))
    } yield {
      courses
    }
  )


...


  private def fetchMoreData(student: Student): 
    DBIO[Seq[Course]] = {
    (for {
      segment <- StudentCourseSegmentTable 
                if segment.studentId === student.id
      course <- segment.course
    } yield {
      course
    }).result
  }





    def execTransact[T](dbio: DBIO[T]): Future[T] = 
        db.run(dbio.transactionally)

Combine operations & fire within transtion

Summary

Future / DBIO / Query

Slick is not ORM

DBIO composition

Think in terms of Collection API

The Slick profiles (previously called “drivers”) for DB2, Oracle and SQL Server are now part of the core open source release. There is no separate Slick Extensions release anymore.

Important change in 3.2

Resources

Slick docs: http://slick.lightbend.com/docs/

Online workshop by Dave Gurnell: https://vimeo.com/148074461

Excelent book: http://underscore.io/books/essential-slick/

Unicorn

Slick with a little bit of magic

https://github.com/liosedhel/play-slick-unicorn-example

Agenda

Unicorn features
- Typesafe ID
- Generic DAO
Structuring your application
- Table composition
- Implementing repository
- Extracting the repository interface
- Domain services with the repository

Unicorn's magic

Join problem

 GamesTable.join(UsersTable).on(_.placeId === _.id)

Wrong!

Typesafe ID

import org.virtuslab.unicorn._

case class UserId(id: Long) 
    extends BaseId[Long]

case class UserRow(
        id: Option[UserId], 
        email: String, 
        firstName: String, 
        lastName: String
    ) extends WithId[Long, UserId]

IdTable

class UsersTable(tag: Tag) 
    extends IdTable[UserId, UserRow](tag, "users") {

    def email = column[String]("email")
    def firstName = column[String]("first_name")
    def lastName = column[String]("last_name")

    override def * = 
        (id.?, email, firstName, lastName) 
        <> 
        (UserRow.tupled, UserRow.unapply)
}

val UsersTable = TableQuery[UsersTable]

Join problem

 GamesTable.join(UsersTable).on(_.placeId === _.id)

Compilation Error - Cannot perform option-mapped operation

Database Access Object

class UsersDao 
    extends BaseIdRepository[
        UserId, UserRow, UsersTable
    ](UsersTable)

//methods you get for free

def findById(id: Id): DBIO[Option[Entity]]
def findExistingById(id: Id): DBIO[Entity]
def findByIds(ids: Seq[Id]): DBIO[Seq[Entity]]
def deleteById(id: Id): DBIO[Int]
def save(elem: Entity): DBIO[Id]
def saveAll(elems: Seq[Entity]): DBIO[Seq[Id]]
//... and more

JunctionTable

 class GamesUsers(tag: Tag) 
    extends JunctionTable[GameId, UserId](tag, "games_users") {
    
    def gameId = column[GameId]("game_id")
    def userId = column[UserId]("user_id")

    def game = foreignKey("game_fk", gameId, GamesTable)(_.id)
    def user = foreignKey("user_fk", userId, UsersTable)(_.id)
    def pk = primaryKey("games_users_pk", (gameId, userId))

    override def columns = gameId -> userId
  }

  val GamesUsersTable = TableQuery[GamesUsers]

  class GamesUsersDao
    extends JunctionRepository[GameId, UserId, GamesUsers](
     GamesUsersTable
    )

Structuring your application

Standard architecture

Onion architecture

https://dzone.com/articles/onion-architecture-is-interesting

Architecture view

Repository Component

BaseRepositoryComponent

import org.virtuslab.unicorn._

trait UserBaseRepositoryComponent {

  protected val unicorn: Unicorn[Long] with HasJdbcDriver
  import unicorn._
  import unicorn.driver.api._

  class UsersTable(tag: Tag) extends IdTable ...

  val UsersTable = TableQuery[UsersTable]

  class UsersDao extends BaseIdRepository ...

}

Compose components

trait GamesBaseRepositoryComponent 
 extends UsersBaseRepositoryComponent {

 import unicorn._
 import unicorn.driver.api._

 class Games(tag: Tag) 
       extends IdTable[GameId, GameRow](tag, "games"){

  def organizerId = column[UserId]("organizer_id")

  def organizer = 
      foreignKey("organizer_fk", organizerId, UsersTable)(_.id)

    ...

  override def *  = ...
 }
}

Repository

Repository Implementation

@Singleton
class UsersRepositoryJdbc
    @Inject() (val unicorn: UnicornPlay[Long])
              (implicit ec: ExecutionContext)
  extends UsersBaseRepositoryComponent
  with UserRepository[DBIO]
  with DbioMonadImplicits{

  val usersDao = new UsersDao

  def findByUserId(userId: UserId): OptionT[DBIO, User] = {
      OptionT(usersDao.findById(userId))
        .map(toDomain)
    }

  def toDomain(userRow: UserRow): User = {
    import userRow._
    User(userRow.id, firstName)
  }
}

"Complicated" domain object

def toDomain(gameRow: GameRow): OptionT[DBIO, Game] = {
    for {
      organizer <- usersRepository
                    .findByUserId(gameRow.organizerId)
      place <- placeRepository
                    .findByPlaceId(gameRow.placeId)
    } yield Game(
        gameRow.id, 
        organizer, 
        gameRow.note, 
        gameRow.date, 
        place
      )
  }

Transactions

import unicorn.driver.api._

def doTransactionalOperations(gameId1: GameId, 
                             gameId2: GameId) = Action.async {
  unicorn.db.run{
    DBIO.seq(
      gameRepository.deleteGame(gameId1),
      gameRepository.deleteGame(gameId2)
    ).transactionally
  }.map(_ => Ok)
}

Repository Interface

trait UserRepository[F[_]]  {
  def findByUserId(userId: UserId): OptionT[F, User]
}

Get rid of DBIO from domain!

Domain Service

@Singleton
class StatisticsService[F[_]: Monad] @Inject()(
    gamesUsersRepository: GamesUsersRepository[F]
    ) {

  def rootMeanSquareOfPlayersPerGame(): F[Double] = {
    for {
      gamesAndParticipants <- 
         gamesUsersRepository.findGamesAndParticipantsNumber()
    } yield {
      val numberOfGames = gamesAndParticipants.size
      val nominator = gamesAndParticipants
                        .map{case (_, p) => p * p}.sum
      if(numberOfGames <= 0) 
        0 
      else 
        Math.sqrt(nominator / numberOfGames)
    }
  }

}

Noel Markham - A purely functional approach to building large applications www.youtube.com/watch?v=V1d3OYYez7s

Testing domain service

"Statistics service" should
"compute mean square number of players per game" in {
  Given("statistic service with repository mock")
  val gamesUsersRepositoryMock = 
        mock[GamesUsersRepository[Id]]
  val statisticService = 
        new StatisticsService(gamesUsersRepositoryMock)
  val gamesAndParticipants = 
        Seq((GameId(1), 2), (GameId(2), 2))

  (gamesUsersRepositoryMock.findGamesAndParticipantsNumber _)
    .expects().returning(gamesAndParticipants)

  When("calculating the root mean square")
  val averageNumberOfPlayersPerGame = 
    statisticService.rootMeanSquareOfPlayersPerGame()

  Then("average must be calculated properly")
  averageNumberOfPlayersPerGame shouldBe 2
}

Unicorn and cat(s)

Cool toolz in the Scalaz and Cats toolboxes by Jan Pustelnik, Scalar Conf 2016

Async controller

@Singleton
class AsyncController @Inject()(
    unicorn: UnicornPlay[Long],
    statisticsService: StatisticsService[DBIO]
)(implicit exec: ExecutionContext) extends Controller {

   def averageNumberOfPlayersPerGame() = Action.async{
    unicorn.db.run {
      statisticsService.averageNumberOfPlayersPerGame()
    }.map(average => Ok(Json.toJson(average)))
  }
 
}

//GUICE
bind(new TypeLiteral[UnicornPlay[Long]](){})
    .to(classOf[LongUnicornPlayJdbc])
bind(new TypeLiteral[GamesUsersRepository[DBIO]](){})
    .to(classOf[GamesUsersRepositoryJdbc])

General trick and tips

Make a good use of DBIO monad
Be familiar with cats or scalaz libraries
Do not couple your domain with DBIO, Future - they are just monads
Separate infrastructure from domain (try onion architecture idea)

Questions?

@pdolega

@l iosedhel

Bonus

    





    for {
          segment <- StudentCourseSegmentTable
          student <- segment.student if student.name === "Tim"
          course <- segment.course
          semester <- segment.semester
    } yield (segment, student, course, semester)


DEBUG s.c.QueryCompilerBenchmark - ------------------- Phase: Time ---------
DEBUG s.c.QueryCompilerBenchmark -       assignUniqueSymbols:    0.446286 ms
DEBUG s.c.QueryCompilerBenchmark -                inferTypes:    0.239283 ms
DEBUG s.c.QueryCompilerBenchmark -              expandTables:    0.984031 ms
DEBUG s.c.QueryCompilerBenchmark -           forceOuterBinds:    1.048910 ms
DEBUG s.c.QueryCompilerBenchmark -         removeMappedTypes:    0.536035 ms
DEBUG s.c.QueryCompilerBenchmark -                expandSums:    0.016068 ms
DEBUG s.c.QueryCompilerBenchmark -         emulateOuterJoins:    0.108066 ms
DEBUG s.c.QueryCompilerBenchmark -             expandRecords:    0.421471 ms
DEBUG s.c.QueryCompilerBenchmark -        flattenProjections:    2.212847 ms
DEBUG s.c.QueryCompilerBenchmark -              rewriteJoins:    2.219579 ms
DEBUG s.c.QueryCompilerBenchmark -             verifySymbols:    0.204175 ms
DEBUG s.c.QueryCompilerBenchmark -             relabelUnions:    0.086386 ms
DEBUG s.c.QueryCompilerBenchmark -          createAggregates:    0.013349 ms
DEBUG s.c.QueryCompilerBenchmark -           resolveZipJoins:    0.126179 ms
DEBUG s.c.QueryCompilerBenchmark -          pruneProjections:    0.567347 ms
DEBUG s.c.QueryCompilerBenchmark -           rewriteDistinct:    0.018858 ms
DEBUG s.c.QueryCompilerBenchmark -    createResultSetMapping:    0.274411 ms
DEBUG s.c.QueryCompilerBenchmark -            hoistClientOps:    0.649790 ms
DEBUG s.c.QueryCompilerBenchmark -         reorderOperations:    0.643005 ms
DEBUG s.c.QueryCompilerBenchmark -     mergeToComprehensions:    4.482695 ms
DEBUG s.c.QueryCompilerBenchmark -            optimizeScalar:    0.179255 ms
DEBUG s.c.QueryCompilerBenchmark -          removeFieldNames:    1.145481 ms
DEBUG s.c.QueryCompilerBenchmark -                   codeGen:    3.783455 ms
DEBUG s.c.QueryCompilerBenchmark -                     TOTAL:   20.406962 ms

      
    val query = Compiled { name: Rep[String] =>
        for {
          segment <- StudentCourseSegmentTable
          student <- segment.student if student.name === name
          course <- segment.course
          semester <- segment.semester
        } yield (segment, student, course, semester)
    }
...
...
    db.run(query("Tim").result)

Precompilation

    Property age = Property.forName("age");
    List cats = sess.createCriteria(Cat.class)
    .add( Restrictions.disjunction()
        .add( age.isNull() )
        .add( age.eq( new Integer(0) ) )
        .add( age.eq( new Integer(1) ) )
        .add( age.eq( new Integer(2) ) )
    ) )
    .add( Property.forName("name").in( 
        new String[] { "Fritz", "Izi", "Pk" } ) 
    )
    .list();

Criteria API?

    StudentTable.filter(row: StudentTable => ...)

Criteria API?

  
  StudentTable.filter(myExtractedFilter)


  def myExtractedFilter(row: StudentTable): Rep[Boolean] = {
    row.name === "Tom" && row.nationality === "American"
  }




    StudentTable
        .map(s => (s.name, s.surname))
        .distinctOn(_._1)

    
    select "NAME", min("SURNAME") 

    from "STUDENT" 

    group by "NAME"


    StudentTable
        .filter(_.surname =!= "Test")
        .groupBy(_.surname)
        .map { case (surname, group) =>
          (surname, group.map(_.name).countDistinct)
        }
        .filter(row => row._2 > 5)


    select "SURNAME", count(distinct "NAME") 

    from "STUDENT" 

    where not ("SURNAME" = 'Test') 

    group by "SURNAME" 

    having count(distinct "NAME") > 5

Repository

Preso

What is Slick?

Not an ORM

ORM problems

N+1 problem (lazy / eager fetching)

Session context scope

Execution under the cover (cache)

O-R impedance mismatch

False promise

Leaky abstraction

FRM

Type-Safe

Reactive (™?)

Server handling 10 000 client connections may need not more than 10 connection

It's still viable

If you were to remember only one thing

Monadic Trio

Tables

Actions

Monadic Trio

DBIOAction / DBIO

DBIOAction[R, S, E]

R - type of result S - streaming / not streaming E - what kind of effect

DBIO[R] =

DBIOAction[R, NoStream, Effect.All]

R - type of result

Queries

Query

Query[M, U, C]

M - mixed type

U - unpacked type

C - collection type

Do you speak it?!

Queries

Joins

More complicated join

Applicative vs monadic

Sometimes one form is more elegant than the other

Outer joins

Composition / Transactions

DBIO.transactionally

Transactions - example

OK, but how do we combine DB operations?

DBIO is a monad

map / flatMap

sequence

Combine operations & fire within transtion

Summary

Important change in 3.2

Resources

Unicorn

Slick with a little bit of magic

Agenda

Unicorn's magic

Join problem

Typesafe ID

IdTable

Join problem

Database Access Object

JunctionTable

Structuring your application

Standard architecture

Onion architecture

Architecture view

Repository Component

BaseRepositoryComponent

Compose components

Repository

Repository Implementation

"Complicated" domain object

Transactions

Repository Interface

Repository Interface

Domain Service

Domain Service

Testing domain service

Unicorn and cat(s)

Async controller

General trick and tips

R - type of result
S - streaming / not streaming
E - what kind of effect