Slick
Slick is a database library for relational databases. In the following guide we will see how to integrate it in a Scalatra application.
Project Overview
The sample project contains a minimal setup. For this guide the following files are important:
project/build.scala
: we configure the sbt build and the dependencies here.src/main/scala/slicksupport/slick.scala
: the scalatra application.src/main/resources/c3p0.properties
: the connection pool is configured here.
.
├── project
│ ├── build.properties
│ ├── build.scala // sbt build configuration
│ └── plugins.sbt
└── src
└── main
├── resources
│ ├── c3p0.properties // connection pool configuration
│ └── logback.xml
├── scala
│ ├── slicksupport
│ │ └── slick.scala // main code goes here
│ └── ScalatraBootstrap.scala
└── webapp
└── WEB-INF
└── web.xml
SBT Configuration
Let us start with the SBT setup by editing project/build.scala
. Slick officially supports Scala 2.10-2.11, so let’s use Scala 2.11:
scalaVersion := "2.11.0"
Also you need to use an appropriate Scalatra version, for example 2.3.0
which supports Scala 2.11:
libraryDependencies += "org.scalatra" %% "scalatra" % "2.3.0"
For this guide we choose the H2 Database, so we need to add a dependency to it too.
libraryDependencies ++= Seq(
"com.typesafe.slick" %% "slick" % "2.1.0",
"com.h2database" % "h2" % "1.3.166"
)
Since we want to use connection pooling, we also need to add c3p0:
libraryDependencies += "c3p0" % "c3p0" % "0.9.1.2"
SBT is all set up. Lets proceed to the code.
Slick Setup
We put the database initialization code into ScalatraBootstrap
. This is the class which gets executed when the web application is started. We do the following here:
- Setup a connection pool when the Scalatra application starts. The configuration is load from
src/main/resources/c3p0.properties
. c3p0 loads the .properties file by searching the classpath. - Stop the connection pool when the Scalatra application shuts down.
- Provide a
scala.slick.session.Database
instance indb
which is a wrapper around the connection pool’sDataSource
and serves as source for database sessions. - Create and mount the application.
import app._
import org.scalatra._
import javax.servlet.ServletContext
import com.mchange.v2.c3p0.ComboPooledDataSource
import org.slf4j.LoggerFactory
import scala.slick.jdbc.JdbcBackend.Database
/**
* This is the ScalatraBootstrap bootstrap file. You can use it to mount servlets or
* filters. It's also a good place to put initialization code which needs to
* run at application start (e.g. database configurations), and init params.
*/
class ScalatraBootstrap extends LifeCycle {
val logger = LoggerFactory.getLogger(getClass)
val cpds = new ComboPooledDataSource
logger.info("Created c3p0 connection pool")
override def init(context: ServletContext) {
val db = Database.forDataSource(cpds) // create a Database which uses the DataSource
context.mount(SlickApp(db), "/*") // mount the application and provide the Database
}
private def closeDbConnection() {
logger.info("Closing c3po connection pool")
cpds.close
}
override def destroy(context: ServletContext) {
super.destroy(context)
closeDbConnection
}
}
The connection pool configuration src/main/resources/c3p0.properties
looks like this:
c3p0.driverClass=org.h2.Driver
c3p0.jdbcUrl=jdbc:h2:mem:test
c3p0.user=root
c3p0.password=
c3p0.minPoolSize=1
c3p0.acquireIncrement=1
c3p0.maxPoolSize=50
Usage
Now we are ready to start with the sample application. The code serves only as a proof of concept. For more detailed information about Slick’s features take a look at the documentation.
We create two tables, one for suppliers and another one for coffees:
import scala.slick.driver.H2Driver.simple._
object Tables {
// Definition of the SUPPLIERS table
class Suppliers(tag: Tag) extends Table[(Int, String, String, String, String, String)](tag, "SUPPLIERS") {
def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
def name = column[String]("SUP_NAME")
def street = column[String]("STREET")
def city = column[String]("CITY")
def state = column[String]("STATE")
def zip = column[String]("ZIP")
// Every table needs a * projection with the same type as the table's type parameter
def * = (id, name, street, city, state, zip)
}
val suppliers = TableQuery[Suppliers]
// Definition of the COFFEES table
class Coffees(tag: Tag) extends Table[(String, Int, Double, Int, Int)](tag, "COFFEES") {
def name = column[String]("COF_NAME", O.PrimaryKey)
def supID = column[Int]("SUP_ID")
def price = column[Double]("PRICE")
def sales = column[Int]("SALES")
def total = column[Int]("TOTAL")
def * = (name, supID, price, sales, total)
// A reified foreign key relation that can be navigated to create a join
def supplier = foreignKey("SUP_FK", supID, suppliers)(_.id)
}
val coffees = TableQuery[Coffees]
}
Now we can create some routes:
GET /db/create-tables
creates the tablesGET /db/drop-tables
drops the tablesGET /db/load-data
loads sample data into the tablesGET /coffees
queries the database
We put the routes in a trait which we later add to the application. Note that we wrap code which uses the database in a session with db withDynSession { .. }
. The value db
is later provided by the application.
import org.scalatra._
import Tables._
import scala.slick.driver.H2Driver.simple._
import scala.slick.jdbc.JdbcBackend.Database.dynamicSession
trait SlickRoutes extends ScalatraServlet {
val db: Database
get("/db/create-tables") {
db withDynSession {
(suppliers.ddl ++ coffees.ddl).create
}
}
get("/db/load-data") {
db withDynSession {
// Insert some suppliers
suppliers.insert(101, "Acme, Inc.", "99 Market Street", "Groundsville", "CA", "95199")
suppliers.insert(49, "Superior Coffee", "1 Party Place", "Mendocino", "CA", "95460")
suppliers.insert(150, "The High Ground", "100 Coffee Lane", "Meadows", "CA", "93966")
// Insert some coffees (using JDBC's batch insert feature, if supported by the DB)
coffees.insertAll(
("Colombian", 101, 7.99, 0, 0),
("French_Roast", 49, 8.99, 0, 0),
("Espresso", 150, 9.99, 0, 0),
("Colombian_Decaf", 101, 8.99, 0, 0),
("French_Roast_Decaf", 49, 9.99, 0, 0)
)
}
}
get("/db/drop-tables") {
db withDynSession {
(suppliers.ddl ++ coffees.ddl).drop
}
}
get("/coffees") {
db withDynSession {
val q3 = for {
c <- coffees
s <- c.supplier
} yield (c.name.asColumnOf[String], s.name.asColumnOf[String])
contentType = "text/html"
q3.list.map { case (s1, s2) => " " + s1 + " supplied by " + s2 } mkString "<br />"
}
}
}
Finally let’s create the application:
case class SlickApp(db: Database) extends ScalatraServlet with SlickRoutes
Congratulations, you have now a basic Slick integration working! Feel free to do your own modifications.