Scala Slick 2.0 for multi-database

Table of contents

Reading Time: 3 minutes

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 !!!!

12 thoughts on “Scala Slick 2.0 for multi-database4 min read”

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

Hi Tim,

Which OS you are using ?.

Tim says:

September 21, 2014 at 1:24 PM

Thanks for the reply. I’m on windows 7
1. Satendra Kumar says:
  
  September 21, 2014 at 2:37 PM
  
  This example works on linux OS. Because in linux you can set environment variable by export command on terminal.
  Could you try => set runMode=test . Right now did’t have windows OS so I am not sure this wil work. Let me know if it works or not ?
2. Tim says:
  
  September 22, 2014 at 1:09 PM
  
  Ah right, I didn’t realise export was a Linux command. set runMode=test worked perfectly. Thanks very much

very helpful..thnx to your work

Can anybody explain why we have to create trait profile?

Satendra Kumar says:

March 25, 2015 at 10:34 PM

Suppose my application have multiple environment(dev,prod,test) and each environment have different database.
Let’s say prod -> postgres, test -> h2 . In this scenario ,We need to define a trait which have driver level abstraction.Let’s define Profile trait :

trait Profile {
val profile: JdbcProfile
}
Now another question , Why JdbcProfile trait ?
Because , Each database driver have implemented JdbcProfile trait(for more info http://slick.typesafe.com/doc/2.1.0/api/#scala.slick.driver.JdbcProfile)

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

Satendra Kumar says:

April 15, 2015 at 12:03 AM

yes, you can
please see method testWideMappedEntity in this URL : https://github.com/slick/slick/blob/2.1/slick-testkit/src/main/scala/com/typesafe/slick/testkit/tests/JdbcMapperTest.scala

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

@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.

Comments are closed.