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.

{-# LANGUAGE TypeApplications #-}

...

import Database.Esqueleto hiding (on, from)
import Database.Esqueleto.Experimental

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:

• Subqueries in JOIN statements
• UNION
• UNION ALL
• INTERSECT
• EXCEPT

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 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.

Below we will give an overview of how to use this module and the features it 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".

Database.Esqueleto:

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

Database.Esqueleto.Experimental:

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.

Database.Esqueleto:

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

Database.Esqueleto.Experimental:

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.

Database.Esqueleto:

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)

Database.Esqueleto.Experimental:

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 JOINs.

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

Database.Esqueleto:

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

Database.Esqueleto.Experimental:

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.

Database.Esqueleto:

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

Database.Esqueleto.Experimental:

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)