How to use PostgreSQL with Haskell: rel8
Tags: haskell, functional, postgres, database
Published: 2023-10-03 03:31:14

*Okay, what if we did something quite similar but quite different?*

Rel8 “*is a Haskell library for interacting with PostgreSQL databases”*, which aims to be concise,  inferrable, and familiar.

For the database connection, instead of `postgresql-simple`, `rel8` uses Hasql.

Install `rel8` (`1.4.1.0` released in 2023), `hasql`, and `hasql-transaction`. 

We bring back the `TypeFamilies` extension and (in case you haven’t already) `DuplicateRecordFields`. The latter is required to disambiguate the record fields when working with inserts, updates, and deletes…

> 💡 We assume you’ve seen the parts on `postgresql-simple`, `hasql`, and `opaleye`.

### How to connect to a database

We use Hasql. Reminder:

```haskell
Right connection <- getConnection
```

```haskell
getConnection :: IO (Either ConnectionError Connection)
getConnection =
  acquire $ settings Hardcoded.host Hardcoded.portNumber Hardcoded.user Hardcoded.password Hardcoded.database
```

### How to define tables

First, we describe the structural mapping of the tables. Take for instance `Product`:

```haskell
newtype ProductId = ProductId Int64
  deriving newtype (DBEq, DBType, Eq, Show)

data Product f = Product
  { id :: Column f ProductId
  , label :: Column f Text
  , description :: Column f (Maybe Text)
  }
  deriving (Generic)
  deriving anyclass (Rel8able)

deriving stock instance (f ~ Result) => Show (Product f)
```

We define fields with  `Column` and derive the `Rel8able` instance. We also declare a newtype for product id with a few instances. 

Imagine that the last line is just `deriving (Show)`.

Then, we describe a `TableSchema` for each table. The relevant table looks like this:

```haskell
productSchema :: TableSchema (Product Name)
productSchema =
  TableSchema
    { name = "product"
    , schema = Nothing
    , columns =
        Product
          { id = "id"
          , label = "label"
          , description = "description"
          }
    }
```

Note that defining columns looks repetitive — we can use some generics machinery to get that information from the `Rel8able`:

```haskell
productSchema :: TableSchema (Product Name)
productSchema =
  TableSchema
    { name = "product"
    , schema = Nothing
    , columns = namesFromLabels @(Product Name)
    }
```

> 💡 `namesFromLabels` generates a table schema where every column name corresponds precisely to the field's name. Alternatively, we can use `namesFromLabelsWith`.

See the [repo](https://github.com/Zelenya/elephants) for the rest of the boilerplate.
### How to modify data

For raw queries, we can use Hasql:

```haskell
cleanUp :: Connection -> IO (Either QueryError ())
cleanUp connection = run cleanUpSession connection
 where
  cleanUpSession = statement () $ Statement rawSql E.noParams D.noResult True
  rawSql = "truncate warehouse, product_category, product, category"
```

Otherwise, we create `Insert`:

```haskell
insert1 :: Statement () [ProductId]
insert1 =
  insert
    $ Insert
      { into = productSchema
      , rows =
          values
            [ Product unsafeDefault "Wood Screw Kit 1" null
            , Product unsafeDefault "Wood Screw Kit 2" (lit $ Just "245-pieces")
            ]
      , returning = Projection (.id)
      , onConflict = Abort
      }
```

We’ve seen this in Opaleye’s insert: the table, rows to insert, conflict-handling strategy, and what to return. 

We use `unsafeDefault` for sql `DEFAULT`, [`lit`](https://hackage.haskell.org/package/rel8-1.4.1.0/docs/Rel8.html#v:lit) to turn Haskell values into expressions, and [`values`](https://hackage.haskell.org/package/rel8-1.4.1.0/docs/Rel8.html#v:values) to construct a query out of the given rows.

---

💡 Note that [`unsafeDefault`](https://hackage.haskell.org/package/rel8-1.4.1.0/docs/Rel8.html#v:unsafeDefault) is named unsafe for a reason; see the docs.

---

And run this like any other Hasql statement:

```haskell
result1 <- run (statement () insert1) connection
```

If we want to return the number of affected rows, we can use `NumberOfRowsAffected`:

```haskell
Insert
  { into = categorySchema
  , rows =
      values
        [ Category unsafeDefault "Screws"
        , Category unsafeDefault "Wood Screws"
        , Category unsafeDefault "Concrete Screws"
        ]
  , returning = NumberOfRowsAffected
  , onConflict = Abort
  }
```

### How to query data

We build select statements using `Query`. We select all rows from a table using `each` and turn (run) the query into `Statement` using `select`:

```haskell
select1 :: Statement () [Product Result]
select1 = select $ each productSchema
```

> 💡 Note that we can use `showQuery` to print sql queries that will be executed.

And once again we run the statement:

```haskell
result1 <- run (statement () select1) connection
```

We can select specific fields and narrow down the results:

```haskell
select2 :: Statement () [(Text, Maybe Text)]
select2 = select $ do
  p <- each productSchema
  where_ $ p.label ==. "Wood Screw Kit 2"
  pure (p.label, p.description)
```

We use `where_` to filter the rows and `==.` to compare for equality. We can also use `in_`:

```haskell
select3 :: Statement () [Text]
select3 = select $ do
  p <- each productSchema
  where_ $ p.label `in_` ["Wood Screw Kit 2", "Wood Screw Kit 3"]
  pure p.label
```

*Note that the order of parameters is different from Opaleye.*

### How to use transactions

We use Hasql for transactions:

```haskell
insertWithTransaction :: Connection -> IO ()
insertWithTransaction connection = do
  result <- run (transaction Serializable Write insertAll) connection
  putStrLn $ "Insert with transaction: " <> show result
 where
  insertAll = do
    productIds <-
      Transaction.statement ()
        $ insert
        $ Insert
          { into = productSchema
          , rows = values [Product unsafeDefault "Drywall Screws Set" (lit $ Just "8000pcs")]
          , returning = Projection (.id)
          , onConflict = Abort
          }

    -- insert category
    -- insert mapping
    -- insert warehouse listing
```

### How to query using joins

Rel8 doesn’t have a specific join operation — we use `where_` (or `filter`) to filter the results and `optional` to do what outer joins do.

```haskell
queryWithJoins :: Connection -> IO ()
queryWithJoins connection = do
  result1 <- run (statement () join) connection
  putStrLn $ "Query with join: " <> show result1
 where
  join :: Statement () [(Int32, Text, Maybe Text, Maybe Text)]
  join = select joinQuery

  joinQuery = do
    w <- each warehouseSchema
    p <- productsInWarehouse w
    pc <- optional $ mappingsForProduct p
    c <- traverseMaybeTable categoriesForMapping pc
    where_ $ w.quantity >. 3
    let category = maybeTable null (nullify . (.label)) c
    pure (w.quantity, p.label, p.description, category)

  productsInWarehouse :: Warehouse Expr -> Query (Product Expr)
  productsInWarehouse w =
    each productSchema >>= filter (\p -> p.id ==. w.product_id)

  mappingsForProduct :: Product Expr -> Query (ProductCategory Expr)
  mappingsForProduct p = do
    each productCategorySchema >>= filter (\pc -> pc.product_id ==. p.id)

  categoriesForMapping :: ProductCategory Expr -> Query (Category Expr)
  categoriesForMapping pc =
    each categorySchema >>= filter (\c -> c.id ==. pc.category_id)
```

We extract “each join” into a specialized function to make the code cleaner (according to the Rel8 tutorials). We use `optional` and `traverseMaybeTable` to account for the partiality of queries. `MaybeTable` results from an outer join, which we unwrap with `maybeTable`.

`filter` is an alternative way to write `where` clauses.

The generated query:

```haskell
SELECT
CAST("quantity2_1" AS int4) as "_1",
CAST("label1_3" AS text) as "_2",
CAST("description2_3" AS text) as "_3",
CAST(CASE WHEN ("rebind0_8") IS NULL THEN CAST(NULL AS text) ELSE "label1_12" END AS text) as "_4"
FROM (SELECT
      *
      FROM (SELECT *
            FROM
            (SELECT *
             FROM
             (SELECT
              *
              FROM (SELECT
                    "id" as "id0_1",
                    "product_id" as "product_id1_1",
                    "quantity" as "quantity2_1",
                    "created" as "created3_1",
                    "modified" as "modified4_1"
                    FROM "warehouse" as "T1") as "T1",
                   LATERAL
                   (SELECT
                    "id" as "id0_3",
                    "label" as "label1_3",
                    "description" as "description2_3"
                    FROM "product" as "T1") as "T2"
              WHERE (("id0_3") = ("product_id1_1"))) as "T1"
             LEFT OUTER JOIN
             LATERAL
             (SELECT
              TRUE as "rebind0_8",
              *
              FROM (SELECT
                    *
                    FROM (SELECT
                          "product_id" as "product_id0_6",
                          "category_id" as "category_id1_6"
                          FROM "product_category" as "T1") as "T1"
                    WHERE (("product_id0_6") = ("id0_3"))) as "T1") as "T2"
             ON
             TRUE) as "T1"
            LEFT OUTER JOIN
            LATERAL
            (SELECT
             TRUE as "rebind0_14",
             *
             FROM (SELECT
                   *
                   FROM (SELECT
                         0) as "T1",
                        LATERAL
                        (SELECT
                         "id" as "id0_12",
                         "label" as "label1_12"
                         FROM "category" as "T1") as "T2"
                   WHERE (("id0_12") = ("category_id1_6")) AND (("rebind0_8") IS NOT NULL)) as "T1") as "T2"
            ON
            TRUE) as "T1"
      WHERE (("quantity2_1") > (CAST(3 AS int4))) AND (((("rebind0_14") IS NULL) AND (("rebind0_8") IS NULL)) OR ((("rebind0_14") = ("rebind0_8")) AND (COALESCE(("rebind0_14") = ("rebind0_8"),FALSE))))) as "T1"
```

Which looks similar to the relevant Opaleye query in the previous section.

### Errors

On top of type-safety, according to the docs, *“Rel8 aims to have excellent and predictable type inference”*. And they deliver — type inference rarely needs any guidance, and the compilation errors are pretty good. 

Although it’s possible to introduce runtime errors using unsafe operations like unsafeDefault, the name is explicit, well documented, and has proper alternatives.

Runtime errors come from Hasql — all error-reporting is explicit and is presented using `Either`. As a reminder, violating the constraint returns a familiar error:

```haskell
errors :: Connection -> IO ()
errors connection = do
  Left failure <-
    run insertDuplicateScrew connection
      >> run insertDuplicateScrew connection
  putStrLn $ "Constraint violation (Left): " <> show failure
 where
  insertDuplicateScrew =
    statement ()
      $ insert
      $ Insert
        { into = productSchema
        , rows = values [Product unsafeDefault "Duplicate screw" null]
        , returning = NumberOfRowsAffected
        , onConflict = Abort
        }
```

> `Constraint violation (Left): QueryError "INSERT INTO \"product\" (\"id\",\n \"label\",\n \"description\")\nVALUES\n(DEFAULT,CAST(E'Duplicate screw' AS text),CAST(NULL AS text))" [] (ResultError (ServerError "23505" "duplicate key value violates unique constraint \"product_label_key\"" (Just "Key (label)=(Duplicate screw) already exists.") Nothing Nothing))`
> 

### Resources

Rel8 has the Getting Started tutorial, the Concepts documentation, the cookbook, and good API docs. This would have been one of the best coverages, but unfortunately, some basic snippets (like running selects or constructing inserts) aren’t valid anymore.

Also, you have to keep in mind `hasql`.

### Migrations

Rel8 assumes a database already exists — no support for migrations or creating tables and databases.

### In summary

Rel8 also allows us to write type-safe postgres queries using *concise*, *inferrable*, and *familiar* Haskell code. It builds on top of `opaleye` and `hasql`, and you must be somewhat familiar with the latter.