How to run multiple plain sql queries ― multiple times ― in a single transaction in slick?

Question

I have a Play/Slick application where I need to run several plain sql queries multiple times (n) in a single transaction.

Here is the code for one iteration, i.e. when n = 1:

val query1 = sql"""insert into some_table_1 (date) values (NOW())""".asUpdate
val query2 = sql"""insert into some_table_2 (date) values (NOW())""".asUpdate
val query3 = sql"""insert into some_table_3 (date) values (NOW())""".asUpdate

val composedAction = for {
  result1 <- query1
  result2 <- query2
  result3 <- query3
} yield result3

val result = db.run(composedAction.transactionally)

Now, I actually want to change the above composedAction so that it would iterate over the 3 queries n = 1,000 times in one transaction (i.e. I want the 3 * 1,000 = 3,000 queries to either all fail or all succeed).

How can I do that?

AminMal AminMal · Accepted Answer · 2022-07-28T13:05:18

I couldn't try this locally, but you can try this and see if it helps:

// A type alias for ease of use
type Query = SqlStreamingAction[Vector[Int], Int, Effect]#ResultAction[Int, NoStream, Effect]
// this type alias is basically the type of q1, q2, q3

val actions = (1 to 1000).foldLeft(List.empty[Query]) {
  case (agg, _) =>
    q1 :: q2 :: q3 :: agg // append queries in order like this
}
db.run(
  DBIO.sequence(actions).transactionally
)

I assumed that you want to have a list of queries such as this:

List(q1, q2, q3, q1, q2, q3, q1, q2, q3, ...)

If you wanted something else, that would be easy to manage. Like if you need:

List(q1, q1, q1, q1, q1, ..., q2, q2, q2, q2, q2, ..., q3, q3, q3, q3, q3, ...)

You would want to do something like this to order your queries:

val range = 1 to 1000
val actions = range.map(_ => q1).toList ::: range.map(_ => q2).toList ::: range.map(_ => q3).toList

Update

Since I find that type aliasing a bit annoying, you can also use this instead:

val queries = q1 :: q2 :: q3 :: Nil
val actions = (1 until 1000).foldLeft(queries) { // note the until instead of to here
  case (aggregator, _) => queries ::: aggregator
}
db.run(
  DBIO.sequence(actions).transactionally
)

How to run multiple plain sql queries ― multiple times ― in a single transaction in slick?

1 Answers

Update