Slick database connections and transactions

Database connections and transactions

You can use database queries anywhere in your program, and you need to have a database connection when you execute the query.

You can connect a JDBC database by creating a Database object, and there are several ways to create a database object.

Use the JDBC URL

You can use the JDBC URL to create a Database object (the format of the URL depends on the type of database connected), such as:

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

Create a memory-based H2 database connection, which we used earlier, using

val db = Database.forURL("jdbc:mysql://127.0.0.1/Chinook",
    driver = "com.mysql.jdbc.Driver",
    user="user",
    password="password")

Use DataSource

You can use an existing datasource object to build a Database object, such as if you took a Datasource object from a connection pool and connected it to the Slick library

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

You then create a Session object that will take a database connection from the connection pool, and when session is closed, the connection is returned to the connection pool for other purposes.

Use the JNDI name

If you use JNDI, you can provide the JNDI name to build a Database object:

val db = Database.forName(jndiName: String)

Session management

Now you have a database object to open a database (the Slick library encapsulates a Session object)

Database's withSession method creates a Session object that can be passed to a function that automatically closes the Session object when it returns, and automatically returns the connection to the connection pool if you use the connection pool, close the Session object.

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

As you can see, we can define a query outside of withSession, and only when the query is actually executed do we need a Session object, noting that Session's default mode is auto-commit. E ach database instruction, such as insert, is automatically submitted to the database. I f you need to use a few instructions as a whole, you need to use the example above transaction, and when we execute the query, we clearly indicate the session object, which you can avoid by using an implicit object, such as:

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

Manage Sessions by hand

This is not recommended, but if you need to manage session objects yourself, you can manage session lifecycles yourself:

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

Transaction processing

You can use the withTransaction method of the Session object to create a transaction, the statement passed to the method will be executed as a whole transaction, and if an exception occurs, Slick automatically rolls back the transaction, or you can use rollback to force the transaction to roll back, not to note that Slick will only roll back database-related operations without canceling other Scala statements.

session.withTransaction {
    // your queries go here

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

} //

If you don't have a Session object, you can also use the withTransaction method for database objects directly, such as:

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