This article is part of our Academy Course titled Developing Modern Applications with Scala.
In this course, we provide a framework and toolset so that you can develop modern Scala applications. We cover a wide range of topics, from SBT build and reactive applications, to testing and database acceess. With our straightforward tutorials, you will be able to get your own projects up and running in minimum time. Check it out here!
We are certainly leaving at the epoch of flourishing data stores. Myriads of NoSQL and NewSQL solutions have emerged over the last couple of years, and even these days the new ones are popping up here and there from time to time.
Nonetheless, long-time players in the form of relational database are still being used in the vast majority of software systems. Bullet-proof and battle-tested, they are number one choice for storing critical to business data.
Table Of Contents
1. Introduction
When we talk about Java and JVM platform in general, JDBC is a standard way of interfacing with relational databases. As such, mostly every single relational database vendor provides a JDBC driver implementation so it becomes possible to connect to the database engine from the application code.
In many respects, JDBC is old-fashioned specification which hardly fits into modern reactive programming paradigms. Although there are some discussions to revamp the specification, in reality no active work is happening in this direction, at least publicly.
2. Database Access, the Functional Way
Inability to speed up the changes in specification does not mean nothing could be done. There many, many different frameworks and libraries available on JVM to access relational databases. Some of them aim to be as close to SQL as possible, others going further than that offering sort of “seamless” mapping of the relational model to programming language constructs (so called class of ORM or object-relational mapping solutions). Although to be fair, most of them are built on top of JDBC abstractions nonetheless.
In this regards, Slick (or in full, Scala Language-Integrated Connection Kit) is a library to provide access to relational databases from Scala application. It is heavily based on functional programming paradigm and as such is often being referred as functional relational mapping (or FRM) library. Slick’s ultimate promise is to reinvent the way to access relational databases by means of regular operations over collections, so familiar to every Scala developer, with strong emphasize on type safety.
Despite having 3.1.1 release out not so long ago, it is relatively very young library, still on its way to reach a certain level of maturity. Many early adopters do remember how significant the difference between versions 2.x and 3.x was. Luckily, things are getting more stable and smoother, with the first milestone of upcoming 3.2 release being just a few weeks old.
Slick goes with the times and is fully asynchronous library. And, as we are going to see very soon, implements reactive streams specification as well.
3. Configuration
Slick fully supports quite a number of popular open-source and commercial relational database engines. To demonstrate that we are going to use at least two of them: MySQL for production deployment and H2 for integration testing.
To give Slick the credits, it is very easy to get started with when the application is developed against single relational database engine. But it is a little bit tricky to configure and use Slick in JDBC-driver independent fashion, due to differences in the database’s capabilities. As we are targeting at least two different engines, MySQL and H2, we are certainly going to take this route.
Typical way of configuring database connections in Slick is to have a dedicated named section in the application.conf file, for example:
db {
driver = "slick.driver.MySQLDriver$"
db {
url = "jdbc:mysql://localhost:3306/test?user=root&password=password"
driver = com.mysql.jdbc.Driver
maxThreads = 5
}
}The configuration should look familiar to JVM developers working with relational database over JDBC. It is worth to mention that Slick supports database connection pooling out of the box using brilliant HikariCP library. The maxThreads setting hints Slick to configure connection pool of the maximum size of 5.
If you are curious why there are two driver settings in the configuration, here is the reason. The first driver setting identifies the Slick-specific JDBC profile (Slick driver), while the second one points out to JDBC driver implementation to use.
To take care of this configuration we are going to define a dedicated DbConfiguration trait, although the purpose of introducing this trait may not be so obvious for now:
trait DbConfiguration {
lazy val config = DatabaseConfig.forConfig[JdbcProfile]("db")
}4. Table Mappings
Arguably the first thing to start with in relational databases universe is data modeling. Essentially, it translates to creation of the database schema, tables, their relations and constraints. Luckily, Slick makes it extremely easy to do.
As an exercise, let us build a sample application to manage users and their addresses, represented by those two classes.
case class User(id: Option[Int], email: String, firstName: Option[String], lastName: Option[String]) case class Address(id: Option[Int], userId: Int, addressLine: String, city: String, postalCode: String)
In turn, our relation data model is going to be constituted from just two tables, USERS and ADDRESSES. Let us use Slick capabilities to shape that out in Scala.
trait UsersTable { this: Db =>
import config.driver.api._
private class Users(tag: Tag) extends Table[User](tag, "USERS") {
// Columns
def id = column[Int]("USER_ID", O.PrimaryKey, O.AutoInc)
def email = column[String]("USER_EMAIL", O.Length(512))
def firstName = column[Option[String]]("USER_FIRST_NAME", O.Length(64))
def lastName = column[Option[String]]("USER_LAST_NAME", O.Length(64))
// Indexes
def emailIndex = index("USER_EMAIL_IDX", email, true)
// Select
def * = (id.?, email, firstName, lastName) <> (User.tupled, User.unapply)
}
val users = TableQuery[Users]
}
For the people familiar with SQL language, there is definitely a very close resemblance with CREATE TABLE statement. However, Slick also has a way to define seamless conversion between domain entity represented by Scala class (User) to table row (Users) and vice versa, using * projection (literally translates to SELECT * FROM USERS).
The one subtle detail we haven’t touched upon yet is Db trait (referenced by this: Db => construct). Let us take a look on how it is defined:
trait Db {
val config: DatabaseConfig[JdbcProfile]
val db: JdbcProfile#Backend#Database = config.db
}
The config is the one from DbConfiguration while db is a new database instance. Later on in the UsersTable trait the respective types for the relevant JDBC profile are introduced into the scope using import config.driver.api._ declaration.
The mapping for the ADDRESSES table looks very much the same, except the fact we need a foreign key reference to the USERS table.
trait AddressesTable extends UsersTable { this: Db =>
import config.driver.api._
private class Addresses(tag: Tag) extends Table[Address](tag, "ADDRESSES") {
// Columns
def id = column[Int]("ADDRESS_ID", O.PrimaryKey, O.AutoInc)
def addressLine = column[String]("ADDRESS_LINE")
def city = column[String]("CITY")
def postalCode = column[String]("POSTAL_CODE")
// ForeignKey
def userId = column[Int]("USER_ID")
def userFk = foreignKey("USER_FK", userId, users)
(_.id, ForeignKeyAction.Restrict, ForeignKeyAction.Cascade)
// Select
def * = (id.?, userId, addressLine, city, postalCode) <>
(Address.tupled, Address.unapply)
}
val addresses = TableQuery[Addresses]
}The users and addresses members serve as a façade to perform any database access operations against respective tables.
5. Repositories
Although repositories are not specific to Slick per se, defining a dedicated layer to communicate with database engine is always a good design principle. There would be only two repositories in our application, UsersRepository and AddressesRepository.
class UsersRepository(val config: DatabaseConfig[JdbcProfile])
extends Db with UsersTable {
import config.driver.api._
import scala.concurrent.ExecutionContext.Implicits.global
...
}
class AddressesRepository(val config: DatabaseConfig[JdbcProfile])
extends Db with AddressesTable {
import config.driver.api._
import scala.concurrent.ExecutionContext.Implicits.global
...
}All data manipulations we are going to show case later on are going to be part of one of those classes. Also, please notice the presence of Db trait in the inheritance chain.
6. Manipulating Schemas
Once the table mappings (or simplify database schema) are defined, Slick has a capability to project it to a sequence of DDL statements, for example:
def init() = db.run(DBIOAction.seq(users.schema.create)) def drop() = db.run(DBIOAction.seq(users.schema.drop)) def init() = db.run(DBIOAction.seq(addresses.schema.create)) def drop() = db.run(DBIOAction.seq(addresses.schema.drop))
7. Inserting
In the simplest scenarios adding a new row to the table is as easy as adding an element to users or addresses (instances of TableQuery), for example:
def insert(user: User) = db.run(users += user)
That works fine when primary keys are assigned from the application code. However, in case when primary keys are generated on database side (for example using auto-increments), like for Users and Addresses tables, we have to ask for these primary identifiers to be returned to us:
def insert(user: User) = db .run(users returning users.map(_.id) += user) .map(id => user.copy(id = Some(id)))
8. Querying
Querying is one of the Slick distinguishing features which really shines. As we already mentioned, Slick tries hard to allow using Scala collection semantics over database operations. However it works surprisingly well please note that you are not working with the standard Scala types but the lifted ones: the technique known as lifted embedding.
Let us take a look on this quick example on the one of the possible ways to retrieve user from the table by its primary key:
def find(id: Int) = db.run((for (user <- users if user.id === id) yield user).result.headOption)
Alternatively to for comprehension we could just use filtering operation, for example:
def find(id: Int) = db.run(users.filter(_.id === id).result.headOption)
The results (and generated SQL query by the way) are exactly the same. In case we need to fetch user and its address, we could use a couple of query options here as well, starting with a typical join:
def find(id: Int) = db.run(
(for ((user, address) <- users join addresses if user.id === id)
yield (user, address)).result.headOption)
Or, alternatively:
def find(id: Int) = db.run(
(for {
user <- users if user.id === id
address <- addresses if address.userId === id
} yield (user, address)).result.headOption)
Slick querying capabilities are really very powerful, expressive and readable. We have just looked at a couple of typical examples but please glance through official documentation to find much more.
9. Updating
Updates in Slick are represented as a combination of a query (which basically outlines what should be updated) and essentially the update itself. For example, let us introduce a method to update user’s first and last names:
def update(id: Int, firstName: Option[String], lastName: Option[String]) = {
def update(id: Int, firstName: Option[String], lastName: Option[String]) = {
val query = for (user <- users if user.id === id)
yield (user.firstName, user.lastName)
db.run(query.update(firstName, lastName)) map { _ > 0 }
}
10. Deleting
Similarly to updates, the delete operation is basically just a query to filter out the rows to be removed, for example:
def delete(id: Int) =
db.run(users.filter(_.id === id).delete) map { _ > 0 }
11. Streaming
Slick offers the capability to stream results of the database query. Not only that, its streaming implementation fully supports reactive streams specification and could be used right away in conjunction with Akka Streams.
For example, let us stream the results from users table and collect them as a sequence using Sink.fold processing stage.
def stream(implicit materializer: Materializer) = Source .fromPublisher(db.stream(users.result.withStatementParameters(fetchSize=10))) .to(Sink.fold[Seq[User], User](Seq())(_ :+ _)) .run()
Please be advised that Slick’s streaming feature is really very sensitive to relational database and JDBC driver you are using and may require more exploration and tuning. Definitely do some extensive testing to make sure the data is streamed properly.
12. SQL
In case there is a need to run a custom SQL queries, Slick has nothing against that and as always tries to make it as painless as possible, providing useful macros. Let say we would like to read user’s first and last names directly using plain old SELECT statement.
def getNames(id: Int) = db.run(
sql"select user_first_name, user_last_name from users where user_id = #$id"
.as[(String, String)].headOption)
It is as easy as that. In case the shape of the SQL query is not known ahead of time, Slick provides the mechanisms to customize the result set extraction. In case you are interested, official documentation has very good section dedicated to plain old SQL queries.
13. Testing
There are multiple ways you can approach testing of the database access layer when using Slick library. The traditional one is by using in-memory database (like H2 for example), which in our case translates into minor configuration change inside application.conf:
db {
driver = "slick.driver.H2Driver$"
db {
url = "jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1"
driver=org.h2.Driver
connectionPool = disabled
keepAliveConnection = true
}
}
Please notice that if in production configuration we turned database connection pooling on, the test one uses just single connection and pool is explicitly disabled. Everything else essentially stays the same. The only thing we have to take care of is creating and dropping the schema between test runs. Luckily, as we saw in section Manipulating Schemas, it is very easy to do with Slick.
class UsersRepositoryTest extends Specification with DbConfiguration
with FutureMatchers with OptionMatchers with BeforeAfterEach {
sequential
val timeout = 500 milliseconds
val users = new UsersRepository(config)
def before = {
Await.result(users.init(), timeout)
}
def after = {
Await.result(users.drop(), timeout)
}
"User should be inserted successfully" >> { implicit ee: ExecutionEnv =>
val user = User(None, "a@b.com", Some("Tom"), Some("Tommyknocker"))
users.insert(user) must be_== (user.copy(id = Some(1))).awaitFor(timeout)
}
}
Very basic Specs2 test specification with single test step to verify that new user is properly inserted into database table.
In case for any reasons you are developing your own database driver for Slick, there is a helpful Slick TestKit module available along with example driver implementation.
14. Conclusions
Slick is extremely expressive and powerful library to access relational databases from Scala applications. It is very flexible and in most cases offers multiple alternative ways of accomplishing things at the same time trying hard to maintain the balance between making developers highly productive and not hiding the fact that they dial with relational model and SQL under the hood.
Hopefully, we all are Slick-infected right now and are eager to try it out. Official documentation is a very good place to begin learning Slick and get started.
15. What’s next
In the next section of the tutorial we are going to talk about developing command line (or simply console) Scala applications.
