Easy queries using Slick in Akka HTTP

Table of contents

Reading Time: 3 minutes

Slick is a modern database query and access library for Scala that made queries easy. It provides a framework to easily connect to databases (both relational and NoSQL) and other data sources in Akka HTTP. Slick uses Functional Relational Mapping (FRM) which is more suitable for functional programming than Object-relational mapping (ORM).

Slick has made queries quite handy to manipulate data. It comes with various functions which can be used in Scala, instead of complex queries of SQL to stream data from a database server. The slick functions get converted to queries and thus, it provides an impression if you are working with Scala collections.

In this blog, we will rather focus on the major use cases than dive deep into the basics. You can check out the documentation for more details. Here, I have integrated Slick with PostgreSQL.

Sample Slick with Akka HTTP —

https://techhub.knoldus.com/dashboard/projects/akka/60db2e45397f83a01374c34f

Let’s start by assuming we have a student table with following details:

column	datatype
student_id	VARCHAR
email	VARCHAR
name	VARCHAR
date_of_birth	TIMESTAMP
marks	JSON
address	JSON

To use slick queries in your project, first step is to add dependencies. You can check this.

The next step is to do functional relational mapping corresponding to the student table.

final case class StudentDetails(studentId: Option[String],
                                email: String,
                                name: String,
                                dateOfBirth: Option[OffsetDateTime] = None,
                                marks: Option[Json] = None,
                                address: Option[Json] = None)

final class StudentTable(tag: Tag)
    extends Table[StudentDetails](tag, Some("template_slick"), "student") {

  def studentId: Rep[Option[String]] =
    column[Option[String]]("student_id", O.PrimaryKey)
  def email: Rep[String] = column[String]("email")
  def name: Rep[String] = column[String]("name")
  def dateOfBirth: Rep[Option[OffsetDateTime]] =
    column[Option[OffsetDateTime]]("date_of_birth")
  def marks: Rep[Option[Json]] = column[Option[Json]]("marks")
  def address: Rep[Option[Json]] = column[Option[Json]]("address")

  def * : ProvenShape[StudentDetails] =
    (studentId, email, name, dateOfBirth, marks, address).shaped <> (StudentDetails.tupled, StudentDetails.unapply)

}

As you have done database mapping with the case class. You can proceed to perform queries on the student table.

Primary Key:

The slick functions i.e. update, upsert will execute on the basis of the primary key, if you pass an instance of StudentDetails class. In my case, I have defined student_id as the primary key in the mapping. You can also define a composite primary key if required.

Let’s say I want to make a composite primary key using student_id and email. So, I need to remove O.PrimaryKey, which is defined in the studentId in StudentTable class and add the code below.

  implicit def primary: (Rep[Option[String]], Rep[String]) =
    (studentId, email)
  def pk: PrimaryKey = primaryKey("pk_a", (studentId, email))

Queries:

You might be aware of traditional SQL queries. As you have reached here browsing. I believe you are aware of CRUD operations. So, I would not explain the CRUD operations. But, will rather give you a glimpse of SQL queries in comparison with Slick Queries.

INSERT:

SQL Query:

INSERT INTO template_slick.student
(student_id, email, "name", date_of_birth, marks, address)
VALUES('123cc518-8f19-413c-bfde-0c5415ba00e8', 'email@gmail.com', 'name', '1996-02-18 20:40:40.000', '[]'::json::json, '{}'::json::json);

Slick Query:

Using Slick you can simple instantiate an object student of the StudentDetails class and use the below function.

db.run(TableQuery[StudentTable]+= student)

SELECT:

SQL Query:

SELECT * FROM template_slick.student WHERE 
student_id='10624631-9ec0-47fa-a4d0-9fbc130c0666';

Slick Query:

To get a specific row on the basis of studentId:

db.run(TableQuery[StudentTable].filter(_.studentId===id).result)

To get a all rows at once :

db.run(TableQuery[StudentTable].result)

UPDATE:

SQL Query:

SET email='email@gmail.com', "name"='name', date_of_birth='1991-02-18 20:40:40.000', marks=''::json::json, address='{}'::json::json
WHERE student_id='f7d8a662-cd23-4e86-8614-044a46f95d42';

Slick Queries

To update all details in the student table on the basis of primary key i.e. student_id which has been defined in the mapping.

db.run(TableQuery[StudentTable].update(student))

To update a specific column let’s say name you can modify query like this.

db.run(TableQuery[StudentTable].filter(_.studentId===id).map(_.name).update(newName))

Upsert:

Slick provides the privilage to perform insertion or updation using a single function. You can upsert a single row or sequence of rows in just one go.

db.run(TableQuery[StudentTable].insertOrUpdate(student))

db.run(TableQuery[StudentTable].insertOrUpdateAll(Seq(student)))

DELETE:

SQL Query:

DELETE FROM template_slick.student
WHERE student_id='10624631-9ec0-47fa-a4d0-9fbc130c0666';

Slick Query:

db.run(TableQuery[StudentTable].filter(_.studentId===id).delete)

Despite operating the VARCHAR columns using Slick. It also provides tremendous support for other datatypes:

ARRAY
Date/Time
Enum
Range
Hstore
LTree
JSON
Inet/MacAddr
text Search
postgis Geometry

To use above datatypes you need to add a slick-pg extension in your project.

The section is not focused on all the operations of this extension. But will discuss some that I have used in the project.

Let’s consider a JSON for the address column

{
“street1” : “first street”,
“street2” : “”,
“landmark” : “Green Avenue”,
“city” : “Noida”,
“state” : Delhi”,
“country” : “India”,
“pinCode” : “121002“
}

Let’s assume I want to get a value from a JSON using key if I choose to get value of landmark from JSON. I can write query like:

db.run(TableQuery[StudentTable].filter(_.studentId===id).map(_.address.+>>("landmark")).result)

Let’s consider an array of JSON for the marks column:

[
{
“subjectId” : “CS103”,
“subjectName” : “Compiler”,
“marks” : 100
},
{
“subjectId” : “ME103”,
“subjectName” : “Autocad”,
“marks” : 95
}
]

Let’s assume I want to get value of a key i.e. subjectName from JSON at index zero. I can write query like:

db.run(TableQuery[StudentTable].filter(_.studentId===id).map(_.marks.~>(0).+>>("subjectName")).result)

So, this was an overview of how to integrate Slick in your project and make link case class with database table to use queries. You can check out slick documentation or slick extension for more information.

Slick with Akka HTTP sample:-

https://techhub.knoldus.com/dashboard/projects/akka/60db2e45397f83a01374c34f