PostgresDbEffect.scala

sealed trait DatabaseDriver {
  //A lot of database config we don't care about
  //Database object
  private val pool = new ConnectionPool(_, _)
  //create an effect from the pool
  lazy val db: UIO[ConnectionPool[MySQLConnection]] = UIO.fromFunction(_ => pool)
}

trait PostgresDatabase extends Database with DatabaseDriver { self =>
  private def runQuery(query: String): ZIO[DatabaseDriver, Throwable, QueryResult] = {
    for {
      db <- ZIO.accessM[DatabaseProvider](_.db)
      result <- ZIO.fromFuture(_ => db.sendPreparedStatement(query))
    } yield result
  }

  override def database: Database.Service = new Database.Service {
    override def findTodo(id: Int): IO[TodoError, TodoItem] = {
      val query = s"select * from todo where id=$id;"
      (for {
        dbResult <- runQuery(query).provide(self)
                 todo = mapTo[TodoItem](dbResult)(resultSetToTodo)
        effect <- ZIO.fromOption(todo)
                     .mapError(_ =>  ToDoItemError(s"id $id doesn't exist"))
      } yield effect).mapError { mapThrowableToTodoError  }
    }

    // ...  Other methods from the Database.Service trait
  }
  private def mapThrowableToTodoError(err : Throwable) : TodoError = {
    val errMsg = err.getMessage
    err match {
      case err : ToDoItemError => err
      case e  =>
        errMsg.toLowerCase.startsWith("error") match {
          case true =>
            //error thrown by the driver when query is wrong
            QueryError(errMsg)
          case false =>
            //errors like database not reachable or wrong auth
            //or something really catastrophic ):
            DbError(e.getMessage)
        }
    }
  }
  private def mapTo[T](result : QueryResult)(f : ResultSet => Option[T]) : Option[T] = {
    result.rows match {
      case None =>
        None
      case Some(resultSet) =>
        f(resultSet)
    }
  }
  private def resultSetToTodo(resultSet: ResultSet): Option[TodoItem] =  ???
}

object PostgresDB extends PostgresDatabase