Cannot find or construct a Read instance for type: Option[A]

Why doobie can't convert query as Option[A]?

abstract class CRUDAbs[A: Read](val tableName: String) extends TransactSQL {
  def table: Fragment = Fragment.const(s"$tableName")
  def columnsList: Array[String] = {
    val cls = classTag[A].runtimeClass
    cls.getDeclaredFields.map(_.getName).map(snakeCase)
  }
  def columns: Fragment = Fragment.const(columnsList.mkString(","))
  def find(id: Int): doobie.Query0[Option[A]] =
    (sql"select " ++ columns ++ sql" from " ++ table ++ sql" where " ++ Fragment.const(
      s"${columnsList.head}"
    ) ++ sql" = $id").query[Option[A]]

I get an error

Cannot find or construct a Read instance for type:

  Option[A]

What am I missing?

Solution

If I restored your code fragment correctly it's something like

import doobie.Read
import doobie.implicits.toSqlInterpolator
import doobie.util.fragment.Fragment

import scala.reflect.{ClassTag, classTag}

object App {
  val snakeCase = ???

  abstract class CRUDAbs[A: Read: ClassTag](val tableName: String) /*extends TransactSQL*/ {
    def table: Fragment = Fragment.const(s"$tableName")

    def columnsList: Array[String] = {
      val cls = classTag[A].runtimeClass
      cls.getDeclaredFields.map(_.getName).map(snakeCase)
    }

    def columns: Fragment = Fragment.const(columnsList.mkString(","))

    def find(id: Int): doobie.Query0[Option[A]] =
      (sql"select " ++ columns ++ sql" from " ++ table ++ sql" where " ++ Fragment.const(
        s"${columnsList.head}"
      ) ++ sql" = $id").query[Option[A]]
  }
}

The whole compile error is

Cannot find or construct a Read instance for type:

  Option[A]

This can happen for a few reasons, but the most common case is that a data
member somewhere within this type doesn't have a Get instance in scope. Here are
some debugging hints:

- For Option types, ensure that a Read instance is in scope for the non-Option
  version.
- For types you expect to map to a single column ensure that a Get instance is
  in scope.
- For case classes, HLists, and shapeless records ensure that each element
  has a Read instance in scope.
- Lather, rinse, repeat, recursively until you find the problematic bit.

You can check that an instance exists for Read in the REPL or in your code:

  scala> Read[Foo]

and similarly with Get:

  scala> Get[Foo]

And find the missing instance and construct it as needed. Refer to Chapter 12
of the book of doobie for more information.

      ) ++ sql" = $id").query[Option[A]]

Please notice For Option types, ensure that a Read instance is in scope for the non-Option version.

There is an instance of type class Read for type Option[A] provided there is an instance of type class Get for type A

implicit def fromGetOption[A](implicit ev: Get[A]): Read[Option[A]] =
  new Read(List((ev, Nullable)), ev.unsafeGetNullable)

https://github.com/tpolecat/doobie/blob/main/modules/core/src/main/scala/doobie/util/read.scala#L76-L77

So try to modify definition of class CRUDAbs using doobie.Get

abstract class CRUDAbs[A: Get: ClassTag](val tableName: String)

Actully, let's return to Read context bound. It turns out ("Chapter 12 of the book of doobie" mentioned in the above compile error) that the type class Get is for non-optional (non-nullable) single-variable (single-column) types while the type class Put is also for optional (nullable) or multi-variable (vector) types. Just let's define an implicit similar to Read.fromGetOption

implicit def fromGetOption[A](implicit ev: Get[A]): Read[Option[A]] =
  new Read(List((ev, Nullable)), ev.unsafeGetNullable)

namely let's define

implicit def fromReadOption[A: Read]: Read[Option[A]] = Read[A].map(Some(_)) // Read[A].map(Option(_))

Now the following code compiles

import doobie.Read
import doobie.implicits.toSqlInterpolator
import doobie.util.fragment.Fragment
import scala.reflect.{ClassTag, classTag}

object App {
  val snakeCase = ???

  implicit def fromReadOption[A: Read]: Read[Option[A]] = Read[A].map(Some(_))

  abstract class CRUDAbs[A: Read: ClassTag](val tableName: String) /*extends TransactSQL*/ {
    def table: Fragment = Fragment.const(s"$tableName")

    def columnsList: Array[String] = {
      val cls = classTag[A].runtimeClass
      cls.getDeclaredFields.map(_.getName).map(snakeCase)
    }

    def columns: Fragment = Fragment.const(columnsList.mkString(","))

    def find(id: Int): doobie.Query0[Option[A]] =
      (sql"select " ++ columns ++ sql" from " ++ table ++ sql" where " ++ Fragment.const(
        s"${columnsList.head}"
      ) ++ sql" = $id").query[Option[A]]
  }

  case class Chain0(i: Int)
  case class Chain(i: Int, s: String)
  case class Chain1(i: Int, s: Option[String])
  class CRUDChain0 extends CRUDAbs[Chain0]("chain") // compiles
  class CRUDChain extends CRUDAbs[Chain]("chain") // compiles
  class CRUDChain1 extends CRUDAbs[Chain1]("chain") // compiles
}

https://scastie.scala-lang.org/DmytroMitin/2kjpdtvaSVqxK6IhnmVEVQ

Or don't define the implicit fromReadOption but replace the context bound with two implicit parameters

abstract class CRUDAbs[A: ClassTag](val tableName: String)(implicit r: Read[A], optR: Read[Option[A]])

abstract class CRUDAbs[A: Read : ClassTag](val tableName: String)(implicit optR: Read[Option[A]])

or in kind-projector syntax

abstract class CRUDAbs[A: Read : λ[X => Read[Option[X]]] : ClassTag](val tableName: String)

https://scastie.scala-lang.org/DmytroMitin/2kjpdtvaSVqxK6IhnmVEVQ/1