Skip to content


Switch branches/tags

Latest commit


Git stats


Failed to load latest commit information.
Latest commit message
Commit time

Over-Complicated Database Query (OCD Query)

Build Status Maven Central License

Doobie queries generated using higher-kinded data.

See complete documentation at GH pages


  1. add in your sbt:
libraryDependencies += "io.scalaland" %% "ocdquery-core" % "0.5.0"

(and maybe some optics library like Quicklens or Monocle)

  1. 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]
  1. 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] =[TicketF[Id, Id]])
    // I suggest using quicklens or monocle's extension methods
    // as they are more reliable than .copy
  1. generate queries
// build these in you services with type safety!

  // no need to pass "empty" fields like "id = Unit"!
  Create.fromTuple(("John", "Smith", "London", "Paris",

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

TicketRepo.update.withFilter { columns =>
  ( `=` "John") and (columns.surname `=` "Smith")

TicketRepo.fetch.withSort(, Sort.Ascending).withLimit(5) {
 _.from `=` "London"

TicketRepo.delete( `=` deletedId).run
  1. perform even joins returning tuples of entities:
val joiner = TicketRepo
  .join(TicketRepo).on(, // after .join() we have a tuple!
  .join(TicketRepo).on(, // and now triple!
  .fetch.withSort(, Sort.Ascending).withLimit(5) { columns => `=` "John"
  }.to[List] // ConnectionIO[(Entity, Entity, Entity)]


  • 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.