Http4s Modules by Example - Petstore Queries
Now that we have our initial schema defined, we need to write some queries that allow us to communicate with the database. To figure out what kinds of queries we will need, let us proceed with defining a simple algebra for the set of operations that should be available.
Specifying Domain
First, a pet in our application will have a few attributes, mirroring what we declared in our postgres table definition in the previous page:
import java.util.UUID
import java.time.Instant
case class PetA(
petId: UUID,
createTime: Option[Instant],
updateTime: Option[Instant],
name: String,
bio: String,
createdBy: Option[UUID],
status: String,
photoUrls: List[String]
)
Pets require creation, read, and update. A simple algebra could read as follows:
trait PetAlgebra1[F[_]]{
// Create a pet, discard errors if creation impossible
def create(pet: Pet): F[Unit]
// look for a single pet by id
def selectById(petId: UUID): F[Option[Pet]]
// Update a single pet by an id.
def update(petId: UUID, pet:Pet): F[Unit]
}
This has a few issues though. Firstly, the pet's id is generated by postgres, so we do not want to have to drag it around in our application layer. We could make the petId
an Option[UUID]
, but this would also be wrong. If a Pet
's petId
is None
, does that mean that the pet does not exist in the database yet? Or does it mean that it might exist, but we just don't have the database id available?
Let us try another way.
case class Pet(
name: String,
bio: String,
createdBy: Option[UUID],
status: String,
photoUrls: List[String],
updateTime: Option[Instant] = None
)
We have dropped all the fields that are created by the database. Now we have the problem in our algebra of how to represent these fields.
import h4sm.db.CRUAlgebra
trait PetAlgebra[F[_]] extends CRUAlgebra[F, UUID, Pet, Instant]
And that's all we have to write. PetAlgebra will have a type member Annotated
, which will be (Pet, UUID, Instant)
, referring to the base datatype Pet
, it's id type, and the remaining fields that the database is in control of.
Let's see how this translates to writing a repository for this algebra:
import cats.data.OptionT
class PetRepositoryUnimplemented[F[_]] extends PetAlgebra[F]{
// Members declared in h4sm.db.CAlgebra
def insert(a: Pet): F[Unit] = ???
// OptionT is used here as a way to handle unresolved insert collisions. We don't have any unique fields on
// our pets.
def insertGetId(a: Pet): OptionT[F,UUID] = ???
// Members declared in h4sm.db.RAlgebra
def byId(id: UUID): OptionT[F,Annotated] = ???
def select: F[List[Annotated]] = ???
// Members declared in h4sm.db.UAlgebra
def update(id: UUID, u: Pet): F[Unit] = ???
}
The members shown above show the base methods expected by PetAlgebra
.
Each of these methods will require a query available. So let's begin by writing some queries.
import doobie._
import doobie.implicits._
import doobie.postgres.implicits._
import h4sm.auth.db.sql._
import h4sm.auth.comm.authIdTypes._
trait PetSql{
def insert(a: Pet): Update0 = sql"""
insert into ct_petstore.pet (name, bio, status, created_by, photo_urls)
values (${a.name}, ${a.bio}, ${a.status}, ${a.createdBy}, ${a.photoUrls})
""".update
def insertGetId(a: Pet): ConnectionIO[UUID] = insert(a).withUniqueGeneratedKeys("pet_id")
def select: Query0[(Pet, UUID, Instant)] = sql"""
select name, bio, created_by, status, photo_urls, update_time, pet_id, create_date
from ct_petstore.pet
""".query
def selectById(uuid: UUID): Query0[(Pet, UUID, Instant)] = (select.toFragment ++ fr"""
where pet_id = $uuid
""").query
def update(id: UUID, pet: Pet): Update0 = sql"""
update ct_petstore.pet
set name = ${pet.name}, bio = ${pet.bio}, update_time = now(), status = ${pet.status}, photo_urls = ${pet.photoUrls}
where pet_id = $id
""".update
def selectByName(name: String): Query0[(Pet, UUID, Instant)] = (select.toFragment ++ fr"""
where name = $name
""").query
}
object PetSql extends PetSql
To make our testing easier, I'll write an arbitrary generator to build Pet
s:
import cats.implicits._
import org.scalacheck.{Arbitrary, Gen}
import org.scalacheck.cats.implicits._
import h4sm.testutil.arbitraries._
object arbitraries {
implicit val arbPet: Arbitrary[Pet] = Arbitrary(
(
nonEmptyString,
nonEmptyString,
Gen.option(Gen.uuid),
nonEmptyString,
Gen.listOf(nonEmptyString),
Gen.option(arbInstant.arbitrary),
).mapN(Pet.apply _)
)
}
Now all our queries are available as PetSql
. Let's write some typechecking tests:
import cats.effect.IO
import h4sm.db.config.DatabaseConfig
import h4sm.testutil.arbitraries._
import h4sm.testutil.DbFixtureSuite
import h4sm.testutil.transactor.getTransactor
import doobie.scalatest.IOChecker
import doobie.Transactor
import arbitraries._
class PetSqlTestSpec extends DbFixtureSuite with IOChecker {
override def testConfig: DatabaseConfig = config.copy(databaseName = "petstoretest") // only needed for documentation
override def colors = doobie.util.Colors.None // only needed for documentation
def schemaNames = List("ct_auth", "ct_files", "ct_permissions", "ct_petstore")
def transactor: Transactor[IO] = getTransactor[IO](cfg)
test("insert should typecheck")(_ => check(applyArb(PetSql.insert _)))
test("select by id should typecheck")(_ => check(applyArb(PetSql.selectById _)))
test("select user files should typecheck")(_ => check(applyArb(PetSql.selectByName _)))
test("update upload time should typecheck")(_ => check(applyArb((PetSql.update _).tupled)))
}
Note the use of DbFixtureSuite
, which will automatically create and then destroy a test postgres database for us.
val t = new PetSqlTestSpec
// t: PetSqlTestSpec = repl.MdocSession$App$PetSqlTestSpec@23512313
t.execute(color = false)
// MdocSession$App$PetSqlTestSpec:
// - insert should typecheck
// - select by id should typecheck *** FAILED ***
// Query0[(App.Pet, UUID, Instant)] defined at 02-queries.md:117
// select name, bio, created_by, status, photo_urls, update_time,
// pet_id, create_date
// from ct_petstore.pet
// where pet_id = ?
// ✓ SQL Compiles and TypeChecks
// ✓ P01 UUID → OTHER (uuid)
// ✓ C01 name VARCHAR (text) NOT NULL → String
// ✕ C02 bio VARCHAR (text) NULL → String
// Reading a NULL value into String will result in a runtime failure.
// Fix this by making the schema type NOT NULL or by changing the
// Scala type to Option[String]
// ✓ C03 created_by OTHER (uuid) NOT NULL → Option[UUID]
// ✓ C04 status VARCHAR (text) NOT NULL → String
// ✓ C05 photo_urls ARRAY (_text) NOT NULL → Array[String]
// ✓ C06 update_time TIMESTAMP (timestamptz) NOT NULL → Option[Timestamp]
// ✓ C07 pet_id OTHER (uuid) NOT NULL → UUID
// ✓ C08 create_date TIMESTAMP (timestamptz) NOT NULL → Timestamp (Checker.scala:56)
// - select user files should typecheck *** FAILED ***
// Query0[(App.Pet, UUID, Instant)] defined at 02-queries.md:127
// select name, bio, created_by, status, photo_urls, update_time,
// pet_id, create_date
// from ct_petstore.pet
// where name = ?
// ✓ SQL Compiles and TypeChecks
// ✓ P01 String → VARCHAR (text)
// ✓ C01 name VARCHAR (text) NOT NULL → String
// ✕ C02 bio VARCHAR (text) NULL → String
// Reading a NULL value into String will result in a runtime failure.
// Fix this by making the schema type NOT NULL or by changing the
// Scala type to Option[String]
// ✓ C03 created_by OTHER (uuid) NOT NULL → Option[UUID]
// ✓ C04 status VARCHAR (text) NOT NULL → String
// ✓ C05 photo_urls ARRAY (_text) NOT NULL → Array[String]
// ✓ C06 update_time TIMESTAMP (timestamptz) NOT NULL → Option[Timestamp]
// ✓ C07 pet_id OTHER (uuid) NOT NULL → UUID
// ✓ C08 create_date TIMESTAMP (timestamptz) NOT NULL → Timestamp (Checker.scala:56)
// - update upload time should typecheck
Doobie's typechecking has alerted us that there is a mismatch between our queries and our domain representation. We need to handle a possibly null bio, and a possibly empty or null photo_urls.
We can handle a null bio by simply making that field optional in our domain object:
case class Pet(
name: String,
bio: Option[String],
createdBy: Option[UUID],
status: String,
photoUrls: List[String],
updateTime: Option[Instant] = None
)
And we can update our schema to set not null
on photoUrls. This update has been made in the petstore module of this project. You should refer to that module code as you read this documentation.
import cats.effect.Bracket
import cats.implicits._
// I require a Bracket[?[_], Throwable] because doobie's transact requires it.
class PetRepository[F[_]: Bracket[?[_], Throwable]](xa: Transactor[F]) extends PetAlgebra[F]{
// Members declared in h4sm.db.CAlgebra
def insert(a: Pet): F[Unit] = PetSql.insert(a).run.transact(xa).void
def insertGetId(a: Pet): OptionT[F,UUID] = OptionT.liftF(PetSql.insertGetId(a).transact(xa))
// Members declared in h4sm.db.RAlgebra
def byId(id: UUID): OptionT[F,Annotated] = OptionT(PetSql.selectById(id).option.transact(xa))
def select: F[List[Annotated]] = PetSql.select.to[List].transact(xa)
// Members declared in h4sm.db.UAlgebra
def update(id: UUID, u: Pet): F[Unit] = PetSql.update(id, u).run.transact(xa).void
}
In the petstore module, these implementations are also complete for managing orders.
At this point, we're ready to implement our endpoints.