This is a study note of the Slick (Scala Language Integrated Connection Kit), a fuctional relational mapping (FRM) library. It is based on the book of Essential Slick and the Slick Docs.

Introduction

Slick is FRM library that allows you to model a schema and preform CRUD operations. It provides db access APIs similar to the Scala collections library. It creates type safe queries that can be transformed by map, flatMap, and fiter before sedning them to the db. Queries are composable to create a complex query. Slick allows plain SQL queries and accessing the underly JDBC functions.

A collection-valued query is of type Query that can be composed from TableQuery objects, literal values and parameters. It can be transformed using combinators such as map and filter.

Actions (DBIOAction) are created from individual queries and can be composed by andThen, flatMap, DBIO.seq or transactionally.

A Database object manages db connection resources and is used toto run actions. Execution is alway asynchronous in a seperate thread pool.

The result of an action execution can be a Future data or a stream.

Each specific database use a profile extended from JdbcProfile.

Query and Action

Starting with a TableQuery, we can create queries using filter and map. Slick represents all queries using a trait Query[M, U, C] where

  • M is the mixed type. This is the function parameter type used in map and filter. It is the type of table.
  • U is the unpacked type. This is the type we collect in our result. It is the data type of each row in the table.
  • C is the collection type. This is the type of collection we accumulate results into.

The map method usually changes both the mixed type and the unpacked type and is called in the final step.

The exists method returns true if the result set is not empty and false otherwise.

Before running a query, we need to covert it to an action by calling the result method on the query. Actions can be composed into a sequence of actions. Actions have a type signature of DBIOAction[R, S, E] where

  • R is the type of result data
  • S indicates whether the results are streamed (Streaming[T]) or not (NoStream)
  • E is the inferred effect type

DBIO[T] is an alias for DBIOAction[T, NoStream, Effect.All]. The possible values of effect are: Read, Write, Schema, Transactional, and All for all effects.

To execute an action, use db.run(...) to return a future value or db.stream(...) to return a DatabasePublisher that can be used to feed Akka streams.

Methods like filter and map require column expression that is represented by the Rep type. The mothods operate on a Rep type are:

  • comparison method: ===, =!=, <, >, <=, >=
  • string methods: ++, like, length, startsWith, endsWith, toUpperCase, toLowerCase, trim, ltrim, rtrim
  • numeric methods: +, -, *, /, %, abs, ceil, floor, round
  • boolean methods: &&, ||, !
  • control methods: sortBy, take, drop
  • control null sort: nullsFirst, nullsDefault, nullsLast
  • conditional filtering: filterOpt, filterIf

Creating and Modifying Data

To retrieve db allocated primary key in insert, use returning method: table returning table.map(_.id) += Row(...). To retrieve the inserted record, use table returning table += Row(...), however, some dbs don’t support this feature.

To delete records, call delete on a TableQuery.

To update records, call update after filter and map of query.

Combining Actions

Actions support map, flatMap, and filter, with an execution context, to combine actions. Other combinators include named, zip, asTry, andThen (same as >>, or via DBIO.seq(...)), andFinally, cleanUp and failed.

The DBIO object has methods working with DBIO results: sequence, seq, from, successful, failed, and fold.

The setting <logger name="slick.jdbc.JdbcBackend.statement" level="DEBUG"/> causes Slick to log every query. Setting slick.jdbc.StatementInvoker.result logs the first few results of each query.

Calling transactionally at the end of a sequence of actions will put all actions into a single transaction.

Data Modelling

Mapping Rows

The Table class uses the following method to define the mapping between db columns and Scala values.

1
2
3
abstract class Table[T] {
    def * : ProvenShape[T]
}

Slick has implicit converter to build a ProvenShape[T] from a single column definition, a tuple of columns, using a projection operator <> that maps from and into a case class – often using the mapTo macro.

Because a tuple can only have up to 22 fields, Slick provides HList that has an arbitrary length like a list but each element can have a different type like a tuple. HList can be mapped to a case class with the same fields.

Column Representation

Slick makes columns non-nullable by default. use Option[T] to represent nullable column. Use col.isEmpty to filter nullable values.

Use O.PrimaryKey to define a primary key, use primaryKey to define a composite key, use index to define an index, use foreignKey to define a foreign key with onDelete optioins.

Slick allow custom column mapping by defining an implicit value of MappedColumnType.base[ScalaType, DbType].

A value class is a compile time wrapper around a value. Good for type safety.

Use sum types,

Joins and Aggregation

Slick support two styles of join: applicative, the SQL JOIN ON statement, and the monadic, the functional flatMap.

The monadic join uses foreign key (or manual conditional) to join a query in a for loop.

Slick support join, joinLeft, joinRight, joinFull, zip and zipWith.

Slick supports length, min, max, sum and avg aggregation functions.

Slick supports groupBy.

Plain SQL

Slick supports type safe plain SQL access that includes interpolator sql for select and sqlu for update.

For custom type, provide an instance of GetResult.

Slick supports type check with tsql.