Scala+Slick 3: Inserting the result of one query into another table

Question

This question is about slick 3.0 or 3.1 (I am flexible about that)

I have an intermediate query which I process with map, for, etc. to obtain the result I want. In the end I have a

val foo: DBIOAction[Seq[MySchema.Bar], NoStream, Effect.Read]

Now I have a val bar: TableQuery[MySchema.Bar] and I want to insert foo in to it.

If foo would be a Seq I could simply do bar ++= foo, but it is not.

The only way I found is to materialize the result by awaiting it. Like this

val query = (bar ++= Await.result(db.run(foo), Duration.Inf))

Obviously query needs to be run at some point with db.run. But now I have two DB-runs. Wouldn't it be better to have everything in a single run?

Is there a better way to do this?

Answer 1

DBIOAction has map/flatMap functions, so you can write something like

val insertAction = foo.flatMap(bar ++= _)

The insertAction will be of type DBIOAction[Int, NoStream, Effect.Write] or something like that (I'm not entirely sure about Int and the effect), then you can run it on the DB as any DBIOAction using db.run; what you then get is a future of the overall query and insert result.

Answer 2

The questions was already answered but I came here looking for a different solution, so maybe this will be helpful to other people.

As @Aldo says, we want to work at the DBIO level as far as possible, but I would go further and say you should work at the Query level as far as possible, as this compiles to a single sql statement that can be sent to the database.

For example, an insert from a select should be compile to a INSERT INTO table1 SELECT.... If you use multiple DBIOS using flatMap, like suggested, this will be compiled into a SELECT, the values will be brought to memory and then an INSERT statement will be compiled, interpolating the values in a string and this new query will be sent to the database. In reality this can be much slower if your select returns many results, and it can drain your memory in the worst case.

So to compile something like this into a single query you can write:

val bar: TableQuery[MySchema.Bar]

val foo: Query[MySchema.Bar, Bar, Seq]

val insert: DBIO[Int] = bar.forceInsertAll(foo)