doobie connections and stuff

connectionio.md

If so, can you give me some advice on how to do standard http transaction boundary stuff. That is

* unpack request
* get stuff from db
* update db based on request
* return request

all this in a single transaction (succeed/fail).

it's the interleaving of ConnectionIO and IO that I'm struggling with. And I can't seem to find any good examples.

So the way we do this at our current project is simply by having a 1:1 mapping between endpoints and ConnectionIO's that perform the database operations necessary for that particular endpoint. Our endpoint might be something like:

  case req @ POST -> Root / "things" =>
    for {
      thing <- req.decodeJson[Thing]
      id <- things.createThing(thing)
      response <- Ok(id)
    } yield response

where things might be:

class Things(transactor: Transactor[IO]) {
  def createThing(thing: Thing): IO[ThingId] = {
    val dbOp = for {
      id <- sql"select nextval from ...".query[ThingId].unique
      _  <- sql"insert into things values ...".update.run
    } yield id
    
    dbOp.transact(transactor)
  }
}

(note: contrived example - in our particular app, we now extract SQL (values of type doobie.Query0) into a queries object, separate from where we compose them together)

With this approach, createThing runs composed database operations, which when executed up the stack, run in a single transaction.

Of course, that doesn't help when you want to do stuff between database operations. In our app, we typically only do pureish things within database transactions. So we do occasionally use connection.delay(...) source which essentially lifts a lazy value into a ConnectionIO

I haven't tried this, but to intersperse pure IO operations within a database transaction, then you might be able to use liftIO - I don't know there is an instance defined for ConnectionIO, or if the type even exists in the scala ecosystem (i'm assuming it does, because it's super handy for this type of thing in haskell).