Use multiple DB / Schema in a single DBIO in Slick

Question

Let's say If I have 2 Schema below.Both of them are in a same MySQL server.

• master
• base

The problem is I can't use more than 2 schema actions in a single Database run. If I execute such plain sql queries via sql, It works without any problem.

def fooAction(name: String) = {
  sql"""
  SELECT AGE FROM MASTER.FOO_TABLE WHERE NAME = $name
  """.as[String].head
}

def barAction(id: String) = {
  sql"""
  SELECT BAZ FROM BASE.BAR_TABLE WHERE ID = $id
  """.as[String].head
}

def execute = {
  //It doesn't matter which Db I use in here But Let's say this baseDb is pointing to BASE schema.
  baseDb.run(for{
    foo <- fooAction("sample")
    bar <- barAction("sample")
  } yield foo + bar)
}

But the case of code blow doesn't

class FooTableDAO @Inject() (@NamedDatabase("master") protected val dbConfigProvider: DatabaseConfigProvider) extends HasDatabaseConfigProvider[JdbcProfile] {
  import dbConfig.driver.api._
  val table = TableQuery[FooTable]
  def fooAction(name: String) = table.filter{_.name == name}.map{_.age}.result.head
}
class BarTableDAO @Inject() (@NamedDatabase("base") protected val dbConfigProvider: DatabaseConfigProvider) extends HasDatabaseConfigProvider[JdbcProfile] {
  import dbConfig.driver.api._
  val table = TableQuery[BarTable]
  def fooAction(id: String) = table.filter{_.id == id}.map{_.baz}.result.head
}

def execute = {
  //com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'BASE.FOO_TABLE' doesn't exist
  baseDb.run(for{
    foo <- fooTableDAO.fooAction("sample")
    bar <- barTableDAO.barAction("sample")
  } yield foo + bar)
}

Since baseDb is pointing to BASE schema, It tries to find FOO_TABLE in MASTER schema. All What I want slick to do is use different schema for each query but I couldn't find the way.

Currently I do DBIO.from(db.run(**)) if another schema action is needed in a for-comprehension of DBIO action or execute each actions via run and wrap them with EitherT which is scala library named cats's monad transformer for Either to keep using for-comprehension.

Is there any way to handle more than 2 schemas in a single DBIO Action except using plain text query?

Thanks in advance.

Answer 1

I think (though I am not an MySQL expert) you mean schema, not a database. At least this is what I see from your SQL samples.

Can't you just use schema attribute in your Slick table mappings? Here you have complete answer for using different schemas: https://stackoverflow.com/a/41090987/2239369

Here is the relevant piece of code:

class StudentTable(tag: Tag) extends Table[Student](tag, _schemaName = Option("database2"), "STUDENT") {
    ...
}

(notice _schemaName attribute).

With this in mind answer to this part of the question:

Is there any way to handle more than 2 schemas in a single DBIO Action except using plain text query?

is: Yes you can.