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, andopaleye.
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)
}
💡
namesFromLabelsgenerates a table schema where every column name corresponds precisely to the field's name. Alternatively, we can usenamesFromLabelsWith.
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
showQueryto 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.
Top comments (0)