Connections / Transactions

You can write queries anywhere in your program. When you want to execute them you need a database connection.

Database connection

You can tell Slick how to connect to the JDBC database of your choice by creating a Database object, which encapsulates the information. There are several factory methods on scala.slick.jdbc.JdbcBackend.Database that you can use depending on what connection data you have available.

Using a JDBC URL

You can provide a JDBC URL to forURL. (see your database’s JDBC driver’s documentation for the correct URL syntax).

val db = Database.forURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1", driver="org.h2.Driver")

Here we are connecting to a new, empty, in-memory H2 database called test1 and keep it resident until the JVM ends (DB_CLOSE_DELAY=-1, which is H2 specific).

Using a DataSource

You can provide a DataSource object to forDataSource. If you got it from the connection pool of your application framework, this plugs the pool into Slick.

val db = Database.forDataSource(dataSource: javax.sql.DataSource)

When you later create a Session, a connection is acquired from the pool and when the Session is closed it is returned to the pool.

Using a JNDI Name

If you are using JNDI you can provide a JNDI name to forName under which a DataSource object can be looked up.

val db = Database.forName(jndiName: String)

Session handling

Now you have a Database object and you can use it to open database connections, which Slick encapsulates in Session objects.

Automatically closing Session scope

The Database object’s withSession method creates a Session, passes it to a given function and closes it afterwards. If you use a connection pool, closing the Session returns the connection to the pool.

val query = for (c <- coffees) yield c.name
val result = db.withSession {
  session =>
  query.list(session)
}

You can see how we are able to already define the query outside of the withSession scope. Only the methods actually executing the query in the database require a Session. Here we use the list method to execute the query and return the results as a List. (The executing methods are made available via implicit conversions).

Note that by default a database session is in auto-commit mode. Each call to the database like insert or insertAll executes atomically (i.e. it succeeds or fails completely). To bundle several statements use Transactions.

Be careful: If the Session object escapes the withSession scope, it has already been closed and is invalid. It can escape in several ways, which should be avoided, e.g. as state of a closure (if you use a Future inside a withSession scope for example), by assigning the session to a var, by returning the session as the return value of the withSession scope or else.

Implicit Session

By marking the Session as implicit you can avoid having to pass it to the executing methods explicitly.

val query = for (c <- coffees) yield c.name
val result = db.withSession {
  implicit session =>
  query.list // <- takes session implicitly
}
// query.list // <- would not compile, no implicit value of type Session

This is optional of course. Use it if you think it makes your code cleaner.

Transactions

You can use the Session object’s withTransaction method to create a transaction when you need one. The block passed to it is executed in a single transaction. If an exception is thrown, Slick rolls back the transaction at the end of the block. You can force the rollback at the end by calling rollback anywhere within the block. Be aware that Slick only rolls back database operations, not the effects of other Scala code.

session.withTransaction {
  // your queries go here

  if (/* some failure */ false){
    session.rollback // signals Slick to rollback later
  }

} // <- rollback happens here, if an exception was thrown or session.rollback was called

If you don’t have a Session yet you can use the Database object’s withTransaction method as a shortcut.

db.withTransaction{
  implicit session =>
  // your queries go here
}

Manual Session handling

This is not recommended, but if you have to, you can handle the lifetime of a Session manually.

val query = for (c <- coffees) yield c.name
val session : Session = db.createSession
val result  = query.list(session)
session.close

Passing sessions around

You can write re-useable functions to help with Slick queries. They mostly do not need a Session as they just produce query fragments or assemble queries. If you want to execute queries inside of them however, they need a Session. You can either put it into the function signature and pass it as a (possibly implicit) argument. Or you can bundle several such methods into a class, which stores the session to reduce boilerplate code:

class Helpers(implicit session: Session){
  def execute[T, C[_]](query: Query[T,_, C]) = query.list
  // ... place further helpers methods here
}
val query = for (c <- coffees) yield c.name
db.withSession {
  implicit session =>
  val helpers = (new Helpers)
  import helpers._
  execute(query)
}
// (new Helpers).execute(query) // <- Would not compile here (no implicit session)

Dynamically scoped sessions

You usually do not want to keep sessions open for very long but open and close them quickly when needed. As shown above you may use a session scope or transaction scope with an implicit session argument every time you need to execute some queries.

Alternatively you can save a bit of boilerplate code by putting

import Database.dynamicSession // <- implicit def dynamicSession : Session

at the top of your file and then using a session scope or transaction scope without a session argument.

db.withDynSession {
  // your queries go here
}

dynamicSession is an implicit def that returns a valid Session if a withDynSession or withDynTransaction : scope is open somewhere on the current call stack.

Be careful, if you import dynamicSession and try to execute a query outside of a withDynSession or withDynTransaction scope, you will get a runtime exception. So you sacrifice some static safety for less boilerplate. dynamicSession internally uses DynamicVariable, which implements dynamically scoped variables and in turn uses Java’s InheritableThreadLocal. Be aware of the consequences regarding static safety and thread safety.

Connection Pools

Slick does not provide a connection pool implementation of its own. When you run a managed application in some container (e.g. JEE or Spring), you should generally use the connection pool provided by the container. For stand-alone applications you can use an external pool implementation like DBCP, c3p0 or BoneCP.

Note that Slick uses prepared statements wherever possible but it does not cache them on its own. You should therefore enable prepared statement caching in the connection pool’s configuration and select a sufficiently large pool size.

JDBC interoperability

To access features not available in Slick directly it can be useful to drop down to JDBC level.

You can access the underlying JDBC Connection of a Slick Session using the conn method.

Session‘s withPreparedInsertStatement, withPreparedStatement, withStatement methods allow you to create automatically closing JDBC Statements.