Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQL Made Easy and Secure With Slick

DZone's Guide to

SQL Made Easy and Secure With Slick

Slick is a way to write SQL queries as strings with a nicer API for handling connections and fetching results that integrates nicely into Scala. Here's how to use it.

· Database Zone
Free Resource

Traditional relational databases weren’t designed for today’s customers. Learn about the world’s first NoSQL Engagement Database purpose-built for the new era of customer experience.

Slick stands for Scala Language-Integrated Connection Kit. It is a Functional Relational Mapping (FRM) library for Scala that makes it easy to work with relational databases.

Slick is a way to write SQL queries as strings with a nicer API for handling connections, fetching results, and using a query language, which is integrated nicely into Scala. You can write your database queries in Scala instead of SQL so that you benefit from static checking, compile-time safety, and compositionality of Scala. Slick features an extensible query compiler that can generate code for different backends.

SQL queries are checked at runtime only when they are actually executed, but with the help of libraries available in Slick, static checking occurs at compile time, preventing the code from breaking at runtime. Thus, 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 and composable queries. Slick comes with a Scala-to-SQL compiler, which allows a (purely functional) subset of the Scala language to be compiled to SQL queries. With the availability of standard libraries, 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 means that they not only catch 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 typesafe query feature. The compiler runs at Scala runtime and takes 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, for example, 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 the 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 SQLInjection. However, it also allows you to drop back to plain SQL if you need to, which introduces the risk of SQL Injection.

What Is SQL Injection?

SQL Injection (SQLi) refers to an injection attack in which an attacker can execute malicious SQL statements that control a web application’s database server. A SQL Injection vulnerability could possibly affect any website or web application that makes use of an SQL-based database.

By leveraging a SQL Injection vulnerability, given the right circumstances, an attacker can 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 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 IntString, 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 with PERSON and address  as example.

The Person(is,name,age,address_Id) table references the 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 queries are:

INSERT

In SQL:

sqlu"""insert into PERSON (NAME, AGE, ADDRESS_ID) values ('John', 12345, 1) """

With Slick, firstly, you have to write a query. Instead of creating an Action that gets the result of this query, call += with value to be inserted. This gives you an Action that performs the insert. ++= allows the insertion of a Seq of rows at once.

people.map(p => (p.name, p.age, p.addressId)) += ("xyz",12345,1)

UPDATE

In SQL:

sqlu"""update PERSON set NAME='John', AGE=54321 where NAME='James' """

With Slick, updates are based on queries that select and filter what should be updated. 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

In SQL:

sqlu"""delete PERSON where NAME='John' """

With Slick, this is based on queries that filter what should be deleted. Instead of getting the result of the query, .delete is used to obtain an Action that deletes the selected rows.

people.filter(p => p.name === "John") .delete

SELECT *

In SQL:

sql"select * from PERSON".as[Person]

The Slick equivalent of SELECT * is the result of the plain TableQuery:

people.result

SELECT

In SQL:

sql"""select AGE, concat(concat(concat(NAME,' ('),ID),')') from PERSON """.as[(Int,String)]

With Slick, Scala’s equivalent for SELECT is map:

people.map(p => (p.age, p.name ++ " (" ++ p.id.asColumnOf[String] ++ ")")).result

WHERE

In SQL:

sql"select * from PERSON where AGE >= 18 AND NAME = 'C. Vogt'".as[Person]

With 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

In SQL:

sql"select * from PERSON order by AGE asc, NAME".as[Person]

With Slick, Scala’s equivalent for ORDER BY is sortBy. Slick’s .asc and .desc methods affect the ordering.

people.sortBy(p => (p.age.asc, p.name)).result

GROUP BY

In SQL:

sql"""select ADDRESS_ID, AVG(AGE) from PERSON group by ADDRESS_ID""".as[(Int,Option[Int])]

With 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

In SQL:

sql"""select ADDRESS_ID from PERSON group by ADDRESS_ID having avg(AGE) > 50""".as[Int]

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 for more.

References

Learn how the world’s first NoSQL Engagement Database delivers unparalleled performance at any scale for customer experience innovation that never ends.

Topics:
database ,tutorial ,sql ,slick

Published at DZone with permission of Shivangi Gupta. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}