Exposing postgres transactions as an effect

[divarvel]

I'm looking at how to integrate talking to a PostgreSQL as an effect.
Currently, i'm using postgresql-simple along with pg-transact and resource-pool (pg-transact provides a DBT monad transformer that's a newtype over a ReaderT Connection. Its purpose is to model transactions with a dedicated datatype (instead of manually starting and closing transactions by calling dedicated functions from postgresql-simple. Additionally, DBT rolls back the whole transaction when an exception is thrown and not caught.

Directly exposing postgresql-simple and pg-transact as an effect is not too hard: a static effect with a Pool Connection as its representation allows to acquire a Connection and running queries, getting back an IO as a result. Sadly, this does not give any control over transactions.

What I'd like to have would be

-- issue a query as part of a transaction
query :: (ToRow a, FromRow b, Transaction :> es) => Query -> a -> Eff es [b]
-- compared to
-- query :: (ToRow a, FromRow b, MonadIO m) => Query -> a -> DBT m [b]

-- given a transaction, run it by acquiring a connection through a `Pool` exposed by the `DB` effect
-- and calling `runDBT` with the provided isolation level
runTransaction :: DB :> es => IsolationLevel -> Eff [Transaction] a -> Eff es a

-- given a pool, interpret the DB effect
runDB :: IOE :> es => Pool -> Eff (DB : es) a -> Eff es a

Going directly from DBT IO a to (DB :> es, IOE :> es) => Eff es a is doable (it's what pg-transact-effectful does: https://github.com/Kleidukos/pg-transact-effectful/blob/main/src/Effectful/PostgreSQL/Transact/Effect.hs). What I'm not super clear on is how to expose a DBT IO a as a Eff [Transaction, IOE] a and then get back a DBT IO a in order to call runTransaction. As a bonus, using DBT (Eff es) a would allow threading effects inside the DBT layer.

[arybczak]

I don't have an answer, but there's hpqtypes-effectful that implements an adapter for hpqtypes that does what you want, i.e. exposes access to PostgreSQL as an effect and has support for basic transaction management.

If you really want to use the libraries you've listed, perhaps you can use code from hpqtypes-effectful as inspiration.

[ocharles]

My personal opinion on transactions is effects is you want something more like:

data DB :: Effect
data Statement :: Effect

transaction :: DB :> es => IsolationLevel -> Eff '[Statement] a -> Eff es a
runDB :: Pool -> Eff (DB : es) a -> Eff es a

I don't think a transaction should have access to everything in es, because at certain isolation levels you'll need to retry the transaction multiple times.

[divarvel]

agreed, the effects inside a transaction should be restricted. At the serializable levels the IO actions can be retried, so yeah you have to make sure they're idempotent (not in a strict sense, for instance logging would be okay). I have needed more complex IO within transactions only a couple times in the past 5 years, so requiring an explicit interpretation layer for extra effects when running the transaction would be a good thing.

[divarvel]

After spending more time with the library, i think exposing DBT directly is not an issue, similar to what is done with the STM monad.