Complete code belonging to https://x.com/vergauwen_simon/status/1707390487299023355?s=20

Server.kt

package io.github.nomisrev

import app.cash.sqldelight.driver.jdbc.asJdbcDriver
import arrow.continuations.SuspendApp
import arrow.continuations.ktor.server
import arrow.fx.coroutines.autoCloseable
import arrow.fx.coroutines.closeable
import arrow.fx.coroutines.resourceScope
import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import io.github.nomisrev.sqldelight.SqlDelight
import io.github.nomisrev.sqldelight.UsersQueries
import io.ktor.server.application.Application
import io.ktor.server.application.call
import io.ktor.server.html.respondHtml
import io.ktor.server.netty.Netty
import io.ktor.server.routing.get
import io.ktor.server.routing.routing
import kotlinx.coroutines.awaitCancellation
import kotlinx.html.BODY
import kotlinx.html.body
import kotlinx.html.p
import org.testcontainers.containers.PostgreSQLContainer
import kotlin.time.Duration.Companion.seconds

sealed interface EmailAddress {
  data object Private : EmailAddress
  data class Public(val email: String) : EmailAddress
}

data class User(val id: Long, val username: String, val email: EmailAddress)

fun String?.toEmailAddress(): EmailAddress =
  if (this == null) EmailAddress.Private
  else EmailAddress.Public(this)

fun UsersQueries.queryUserById(id: Long): User? =
  selectUser(id) { userId, username, email ->
    User(userId, username, email.toEmailAddress())
  }.executeAsOneOrNull()

fun BODY.userNotFoundPage() =
  p { +"This user does not exist :(" }

fun BODY.userView(user: User) {
  p { +"User ID: ${user.id}" }
  p { +"Username: ${user.username}" }
  when (user.email) {
    EmailAddress.Private -> p { +"This user has a private email address" }
    is EmailAddress.Public -> p { +"Email address: ${user.email}" }
  }
}

fun BODY.renderUserOrNotFound(potentialUser: User?) =
  potentialUser?.let { userView(it) } ?: userNotFoundPage()

fun Application.handler(queries: UsersQueries) =
  routing {
    get("/users/{id}") {
      val id = call.parameters["id"]?.toLongOrNull()
      requireNotNull(id) { "Expected a valid user id" }
      val potentialUser = queries.queryUserById(id)
      call.respondHtml {
        body {
          renderUserOrNotFound(potentialUser)
        }
      }
    }
  }

fun main(): Unit = SuspendApp {
  resourceScope {
    val container = autoCloseable {
      PostgreSQLContainer("postgres:latest")
        .withEnv(mapOf("PGDATA" to "/var/lib/postgresql/data"))
        .withTmpFs(mapOf("/var/lib/postgresql/data" to "rw"))
        .apply { start() }
    }
    val dataSource = autoCloseable {
      HikariDataSource(HikariConfig().apply {
        jdbcUrl = container.getJdbcUrl()
        username = container.username
        password = container.password
        driverClassName = container.driverClassName
      })
    }
    val driver = closeable { dataSource.asJdbcDriver() }
    SqlDelight.Schema.create(driver)
    val db = SqlDelight(driver)
    db.usersQueries.insertUser("Simon", "[email protected]")
    db.usersQueries.insertUser("anonymous", null)
    val server = server(Netty, preWait = 0.seconds, port = 9955) {
      handler(db.usersQueries)
    }
    println("Server running ${server.environment.connectors}, press ctrl-c to terminate")
    awaitCancellation()
  }
}

Users.sql

CREATE TABLE IF NOT EXISTS users(
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(200)
);

selectUser:
SELECT *
FROM users
WHERE id = :id;

insertUser:
INSERT INTO users(username, email)
VALUES (:username, :email);