how to implement error handling with doobie

  override def getUser(uuid: UUID): F[Either[String, User]] = {
    val query = sql"""SELECT email, password FROM "user" WHERE "userId" = $uuid """
      .query[User]
      .option

    Sync[F].delay {
      query.transact(transactor).attempt.flatMap {
        case Right(Some(user)) => Right(user) // Simply return the user
        case Right(None) => Left("User not found") // Return the error message directly
        case Left(error) =>
          println(s"Error occurred during getUser query: $error")
          Left("Something went wrong while fetching user details") // Return the error message directly
      }
    }
  }
  
[error] -- [E007] Type Mismatch Error: C:\Users\Vaivhav Pandey\Desktop\jobgo\jobgo-ai\src\main\scala\repositories\UserRepository.scala:33:39 
[error] 33 |        case Right(Some(user)) => Right(user) // Simply return the user
[error]    |                                  ^^^^^^^^^^^
[error]    |Found:    Right[Nothing, domain.User]
[error]    |Required: F[B]
[error]    |
[error]    |where:    B is a type variable with constraint
[error]    |          F is a type in class UserRepositoryImpl with bounds <: [_] =>> Any
[error]    |
[error]    | longer explanation available when compiling with `-explain`
[error] -- [E007] Type Mismatch Error: C:\Users\Vaivhav Pandey\Desktop\jobgo\jobgo-ai\src\main\scala\repositories\UserRepository.scala:34:32 
[error] 34 |        case Right(None) => Left("User not found") // Return the error message directly
[error]    |                            ^^^^^^^^^^^^^^^^^^^^^^
[error]    |Found:    Left[("User not found" : String), Nothing]
[error]    |Required: F[B]
[error]    |
[error]    |where:    B is a type variable with constraint
[error]    |          F is a type in class UserRepositoryImpl with bounds <: [_] =>> Any
[error]    |
[error]    | longer explanation available when compiling with `-explain`
[error] -- [E007] Type Mismatch Error: C:\Users\Vaivhav Pandey\Desktop\jobgo\jobgo-ai\src\main\scala\repositories\UserRepository.scala:37:14 
[error] 37 |          Left("Something went wrong while fetching user details") // Return the error message directly
[error]    |          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[error]    |Found:    Left[("Something went wrong while fetching user details" : String), Nothing]
[error]    |Required: F[B]
[error]    |
[error]    |where:    B is a type variable with constraint
[error]    |          F is a type in class UserRepositoryImpl with bounds <: [_] =>> Any
[error]    |
[error]    | longer explanation available when compiling with `-explain`
[error] three errors found
[error] (Compile / compileIncremental) Compilation failed

I provided code and compile time error I used cats-effect and doobie I want to control error handling from sql side as well so I can implement error handling from db side as well as custom errors like user not found etc

Solution

You are not chaining the cats-effect constructors appropriately.

First, transact returns an F[Option[User]] thus when you flatMap that, you need to return an F not an Either.
Second, the Sync[F].delay at the outside is not doing anything helpful and will just add more type errors.
Finally, bare println that is not properly suspended does not follow the cats-effect paradigm and will hurt the performance of your app. Also, you probably want to use a proper logger library rather than bare println.

BTW, as advice, I recommend using concrete IO to simplify things rather than trying to use the tagless-final style.

The final code you want is something like this:

// We assume these already exist in the scope.
val xa: Transactor[IO]
val logger: Logger[IO] // If you don't want to use a logger, you should use IO.println instead.

// Rather than Either[Error, User],
// I usually prefer to have a single ADT QueryResult that models all the important outcomes.
override def getUser(uuid: UUID): IO[Either[Error, User]] = {
  val query = sql"""SELECT email, password FROM "user" WHERE "userId" = ${uuid}"""
    .query[User]
    .option

  query.transact(xa).attempt.flatMap {
    case Right(Some(user)) =>
      // Simply return the user.
      IO.pure(Right(user))

    case Right(None) =>
      // Return the appropriate error.
      IO.pure(Left(UserNotFoundError)

    case Left(ex) =>
      // Return the unknown error.
      logger.error("Error occurred during getUser query", ex).as(
        Left(UnknownError(ex))
      )
  }

PS: I have some resources that may help you grasp how to properly chain operations in this paradigm here: https://github.com/BalmungSan/programs-as-values