2

I have a Scala/Play application and I need to join tables from 2 different PostgreSQL databases on the same server using Slick 2. The databases are massive so I don't create classes for my tables manually, I use Slick 2 schema code generator, so if there need to be changes made it has to work with the code generator.

Instead of defining my db url in the conf like this

db.pg1.url="jdbc:postgresql://localhost:5432/db1"
db.pg2.url="jdbc:postgresql://localhost:5432/db2"
...

I'd like to do it like this:

db.pg.url="jdbc:postgresql://localhost:5432"

and then my method would look something like this:

def test = DB("pg").withDynSession { //not sure if this is 100% correct
    //query joining two tables from different databases here
}

As in, connect to the server, but not a particular database, so that I could work with multiple databases using the same connection. Is this possible? I use this approach already with Slick Plain SQL and it works just fine. Side question: would this have some negative impact on performance?

If what I want to accomplish is possible, can you please provide me with a simple example. If this is not possible, what are my alternatives? Thanks.

My stack:

  • Scala 2.10.3
  • Play 2.2.2
  • Slick 2.0
  • PostgreSQL 9.3.3
  • Java 8
Caballero
  • 11,546
  • 22
  • 103
  • 163
  • This should work either out-of-the-box or after minor adjustments. Did you try if it "just works"? And if not, where is the problem? – cvogt Mar 16 '14 at 15:18
  • @cvogt How would this "out-of-the-box" look like? There is nothing to describe this situation in Slick 2 documentation, at least I couldn't find. – Caballero Mar 16 '14 at 15:21
  • Slick Table classes contain information about which database the corresponding table resides in. You can simply use them together in a query. You said you are already using Slick with the code generator. Doesn't it generate the required classes or where is the problem? – cvogt Mar 16 '14 at 16:29
  • @cvogt So is it possible to do so without defining a separate connection to each database, instead just defining a connection to the whole DB server (like in my example)? If so then how would that look? Also, let's say that's not possible and I have to manually define a connection to each database, how would the query look then? I can't find any examples whatsoever of a query where two tables from different databases are joined using Slick 2. – Caballero Mar 16 '14 at 16:41
  • @cvogt In your answer of this question http://stackoverflow.com/questions/21603506/how-can-i-join-tables-in-different-databases-on-the-same-server-using-slick you've posted an example of a table class, however when Slick 2 code generator generates the class - it doesn't include `Some("SOME_DB")`, is there a way to make it include it? – Caballero Mar 16 '14 at 16:45

0 Answers0