Setting up¶

Throughout this talk, we will play with the world database used in the documentation of doobie, a most convenient JDBC wrapper for Scala. You can find instructions here for the set-up of a postgres database server and the installation of the world database. The script common.sc contains several utilities as well as a ready-to-use doobie transactor for issuing SQL queries to the world database.

In [1]:

import $file.common
import common._
import doobie._, doobie.implicits._

Out[1]:

import $file.$     

import common._

import doobie._, doobie.implicits._

Test the server!

In [2]:

sql"select 1".query[Int].unique.transact(xa).unsafeRunSync

Out[2]:

res1: Int = 1

Variation 1. Plain and direct SQL¶

Let's say that we need to query the database to know the names of the capitals with a population larger than eight million people, together with their country names. The most direct way to bring this data into memory is to write an SQL query:

In [5]:

val sqlLargeCapitals: Query0[(String, String)] = 
    sql"""
        | select C.name, X.name 
        | from city as C, country as X 
        | where C.id = X.capital and C.population > 8000000""".stripMargin
        .query[(String, String)]

Out[5]:

sqlLargeCapitals: Query0[(String, String)] = doobie.util.query$Query$$anon$3@65cd9960

A value of type Query0[T] represents a SQL query which will return rows of type T. In order to execute this query, we have to compile it into a JDBC program. For instance, using to[List] we will obtain a ConnectionIO[List[T]] program, a pure value. In turn, this program has to be compiled into an input/output monad, e.g. cats.effect.IO, which is also a pure program, although at a lower-level of abstraction. Finally, the interpreter unsafeRunSync of the IO program launches the rockets and the database is accessed!

In [6]:

sqlLargeCapitals
    .to[List]            // ConnectionIO[List[(String, String)]]
    .transact(xa)        // IO[List[(String, String)]]
    .unsafeRunSync       // List[(String, String)]
    .timed(1)            // measure average execution time in milliseconds
    .millis

50 millis

Out[6]:

res5: List[(String, String)] = List(
  ("Jakarta", "Indonesia"),
  ("Seoul", "South Korea"),
  ("Ciudad de M\u00e9xico", "Mexico"),
  ("Moscow", "Russian Federation")
)

In [ ]: