How can I include a scala operation between a sequence of database queries in Slick?

Question

Let's say I have a model that looks something like so:

case class User(username: String, dateOfBirth: Timestamp, lastSentGift: Timestamp)

Assuming I have an appropriate accompanying Slick schema, Users, how can I perform multiple queries against this table within a transaction, with a scala function to be called in between?

I've seen that Slick provides a DBIOAction and DBIO.seq to allow composition of multiple database operations within a single transaction, but I don't understand if/how I can use these with a scala function called in between.

For example, I'd like to do something such as this, but keep everything in a single transaction:

def prepareGiftFor(user: User): Timestamp = ???

val usersWithBirthdays = db.run(
  Users.filter { user => 
    user.dateOfBirth > (now - 1 month) && user.lastSentGift < (now - 1 month)
  }
  .limit(100)
  .forUpdate
)

usersWithBirthdays
  .map(user => (user.username, prepareGiftFor(user)))
  .map { case (username, lastSentGift) =>
    db.run(
      Users.withFilter(_.username === username)
        .map(row => row.lastSentGift)
        .update(lastSentGift)
    )
  }

Answer 1

General idea of slick is to delay db.run call as much as possible. Preferred way is to work with DBIOAction instances and chain them as we do with scala Future or Option.

For this DBIOAction supports map and flatMap methods. DBIOAction companion object also contains helper methods from, successful and failed. With them you can construct DBIOAction from primitive values. For more information check this section of slick documentation about actions composition.

It's possible to run all sql quires in single transaction by calling transactionally on DBIOAction instance.

Your example could be rewritten as:

def prepareGiftFor(user: User): Timestamp = ???

def findUsersWithBirthdays(): DBIO[Seq[User]] = {
  Users
    .filter { user =>
      user.dateOfBirth > (now - 1 month) && user.lastSentGift < (now - 1 month)
    }
    .limit(100)
    .forUpdate
}

def updateUsers(users: Seq[User]): Seq[DBIO[Int]] = {
  users
    .map(user => (user.username, prepareGiftFor(user)))
    .map {
      Users
        .withFilter(_.username === username)
        .map(row => row.lastSentGift)
        .update(lastSentGift)
    }
}

db.run(
  (for {
    users <- findUsersWithBirthdays()
    _ <- DBIO.sequience(updateUsers(users))
  } yield ()).transactionaly
)