Doobie code structure example

doobie.scala

package myapp.users
// This is the shape of code I've found to work best for doobie in a cats-effect application using capability traits

trait UserOnboarding[F[_]] {
  // The api expresses the business-level workflow, not the implementation of inserting to a database
  // Keep the interface expressing the high level concern; database is only *one* implementation and it can change later
  def registerUser(userInfo: UserInfo): F[UserId]
}


class DbUserOnboarding[F[_]: MonadThrow](
    xa: Transactor[F],
    email: EmailService[F]
) extends BusinessLogic[F] {
  import DbUserOnboarding.queries
  // DbUserOnboarding is responsible for structuring transactions and the things that happen around them
  def registerUser(userInfo: UserInfo): F[UserId] =
    queries
      .registerUser(userInfo)
      .withUniqueGeneratedKeys[Int]("id")
      .transact(xa) >> email.notifySuccessfulRegistration(userInfo)
}

object DbUserOnboarding {
  // Queries are rarely generic/reusable across business domains in practice
  // Because our interface is around the business domain and not the database shape,
  // our queries can be private
  // We make the package-private for the sole purpose of unit testing the query correctness
  private[users] object queries {
    // queries all return Update0 / Query0 for doobie `check` tests
    def registerUser(userInfo: UserInfo): Update0 = 
      sql"INSERT INTO users (name, email) VALUES (${userInfo.name}, ${userInfo.email})".update
  }
}

// Unit tests on db interactions should primarily be `check` calls for simple CRUD operations
// full-scale cross-transaction tests are slow and non-parallelizable. Avoid them for everything except critical logic-heavy queries
// which have a high risk of bugs or dangerous impact if bugs happen