Which Haskell database library manages complexity best in 2022?
Now that I’ve used Esqueleto professionally in anger, I would understand anyone who used it and then swore off static types forever. - @ttuegel
Seeing this quote got me thinking about writing this very node again since I:
- frequently use Esqueleto
- have used Beam in the past
- have looked on longingly at rel8 (built on top of Opaleye)
- Recently annoyed with complexity while trying to teach others to use soon to be default Esqueleto.Experimental
I had the thought on a mentally slow day where I was struggling to line up types that Esqueleto.Experimental didn’t seem much easier than Beam did and wondered if it could be more complex.
This is my exploration to see if using a complex (rather than simple) example if Esqueleto.Experimental is still the simplest.
Note that I’ll use Microsoft’s northwind database as an example since it’s pretty widely known. If you use NixOS you might be interested in Provisioning a sample database along with postgres in nixos.
Real-world example with some complexity
Typical complexity: Get most expensive product bought in a given order
Modifying a string, inner joins across a couple tables, and an aggregation.
select column_name, data_type, character_maximum_length, column_default, is_nullable
from INFORMATION_SCHEMA.COLUMNS where table_name = 'orders';
select column_name, data_type, character_maximum_length, column_default, is_nullable
from INFORMATION_SCHEMA.COLUMNS where table_name = 'order_details';
select upper(o.ship_name), max(p.unit_price) as expensivest
from orders o
inner join order_details od on od.order_id = o.order_id
inner join products p on p.product_id = od.product_id
group by o.ship_name
order by expensivest desc
limit 5
Esqueleto
define models and enable extensions
{-# LANGUAGE TypeFamilies #-}
{-# LANGUAGE GADTs #-}
{-# LANGUAGE DerivingStrategies #-}
{-# LANGUAGE GeneralizedNewtypeDeriving #-}
{-# LANGUAGE StandaloneDeriving #-}
{-# LANGUAGE UndecidableInstances #-}
{-# LANGUAGE DataKinds #-}
{-# LANGUAGE FlexibleInstances #-}
{-# LANGUAGE MultiParamTypeClasses #-}
{-# LANGUAGE OverloadedStrings #-}
module Models where
import Database.Persist
import Database.Persist.TH
import Data.Int
share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Order sql=orders
Id sql=order_id
shipName String
deriving Show
OrderDetail sql=order_details
Id (Key OrderId) sql=order_id
productId Int64
deriving Show
Product sql=products
ProductId sql=product_id
unitPrice Double
|]
:l Models
now try simply querying a table
a few more extensions and imports apparently
:set -XOverloadedStrings
:set -XTypeApplications
:set -XGADTs
:set -XFlexibleContexts
:set +m
import Database.Persist.Postgresql
:t selectList @Orders [] []
import Control.Monad.Reader
import Control.Monad.Reader
import Control.Monad.Logger
import Database.Esqueleto.Experimental
import qualified Database.Esqueleto.Experimental as E
a runDB helper
runDB = runStdoutLoggingT . withPostgresqlConn "port=5435 dbname=northwind user=postgres" . runReaderT
and now the query with persistent
runDB $ selectList @Orders [] [LimitTo 1]
then the simple query with esqueleto
:{
runDB $ select $ do
orders <- from $ table @Orders
limit 1
pure orders
:}
Now let’s write Typical complexity: Get most expensive product bought in a given order with Esqueleto
:t OrderDetail
:{
runDB $ select $ do
(order :& orderDetail) <- do
from $ table @Order
`innerJoin` table @OrderDetail
`on` (\(order :& orderDetail) ->
orderDetail ^. OrderDetailId E.==. order ^. OrderId)
limit 1
pure (order, orderDetail)
:}
TODO HERE I’m stuck because of a template haskell bug I think, posted issue here https://github.com/yesodweb/persistent/issues/1353
Beam (not building on nixos)
rel8
load the models
:l rel8models.hs
try simple query
tons of imports/extensions
:set +m
:set -XOverloadedStrings
Right conn <- acquire "user=postgres port=5435 dbname=northwind"
import Hasql.Transaction (statement)
import Hasql.Transaction.Sessions (transaction, Mode(Write), IsolationLevel(ReadCommitted))
import Hasql.Connection (acquire)
import Hasql.Session (run)
import Rel8
simple query
:{
(flip run conn) $ do
transaction ReadCommitted Write $ do
statement () $ do
select $ limit 1 $ do
each orderSchema
:}
query with a join
:{
(flip run conn) $ do
transaction ReadCommitted Write $ do
statement () $ do
select $ limit 5 $ do
order <- each orderSchema
orderDetail <- each orderDetailsSchema
products <- each productsSchema
where_ $ orderId order ==. orderDetailsId orderDetail &&. productsId products ==. orderProductId orderDetail
return $ do
maxPrice <- Rel8.max (productUnitPrice products)
return (shipName order, maxPrice)
:}
Currently above incorrect, might want:
https://hackage.haskell.org/package/rel8-1.0.0.0/docs/Rel8.html#v:listAgg
also useful:
<interactive>:(763,9)-(770,45): error:
• Couldn't match type ‘Aggregate (Expr Text, Int64)’
with ‘(Expr (Expr Text), Expr Int64)’
arising from a use of ‘select’
• In a stmt of a 'do' block:
select
$ limit 5
$ do order <- each orderSchema
orderDetail <- each orderDetailsSchema
products <- each productsSchema
where_
$ orderId order ==. orderDetailsId orderDetail
&&. productsId products ==. orderProductId orderDetail
....
In the second argument of ‘($)’, namely
‘do select
$ limit 5
$ do order <- each orderSchema
orderDetail <- each orderDetailsSchema
....’
In a stmt of a 'do' block:
statement ()
$ do select
$ limit 5
$ do order <- each orderSchema
orderDetail <- each orderDetailsSchema
....
Now let’s write Typical complexity: Get most expensive product bought in a given order with rel8
:{
(flip run conn) $ do
transaction ReadCommitted Write $ do
statement () $ do
select $ limit 1 $ do
each orderSchema
:}
squeal (not on nixos)
Ok… I love that this is a deep embedding but it’s wayyy uglier than SQL and so damn noisy :/