A fresh look on accessing database on JVM platform: Slick from Typesafe

In today's post we are going to open our mind, step away from traditional Java EE / Java SE JPA-based stack (which I think is great) and take a refreshing look on how to access database in your Java applications using the new kid on the block: Slick 2.1 from Typesafe. So if JPA is so great, why bother? Well, sometimes you need to do very simple things and there is no need to bring the complete, well modeled persistence layer for that. In here Slick shines.

In the essence, Slick is database access library, not an ORM. Though it is written in Scala, the examples we are going to look at do not require any particular knowledge of this excellent language (although, it is just Scala that made Slick possible to exist). Our relational database schema will have only two tables, customers and addresses, linked by one-to-many relationships. For simplicity, H2 has been picked as an in-memory database engine.

The first question which comes up is defining database tables (schema) and, naturally, database specific DDLs are the standard way of doing that. Can we do something about it and try another approach? If you are using Slick 2.1, the answer is yes, absolutely. Let us just describe our tables as Scala classes:

// The 'customers' relation table definition
class Customers( tag: Tag ) extends Table[ Customer ]( tag, "customers" ) {
  def id = column[ Int ]( "id", O.PrimaryKey, O.AutoInc )

  def email = column[ String ]( "email", O.Length( 512, true ), O.NotNull )
  def firstName = column[ String ]( "first_name", O.Length( 256, true ), O.Nullable )
  def lastName = column[ String ]( "last_name", O.Length( 256, true ), O.Nullable )

  // Unique index for customer's email
  def emailIndex = index( "idx_email", email, unique = true )
}

Very easy and straightforward, resembling a lot typical CREATE TABLE construct. The addresses table is going to be defined the same way and reference users table by foreign key.

// The 'customers' relation table definition
class Addresses( tag: Tag ) extends Table[ Address ]( tag, "addresses" ) {
  def id = column[ Int ]( "id", O.PrimaryKey, O.AutoInc )

  def street = column[ String ]( "street", O.Length( 100, true ), O.NotNull )
  def city = column[ String ]( "city", O.Length( 50, true ), O.NotNull )
  def country = column[ String ]( "country", O.Length( 50, true ), O.NotNull )

  // Foreign key to 'customers' table
  def customerId = column[Int]( "customer_id", O.NotNull )
  def customer = foreignKey( "customer_fk", customerId, Customers )( _.id )
}

Great, leaving off some details, that is it: we have defined two database tables in pure Scala. But details are important and we are going to look closely on following two declarations: Table[ Customer ] and Table[ Address ]. Essentially, each table could be represented as a tuple with as many elements as column it has defined. For example, customers is a tuple of (Int, String, String, String), while addresses table is a tuple of (Int, String, String, String, Int). Tuples in Scala are great but they are not very convenient to work with. Luckily, Slick allows to use case classes instead of tuples by providing so called Lifted Embedding technique. Here are our Customer and Address case classes:

case class Customer( id: Option[Int] = None, email: String, 
  firstName: Option[ String ] = None, lastName: Option[ String ] = None)

case class Address( id: Option[Int] = None,  street: String, city: String, 
  country: String, customer: Customer )

The last but not least question is how Slick is going to convert from tuples to case classes and vice-versa? It would be awesome to have such conversion out-of-the box but at this stage Slick needs a bit of help. Using Slick terminology, we are going to shape * table projection (which corresponds to SELECT * FROM ... SQL construct). Let see how it looks like for customers:

// Converts from Customer domain instance to table model and vice-versa
def * = ( id.?, email, firstName.?, lastName.? ).shaped <> ( 
  Customer.tupled, Customer.unapply )

For addresses table, shaping looks a little bit more verbose due to the fact that Slick does not have a way to refer to Customer case class instance by foreign key. Still, it is pretty straightforward, we just construct temporary Customer from its identifier.

// Converts from Customer domain instance to table model and vice-versa
def * = ( id.?, street, city, country, customerId ).shaped <> ( 
  tuple => {
    Address.apply(
      id = tuple._1,
      street = tuple._2,
      city = tuple._3,
      country = tuple._4,
      customer = Customer( Some( tuple._5 ), "" )
    )
  }, {
    (address: Address) =>
      Some { (
        address.id,
        address.street,
        address.city,
        address.country,
        address.customer.id getOrElse 0 
      )
    }
  }
)

Now, when all details have been explained, how can we materialize our Scala table definitions into the real database tables? Thankfully to Slick, it is a easy as that:

implicit lazy val DB = Database.forURL( "jdbc:h2:mem:test", driver = "org.h2.Driver" )
  
DB withSession { implicit session =>
  ( Customers.ddl ++ Addresses.ddl ).create
}

Slick has many ways to query and update data in database. The most beautiful and powerful one is just using pure functional constructs of the Scala language. The easiest way of doing that is by defining companion object and implement typical CRUD operations in it. For example, here is the method which inserts new customer record into customers table:

object Customers extends TableQuery[ Customers ]( new Customers( _ ) ) {
  def create( customer: Customer )( implicit db: Database ): Customer = 
    db.withSession { implicit session =>
      val id = this.autoIncrement.insert( customer )
      customer.copy( id = Some( id ) )
    } 
}

And it could be used like this:

Customers.create( Customer( None, "tom@b.com",  Some( "Tom" ), Some( "Tommyknocker" ) ) )
Customers.create( Customer( None, "bob@b.com",  Some( "Bob" ), Some( "Bobbyknocker" ) ) )

Similarly, the family of find functions could be implemented using regular Scala for comprehension:

def findByEmail( email: String )( implicit db: Database ) : Option[ Customer ] = 
  db.withSession { implicit session =>
    ( for { customer <- this if ( customer.email === email.toLowerCase ) } 
      yield customer ) firstOption
  }
   
def findAll( implicit db: Database ): Seq[ Customer ] = 
  db.withSession { implicit session =>      
    ( for { customer <- this } yield customer ) list
  }

And here are usage examples:

val customers = Customers.findAll
val customer = Customers.findByEmail( "bob@b.com" )

Updates and deletes are a bit different though very simple as well, let us take a look on those:

def update( customer: Customer )( implicit db: Database ): Boolean = 
  db.withSession { implicit session =>
    val query = for { c <- this if ( c.id === customer.id ) } 
      yield (c.email, c.firstName.?, c.lastName.?)
    query.update(customer.email, customer.firstName, customer.lastName) > 0
  }
  
def remove( customer: Customer )( implicit db: Database ) : Boolean = 
  db.withSession { implicit session =>
    ( for { c <- this if ( c.id === customer.id ) } yield c ).delete > 0
  }

Let us see those two methods in action:

Customers.findByEmail( "bob@b.com" ) map { customer =>
  Customers.update( customer.copy( firstName = Some( "Tommy" ) ) )
  Customers.remove( customer )
}

Looks very neat. I am personally still learning Slick however I am pretty excited about it. It helps me to have things done much faster, enjoying the beauty of Scala language and functional programming. No doubts, the upcoming version 3.0 is going to bring even more interesting features, I am looking forward to it.

This post is just an introduction into world of Slick, a lot of implementation details and use cases have been left aside to keep it short and simple. However Slick's documentation is pretty good and please do not hesitate to consult it.

The complete project is available on GitHub.