How to use PostgreSQL with Haskell: rel8

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:

Right connection <- getConnection

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:

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:

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:

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 for the rest of the boilerplate.

How to modify data

For raw queries, we can use Hasql:

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:

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 to turn Haskell values into expressions, and values to construct a query out of the given rows.

---

💡 Note that unsafeDefault is named unsafe for a reason; see the docs.

---

And run this like any other Hasql statement:

result1 <- run (statement () insert1) connection

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

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:

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:

result1 <- run (statement () select1) connection

We can select specific fields and narrow down the results:

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_:

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:

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.

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:

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:

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.