slick 3.2 - how to define an "abstract" trait with tables and queries definitions but no concrete profile?

Question

I want to define the queries and tables without the direct import of the final database profile (H2, MySQL etc) - so in unit tests I would use H2, and for the staging/production I would use MySQL. So far I couldn't find a way to import all necessary abstract components to get this working:

import slick.jdbc.H2Profile.api._

class OAuthCredentialsTable(tag: Tag) extends Table[OAuth](tag, "credentials_oauth") {

  def username: Rep[String] = column[String]("username", O.SqlType("VARCHAR"))

  def service: Rep[String] = column[String]("service", O.SqlType("VARCHAR"))

  def serviceId: Rep[String] = column[String]("service_id", O.SqlType("VARCHAR"))

  def userRef: ForeignKeyQuery[UserTable, User] = foreignKey("oauth_user_fk", username, userTable)(_.username, onDelete = ForeignKeyAction.Cascade)

  override def * = (username, service, serviceId) <> (OAuth.tupled, OAuth.unapply)

}

val oauthTable: TableQuery[OAuthCredentialsTable] = TableQuery[OAuthCredentialsTable]

Answer 1

To accomplish this in my project I created an object out of the JdbcProfile trait and imported that everywhere:

JdbcProfile.scala:

package my.application.data.support

import slick.jdbc.JdbcProfile

object JdbcProfile extends JdbcProfile

Wherever I define tables I import it as follows:

import my.application.data.support.JdbcProfile.api._
...

In order to support extensions to slick, I created an abstraction called DatabaseSupport where each database type would have their own concrete implementation which is injected at startup-time depending on configuration.

DatabaseSupport.scala:

package my.application.data.support

/**
  * Database support implicits and methods.
  */
trait DatabaseSupport {

  /**
    * Implicit database array type.
    */
  implicit val seqStringType: JdbcProfile.DriverJdbcType[Seq[String]]
}

This way I can have seperate H2DatabaseSupport.scala and PostgresDatabaseSupport.scala implementations that specifies seqStringType in database-specific ways.

Answer 2

I used this configuration in order to change the DB profile in a application.conf file.

import slick.jdbc.JdbcProfile
import slick.basic._
trait DBComponent {
    // use the application.conf to change the profile  
    val database = DatabaseConfig.forConfig[JdbcProfile]("h2")  
    val driver = database.profile

}


trait BankTable extends DBComponent  {
    import driver.api._
    class BankTable(tag: Tag) extends Table[Bank](tag, "bank") {
        val id = column[Int]("id", O.PrimaryKey, O.AutoInc)
        val name = column[String]("name")
        def * = (name, id.?) <> (Bank.tupled, Bank.unapply)

    }

    protected val bankTableQuery = TableQuery[BankTable]
    protected def bankTableAutoInc = bankTableQuery returning 
bankTableQuery.map(_.id)
}

class BankRepositoryBDImpl extends BankTable  with BankRepository  {
    import driver.api._
    val db = database.db
    def createBank(bank: Bank): Future[Int] = db.run { bankTableAutoInc += bank }
}

and use a application.conf file

h2 = {
url = "jdbc:h2:mem:test1"
driver = org.h2.Driver
connectionPool = disabled
keepAliveConnection = true
}

sqlite = {
url = "jdbc:sqlite::memory:"
driver = org.sqlite.JDBC
connectionPool = disabled
keepAliveConnection = true
}

Answer 3

Eventually I discovered that to accomplish the driver-agnostic setup, it could be done as simple as that:

object UserPersistence extends JdbcProfile {

  import api._

  class UserTable(tag: Tag) extends Table[User](tag, "users") {
    def username: Rep[String] = column[String]("username", O.PrimaryKey, O.SqlType("VARCHAR"))

    def password: Rep[String] = column[String]("password", O.SqlType("VARCHAR"))

    def serverkey: Rep[String] = column[String]("serverkey", O.SqlType("VARCHAR"), O.Length(64))

    def salt: Rep[String] = column[String]("salt", O.SqlType("VARCHAR"), O.Length(64))

    def iterations: Rep[Int] = column[Int]("iterationcount", O.SqlType("INT"))

    def created: Rep[Timestamp] = column[Timestamp]("created_at", O.SqlType("TIMESTAMP"))

    val mkUser: ((String, String, String, String, Int, Timestamp)) ⇒ User = {
      case ((name, pwd, _, _, _, created)) ⇒ User(name, pwd, created.toInstant)
    }

    def unMkUser(u: User) = Some(u.username, u.password, "", "", 0, new Timestamp(u.createdAt.toEpochMilli))

    override def * = (username, password, serverkey, salt, iterations, created) <> (mkUser, unMkUser)
  }

  val userTable: TableQuery[UserTable] = TableQuery[UserTable]

}

and then in order to use different profiles - you need to supply different database implementation when you run something, e.g

trait UserPersistence {

  protected def db: Database

  protected implicit val ec: ExecutionContext

  override def findCredentialsOrRegister(oauthCredentials: OAuth): Future[User] = {

    val userFound = (for (
      creds ← oauthTable.filter(x ⇒ x.service === oauthCredentials.service && x.serviceId === oauthCredentials.serviceId);
      user ← userTable.filter(x ⇒ x.username === creds.username)
    ) yield user).result

    val authenticate = userFound.flatMap {
      case Seq(user) ⇒
        DBIO.from(Future.successful(user))
      case Seq() ⇒
        val newUUID = UUID.randomUUID
        val user = User(newUUID.toString, UUID.randomUUID().toString, Instant.now())
        DBIO.seq(
          userTable += user,
          oauthTable += oauthCredentials.copy(username = newUUID.toString)
        ) andThen DBIO.from(Future.successful(user))
    }

    db.run(authenticate.transactionally)
  }

and then in test

val impl = new UserPersistence {

  override def db: H2Profile.api.Database = // initialize and populate the database

  override implicit val ec: ExecutionContext = scala.concurrent.ExecutionContext.global

}

For MySql just assign the MySQL profile to db property (and change type).

Answer 4

I hope I got you right, you can achieve it with injections and your configuration, separating test config from prod,

So I think you can do something like this-

Create binding for injection-

class DbModule extends Module {
  bind[slick.driver.JdbcProfile#Backend#Database] toNonLazy DatabaseConfigProvider.get[JdbcProfile](inject[Application]).db
}

Then (for example)-

abstract class BaseDaoImpl[T <: IdentifiableTable[S] : ClassTag, S <: RichEntity[S]](implicit injector: Injector)
  extends BaseDao[S] with Injectable with Logger{

  protected val db: slick.driver.JdbcProfile#Backend#Database = inject[slick.driver.JdbcProfile#Backend#Database]
  protected val table: TableQuery[T]


  def insert(obj: S): Future[S] = {
    val insertQuery = table.returning(table.map(_.id)) into ((item, id) => item.withDifferentId(Some(id)))
    val res = db.run(insertQuery += obj)
    logger.debug(s"Inserting ${this.getClass} - ${res.toString}")
    res
  }

  def all(): Future[Seq[S]] = {
    db.run(table.result)
  }
}