1. Introduction
Slick is a Functional Relational Mapping library for Scala that allows us to query and access a database like other Scala collections. We can write database queries in Scala instead of SQL, thus providing typesafe queries. In this tutorial, let’s see how we can use Slick to connect and query from a database.
2. Benefits
Slick is the most popular library for database access in the Scala ecosystem. Slick provides compile-time safety and composability to our queries. By default, it also encourages an asynchronous and non-blocking style of programming. Also, it follows the Reactive Stream based streaming APIs.
We can also write plain SQL queries and execute them similarly to any other Slick queries. Slick supports most of the popular databases like PostgreSQL, MySQL, Oracle, MS SQL Server, etc.
3. Dependencies
Let’s see how we can add Slick to our project using sbt:
libraryDependencies += "com.typesafe.slick" %% "slick" % "3.5.0"
In this article, we are going to use the H2 database for simplicity. So, we’ll add the H2 driver to our build.sbt file:
libraryDependencies += "com.h2database" % "h2" % "2.1.220"
If we want to use any other database, we need to add the relevant driver to the project. For example, let’s add the driver for a PostgreSQL database:
libraryDependencies += "org.postgresql" % "postgresql" % "42.2.14"
4. Connecting to the Database
Let’s see how we’ll connect to the database.
4.1. Providing the Database Configurations
We can provide the connection properties for one or more databases, in our application.conf file. Let’s say we would like to create configurations for H2 databases:
h2mem {
url = "jdbc:h2:mem:testDB"
driver = org.h2.Driver
keepAliveConnection = true
connectionPool = disabled
}
Alternatively, we could connect to Postgres instead of H2 as:
postgres {
dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"
properties = {
serverName = "localhost"
portNumber = "5432"
databaseName = "slick-tutorial"
user = "postgres"
password = "admin"
}
}
4.2. Establishing a Connection
Once the configurations are set, we can establish a connection to the database. Firstly, we need to import the API for the database we are connecting. For the H2 database:
import slick.jdbc.H2Profile.api._
Now, we’ll create a connection to the database using:
val db = Database.forConfig("h2mem")
We’ll use the variable db to execute queries on the database.
If we want to use any other database, we’ll need to use the profile for that database and use database-specific configurations.
5. Setting up Schema
Before we start writing queries, we need to provide the database schema. Using Slick Tables, we’ll provide a mapping for the database table to the entity. Let’s describe the database structure we are going to use.
We’re going to create a database for Tennis Players. Additionally, we’ll use a case class to model the database table:
case class Player(id:Long, name:String, country:String, dob:Option[LocalDate])
Now, let’s create a schema, which maps database columns to case class fields:
class PlayerTable(tag: Tag) extends Table[Player](tag, None, "Player") {
override def * = (id, name, country, dob).mapTo[Player]
val id : Rep[Long] = column[Long]("PlayerId", O.AutoInc, O.PrimaryKey)
val name: Rep[String] = column[String]("Name")
val country : Rep[String] = column[String]("Country")
val dob : Rep[Option[LocalDate]] = column[Option[LocalDate]]("Dob")
}
The schema PlayerTable extends the Table class from the relevant database driver. The database schema and table names are passed to the Table class.
We also need to define the fields for columns based on their types. The * method is a mapper between the case class fields and the database columns.
6. Executing Queries
Now we are ready to execute some queries. Let’s take a look at how different operations like select, insert, delete, and update are done.
6.1. Querying from Database
Let’s see how we’ll use the PlayerTable to query from the database. For that, we’ll need to create a reference for the PlayerTable:
val playerTable = TableQuery[PlayerTable]
Now, we can use playerTable to create Slick queries.
Let’s say we want to select all players from Germany:
val germanPlayersQuery = playerTable.filter(_.country === "Germany")
This is equivalent to SQL query:
SELECT "PlayerId", "Name", "Country", "Dob" FROM "Player" WHERE "Country" = 'Germany'
The variable germanPlayersQuery is a Slick Query. To execute this query, we need to use the run() method from the db instance we have created before. Since Slick executes queries asynchronously, the result will be a Future value:
val germanPlayers: Future[Seq[Player]] = db.run[Seq[Player]](germanPlayersQuery.result)
The db.run() method takes a parameter of type DBIOAction, but germanPlayersQuery is of type Query. The method result on Query converts it into the type DBIOAction.
Similar to filter, Slick* also provides other methods like *drop, take*, and *groupBy, which can be used for additional filtration and grouping.
6.2. Inserting into Database
Let’s see how we can add new players into the Player table:
val insertPlayerQuery = playerTable += player
val insertResult:Future[Int] = db.run(insertPlayerQuery)
The += method inserts a single row, ignoring any auto-increment columns. If we don’t want to ignore the auto-increment column, we can use forceInsert instead:
val forceInsertAction = playerTable.forceInsert(player)
Additionally, we can insert multiple records using ++= method:
val insertMultipleAction = playerTable ++= Seq(player)
6.3. Updating a Row
We can use the update method to modify the selected row(s). Let’s say we’d like to update the country for the player with an id of 500:
val updateCountryAction = playerTable.filter(_.id === 500L).map(_.country).update("Germany")
If the filter condition returns many results, the update operation will modify all the selected rows. Let’s update the country from Deutschland to Germany for all Swiss players:
val updateMultipleAction = playerTable.filter(_.country === "Swiss").map(_.country).update("Switzerland")
6.4. Deleting a Row
The delete method is used to remove the selected record(s). Let’s say we want to delete the player with the name Nadal:
val deleteAction = playerTable.filter(_.name === "Nadal").delete
6.5. Composing Multiple Queries
Slick allows us to compose and execute multiple actions. For instance, if we need to insert a new player and delete an existing player, we can combine both the operations into a single action:
val insertAction = playerTable += player1
val insertAnotherAction = playerTable += player2
val updateAction = playerTable
.filter("_.name" === "Federer")
.map(_.country)
.update("Swiss")
val combinedAction = DBIO.seq(insertAction, updateAction, insertAnotherAction)
The action combinedAction can now be executed using db.run() method.
6.6. Executing Queries in a Transaction
When there are multiple queries involved, it is important to run them in a single transaction. This helps to preserve the atomicity. In Slick, we can run combined action in a single transaction by using the method transactionally. For example, we can execute the combinedAction from the previous section in a transaction:
val transactionStatus:Future[Unit] = db.run(combinedAction.transactionally)
If transactionally is not applied, it will still run both the queries, but the insert and delete will execute in two separate transactions.
7. Using Plain SQL Queries
Slick lets us write plain queries and use them like any other Slick action. We can use String Interpolation using sql, sqlu, and tsql interpolators. For example, to create a table:
val createQuery: DBIO[Int] =
The createQuery can be executed using the db.run() method.
The sql interpolator can be used when we need to return a result-set from a plain query. For example, for running Select query:
val selectCountryAction:DBIO[Seq[String]] =
sql"""select "name" from "Player" where "country" = 'Spain' """.as[String]
8. Conclusion
In this article, we looked at Slick and some basic features. As always, the code samples used in this tutorial are available over the GitHub.