Introduction to Skunk – Scala Driver for PostgreSQL

1. Introduction

In this tutorial, we’ll learn about Skunk, a purely-functional, non-blocking Postgres client. We’ll use cats-effect and see how to query and modify the database in a purely-functional way.

2. Connecting to Database

Skunk uses a Session object to represent a connection to the database. We’ll use a basic Session creation without encryption or pooling.

The simplest way to create a Session is to use the Session.single() function and provide all the required parameters:

def createSession(): Resource[IO, Session[IO]] =
  Session.single[IO]( host = "localhost",
    user = "baeldung",
    database = "baeldung",
    password = Some("baeldung"),
    port = 5432
  )

The single() method returns Resource, so we don’t need to worry about closing the connection after using it.

Let’s assume that the baeldung database already exists and it contains the following table:

                        Table "public.users"
  Column  |          Type          | Collation | Nullable | Default 
----------+------------------------+-----------+----------+---------
 id       | integer                |           | not null | 
 username | character varying(255) |           |          | 
 email    | character varying(255) |           |          | 
 age      | integer                |           |          | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

3. Querying

After creating a connection to the database, we can start querying the existing tables. Generally, similarly to the classic JDBC driver, Skunk provides two types of queries: simple queries and prepared queries.

3.1. Simple Queries

Simple queries are queries that don’t contain any parameters and, generally, aren’t going to be reused.

Before we start querying the database, let’s create a simple User case class to represent our database rows:

case class User(id: Int, username: String, email: String, age:Int)

Then, we can create our query:

val query: Query[Void, Int *: String *: String *: Int *: EmptyTuple] =
  sql"SELECT * FROM Users".query(int4 *: varchar(255) *: varchar(255) *: int4)

In the example above, we used sql String interpolator to create a Fragment from our SQL query. Since Skunk isn’t able to automatically detect the types returned by the table, we need to provide Encoder instances for all the expected types as arguments to the query() method. This tells Skunk that the Fragment represents a Query, and it returns the provided types.

It’s worth noticing that the first type parameter of our Query is Void. That’s because our query doesn’t take any parameters.

Since the type Int *: String *: String *: Int may be quite hard to work with, we can use the to() function to map the results to our User class:

val mappedQuery: Query[Void, User] = query.to[User]

After that, we can finally use our Resource[IO, Session[IO]] to query the database:

val results: IO[List[User]] = resource.use(s => s.execute(mappedQuery))

3.2. Prepared Queries

On the other hand, if we want to query the database using parameters or reuse our query or stream the results of the query, we should use prepared queries. They work and look similar to JDBC PreparedStatement.

Using prepared queries has three steps. The first one is defining the query and its parameters:

val userDecoder: Decoder[User] = (int4 *: varchar(255) *: int4).to[User]
val query:Query[Int ~ String, User]  =
  sql"""
    SELECT * FROM Users WHERE
      id = $int4 AND username LIKE $varchar
  """
  .query(userDecoder)

Next, we use prepare() to create a PreparedQuery:

val preparedQuery: Resource[IO, PreparedQuery[IO, Int *: String *: EmptyTuple, User]] =  resource
  .flatMap(session => session.prepareR(query))

The last step is to execute the PreparedQuery with the provided parameters:

preparedQuery.use(pq => pq.unique(1, "baeldungUser"))

We should note that, in our case, we’re expecting a single result. Therefore, we used the unique() method. Alternatively, we could use the stream() method if we wanted to get multiple results as a Stream or the cursor() method that enables us to paginate the results.

4. Commands

So far, we’ve used Skunk to execute queries from an existing database. In this part, let’s use commands to perform changes on the database. Again, there are two ways of using commands – simple commands and prepared commands.

4.1. Simple Commands

Simple commands are very similar to simple queries. They are meant to be self-contained without any parameters. Generally, they aren’t meant to be reused.

Working with simple commands is similar to working with simple queries:

def removeUserWithId(resource: Resource[IO, Session[IO]]) = {
  val command: Command[Void]  =
    sql"""
      DELETE FROM Users WHERE
        id = 5
    """
    .command

  resource.use(session => session.execute(command))
}

4.2. Prepared Commands

Similarly to queries, we can also use prepared commands if we want to reuse them or run them with parameters.

Defining and using prepared commands is very similar to prepared queries:

def removePrepared(resource: Resource[IO, Session[IO]]) = {
  val command: Command[Int *: String *: EmptyTuple]  =
    sql"""
      DELETE FROM Users WHERE
        id = $int4 and username = $varchar
    """
    .command
  resource.flatMap(session => session.prepareR(command))
    .use(pc => pc.execute((1, "baeldungUser")))
}

The main difference is that PreparedCommand only offers a single method, execute(), to pass the parameters and run the command.

5. Conclusion

In this article, we have learned the basics of Skunk, a purely functional Scala driver for PostgreSQL. We saw a way to connect to an existing database, the differences between Query and Command, and how to perform simple and prepared operations on the database.

As always, all code samples can be found on over on GitHub.