Slick stands for Scala Language-Integrated Connection Kit. It is Functional Relational Mapping (FRM) library for Scala that makes it easy to work with relational databases.
Slick can be considered as a replacement of writing SQL queries as Strings with a nicer API for handling connections, fetching results and using a query language, which is integrated more nicely into Scala. You can write your database queries in Scala instead of SQL, thus profiting from the static checking, compile-time safety and compositionality of Scala. Slick features an extensible query compiler which can generate code for different backends.
SQL queries are checked at run time only when they are actually executed, but with the help of libraries available in Slick, static checking occurs a compile time, thereby preventing the code to break at runtime i.e. when using Scala instead of raw SQL for your queries you benefit from compile-time safety and compositionality.
Slick’s key features are type-safe, composable queries. Slick comes with a Scala-to-SQL compiler, which allows a (purely functional) sub-set of the Scala language to be compiled to SQL queries. With the availability of standard libraries the Scala developers can write many queries against all supported relational databases with little learning required and without knowing SQL or remembering the particular dialect. Such Slick queries are composable, which means that you can write and re-use fragments and functions to avoid repetitive code like join conditions in a much more practical way than concatenating SQL strings. The fact that such queries are type-safe not only catches many mistakes early at compile time, but also eliminates the risk of SQL injection vulnerabilities.
Scala to SQL compilation during compile time :
Slick runs a Scala-to-SQL compiler to implement its type-safe query feature. The compiler runs at Scala run-time and it does take its time which can even go up to second or longer for complex queries. It can be very useful to run the compiler only once per defined query and upfront, e.g. at app startup instead of each execution over and over. Compiled queries allow you to cache the generated SQL for re-use.
Slick allows you to interact with stored data as if you were working with Scala collections. It has good support for MySQL and PostgreSQL.
To use slick library, if you are using MySQL database, you have to import :
import slick.driver.MySQLDriver
and if you are using Postgres database, you have to import :
import slick.driver.PostgresDriver
When combined with Lifted Embedding, Slick gives some protection against SQL Injection. However, it also allows you to drop back to plain SQL if you need to, which introduces the risk of SQL Injection.
What SQL Injection is?
SQL Injection (SQLi) refers to an injection attack wherein an attacker can execute malicious SQL statements that control a web application’s database server. Since an SQL Injection vulnerability could possibly affect any website or web application that makes use of an SQL-based database.
By leveraging an SQL Injection vulnerability, given the right circumstances, an attacker can use it to bypass a web application’s authentication and authorization mechanisms and retrieve the contents of an entire database. SQL Injection can also be used to add, modify and delete records in a database, affecting data integrity.
To such an extent, SQL Injection can provide an attacker with unauthorized access to sensitive data including, customer data, personal information, trade secrets, intellectual property and other sensitive information.
Lifted Embedding : Slick takes your collections and converts or “lifts” them into Rep objects. The original type is preserved and passed into the constructor to Rep, allowing for type safety and clean separation between code and data when communicating with the database. The example below demonstrates how Slick views your Int, String and Double values after lifting occurs.
private[example] class EmployeeTable(tag: Tag) extends Table[Employee](tag, "experienced_employee"){
val id = column[Int]("id",O.PrimaryKey)
val name = column[String]("name")
val experience = column[Double]("experience")
def * = (id,name,experience) <> (Employee.tupled, Employee.unapply)
}
Upon lifting the variables, an Int becomes Rep[Int], String becomes Rep[String] and Double becomes Rep[Double].
SQL vs Slick examples
Let’s take a table PERSON and address as example.
Person(is,name,age,address_Id) table references Address(is,street,city) table. This database schema is mapped to slick using the following code :
type Person = (Int,String,Int,Int)
class People(tag: Tag) extends Table[Person](tag, "PERSON") {
def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)
def name = column[String]("NAME")
def age = column[Int]("AGE")
def addressId = column[Int]("ADDRESS_ID")
def * = (id,name,age,addressId)
def address = foreignKey("ADDRESS",addressId,addresses)(_.id)
}
lazy val people = TableQuery[People]
type Address = (Int,String,String)
class Addresses(tag: Tag) extends Table[Address](tag, "ADDRESS") {
def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)
def street = column[String]("STREET")
def city = column[String]("CITY")
def * = (id,street,city)
}
lazy val addresses = TableQuery[Addresses]
Some important types of SQL queries and their corresponding type-safe Slick query are:
- INSERT
SQL :
sqlu"""insert into PERSON (NAME, AGE, ADDRESS_ID) values ('John', 12345, 1) """
SLICK :
Firstly you have to write a query and instead of creating an Action that gets the result of this query, call += on with value to be inserted, which gives you an Action that performs the insert. ++= allows insertion of a Seq of rows at once.
people.map(p => (p.name, p.age, p.addressId)) += ("xyz",12345,1)
- UPDATE
SQL :
sqlu"""update PERSON set NAME='John', AGE=54321 where NAME='James' """
SLICK :
Updates are based on queries that select and filter what should be updated and instead of running the query and fetching the data .update is used to replace it.
people.filter(_.name === "xyz").map(p => (p.name,p.age)).update(("abc",54321))
- DELETE
SQL :
sqlu"""delete PERSON where NAME='John' """
SLICK :
Delete is based on queries that filter what should be deleted. Instead of getting the query
result of the query, .delete is used to obtain an Action that deletes the selected rows.
people.filter(p => p.name === "John") .delete
- SELECT *
SQL :
sql"select * from PERSON".as[Person]
SLICK :
The Slick equivalent of SELECT * is the result of the plain TableQuery:
people.result
- SELECT
SQL :
sql"""select AGE, concat(concat(concat(NAME,' ('),ID),')') from PERSON """.as[(Int,String)]
SLICK :
Scala’s equivalent for SELECT is map
people.map(p => (p.age, p.name ++ " (" ++ p.id.asColumnOf[String] ++ ")")).result
- WHERE
SQL :
sql"select * from PERSON where AGE >= 18 AND NAME = 'C. Vogt'".as[Person]
SLICK :
Scala’s equivalent for WHERE is filter. Use === instead of == for comparison.
people.filter(p => p.age >= 18 && p.name === "abc").result
- ORDER BY
SQL :
sql"select * from PERSON order by AGE asc, NAME".as[Person]
SLICK :
Scala’s equivalent for ORDER BY is sortBy. Slick’s .asc and .desc methods allow to affect the ordering.
people.sortBy(p => (p.age.asc, p.name)).result
- GROUP BY
SQL :
sql"""select ADDRESS_ID, AVG(AGE) from PERSON group by ADDRESS_ID""".as[(Int,Option[Int])]
SLICK :
Scala’s groupBy returns a Map of grouping keys to Lists of the rows for each group. There
is no automatic conversion of individual columns into collections. This has to be done
explicitly in Scala, by mapping from the group to the desired column, which then allows
SQL-like aggregation.
people.groupBy(p => p.addressId).map{ case (addressId, group) => (addressId, group.map(_.age).avg) }.result
- HAVING
SQL :
sql"""select ADDRESS_ID from PERSON group by ADDRESS_ID having avg(AGE) > 50""".as[Int]
SLICK :
Slick does not have different methods for WHERE and HAVING. For achieving semantics
equivalent to HAVING, you have to use filter after groupBy and the following map.
people.groupBy(p => p.addressId).map{ case (addressId, group) => (addressId,group.map(_.age).avg) }.filter{ case (addressId, avgAge) => avgAge > 50 }.map(_._1).result
You can refer to my github reposity : Slick Project
References :
