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

4
votes

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.

3
sql-serverscalajdbcplayframeworkslick
Split them to separate actions and do a for comprehension? And then add .transactionally? - insan-e
@insan-e I was trying something like that, but couldn't work out the syntax, there's just no examples for that at all. - Caballero
Yeah, the docs say almost 2 sentences about query composition... - insan-e

3 Answers

14
votes

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

1
votes

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

To extend existing answers, provided queries could be run as a single query by omiting INTO clause. Instead:

DECLARE @T TABLE (id INTEGER)
INSERT INTO Foo (name) OUTPUT INSERTED.id INTO @T VALUES ('bar')
SELECT id FROM @T

The following could be used:

INSERT INTO Foo (name) OUTPUT INSERTED.id VALUES ('bar')

db<>fiddle demo