Over-Complicated Database Query (OCD Query)

Doobie queries generated using higher-kinded data.

See complete documentation at GH pages

Instalation

1. add in your sbt:

libraryDependencies += "io.scalaland" %% "ocdquery-core" % "0.5.0"

(and maybe some optics library like Quicklens or Monocle)

2. create higher-kinded data representation:

import java.time.LocalDate
import java.util.UUID

final case class TicketF[F[_], C[_]](
  id:      C[UUID],
  name:    F[String],
  surname: F[String],
  from:    F[String],
  to:      F[String],
  date:    F[LocalDate]
)

3. create a repository:

import cats.Id
import com.softwaremill.quicklens._
import doobie.h2.implicits._
import io.scalaland.ocdquery._

// only have to do it once!
val TicketRepo: Repo.EntityRepo[TicketF] = {
  // I have no idea why shapeless cannot find this Generic on its own :/
  // if you figure it out, please PR!!!
  implicit val ticketRead: doobie.Read[Repo.ForEntity[TicketF]#Entity] =
    QuasiAuto.read(shapeless.Generic[TicketF[Id, Id]])
    
  Repo.forEntity[TicketF](
    "tickets".tableName,
    // I suggest using quicklens or monocle's extension methods
    // as they are more reliable than .copy
    DefaultColumnNames.forEntity[TicketF].modify(_.from).setTo("from_".columnName)
  )
}

4. generate queries

// build these in you services with type safety!

TicketRepo.insert(
  // no need to pass "empty" fields like "id = Unit"!
  Create.fromTuple(("John", "Smith", "London", "Paris", LocalDate.now))
).run

import io.scalaland.ocdquery.sql._ // common filter syntax like `=`, `<>`

TicketRepo.update.withFilter { columns =>
  (columns.name `=` "John") and (columns.surname `=` "Smith")
}(
  TicketRepo.emptyUpdate.modify(_.data).setTo(LocalDate.now)
).run

TicketRepo.fetch.withSort(_.name, Sort.Ascending).withLimit(5) {
 _.from `=` "London"
}.to[List]

TicketRepo.delete(_.id `=` deletedId).run

5. perform even joins returning tuples of entities:

val joiner = TicketRepo
  .join(TicketRepo).on(_._1.id, _._2.id) // after .join() we have a tuple!
  .join(TicketRepo).on(_._2.id, _._3.id) // and now triple!
  .fetch.withSort(_._1.name, Sort.Ascending).withLimit(5) { columns =>
    columns._1.name `=` "John"
  }.to[List] // ConnectionIO[(Entity, Entity, Entity)]

Limitations

• Library assumes that EntityF is flat, and automatic generation of Doobie queries is done in a way which doesn't allow you to use JOINs, nested SELECTs etc. If you need them you can use utilities from RepoMeta to write your own query, while delegating some of the work to RepoMeta (see how Repo does it!).
• Using EntityF everywhere is definitely not convenient. Also it doesn't let you define default values like e.g. None/Skipped for optional fields. So use them internally, as entities to work with your database and separate them from entities exposed in your API/published language. You can use chimney for turning public instances to and from internal instances,
• types sometimes confuse compiler, so while it can derive something like shapeless.Generic[TicketF[Id, Id]], it has issues finding Generic.Aux, so Doobie sometimes get's confused - QuasiAuto let you provide the right values explicitly, so that the derivation is not blocked by such silly issue.