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
DBIOAction) are created from individual queries and can be composed by
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
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
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.
map method usually changes both the mixed type and the unpacked type and is called in the final step.
exists method returns
true if the result set is not empty and
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 (
- E is the inferred effect type
DBIO[T] is an alias for
DBIOAction[T, NoStream, Effect.All]. The possible values of effect are:
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.
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
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
To update records, call
map of query.
filter, with an execution context, to combine actions. Other combinators include
andThen (same as
>>, or via
The DBIO object has methods working with DBIO results:
<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.
transactionally at the end of a sequence of actions will put all actions into a single transaction.
Table class uses the following method to define the mapping between db columns and Scala values.
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
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.
Slick makes columns non-nullable by default. use
Option[T] to represent nullable column. Use
col.isEmpty to filter nullable values.
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
Slick allow custom column mapping by defining an implicit value of
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
The monadic join uses foreign key (or manual conditional) to join a query in a for loop.
avg aggregation functions.
Slick supports type safe plain SQL access that includes interpolator sql for select and sqlu for update.
For custom type, provide an instance of
Slick supports type check with tsql.