Scala Tutorial: Create CRUD with Slick and MySQL

9 min readAug 31, 2016

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.

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 programmingparadigms. 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 modelto 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 torelational 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 Scaladeveloper, 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.xwas. 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, implementsreactive streams specification as well.

. 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 singlerelational 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 theapplication.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. ThemaxThreads 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 TABLEstatement. However, Slick also has a way to define seamless conversion between domain entity represented byScala 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.foldprocessing 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, "[[email protected]](/cdn-cgi/l/email-protection)<script data-cfhash="f9e31" type="text/javascript">/* <![CDATA[ */!function(t,e,r,n,c,a,p){try{t=document.currentScript||function(){for(t=document.getElementsByTagName('script'),e=t.length;e--;)if(t[e].getAttribute('data-cfhash'))return t[e]}();if(t&&(c=t.previousSibling)){p=t.parentNode;if(a=c.getAttribute('data-cfemail')){for(e='',r='0x'+a.substr(0,2)|0,n=2;a.length-n;n+=2)e+='%'+('0'+('0x'+a.substr(n,2)^r).toString(16)).slice(-2);p.replaceChild(document.createTextNode(decodeURIComponent(e)),c)}p.removeChild(t)}}catch(u){}}()/* ]]> */</script>", 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 TestKitmodule 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 withrelational 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.