# bluefin-postgresql
This package provides `bluefin` effects for [postgresql-simple](https://hackage.haskell.org/package/postgresql-simple)'s `Connection` type.
It defines:
- a dynamic `WithConnection` effect to allow effectful functions to use a `Connection`, without worrying about where that `Connection` comes from.
- a dynamic `PostgreSQL` effect ro run database operations from `postgresql-simple`.
For a higher-level effect library using [Opaleye](https://hackage.haskell.org/package/opaleye), see [bluefin-opaleye](https://github.com/fpringle/bluefin-postgresql/blob/main/bluefin-opaleye#readme).
## Effectful functions
In the `WithConnection` effect we can always request a `Connection` and use it as we normally
would:
```haskell
import Bluefin.PostgreSQL as BP
import qualified Database.PostgreSQL.Simple as PSQL
insertAndList ::
(e :> es, e1 :> es) =>
WithConnection e ->
IOE e1 ->
Eff es [User]
insertAndList wc ioe = BP.withConnection wc $ \conn -> do
effIO ioe $ PSQL.execute conn "insert into users (first_name) values (?)" ["Nuala"]
effIO ioe $ PSQL.query conn "select * from users where first_name in ?" $ PSQL.Only $ PSQL.In ["Anna", "Boris", "Carla"]
```
The `PostgreSQL` effect lets us completely forget about `Connection` and rewrite the above to:
```haskell
import Bluefin.PostgreSQL
insertAndList ::
(e :> es) =>
PostgreSQL e ->
Eff es [User]
insertAndList psql = do
BP.execute psql "insert into users (first_name) values (?)" ["Nuala"]
BP.query psql "select * from users where first_name in ?" $ PSQL.Only $ PSQL.In ["Anna", "Boris", "Carla"]
```
The same goes for other functions:
```haskell
-- use a transaction
insertAndListCarefully ::
(e :> es) =>
PostgreSQL e ->
Eff es [User]
insertAndListCarefully psql = BP.withTransaction psql $ insertAndList psql
-- stream + fold over results (in Eff)
countUsersIneffeciently ::
(e :> es, e1 :> es) =>
PostgreSQL e ->
IOE e1 ->
Eff es Int
countUsersIneffeciently psql ioe =
BP.fold_ psql "select * from users" 0 $ \acc (row :: User) -> do
effIO ioe . putStrLn $ "User: " <> show row
pure $ acc + 1
```
## Interpreters
In order to discharge the `PostgreSQL` effect we use the `WithConnection` effect:
```haskell
dischargePostgreSQL :: (e :> es, e1 :> es) => WithConnection e -> IOE e1 -> Eff es [User]
dischargePostgreSQL withConn ioe =
runPostgreSQL withConn ioe $ \psql -> insertAndListCarefully psql
```
Alternatively we can use the OpenTelemetry support provided by [hs-opentelemetry-instrumentation-postgresql-simple](https://hackage-content.haskell.org/package/hs-opentelemetry-instrumentation-postgresql-simple/docs/OpenTelemetry-Instrumentation-PostgresqlSimple.html) (note that this requires enabling the `enable-opentel` cabal flag):
```haskell
dischargePostgreSQLUsingOpenTelemetry :: (e :> es, e1 :> es) => WithConnection e -> IOE e1 -> Eff es [User]
dischargePostgreSQLUsingOpenTelemetry withConn ioe =
runPostgreSQLOT withConn ioe $ \psql -> insertAndListCarefully psql
```
The simplest way of running the `WithConnection` effect is by just providing a `Connection`, which we can get in the normal ways:
```haskell
import Bluefin.PostgreSQL as BP
import qualified Database.PostgreSQL.Simple as PSQL
usingConnection :: IO ()
usingConnection =
runEff $ \ioe ->
bracket (effIO ioe $ PSQL.connectPostgreSQL "") (effIO ioe . PSQL.close) $ \conn ->
BP.runWithConnection conn $ \wc ->
BP.runPostgreSQL wc ioe $ \psql ->
insertAndListCarefully wc ioe >>= effIO ioe . print
usingConnectInfo :: IO ()
usingConnectInfo =
runEff $ \ioe ->
BP.runWithConnectInfo ioe PSQL.defaultConnectInfo $ \wc ->
BP.runPostgreSQL wc ioe $ \psql ->
insertAndListCarefully psql >>= effIO ioe . print
```
Alternatively, we can use a connection pool (from [resource-pool](https://hackage.haskell.org/package/resource-pool)
and [unliftio-pool](https://hackage.haskell.org/package/unliftio-pool)), which is much better suited to
long-running processes like servers.
```haskell
import Bluefin.PostgreSQL as BP
import qualified Database.PostgreSQL.Simple as PSQL
import qualified UnliftIO.Pool as P
usingConnectionPool :: IO ()
usingConnectionPool = do
poolCfg <- P.mkDefaultPoolConfig (PSQL.connectPostgreSQL "") PSQL.close 5.0 10
pool <- P.newPool poolCfg
runEff $ \ioe ->
BP.runWithConnectionPool ioe pool $ \wc ->
insertAndListCarefully wc ioe >>= effIO ioe . print
```