How to Write SQL Queries in Scala

1. Write SQL Queries in Scala
2. Establish Reference to the Table
3. Select From Database in Scala
4. Update a Row in the Table in Scala
5. Delete a Row in the Table in Scala
6. Conclusion

In this article, we will learn how to write SQL queries in Scala with the help of Slick.

Write SQL Queries in Scala

Slick (Scala Language-Integrated Connection Kit) is a Scala library that provides functional relational mapping, making it easy to query and access relational databases. It is typesafe in nature.

Pre-requisites:

1. Add the Slick dependencies in the Scala sbt file.

   libraryDependencies += "com.typesafe.slick" %% "slick" % "3.3.1"
   

2. Connect to databases that you would like to use, like the PostgreSQL database.

   postgres {
       dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"
       properties = {
           serverName = "localhost"
           portNumber = "5432"
           databaseName = "slickDemo"
           user = "ScalaDemo"
           password = "1234"
       }
   }
   

Let’s assume that we have a students table in the database with the following columns: id, name, country, dob.

case class students(id:Long, name:String, country:String, dob:Option[LocalDate])

Now let’s write queries on it.

Establish Reference to the Table

To query from the table students, first, we need to create a reference to it.

val studentTable = TableQuery[students]

Now we can use studentTable to write our Slick queries.

Select From Database in Scala

Let’s write a query equivalent to:

select * from students where country="Japan".

The above query gives us information about students from Japan.

Now let’s write this same query in Scala using Slick.

val students_from_Japan = studentTable.filter(_.country === "Japan")

Update a Row in the Table in Scala

Let’s say we want to modify the country to "Germany" for all students with id equal to 200.

val updateQuery = studentTable.filter(_.id ===200).map(_.country).update("Germany")

In the above query, we first filtered the rows with id equal to 200 and then updated their countries to Germany.

Delete a Row in the Table in Scala

Let’s delete a student record whose name is "tony".

val deleteQuery = studentTable.filter(_.name==="tony").delete

In the above query, we first filtered the rows with names as "tony" and then deleted them.

Conclusion

In this article, we saw how to use Slick to write some basic SQL queries in Scala.