If you're already using Database.Esqueleto , then you can get started using this module just by changing your imports slightly, as well as enabling the TypeApplications extension.

Below we will give an overview of how to use this module and the features it enables.

This module can be used in conjunction with the main Database.Esqueleto module, but doing so requires qualified imports to avoid ambiguous definitions of on and from , which are defined in both modules.

As a consequence of this, several classes of runtime errors are now caught at compile time. This includes missing on clauses and improper handling of Maybe values in outer joins.

This module is fully backwards-compatible extension to the esqueleto EDSL that expands subquery functionality and enables SQL set operations to be written directly in Haskell. Specifically, this enables:

This module introduces a new syntax that serves to enable the aforementioned features. This new syntax also changes how joins written in the esqueleto EDSL to more closely resemble the underlying SQL.

For our examples, we'll use a schema similar to the one in the Getting Started section of Database.Esqueleto:

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persist| Person name String age Int Maybe deriving Eq Show BlogPost title String authorId PersonId deriving Eq Show Follow follower PersonId followed PersonId deriving Eq Show |]

Example 1: Simple select

Let's select all people who are named "John".

select $ from $ \people -> do where_ (people ^. PersonName ==. val "John") pure people

select $ do people <- from $ Table @Person where_ (people ^. PersonName ==. val "John") pure people

Example 2: Select with join

Let's select all people and their blog posts who are over the age of 18.

select $ from $ \(people `LeftOuterJoin` blogPosts) -> do on (people ^. PersonId ==. blogPosts ?. BlogPostAuthorId) where_ (people ^. PersonAge >. val 18) pure (people, blogPosts)

Here we use the :& operator to pattern match against the joined tables.

select $ do (people :& blogPosts) <- from $ Table @Person `LeftOuterJoin` Table @BlogPost `on` (\(people :& blogPosts) -> people ^. PersonId ==. blogPosts ?. BlogPostAuthorId) where_ (people ^. PersonAge >. val 18) pure (people, blogPosts)

Example 3: Select with multi-table join

Let's select all people who follow a person named "John", including the name of each follower.

select $ from $ \( people1 `InnerJoin` followers `InnerJoin` people2 ) -> do on (people1 ^. PersonId ==. followers ^. FollowFollowed) on (followers ^. FollowFollower ==. people2 ^. PersonId) where_ (people1 ^. PersonName ==. val "John") pure (followers, people2)

In this version, with each successive on clause, only the tables we have already joined into are in scope, so we must pattern match accordingly. In this case, in the second InnerJoin , we do not use the first Person reference, so we use _ as a placeholder to ignore it. This prevents a possible runtime error where a table is referenced before it appears in the sequence of JOIN s.

select $ do (people1 :& followers :& people2) <- from $ Table @Person `InnerJoin` Table @Follow `on` (\(people1 :& followers) -> people1 ^. PersonId ==. followers ^. FollowFollowed) `InnerJoin` Table @Person `on` (\(_ :& followers :& people2) -> followers ^. FollowFollower ==. people2 ^. PersonId) where_ (people1 ^. PersonName ==. val "John") pure (followers, people2)

Example 4: Counting results of a subquery

Let's count the number of people who have posted at least 10 posts

select $ pure $ subSelectCount $ from $ \( people `InnerJoin` blogPosts ) -> do on (people ^. PersonId ==. blogPosts ^. BlogPostAuthorId) groupBy (people ^. PersonId) having ((count $ blogPosts ^. BlogPostId) >. val 10) pure people

select $ do peopleWithPosts <- from $ SelectQuery $ do (people :& blogPosts) <- from $ Table @Person `InnerJoin` Table @BlogPost `on` (\(p :& bP) -> p ^. PersonId ==. bP ^. BlogPostAuthorId) groupBy (people ^. PersonId) having ((count $ blogPosts ^. BlogPostId) >. val 10) pure people pure $ count (peopleWithPosts ^. PersonId)

We now have the ability to refactor this

Example 5: Sorting the results of a UNION with limits

Out of all of the posts created by a person and the people they follow, generate a list of the first 25 posts, sorted alphabetically.

Since UNION is not supported, this requires using rawSql . (Not shown)

Since this module supports all set operations (see SqlSetOperation ), we can use Union to write this query.

select $ do (authors, blogPosts) <- from $ (SelectQuery $ do (author :& blogPost) <- from $ Table @Person `InnerJoin` Table @BlogPost `on` (\(a :& bP) -> a ^. PersonId ==. bP ^. BlogPostAuthorId) where_ (author ^. PersonId ==. val currentPersonId) pure (author, blogPost) ) `Union` (SelectQuery $ do (follow :& blogPost :& author) <- from $ Table @Follow `InnerJoin` Table @BlogPost `on` (\(f :& bP) -> f ^. FollowFollowed ==. bP ^. BlogPostAuthorId) `InnerJoin` Table @Person `on` (\(_ :& bP :& a) -> bP ^. BlogPostAuthorId ==. a ^. PersonId) where_ (follow ^. FollowFollower ==. val currentPersonId) pure (author, blogPost) ) orderBy [ asc (blogPosts ^. BlogPostTitle) ] limit 25 pure (authors, blogPosts)