Getting Started¶
The easiest way to get started is with a working application in Typesafe Activator. To learn the basics of Slick start with the Hello Slick template. To learn how to integrate Slick with Play Framework check out the Play Slick with Typesafe IDs template.
To include Slick into an existing project use the library published on Maven Central. For sbt projects add the following to your libraryDependencies:
"com.typesafe.slick" %% "slick" % "2.0.2-RC1", "org.slf4j" % "slf4j-nop" % "1.6.4"
For Maven projects add the following to your <dependencies>:
<dependency> <groupId>com.typesafe.slick</groupId> <artifactId>slick_2.10</artifactId> <version>2.0.2-RC1</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-nop</artifactId> <version>1.6.4</version> </dependency>
Slick uses SLF4J for its own debug logging so you also need to add an SLF4J implementation. Here we are using slf4j-nop to disable logging. You have to replace this with a real logging framework like Logback if you want to see log output.
Slick Examples¶
Check out the Slick Examples project for more examples like using multiple databases, using native queries, and advanced invoker usage.
Quick Introduction¶
To use Slick you first need to import the API for the database you will be using, like:
// Use H2Driver to connect to an H2 database
import scala.slick.driver.H2Driver.simple._
Since we are using H2 as our database system, we need to import features from Slick’s H2Driver. A driver’s simple object contains all commonly needed imports from the driver and other parts of Slick such as session handling.
Database Connection¶
In the body of the application we create a Database object which specifies how to connect to a database, and then immediately open a session, running all code within the following block inside that session:
Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver") withSession {
implicit session =>
// <- write queries here
}
In a Java SE environment, database sessions are usually created by connecting to a JDBC URL using a JDBC driver class (see the JDBC driver’s documentation for the correct URL syntax). If you are only using plain SQL queries, nothing more is required, but when Slick is generating SQL code for you (using the direct embedding or the lifted embedding), you need to make sure to use a matching Slick driver (in our case the H2Driver import above).
Schema¶
We are using the lifted embedding in this application, so we need Table row classes and TableQuery values for our database tables. You can either use the code generator to automatically create them for your database schema or you can write them by hand:
// 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]
All columns get a name (usually in camel case for Scala and upper case with underscores for SQL) and a Scala type (from which the SQL type can be derived automatically). The table object also needs a Scala name, SQL name and type. The type argument of the table must match the type of the special * projection. In simple cases this is a tuple of all columns but more complex mappings are possible.
The foreignKey definition in the coffees table ensures that the supID field can only contain values for which a corresponding id exists in the suppliers table, thus creating an n to one relationship: A Coffees row points to exactly one Suppliers row but any number of coffees can point to the same supplier. This constraint is enforced at the database level.
Populating the Database¶
The connection to the embedded H2 database engine provides us with an empty database. Before we can execute queries, we need to create the database schema (consisting of the coffees and suppliers tables) and insert some test data:
// Create the tables, including primary and foreign keys
(suppliers.ddl ++ coffees.ddl).create
// Insert some suppliers
suppliers += (101, "Acme, Inc.", "99 Market Street", "Groundsville", "CA", "95199")
suppliers += ( 49, "Superior Coffee", "1 Party Place", "Mendocino", "CA", "95460")
suppliers += (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 ++= Seq(
("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)
)
The TableQuery‘s ddl method creates DDL (data definition language) objects with the database-specific code for creating and dropping tables and other database entities. Multiple DDL values can be combined with ++ to allow all entities to be created and dropped in the correct order, even when they have circular dependencies on each other.
Inserting the tuples of data is done with the += and ++= methods, similar to how you add data to mutable Scala collections. Note that by default a database Session is in auto-commit mode. Each call to the database like += or ++= executes atomically in its own transaction (i.e. it succeeds or fails completely but can never leave the database in an inconsistent state somewhere in between). In this mode we have to populate the suppliers table first because the coffees data can only refer to valid supplier IDs.
We could also use an explicit transaction bracket encompassing all these statements. Then the order would not matter because the constraints are only enforced at the end when the transaction is committed.
Querying¶
The simplest kind of query iterates over all the data in a table:
// Iterate through all coffees and output them
coffees foreach { case (name, supID, price, sales, total) =>
println(" " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" + total)
}
This corresponds to a SELECT * FROM COFFEES in SQL (except that the * is the table’s * projection we defined earlier and not whatever the database sees as *). The type of the values we get in the loop is, unsurprisingly, the type parameter of Coffees.
Let’s add a projection to this basic query. This is written in Scala with the map method or a for comprehension:
// Why not let the database do the string conversion and concatenation?
val q1 = for(c <- coffees)
yield LiteralColumn(" ") ++ c.name ++ "\t" ++ c.supID.asColumnOf[String] ++
"\t" ++ c.price.asColumnOf[String] ++ "\t" ++ c.sales.asColumnOf[String] ++
"\t" ++ c.total.asColumnOf[String]
// The first string constant needs to be lifted manually to a LiteralColumn
// so that the proper ++ operator is found
q1 foreach println
The output will be the same: For each row of the table, all columns get converted to strings and concatenated into one tab-separated string. The difference is that all of this now happens inside the database engine, and only the resulting concatenated string is shipped to the client. Note that we avoid Scala’s + operator (which is already heavily overloaded) in favor of ++ (commonly used for sequence concatenation). Also, there is no automatic conversion of other argument types to strings. This has to be done explicitly with the type conversion method asColumnOf.
Joining and filtering tables is done the same way as when working with Scala collections:
// Perform a join to retrieve coffee names and supplier names for
// all coffees costing less than $9.00
val q2 = for {
c <- coffees if c.price < 9.0
s <- suppliers if s.id === c.supID
} yield (c.name, s.name)
Note the use of === instead of == for comparing two values for equality. Similarly, the lifted embedding uses =!= instead of != for inequality. (The other comparison operators are the same as in Scala: <, <=, >=, >.)
The generator expression suppliers if s.id === c.supID follows the relationship established by the foreign key Coffees.supplier. Instead of repeating the join condition here we can use the foreign key directly:
val q3 = for {
c <- coffees if c.price < 9.0
s <- c.supplier
} yield (c.name, s.name)