Typesafe Activator

Hello Slick!

Hello Slick!

typesafehub
Source
July 6, 2014
basics slick starter scala

Slick is Typesafe's modern database query and access library for Scala. It allows you to work with stored data almost as if you were using Scala collections while at the same time giving you full control over when a database access happens and which data is transferred. You can also use SQL directly. This tutorial will get you started with a simple standalone Scala application that uses Slick.

How to get "Hello Slick!" on your computer

There are several ways to get this template.

Option 1: Choose hello-slick in the Typesafe Activator UI.

Already have Typesafe Activator (get it here)? Launch the UI then search for hello-slick in the list of templates.

Option 2: Download the hello-slick project as a zip archive

If you haven't installed Activator, you can get the code by downloading the template bundle for hello-slick.

  1. Download the Template Bundle for "Hello Slick!"
  2. Extract the downloaded zip file to your system
  3. The bundle includes a small bootstrap script that can start Activator. To start Typesafe Activator's UI:

    In your File Explorer, navigate into the directory that the template was extracted to, right-click on the file named "activator.bat", then select "Open", and if prompted with a warning, click to continue:

    Or from a command line:

     C:\Users\typesafe\hello-slick> activator ui 
    This will start Typesafe Activator and open this template in your browser.

Option 3: Create a hello-slick project from the command line

If you have Typesafe Activator, use its command line mode to create a new project from this template. Type activator new PROJECTNAME hello-slick on the command line.

Option 4: View the template source

The creator of this template maintains it at https://github.com/typesafehub/activator-hello-slick#master.

Option 5: Preview the tutorial below

We've included the text of this template's tutorial below, but it may work better if you view it inside Activator on your computer. Activator tutorials are often designed to be interactive.

Preview the tutorial

Intro to Slick

Slick is a Functional Relational Mapping (FRM) library for Scala where you work with relational data in a type-safe and functional way. Here is an example:

coffees.filter(_.price < 10.0).map(_.name)
This will produce a query equivalent to the following SQL:
select COF_NAME from COFFEES where PRICE < 10.0
Developers benefit from the type-safety and composability of FRM as well as being able to reuse the typical Scala collection APIs like filter, map, foreach, etc. This template will get you started learning Slick using a working application. Continue the tutorial to learn about how to run the application, run the tests, and explore the basics of Slick.

Run the App and the Tests

This template includes a simple Scala application, HelloSlick.scala, that does basic FRM operations with Slick. This application automatically runs when Activator is started and then re-runs after every successful compile. You can see the output in Run. Note: The example code in this app has intentionally verbose type information. In normal applications type inference is used more extensively but to assist with learning the type information was included.

The TablesSuite.scala file contains ScalaTest tests which do some basic integration tests. Check out the test results in Test.

Project Setup

Slick is a library that is easy to include in any project. This project uses the sbt build tool so the dependency for Slick is specified in the build.sbt file. To make things simple this project uses the H2 database in-memory. Learn more about connecting to other databases in the Slick docs.

Schema / Table Mapping

The Tables.scala file contains the mappings for a Suppliers and a Coffees table. These Table create a mapping between a database table and a class. The table's columns are also mapped to functions. This mapping is called Lifted Embedding since the types of a column mappings are not the actual column value's type, but a wrapper type. For a column that contains a Double value the type of mapping will be Column[Double]. This enables type-safe queries to be built around meta-data and then executed against the database. Using a table mapping object requires creating a TableQuery instance for the Table classes. In HelloSlick.scala the suppliers is the TableQuery instance for the Suppliers class.
Learn more about mapping tables and columns in the Slick docs.

Database Connection

Every query that runs against the database needs a database session to run with. The HelloSlick.scala file sets up a database connection and gets a session:

val db = Database.forURL("jdbc:h2:mem:hello", driver = "org.h2.Driver")
db.withSession { implicit session => ... }
Note: the session can be implicit to avoid specifying it explicitly with every query.
The session can now be used in the scope of the provided function to make queries to the database. The session is automatically closed after the function completes. It is also possible to create a session and manually close it. The TablesSuite.scala tests do this in the before and after functions. Learn more about session and connnection handling in the Slick docs.

Creating the Schema

Once a session is available you can use it to perform operations on the database. To create corresponding tables from a mapping you can get the DDL via its TableQuery and then call the create method, like:

suppliers.ddl.create
Multiple DDLs can also be combined together and created, like in HelloSlick.scala:
(suppliers.ddl ++ coffees.ddl).create
This will create all database entities and links (like foreign key references) in the correct order, even in the presence of cyclic dependencies between tables.

Basic CRUD

Creates / Inserts are as simple as appending the values to a TableQuery instance using either the += operator for a single row, or ++= for multiple rows. In HelloSlick.scala both of these ways of doing inserts are used.

Basic reads / queries can be done through the TableQuery instance using Invoker functions. A simple example of invoking a query is to just call list on the Query, like:

suppliers.list
That would produce a List[(Int, String, String, String, String, String)] that corresponds to the columns defined in the Table mapping. Other methods like foreach, first, foldLeft also perform queries. Filtering, sorting, and joining will be covered in the next few sections of the tutorial. In HelloSlick.scala you can see how a foreach is used to do a select * query and then print each row.

Filtering / adding where statements to a query is done using functions like filter and take on a TableQuery to construct a new query. For example, to create a new query on the Coffees table that selects only rows where the price is higher than 9.0, just do:

coffees.filter(_.price > 9.0)
This produces a SQL statement equivalent to:
select * from COFFEES where PRICE > 9.0

Updates are done through the TableQuery instance by calling the update function. To update the sales column on all rows of the Suppliers table, create a new query for just that column:

val updateQuery: Query[Column[Int], Int] = coffees.map(_.sales)
Then call the update with the new value:
updateQuery.update(1)

Deletes are done by just running delete on a query. So to delete coffees with a price less than 8.0, just do:

coffees.filter(_.price < 8.0).delete
This will produce SQL equivalent to:
delete from COFFEES where PRICE < 8.0

Selecting Specific Columns

The default query we've been using uses the * function on the Table mapping class. For instance, the suppliers TableQuery uses the * function defined in Tables.scala and returns all of the columns when executed because the * combines all of the columns. Often we just want to select a subset of the columns. To do this use the map function on a query, like:

suppliers.map(_.name)
This will create a new query that when executed just returns the name column. The generated SQL will be something like:
select SUP_NAME from SUPPLIERS

Sorting / Order By

Sorting / adding order by statements is done using functions like sortBy on a TableQuery to create a new query. For example in the HelloSlick.scala you can see an example sorting of coffees by price:

coffees.sortBy(_.price)
This would produce SQL equivalent to:
select * from COFFEES order by PRICE

Query Composition

The examples so far have taken a base query and used a function to produce a new, more specific query. Due to the functional nature of the query API, this can be done repeatedly to produce more specific queries. For example, to create a query on the Coffees table that sorts them by name, takes the first three rows, filters those with a prices greater than 9.0, and finally just returns the names, simply do:

coffees.sortBy(_.name).take(3).filter(_.price > 9.0).map(_.name)
That results in a new query that has a fairly complex implementation in SQL.

Joins

The Coffees table mapping in the Tables.scala file includes a foreign key mapping to the Suppliers table:

foreignKey("SUP_FK", supID, TableQuery[Suppliers])(_.id)
To use this foreign key in a joined query it is easiest to use a for comprehension, like:
for {
  c <- coffees if c.price > 9.0
  s <- c.supplier
} yield (c.name, s.name)
This creates a new query that gets the coffees with a price greater than 9.0 and then joins them with their suppliers and returns their names.

Computed Values

Computed values like minimum, maximum, summation, and average can be done in the database using the query functions min, max, sum and avg like:

coffees.map(_.price).max
This creates a new Column where you can run the query by calling run to get back the value. Check out the example in HelloSlick.scala.

Plain SQL / String Interpolation

Sometimes writing manual SQL is the easiest and best way to go but we don't want to lose SQL injection protection that Slick includes. SQL String Interpolation provides a nice API for doing this. Start by importing the interpolation API:

import scala.slick.jdbc.StaticQuery.interpolation
Then use the sql String Interpolator:
val state = "CA"
val plainQuery = sql"select SUP_NAME from SUPPLIERS where STATE = $state".as[String]
This produces a query that can be run using the normal functions like list.

You can learn more about Slick's Plain SQL queries in the Slick Plain SQL Queries template for Activator.

Case Class Mapping

The CaseClassMapping.scala file provides an example which uses a case class instead of tupled values. Run this example by selecting CaseClassMapping in Run. To use case classes instead of tuples setup a def * projection which transforms the tuple values to and from the case class. For example:

def * = (id.?, name) <> (User.tupled, User.unapply)
This uses the User's tupled function to convert a (Option[Int], String) to a User and the unapply function to do the opposite. Now all of the queries can work with a User instead of the tuples.

Auto-Generated Primary Keys

The Users table mapping in CaseClassMapping.scala defines an id column which uses an auto-incrementing primary key:

def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)

Running Queries

So far you have seen the Invoker methods .list and .foreach being used to run a collection-valued query. There are several other useful methods which are shown in InvokerMethods.scala. They are equally applicable to Lifted Embedding and Plain SQL queries.

Note the use of Compiled in this app. It is used to define a pre-compiled query that can be executed with different parameters without having to recompile the SQL statement each time. This is the prefered way of defining queries in real-world applications. It prevents the (possibly expensive) compilation each time and leads to the same SQL statement (or a small, fixed set of SQL statements) so that the database system can also reuse a previously computed execution plan. As a side-effect, all parameters are automatically turned into bind variables:

val upTo = Compiled { k: Column[Int] =>
  ts.filter(_.k <= k).sortBy(_.k)
}

Next Steps

Check out the full Slick manual and API docs.

You can also find more Slick templates, contributed by both, the Slick team and the community, here in Activator.

comments powered by Disqus