Scala Slick - dealing with large tables of more than 22 columns

Slick is a tool for managing access to a database in Scala. So far I have mainly quite liked it, but have also found that documentation can be a bit lacking in some areas, especially around managing tables with more than 22 columns, or mapping your columns into a nested case class structure.

This post attempts to stitch together suggestions from a few different sources so you can see what the options are and which may be best suited to your use case. This blog is supported by code examples that all compile and form part of this project:
https://github.com/timgent/spray-slick-template

The simple case - mapping a small, flat case class

As with all good products Slick's tutorial starts off with an example that makes everything seem straightforward and easy - mapping from a small number of columns to an unnested case class.

As you can see we just define our case class, create a table based on this with it's respective columns, and finally define a * projection.

The * projection is there to provide a mapping from the columns, to a tuple, to your case class. It first sets out the columns you are mapping from, and then takes 2 functions - one to map from the tuple to your case class (hence the use of the tupled function on the case class here), and one to map from the case class back to a tuple (which is exactly what unapply does)

Oh no! More than 22 columns!

This approach stops working as soon as you have more than 22 columns. You can see this as Case Classes stop having the tupled and unapply methods as soon as you have more than 22 columns, scuppering our earlier simple approach.

But fear not, there are 2 fundamental approachs to dealing with this - using nested tuples and nested case classes (which come in 2 flavours), or using HLists.

Using Nested Tuples and Case Classes

Our challenge comes from having too many fields in our tuple and case class so one simple solution is to nest them. This means on each nested case class we can still use the unapply and tupled methods, though it does add a little boiler plate for us.

 

Using projections for each case class

This is my preferred method. We have to:

1. Create our nested case class
2. Group our columns to match the nesting of our case class
3. Create projections for each nested case class
4. Use these projections in our * projection
5. The mapping functions as you can see remain fairly simple

The gist is here

 

Using custom mappings

You can just use your own custom mapping functions, though personally I find this can get quite messy quite quickly. In particular when you have a compile error listing over 22 types it is rather confusing.

The steps are the same as above except we don't create projections for each nested case class, which means our mapping functions have to do all the unapplying and tupling themselves.

Gist here

Using HLists

An alternative to all this is using HLists (essentially lists with types for each element).

 

Plain HLists (no mapping to case class)

Another method I favour is using plain HLists - fairly simple with minimal boilerplate.

This is as simple as having the right imports and just defining a * projection with your columns all stuffed in a HList.

Gist here

 

HLists with a mapping to a large case class

This example takes it to the extreme of using HLists instead of tuples, but then using a custom mapping to transform this to a case class.

The advantage is you can use a case class with more than 22 fields, but it does require a little more boiler plate.

The steps are:

1. Create our large unnested case class
2. Create our columns as usual (no nesting required)
3. Define a * projection, with custom mappings to go from HList to our Case Class, and vice versa

Gist here