Scalac
Published in

Scalac

ZIO SQL: Type-safe SQL for ZIO applications

This article was inspired by a great blog post by Adam Warski, who wrote a comparison of Slick, Doobie, Quill and ScalikeJDBC a few years ago. As ZIO SQL just had its first non-production release, I wanted to add ZIO SQL to the mix and see how it compares to others. Therefore, in the section, I have used the same examples as Adam did, so it's easier for readers to compare the libraries. However, I won't spend too much time covering the other libraries, we'll just make a quick review of how queries are constructed in each of them. You can find code snippets for this blog in Softwaremill's original repo.

Introduction

When it comes to accessing relational data, Scala developers nowadays have lots of interesting choices. Slick, Doobie or Quill are all great, production ready, battle tested libraries that have survived the test of time. So do we even need another library? Does ZIO SQL offer something that others don’t? If you’re eager to find out what makes ZIO SQL special and see this library in action, keep reading.

Who is ZIO SQL for?

I find it interesting that every library takes a slightly different approach to solving the problem of accessing relational data. Especially when it comes to writing queries, users usually have to choose between these two options:

  • Having a type-safe domain-specific language (DSL) which doesn’t much resemble SQL and therefore has a steeper learning curve.
  • Having an ability to write SQL as , but at the cost of limited or no type-safety.

Now, let’s take a quick look at some of the most frequent choices and quickly explore their approach to writing queries.

Slick

Slick lets you define a meta-model for your table. Using values representing your table and methods representing columns, you can use Slick’s DSL, which feels quite intuitive for simple examples.

class Persons(tag: Tag) extends Table[(UUID, String, Int)](tag, "persons"){
def id = column[UUID]("id", O.PrimaryKey)
def name = column[String]("name")
def age = column[Int]("age")
def * = (id, name, age)
}
val persons = TableQuery[Persons]
( for( p <- persons; if p.age > 18 ) yield p.name )
//or
persons.filter(_.age > 18).map(_.name)

In general, writing queries in Slick resembles working with Scala collections, rather than working with SQL. However, queries are typesafe. Using when comparing would result in compile type errors.

In more complex examples, such as when you join multiple tables, users have to deal with nested tuples and using the DSL gets a little bit more complicated. The good news is that you can also write a query in Slick as a raw .

sql"SELECT name FROM persons WHERE age < 18".as[String]

Obviously, in this case you’re losing type safety as Slick doesn’t use any macros to parse or anything like that. If you misspell SELECT or map the result to , your program will fail at runtime.

Doobie

Doobie doesn’t require writing any meta-model. It actually doesn’t abstract over SQL at all. With Doobie you write SQL directly and then you map to your model representation.

val age = 18
sql"""SELECT name FROM persons WHERE age > $age"""
.query[String]
.option

While writing is just not typesafe, Doobie offers query type checking capabilities.

val y = transactor.yolo
import y._
val query: Query0[String] =
sql"""SELECT name FROM persons WHERE age > 18"""
.query[String]
query.check.runSyncUnsafe()

You can incorporate this check, for example, into your unit tests. Output of lets you know if you made a syntax typo. It also gives you recommendations about the model representation which you map your query to, like a nullable column mapped to a non-optional type, unused column, type mismatch and more. Anyway, this is not a compile type query checking, as it's happening at runtime, but it's still a nice feature.

Quill

Quill generates a query at compile time. Let’s take a look at an example of how we can write our query of adults.

final case class Person(id: UUID, name: String, age: Int)ctx.run(
quote {
query[Person].filter(_ > 18).map(_.name)
}
)

When you hover over your query description, you can see the actual SQL query that you’ll eventually end up executing on the database!

On the other hand, when it comes to this Quoted DSL, in my opinion, some getting used to is required and there isn’t much resemblance to SQL itself.

So what about ZIO SQL?

According to the website, it’s “Type-safe, composable SQL for ZIO applications”.

Indeed, the goal of ZIO SQL is to bring you the best of both worlds. It lets you write type-safe and composable SQL queries in ordinary Scala.

val persons = (uuid("id") ++ string("name") ++ int("age")).table("persons")
val (id, name, age) = persons.columns
select(name).from(persons).where(age > 18)

This query very much resembles SQL itself and it’s also type-safe. Trying to select columns from some other table results in a compile time error. Also, query typos are impossible, as you are using scala functions.

There are four databases currently supported: PostgreSQL, MySQL, MSSQL Server, Oracle — with PostgreSQL being the most feature-complete out of the four. Support for more databases is coming in the future.

How does ZIO JDBC fit into the picture?

ZIO JDBC is a new library that has just recently started to be developed so it’s a lower-level library than ZIO SQL. Let’s take a look at an example.

val query = sql"select name from persons where age > 18".as[String]val select: ZIO[ZConnectionPool, Throwable, Chunk[String]] = transaction {
selectAll(query)
}

We’re back in the world of writing . In fact, you can use ZIO SQL to describe your query and render it to the . So in the end, ZIO SQL and ZIO JDBC could cooperate. At the moment, one of the modules of ZIO SQL is the JDBC module which could in the future be replaced by ZIO JDBC. So users could use ZIO SQL for its typesafe, sql-looking DSL and ZIO JDBC for connection pool, transactions and other JDBC related stuff. And again, if you're dealing with some unusual, not yet supported, db specific query, with ZIO JDBC you can execute it directly as a String.

Tour of ZIO SQL

Now that we know whether ZIO SQL is interesting for us or not, let’s do a tour of the library and see it in action.

Define meta model.

We’ll be working with 3 tables representing a city, its metro system and a specific metro line.

Before we can start using DSL, we need to create a table description and extract columns, which we’ll then use when constructing queries.

import zio.sql.postgresql.PostgresModuletrait TableModel extends PostgresModule {
import ColumnSet._
val city = (int("id") ++ string("name") ++
int("population") ++ float("area") ++ string("link")).table("city")
val (cityId, cityName, population, area, link) = city.columns
val metroSystem = (int("id") ++ int("city_id") ++ int("name") ++ int("daily_ridership")).table("metro_system")
val (metroSystemId, cityIdFk, metroSystemName, dailyRidership) = metroSystem.columns
val metroLine = (int("id") ++ int("system_id") ++ string("name") ++ int("station_count") ++ int("track_type")).table("metro_line")
val (metroLineId, systemId, metroLineName, stationCount, trackType) = metroLine.columns
}

Your extends the which comes from import. If you are using another database, you just need to extend an appropriate module - like e.g. .

Architecturally ZIO SQL uses modules represented by traits. Core project defines an which consists of , , and more. These contain database agnostic DSL. Built on top of that is a which adds the abilities to execute queries, render them to string, launch transactions and so on. Database specific functionality is then built into database specific modules which also take care of translating ADT into a query.

So in our case, we defined table description in the trait extending . Each column is represented by a type and a name of the column. You compose columns with the ++ operator, which results in a so-called . In the end, you define the table by calling on the. As everything is a value, you can assign columns to a and reuse them for multiple tables. You get columns by calling the method on the table, which gives you the flat tuple of all the columns.

The resulting columns have type whereis a type of column, is an abstract type representing the table and is a phantom type which helps the compiler validate the DSL soundness. From now on, we will be using those 's inside our queries.

If you want to describe a column which could contain s, you can use the aspect. Extracted will then be of the type

import ColumnSetAspect._
string("link") @@ nullable

Even though it forces you to handle when executing a query that contains this column, keep in mind that nullability is simply a part of SQL - for example even columns marked with constraint can be when queried with outer join, and then you just have to deal with s.

DB Connection

Let’s see how to connect to a database.

method returns either a or a - depending on whether we are executing a streaming query or not - and sets the environment type to . That means that in order to execute a query, we need to provide to the program.

val query: ZStream[SqlDriver, Exception, String] =   
execute(select(cityName).from(city))

In order to construct we will need at least where we set the connection URL, user, password and any other configuration if needed.

val poolConfigLayer = ZLayer.succeed(
ConnectionPoolConfig("jdbc:postgresql://localhost:5432/zio-sql-compare",
connProperties("postgres", "reallyStringPSW")))

private def connProperties(user: String, password: String): Properties = {
val props = new Properties
props.setProperty("user", user)
props.setProperty("password", password)
props
}

val driverLayer = ZLayer.make[SqlDriver](
poolConfigLayer,
ConnectionPool.live,
SqlDriver.live
)

As you can see, defining is pretty straightforward. ZIO 2.0 introduces amazing improvements to the way we compose . If you mess something up, the compiler will remind you what you are missing or what is redundant.

It should be possible to use as a custom instead of the default from ZIO SQL. I have not tried it, though. Any kind of module that would provide as another implementation of would be a great contribution to this library.

Simple query

Now that we have all the building blocks ready, let’s start writing some queries. First off, let’s write a query that selects only the big cities.

val bigCities = select(cityId ++ cityName ++ population ++ area ++ link)
.from(city)
.where(population > 4000000)

So far so good. If you’re familiar with SQL in general, the query itself should feel very natural.

For the query, we’re using the s that we defined earlier. Also, the query is completely type-safe, so if you try to select a column from some other table than , you end up with a compile time error.

The resulting value of this query is a which is just a description of a query, so you're not executing anything on the database. This concept should be familiar from ZIO or functional programming in general. We're dealing with pure data here which can be accepted in a function, mapped over or refactored.

Lets execute our query now:

case class CityId(id: Int) extends AnyVal
case class City(id: CityId, name: String, population: Int, area: Float, link: Option[String])

val result: ZStream[Has[SqlDriver], Exception, (Int, String, Int, Float, String)] = execute(bigCities)

val bigCities2 = bigCities.to {
case (id, name, population, area, link) => City(CityId(id), name , population, area, Option(link))
}

val result2: ZStream[Has[SqlDriver], Exception, City] = execute(bigCities2)

gives us a stream of rows, each one represented as a tuple. You can map over , run it to of a , get only the first element with or do something else, like write elements to the console. Basically, after the query execution, you're in the world of ZIO, so you get all the benefits of working with ZIO.

Alternatively, we can call which maps type to our custom domain model. In our case, our model contains an as well as a newtype wrapper for ID, so we can do the mapping of column types to our model before query execution. Then, executing the query returns a stream of .

However, it’s fair to mention that we’re not dealing with real streaming yet, because the whole result set is put into . The library authors will definitely be improving that in future releases.

Complex queries

An example of a more complex query consists of selecting metro systems with city names and a total count of the lines in each system, sorted by that count.

/**
SELECT ms.name, c.name, COUNT(ml.id) as line_count
FROM metro_line as ml
JOIN metro_system as ms on ml.system_id = ms.id
JOIN city AS c ON ms.city_id = c.id
GROUP BY ms.name, c.name
ORDER BY line_count DESC
*/

val lineCount = (Count(metroLineId) as "line_count")

val complexQuery = select(metroLineName ++ cityName ++ lineCount)
.from(metroLine
.join(metroSystem).on(metroSystemId === systemId)
.join(city).on(cityIdFk === cityId))
.groupBy(metroLineName, cityName)
.orderBy(Desc(lineCount))


val streamRow: ZStream[Has[SqlDriver], Exception, (String, String, Long)] = execute(complexQuery)

We extracted to a value and reused it inside and inside clause. If we inline it, the query won't change. I find it nice that the compilation fails when we don't group by and . After all, is an aggregation function, so grouping by other selected columns is required. After execution, we have a stream of flat tuples representing the name of the metro line, city and number of lines.

Dynamic queries

Our goal is to construct a query based on some runtime values, so let’s see how ZIO SQL handles it.

val base = select(metroLineId ++ systemId ++ metroLineName ++ stationCount ++ trackType).from(metroLine)

val minStations: Option[Int] = Some(10)
val maxStations: Option[Int] = None
val sortDesc: Boolean = true

val minStationsQuery = minStations.map(m => stationCount >= m).getOrElse(Expr.literal(true))
val maxStationsQuery = maxStations.map(m => trackType <= m).getOrElse(Expr.literal(true))

val ord =
if (sortDesc)
stationCount.desc
else
stationCount.asc
val whereExpr =
minStationsQuery && maxStationsQuery

In this case, we are dealing with minimum and maximum number of stations that would form a clause depending on whether they are available or not. Also, boolean value decides on final order direction.

Plain SQL

ZIO SQL does not allow you to write queries as s. For that, you could use the ZIO JDBC that I mentioned before.

On the other hand, you don’t actually need a database connection in order to use ZIO SQL. You don’t even need ZIO. :) You can simply use the DSL to describe your query as pure data and then render it to a . Depending on the query, you can use , , or r methods.

val selectCityName: String = renderRead(select(cityName).from(city))

The value of the is . Therefore, if you like, you can take this and send it to the database some other way. Not using ZIO at all. This is a potential integration point for other libraries such as Slick, Doobie or ZIO JDBC. It's also a way to debug your application and log ZIO SQL queries.

Transactions

For writing transactions, ZIO SQL introduces a data type - a surprising name indeed, I know :)

val transaction: ZTransaction[Any, Exception, Int] = for {
_ <- ZTransaction(insertCity(id))
rows <- ZTransaction(deleteCity(id))
} yield (rows)

val deletedRows: ZIO[SqlDriver, Exception, Int] = execute(transaction).useNow

defines the operator, so we can compose multiple queries inside for comprehension. Under the hood, is a wrapper around effect. The transaction is committed when the underlying effect succeeds and rollbacked when the effect fails.

Summary

ZIO SQL provides type-safe and composable DSL. Using it resembles writing SQL, when the compiler does query validation. The JDBC module adds ZIO integration, connection pool and data type to handle query execution, sql rendering and transactions.

While there’s still some room for improvements and some work to do before the library is ready for production, once a stable version is released, ZIO SQL will provide a great choice for ZIO based functional applications that need to work with relational databases in a type safe way.

ZIO SQL is the most Scalac-influenced ZIO library, with 2 out of 3 top contributors and many more involved in the library.

Check out other articles on ZIO

--

--

We specialize in functional programming, distributed computing, blockchain, analytical dashboards,and data engineering. Work with us if you needa team to build your dedicated solution, a consultation to move forward with your project or a team extension to get rid of bottlenecks

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Scalac

Scalac is a web & software development company with 122 people including Backend, Frontend, DevOps, Machine Learning, Data Engineers, QA’s and UX/UI designers