CODEWITHSUNDEEP
sqlscaladoobie
djsumdog
djsumdog

Reputation: 2710

Get or Insert within a Transaction on Doobie in Scala

I'm reading through the Doobie documentation and trying to do a simple get or create within a transaction. I get an option off the first query and attempt to do a getOrElse and run an insert within the else, however I keep getting a value map is not a member of Any within the getOrElse call. What's the correct way to either get an existing or create a new row in instances and return that result in a transaction? 

import doobie._
import doobie.implicits._
import cats._
import cats.effect._
import cats.implicits._
import org.joda.time.DateTime

import scala.concurrent.ExecutionContext

case class Instance(id : Int, hostname : String)

case class User(id : Int, instanceId: Int, username : String, email : String, created : DateTime)

class Database(dbUrl : String, dbUser: String, dbPass: String) {

  implicit val cs = IO.contextShift(ExecutionContext.global)

  val xa = Transactor.fromDriverManager[IO](
    "org.postgresql.Driver", dbUrl, dbUser, dbPass
  )

  def getOrCreateInstance(hostname: String) = for {
    existingInstance <- sql"SELECT id, hostname FROM instances i WHERE i.hostname = $hostname".query[Instance].option
    ensuredInstance <- existingInstance.getOrElse(sql"INSERT INTO instances(hostname) VALUES(?)".update.withGeneratedKeys[Instance]("id", "hostname"))
  } yield ensuredInstance

}

Upvotes: 1

Views: 1773

Answers (2)

djsumdog
djsumdog

Reputation: 2710

I got the following answer thanks to the people on the #scala/freenode chatroom. I'm posting it here for completeness and if people are interested in doing this without the for comprehension in the other answer. 

  def getOrCreateInstance(hostname: String): ConnectionIO[Instance] =
        OptionT(sql"SELECT id, hostname FROM instances i WHERE i.hostname = $hostname".query[Instance].option)
          .getOrElseF(sql"INSERT INTO instances(hostname) VALUES($hostname)".update.withGeneratedKeys[Instance]("id", "hostname").compile.lastOrError)

Upvotes: 5

Lasf
Lasf

Reputation: 2582

I believe something like this should work for you,

def getOrCreateInstance(hostname: String): ConnectionIO[Instance] = for {
  existingInstance <- sql"SELECT id, hostname FROM instances i WHERE i.hostname = $hostname".query[Instance].option
  ensuredInstance <- existingInstance.fold(sql"INSERT INTO instances(hostname) VALUES($hostname)".update.withGeneratedKeys[Instance]("id", "hostname").take(1).compile.lastOrError)(_.pure[ConnectionIO])
} yield ensuredInstance

where you are compiling the fs2 Stream and also lifting the existing instance into a ConnectionIO in the case that it does already exist.

Upvotes: 0

Related Questions