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

Question

I have a Play/Slick application where I need to run multiple plain sql queries in a single transaction retrieving result of the last query like so:

val id: Future[Int] = db.run {
    sql"""
        DECLARE @T TABLE (id INTEGER)
        INSERT INTO Foo (name) OUTPUT INSERTED.id INTO @T VALUES ('bar')
        SELECT id FROM @T
    """.as[Int].head
}

The problem with the above is that it always returns 1 which is a result of the top query that declares a temporary table. How do I get the result of the last query instead? Database in question is MS SQL Server. Play 2.5.4, Slick 3.1.1.

Answer 1

One of the most important features of Slick Actions is that they are composable, similar to the Scala Futures. IMHO, the docs don't treat it as much as it should... Here's an example:

val createAction = sqlu"""DECLARE @T TABLE (id INTEGER)"""
val insertAction = sqlu"""INSERT INTO Foo (name) OUTPUT INSERTED.id INTO @T VALUES ('bar')"""
val selectAction = sql"""SELECT id FROM @T""".as[Int].head

val composedAction = for {
  createRes <- createAction
  insertRes <- insertAction
  selectRes <- selectAction
} yield selectRes

val id = db.run(composedAction.transactionally) // Future[...]

You could replace the for comprehension with flatMaps and a map at the end if you find it easier.

There are also two handy helpers from Slick, namely DBIO.seq (runs queries in sequence and discards the result, returns Unit) and DBIO.sequence (the same but preserves the results of all individual actions).

Answer 2

How about using the DBIO.seq(). Since DBIO.seq return Unit the selectAction then follows by andThen().

val createAction = sqlu"""DECLARE @T TABLE (id INTEGER)"""
val insertAction = sqlu"""INSERT INTO Foo (name) OUTPUT INSERTED.id INTO @T VALUES ('bar')"""
val selectAction = sql"""SELECT id FROM @T""".as[Int].head

val id = db.run(DBIO.seq(createAction, insertAction) andThen selectAction)