20. Postgres with doobie

Stop chapter 19’s server, start it again and ask for the bookings. The Hugheses and the Mbekis have gone. Their bookings only ever existed in a Ref, which lasts as long as the process that made it.

We’ll keep the bookings in PostgreSQL, through doobie, a library that runs SQL from Cats Effect. It isn’t an ORM of the kind Hibernate is. You write the queries yourself, in SQL, and doobie deals with connections, transactions and turning rows into values. I prefer that, because I can see exactly the SQL that will run.

20.1. A database to talk to

The simplest way to get a Postgres for development is Docker, which chapter 22 needs in any case. If you don’t have it, https://docs.docker.com/get-started/get-docker/ has the installers. This command starts a Postgres in the background:

$ docker run --name fully-booked-db -e POSTGRES_PASSWORD=table-for-two \
    -e POSTGRES_DB=fullybooked -p 5432:5432 -d postgres:18

To turn it off and on, use docker stop fully-booked-db and docker start fully-booked-db. The data survives both. If you already run Postgres on your machine, create a database called fullybooked in it and use your own user and password below.

Add four lines to the service list in build.sbt, after the one for http4s-ember-server:

      "org.tpolecat" %% "doobie-core" % "1.0.0-RC12",
      "org.tpolecat" %% "doobie-hikari" % "1.0.0-RC12",
      "org.tpolecat" %% "doobie-postgres" % "1.0.0-RC12",
      "org.flywaydb" % "flyway-database-postgresql" % "13.12.2",

The service has to know where the database is. Add a block to the end of application.conf:

database {
  url = "jdbc:postgresql://localhost:5432/fullybooked"
  url = ${?DATABASE_URL}
  user = "postgres"
  password = "table-for-two"
}

The block sets url twice. The second setting only applies when the environment variable DATABASE_URL exists, so the file works as it is on your machine and can be pointed somewhere else without being edited. Do the same for the port while you’re there, by adding port = ${?PORT} under the line that sets it to 8080. A password in a file is fine for a database that lives on your laptop. Chapter 22 moves it out of the file. In config.scala, add a class for the block and a field to AppConfig:

case class DatabaseConfig(url: String, user: String, password: String) derives ConfigReader

case class AppConfig(
    restaurant: String,
    journal: String,
    storeDelay: FiniteDuration,
    port: Int,
    database: DatabaseConfig
) derives ConfigReader

20.2. Migrations

We’ll create the table, and change it later as the service grows, with migrations. These are numbered SQL files that a library called Flyway applies in order. Create the directories db/migration under service/src/main/resources and put the first migration in a file called V1__bookings.sql, with two underscores:

CREATE TABLE bookings (
  id            BIGSERIAL PRIMARY KEY,
  name          TEXT NOT NULL,
  party_size    INT  NOT NULL,
  phone         TEXT,
  table_number  INT  NOT NULL,
  starts        TIME NOT NULL,
  ends          TIME NOT NULL,
  status        TEXT NOT NULL,
  seated_at     TIME,
  cancel_reason TEXT
);

A booking’s status takes three columns, because two of the statuses carry data. The ends column, the time the party is due to leave, is there for the second migration.

Now create database.scala in service/src/main/scala:

package fullybooked

import cats.effect.{IO, Resource}
import com.zaxxer.hikari.HikariConfig
import doobie.Transactor
import doobie.hikari.HikariTransactor
import org.flywaydb.core.Flyway

def migrate(config: DatabaseConfig): IO[Int] =
  IO.blocking:
    Flyway
      .configure()
      .dataSource(config.url, config.user, config.password)
      .load()
      .migrate()
      .migrationsExecuted

def transactor(config: DatabaseConfig): Resource[IO, Transactor[IO]] =
  val pool = HikariConfig()
  pool.setJdbcUrl(config.url)
  pool.setUsername(config.user)
  pool.setPassword(config.password)
  pool.setMaximumPoolSize(8)
  HikariTransactor.fromHikariConfig[IO](pool)

The first function, migrate, runs every migration the database hasn’t seen. Flyway keeps a table of its own in the database listing the ones it has applied, which makes it safe to call migrate on every start. It refuses to run if a migration in that table has been edited since. A change to the schema goes in a new file with the next number.

The second function returns a Transactor, the thing doobie runs SQL through, together with a pool of connections. Opening a connection takes long enough that a service keeps a few open and shares them out among the requests that need one. This pool has eight, so at most eight pieces of database work are in progress at once and a ninth waits its turn. The pool itself is HikariCP, a Java library. The Transactor is wrapped in a Resource because the pool has to be closed.

Flyway and Hikari both log a good deal at INFO. Add two lines to logback.xml, above the <root> element, to turn them down:

  <logger name="org.flywaydb" level="WARN"/>
  <logger name="com.zaxxer.hikari" level="WARN"/>

20.3. Rows and bookings

The rest of this chapter’s Scala files go in service/src/main/scala. A row in the table is ten columns of text, numbers and times. A Booking has a Party with an opaque Phone, a Table and a Status that’s an enum. I do the translating in plain functions that can be tested without a database. Create rows.scala:

package fullybooked

import doobie.Meta
import java.time.LocalTime

given Meta[Phone] =
  Meta[String].tiemap(text => Phone.parse(text).toRight(s"not a phone number: $text"))(_.toString)

case class BookingRow(
    name: String,
    size: Int,
    phone: Option[Phone],
    table: Int,
    starts: LocalTime,
    status: String,
    seatedAt: Option[LocalTime],
    cancelReason: Option[String]
)

def toRow(booking: Booking): BookingRow =
  val (seatedAt, cancelReason) = booking.status match
    case Status.Seated(at) => (Some(at), None)
    case Status.Cancelled(reason) => (None, Some(reason))
    case _ => (None, None)
  BookingRow(
    booking.party.name,
    booking.party.size,
    booking.party.phone,
    booking.table.number,
    booking.time,
    booking.status.productPrefix,
    seatedAt,
    cancelReason
  )

def toBooking(row: BookingRow): Either[String, Booking] =
  for
    table <- Table.all.find(_.number == row.table).toRight(s"no table numbered ${row.table}")
    status <- (row.status, row.seatedAt, row.cancelReason) match
      case ("Requested", _, _) => Right(Status.Requested)
      case ("Confirmed", _, _) => Right(Status.Confirmed)
      case ("Completed", _, _) => Right(Status.Completed)
      case ("NoShow", _, _) => Right(Status.NoShow)
      case ("Seated", Some(at), _) => Right(Status.Seated(at))
      case ("Cancelled", _, Some(reason)) => Right(Status.Cancelled(reason))
      case _ => Left(s"can't read the status of ${row.name}: ${row.status}")
  yield Booking(Party(row.name, row.size, row.phone), table, row.starts, status)

There’s a field in BookingRow for each column we read. doobie already knows how to fetch most of the fields' types, and the given Meta[Phone] teaches it Phone. tiemap builds a Meta[Phone] from the one for String and two functions, one for each direction. The function for reading returns an Either, since the column might hold something Phone.parse rejects.

Going from a booking to a row can’t fail, so toRow returns a plain BookingRow. Coming back, toBooking returns an Either with a message on the left, because a row could hold a table number we don’t have or a status we’ve never heard of. The status is stored as its productPrefix, the name of an enum case as a string: "Seated" for Status.Seated(at).

Create database.test.scala in service/src/test/scala and run it with sbt "service/test fullybooked.RowTests":

package fullybooked

import java.time.LocalTime

class RowTests extends munit.FunSuite:
  val phone = Phone.parse("07700 900141")
  val booking = Booking(Party("Okonkwo", 5, phone), Table(5, 6), LocalTime.of(19, 30))

  test("a booking survives the trip to a row and back in every status"):
    val statuses = List(
      Status.Requested,
      Status.Confirmed,
      Status.Completed,
      Status.NoShow,
      Status.Seated(LocalTime.of(19, 35)),
      Status.Cancelled("flu")
    )
    statuses.foreach: status =>
      val original = booking.copy(status = status)
      assertEquals(toBooking(toRow(original)), Right(original))

  test("a row that makes no sense is refused"):
    val row = toRow(booking)
    assert(toBooking(row.copy(table = 12)).isLeft)
    assert(toBooking(row.copy(status = "Seated", seatedAt = None)).isLeft)
$ sbt "service/test fullybooked.RowTests"
[info] Passed: Total 2, Failed 0, Errors 0, Passed 2
[success] elapsed time: 2 s

20.4. Queries and transactions

The routes from chapter 19 are written against Bookings, a class with a Ref inside. They shouldn’t have to care where bookings are kept, so Bookings becomes a trait. In concurrency.scala, replace the class and its companion object with this:

trait Bookings:
  def all: IO[List[Booking]]
  def reserve(party: Party, time: LocalTime): IO[Either[BookingError, Booking]]

class InMemoryBookings(ref: Ref[IO, List[Booking]]) extends Bookings:
  def all: IO[List[Booking]] = ref.get

  def reserve(party: Party, time: LocalTime): IO[Either[BookingError, Booking]] =
    ref.modify: bookings =>
      decide(bookings, party, time) match
        case Right(booking) => (bookings :+ booking, Right(booking))
        case Left(error) => (bookings, Left(error))

object Bookings:
  def starting(bookings: List[Booking]): IO[Bookings] =
    Ref.of[IO, List[Booking]](bookings).map(ref => InMemoryBookings(ref))

Bookings.starting still exists and now makes an InMemoryBookings, so the code that called it compiles as before. The second implementation goes in a new file, postgres.scala:

package fullybooked

import cats.effect.IO
import cats.syntax.all.*
import doobie.*
import doobie.implicits.*
import doobie.postgres.implicits.*
import java.time.LocalTime

val selectBookings: Query0[BookingRow] =
  sql"""SELECT name, party_size, phone, table_number, starts, status, seated_at, cancel_reason
        FROM bookings
        ORDER BY starts, id""".query[BookingRow]

def insert(booking: Booking): Update0 =
  val row = toRow(booking)
  sql"""INSERT INTO bookings
          (name, party_size, phone, table_number, starts, ends, status, seated_at, cancel_reason)
        VALUES
          (${row.name}, ${row.size}, ${row.phone}, ${row.table}, ${row.starts}, ${booking.leaveBy},
           ${row.status}, ${row.seatedAt}, ${row.cancelReason})""".update

val loadBookings: ConnectionIO[List[Booking]] =
  selectBookings
    .to[List]
    .flatMap: rows =>
      rows.traverse(row => toBooking(row).leftMap(IllegalStateException(_)).liftTo[ConnectionIO])

class PostgresBookings(xa: Transactor[IO]) extends Bookings:
  def all: IO[List[Booking]] = loadBookings.transact(xa)

  def reserve(party: Party, time: LocalTime): IO[Either[BookingError, Booking]] =
    val attempt: ConnectionIO[Either[BookingError, Booking]] =
      loadBookings.flatMap: bookings =>
        decide(bookings, party, time) match
          case Left(error) => Left(error).pure[ConnectionIO]
          case Right(booking) => insert(booking).run.map(_ => Right(booking))
    attempt
      .transact(xa)

The queries are written with doobie’s sql interpolator, inside the triple quotes that Scala uses for a string that runs over several lines. A value interpolated with $ is sent to the database separately from the SQL text, as a parameter, so a name a customer typed can’t change the query. The columns that come back are matched to the fields of BookingRow by position. The last doobie import is there so that a LocalTime can be one of them.

Nothing in the first three definitions talks to a database. A Query0 or an Update0 describes a statement. Calling to[List] on a query, or run on an update, gives a ConnectionIO, a description of work to be done on one connection. A ConnectionIO has flatMap, as an IO does, and loadBookings uses it to follow the query with toBooking on each row. A row that can’t be read fails the whole ConnectionIO, because a table we can’t make sense of is a fault in the system and chapter 15 said those belong in a failed effect. leftMap, Cats’s name for left.map, turns the message into an exception. liftTo[ConnectionIO] then turns the Either into a ConnectionIO: a Right becomes one that succeeds and a Left one that fails with the exception.

A ConnectionIO becomes an IO at transact(xa), which borrows a connection from the pool, runs everything in one transaction and commits, or rolls back if any part fails. So in reserve, reading the bookings, deciding and inserting are one transaction. The .pure[ConnectionIO] in reserve wraps a plain value in a ConnectionIO, as IO.pure does for IO. There’s no IO.blocking in this file because doobie itself moves the blocking JDBC calls off Cats Effect’s main pool of threads.

20.5. The last table, in a transaction

One transaction sounds like the atomic step from chapter 14. Create lasttable.scala to find out:

package fullybooked

import cats.effect.{IO, IOApp}
import cats.syntax.all.*
import doobie.implicits.*

object LastTableDb extends IOApp.Simple:
  def run: IO[Unit] =
    for
      config <- loadConfig
      _ <- migrate(config.database)
      holders <- transactor(config.database).use: xa =>
        val bookings = PostgresBookings(xa)
        val parties = List.tabulate(20)(n => Party(s"Party $n", 7))
        for
          _ <- sql"TRUNCATE bookings".update.run.transact(xa)
          _ <- regulars.traverse_(booking => insert(booking).run).transact(xa)
          _ <- parties.parTraverse_(party => bookings.reserve(party, eight))
          stored <- bookings.all
        yield stored.count(_.table.number == 6)
      _ <- IO.println("20 parties asked for table 6 at eight")
      _ <- IO.println(if holders == 1 then "One of them has it" else "More than one of them has it")
    yield ()

The program empties the bookings table in the database and books the regulars into every table but number 6. Then it sends twenty parties of seven after that one, all at once. At the end it counts the bookings for table 6.

$ sbt "service/runMain fullybooked.LastTableDb"
20 parties asked for table 6 at eight
More than one of them has it

On my machine a few of the twenty end up with table 6. A transaction guarantees that its own statements happen together or not at all. It doesn’t, at Postgres’s default setting, stop a second transaction reading the same rows in the meantime. Two of our transactions each read the bookings, saw nobody at table 6, decided it was free and inserted.

Chapter 14’s Ref and semaphore each live in one process, so neither would help once two copies of the service are running. I’d rather the rule were in the database, which is the one place every writer has to go through, whether that’s a second copy of the service or somebody fixing a booking by hand. Add a second migration, V2__one_party_per_table.sql, beside the first:

TRUNCATE bookings;

CREATE EXTENSION btree_gist;
CREATE TYPE timerange AS RANGE (subtype = time);

ALTER TABLE bookings
  ADD CONSTRAINT one_party_per_table
  EXCLUDE USING gist (table_number WITH =, timerange(starts, ends) WITH &&)
  WHERE (status IN ('Confirmed', 'Seated'));

The constraint says that no two rows may have the same table number and overlapping times, counting only the bookings that hold a table. The times are the range from starts to ends. Postgres has no range type for times of day built in, so the CREATE TYPE line makes one. The btree_gist extension lets a plain number such as table_number take part in the constraint’s gist index. The TRUNCATE is there because the bookings table already contains a double booking, and Postgres won’t add the constraint to a table that breaks it. A migration on real data would have to repair the rows first.

With the constraint in place, one of two clashing inserts fails with an exception. Catching it and trying again isn’t enough when many parties clash at once. An insert that clashes with another transaction’s insert waits for that transaction to finish. When several are waiting for each other, Postgres calls it a deadlock and fails one of them. If that reservation tries again at once, it joins the others and waits again. With twenty parties, LastTableDb can go on like that and never finish. We’ll have Postgres skip a clashing row instead. In postgres.scala, change the end of the SQL in insert so that the function reads:

def insert(booking: Booking): Update0 =
  val row = toRow(booking)
  sql"""INSERT INTO bookings
          (name, party_size, phone, table_number, starts, ends, status, seated_at, cancel_reason)
        VALUES
          (${row.name}, ${row.size}, ${row.phone}, ${row.table}, ${row.starts}, ${booking.leaveBy},
           ${row.status}, ${row.seatedAt}, ${row.cancelReason})
        ON CONFLICT DO NOTHING""".update

With ON CONFLICT DO NOTHING, an insert that clashes with a row already in the table adds no row and raises no error. If the other row’s transaction is still in progress, Postgres waits for it, then skips our row if it commits and inserts ours if it doesn’t. Running an Update0 gives the number of rows it changed: for this insert, 1 or 0. Replace reserve with a version that looks at that number:

  def reserve(party: Party, time: LocalTime): IO[Either[BookingError, Booking]] =
    val attempt: ConnectionIO[Option[Either[BookingError, Booking]]] =
      loadBookings.flatMap: bookings =>
        decide(bookings, party, time) match
          case Left(error) => Some(Left(error)).pure[ConnectionIO]
          case Right(booking) =>
            insert(booking).run.map(rows => if rows == 1 then Some(Right(booking)) else None)
    def upTo(tries: Int): IO[Either[BookingError, Booking]] =
      attempt
        .transact(xa)
        .flatMap:
          case Some(answer) => IO.pure(answer)
          case None if tries > 1 => upTo(tries - 1)
          case None => IO.raiseError(IllegalStateException(s"ten clashes for ${party.name}"))
    upTo(10)

A transaction that inserted nothing answers None, and upTo then tries the whole reservation again in a new transaction. The booking that won has been committed by then, so loadBookings returns it and decide picks another table or says there’s none. Each lost attempt means one more of the six tables is taken, so ten attempts are more than a reservation can need. If all ten are lost, the database is refusing a table that decide thinks is free, perhaps because a row was changed by hand. When that happens, reserve gives up with a failed IO and the service answers 500.

$ sbt "service/runMain fullybooked.LastTableDb"
20 parties asked for table 6 at eight
One of them has it

20.6. Serving from the database

The server now has to run the migrations, open the pool and hand the routes a PostgresBookings. Replace server.scala:

package fullybooked

import cats.effect.{IO, IOApp}
import com.comcast.ip4s.{ipv4, Port}
import org.http4s.ember.server.EmberServerBuilder
import org.http4s.server.middleware
import org.typelevel.log4cats.Logger
import org.typelevel.log4cats.slf4j.Slf4jLogger
import sttp.tapir.server.http4s.Http4sServerInterpreter
import sttp.tapir.swagger.bundle.SwaggerInterpreter

object Server extends IOApp.Simple:
  val logger: Logger[IO] = Slf4jLogger.getLogger[IO]

  def run: IO[Unit] =
    for
      config <- loadConfig
      applied <- migrate(config.database)
      _ <- logger.info(s"$applied migrations applied")
      port <- portFrom(config)
      _ <- transactor(config.database).use(xa => serve(config, port, PostgresBookings(xa)))
    yield ()

  def portFrom(config: AppConfig): IO[Port] =
    IO.fromOption(Port.fromInt(config.port))(
      IllegalArgumentException(s"${config.port} isn't a port")
    )

  def serve(config: AppConfig, port: Port, bookings: Bookings): IO[Unit] =
    val routes = bookingRoutes(bookings)
    val docs = SwaggerInterpreter().fromServerEndpoints[IO](routes, config.restaurant, "0.1.0")
    val app = Http4sServerInterpreter[IO]().toRoutes(routes ++ docs).orNotFound
    EmberServerBuilder
      .default[IO]
      .withHost(ipv4"0.0.0.0")
      .withPort(port)
      .withHttpApp(middleware.Logger.httpApp(logHeaders = false, logBody = false)(app))
      .build
      .useForever

http4s and log4cats both have something called Logger, so the middleware is written middleware.Logger here. The use around serve keeps the pool open for as long as the server runs. Empty the bookings table, so that the regulars don’t get in the way:

$ docker exec fully-booked-db psql -U postgres fullybooked -c 'TRUNCATE bookings'

Start the server with sbt "service/runMain fullybooked.Server" and make a booking:

$ curl -d '{"name":"Hughes","size":4,"time":"18:30","phone":"07700 900201"}' -H 'Content-Type: application/json' localhost:8080/bookings
{"name":"Hughes","size":4,"table":3,"time":"18:30","status":"confirmed"}

Now stop the server with Ctrl+C and start it again:

$ sbt "service/runMain fullybooked.Server"
INFO  Server: 0 migrations applied
INFO  EmberServerBuilderCompanionPlatform: Ember-Server service bound to address: [::]:8080

There were no migrations to apply, since LastTableDb had already run both. The booking is still there:

$ curl localhost:8080/bookings
[{"name":"Hughes","size":4,"table":3,"time":"18:30","status":"confirmed"}]

You can see it from outside the service as well:

$ docker exec fully-booked-db psql -U postgres fullybooked -c 'SELECT name, table_number, starts, ends, status FROM bookings'
  name  | table_number |  starts  |   ends   |  status
--------+--------------+----------+----------+-----------
 Hughes |            3 | 18:30:00 | 20:00:00 | Confirmed
(1 row)

20.7. Exercises

Solutions are in the book’s downloadable code, under ch20/exercises. ExerciseRun there runs all four against the database.

  1. find in routes.scala loads every booking and searches the list. Write selectByName(name: String): Query0[BookingRow], which has the database do the search.

  2. Write coversByTable: Query0[(Int, Int)], the number of people booked at each table, counting only bookings that hold one. sum(party_size)::int gives a column doobie can read as an Int.

  3. Write cancel(name: String, reason: String): Update0, which marks a requested or confirmed booking as cancelled. run on an Update0 gives the number of rows changed. What should the service answer when that’s 0?

  4. Write moveToLater(name: String, minutes: Int): ConnectionIO[Int], which moves a booking’s start and end later by that many minutes, and fails when no row was changed. In SQL, starts + ($minutes * interval '1 minute') adds minutes to a time. Fragments made with sql can be joined with ++.