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

Question

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

I have an intermediate request that I process with map , for , etc. to get the result I want. In the end i have

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

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

If foo is Seq , I could just do bar ++= foo , but that is not the case.

The only way I found is to materialize the result while waiting for it. Like this

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

Obviously, query needs to be started at some point using db.run . But now I have two DB starts. Would it be better if everything was in one go?

Is there a better way to do this?

Answer 1

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

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

insertAction will be of type DBIOAction[Int, NoStream, Effect.Write] or something like this (I'm not quite sure about Int and effect), then you can run it in the database like any DBIOAction , using db.run ; what you get is the future of the general query and the result of the insert.

Answer 2

The questions have already been answered, but I came here looking for a different solution, so maybe this will be useful for other people.

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

For example, an insert from select should compile into an INSERT INTO table1 SELECT... If you use multiple DBIOS using flatMap , as suggested, this will be compiled into SELECT , the values ​​will be transferred to memory, and then the INSERT will be compiled, interpolating the values ​​in the string, and this new query will be sent to the database. In fact, it can be much slower if your choice returns a lot of 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)