Database patterns in Scala

This is the first in a three part series about our database access layer and patterns we’ve adopted (part 2: Clean and re-usable Slick modules). We use Slick to type-check and build our queries, but many of these ideas may help with other libraries.

There’s a decent amount of material out about Slick basics (how to query), but not much about building a larger, multi-module app using it. This post is a soft introduction into some of the lessons we’ve learned and patterns that keep us sane.

  • Type what’s important. In Scala, typing is easy (and with value classes, cheap). So, type your values. Id[User] is much better than Long. The compiler will make sure you don’t mix your IDs, and it’s always clear what you’re working with.
  • Determine common patterns between your tables, and only write it once. All of our tables have id, createdAt, and updatedAt columns, so we implement them on the RepoTable (which extends Slick’s Table) level. Additionally, some of our tables have an externalId column, so it’s an optional mix-in.
  • Log your query statements, watch out for inefficient generated SQL. The easiest way to do this is to wrap java.sql.Connection with logging around prepareStatement, prepareCall, nativeSQL, and createStatement.
  • Sometimes, hand writing SQL is the best option. Slick makes it easier than writing your own prepared statements.
  • Precompile your hot queries. Slick lets you compile queries ahead of time, and provide parameters as needed. It’s more verbose, and isn’t nearly as flexible, but in our benchmarks, you can sometimes save ~1ms per query.
  • Use a caching layer for complex queries. Of course, cache invalidation can get complex, so be careful. Yasuhiro Matsuda wrote a wonderful transactional caching layer that sits on top of in-memory and memcached caches. A little birdie told me he’ll write about it soon, so stay tuned.

We wrote this post while working on Kifi — Connecting people with knowledge. Learn more.


Originally published at eng.kifi.com on February 11, 2014.