Scala Slick 2.0 for multi-database


Here i am going to explain how to use Slick 2.0 (Lifted Embedding API) in an application which have  multiple-database for multiple environment . In any application generally we have different environments like testing, development, production etc.

I want to demonstrate this concept using simple use case. lets say we want to use  H2 for testing ,MySql for development and PostgreSql for production environment. In database  there is an employee table which have ID, NAME, EMAIL, DESIGNATION, DOJ (date of joining) as columns.  This table contains some records. We need to perform basic database operation on this table using slick. Database must be selected at runtime on the bases of run mode.

Here is the Scala & Slick way to implementing this functionality.  The logic has been extracted out in three main classes.

a) domain.scala    b) SlickDBDriver.scala c)DBConnection.scala

a) domain.scala

import scala.slick.driver.JdbcProfile
import java.sql.Date
//define driver
trait Profile {
val profile: JdbcProfile
}

trait DomainComponent { this: Profile =>
import profile.simple._

case class Employee(name: String, email: String, designation: String, doj: Date, id: Int = 0)
class Employees(tag: Tag) extends Table[Employee](tag, "employee") {
def id = column[Int]("id", O.PrimaryKey , O.AutoInc)
def name = column[String]("name", O.NotNull, O.DBType("VARCHAR(100)"))
def email = column[String]("email", O.NotNull, O.DBType("VARCHAR(100)"))
def designation = column[String]("designation", O.NotNull, O DBType ("VARCHAR(100)"))
def doj = column[Date]("doj", O.NotNull)
def * = (name, email, designation, doj, id) <> (Employee.tupled, Employee.unapply)
}
val employees= TableQuery[Employees]
}

As It is required to access different database in different environment so  database connection properties like url, username, password is required.We have externalized these properties in  three configuration file.

test.conf

db.driver=org.h2.Driver
db.url="""jdbc:h2:mem:test"""
db.password=""
db.username=""

dev.conf

db.driver=com.mysql.jdbc.Driver
db.url="""jdbc:mysql://localhost:3306/slickdemo"""
db.username=root
db.password=root

prod.conf

db.driver=org.postgresql.Driver
db.url="""jdbc:postgresql://localhost:5432/slickdemo"""
db.username=postgres
db.password=postgres

Slick will take the property from appropriate config file and create the database connection with respect to the environment in which application is running.

b)SlickDBDriver.scala : By this object get slick database drive with respect to selected run mode.

import scala.slick.driver.JdbcProfile
import scala.slick.driver.H2Driver
import scala.slick.driver.MySQLDriver
import scala.slick.driver.PostgresDriver

object SlickDBDriver {
val TEST = "test"
val DEV = "dev"
val PROD = "prod"
def getDriver: JdbcProfile = {
scala.util.Properties.envOrElse("runMode", "prod") match {
case TEST => H2Driver
case DEV => MySQLDriver
case PROD => PostgresDriver
case _ => PostgresDriver
}
}
}

DBConnection.scala : This class creates database object using which we can access database.

import scala.slick.driver.JdbcProfile
import com.typesafe.config.Config
import com.typesafe.config.ConfigFactory
class DBConnection(override val profile: JdbcProfile) extends Profile {
import profile.simple._
def dbObject(): Database = {
val env = scala.util.Properties.envOrElse("runMode", "prod")
val config = ConfigFactory.load(env)
val url = config.getString("db.url")
val username = config.getString("db.username")
val password = config.getString("db.password")
val driver = config.getString("db.driver")
println("Connection info =>" + "Run mode: " + env + ", db url: " + url + ", driver: " + driver)
Database.forURL(url, username, password, null, driver)
}
}

Now we can access  multiple database by SLICK :

SlickDemoApp.scala :

import scala.slick.driver.JdbcProfile
import java.sql.Date
class SlickDemo(override val profile: JdbcProfile = SlickDBDriver.getDriver) extends DomainComponent with Profile {
 import profile.simple._
 val conn = new DBConnection(profile)
 def test: Unit = {
 conn.dbObject withSession { implicit session: Session =>
 // create  table  selected environment
 try {
 employees.ddl.create
 } catch {
 case ex: Exception => println(ex.getMessage)
 }
 // insert employees into database
 employees.insert(Employee("satendra", "satendra@knoldus.com", "consultant", Date.valueOf("2013-06-03")))
 employees.insert(Employee("anand", "anand@knoldus.com", "consultant", Date.valueOf("2013-07-03")))
 println("======================retrieve from database ====================")
 employees.list foreach println
 // delete
 val query = for { emp <- employees if (emp.name === "satendra") } yield emp
 query.delete
 println("======================retrieve after delete ====================")
 employees.list foreach println
 }
 }
 }
 object SlickDemoApp extends App {
 (new SlickDemo).test
 }

Now set environment and run the application:

for test:
sky@Sky:~/knols_sessions/slickdemo/slick2demo$ export runMode=test
sky@Sky:~/knols_sessions/slickdemo/slick2demo$ sbt run

for dev:
sky@Sky:~/knols_sessions/slickdemo/slick2demo$ export runMode=dev
sky@Sky:~/knols_sessions/slickdemo/slick2demo$ sbt run

for prod:
sky@Sky:~/knols_sessions/slickdemo/slick2demo$ export runMode=prod
sky@Sky:~/knols_sessions/slickdemo/slick2demo$ sbt run

Download complete code example from  here

Enjoy with Slick !!!!

About Satendra Kumar

Satendra kumar is the Senior Associate @ Sapient Global Market. In his 5 years of experience he has become developer with proven experience in architecting and developing the Reactive Systems and Big Data Platform with Scala, Akka, Spark, Kafka & Hadoop. He is the big fan of Scala. Satendra has a B.Tech in Computer Engineering from Uttar Pradesh Technical University.
This entry was posted in Scala. Bookmark the permalink.

12 Responses to Scala Slick 2.0 for multi-database

  1. Tim says:

    This was a really helpful post, thanks.
    I’ve had a bit of trouble with the very last part though. I don’t understand where the ‘export’ command comes from in lines like:
    ~/knols_sessions/slickdemo/slick2demo$ export runMode=test

    When I try that command, I get:
    ‘export’ is not recognized as an internal or external command,
    operable program or batch file.

    So I can’t figure out how to actually change between test, dev and prod modes. Any help would be much appreciated.
    Cheers

  2. Hi Tim,

    Which OS you are using ?.

  3. Nikhil says:

    very helpful..thnx to your work

  4. Andrey says:

    Can anybody explain why we have to create trait profile?

  5. Nikhil Gupta says:

    Hi,
    Just an off topic question .. Is there a way to handle more than 22 columns in a table using slick ?

  6. José Carlos says:

    several mistakes here, Database is for h2 engine, Database.forURL doesnt even exists at version 3.0.3.
    useless sample.

  7. @Jose Carlos: Thanks for feedback.
    Database.forURL exists at version 3.0.3.
    please see doc => http://slick.typesafe.com/doc/3.0.3/api/index.html#slick.jdbc.JdbcBackend$DatabaseFactoryDef
    and also see source code line no 100 at => https://github.com/slick/slick/blob/615fc0da4d130c5f25d67b629bdb60a0d20fb27e/slick/src/main/scala/slick/jdbc/JdbcBackend.scala

    This blog post have been written for slick 2.0. So there are lot of changes from slick2.0 to Slick 3.0.3.
    If you want use Slick 3.0.3 please see my activator template =>https://github.com/knoldus/slick-starting-on-the-right-foot

    Let me know any if issue.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s