Escalating Esqueleto

Mercury’s Engineering Training team strongly believes in the power of exercises. Actually trying something for yourself is a great way to discover any rough edges in your understanding of how it works.

Esqueleto is a library that lets us write SQL queries with Haskell syntax and typechecking. It’s often a bit of a sticking point for Haskell beginners in our codebase, which I’ll go into more below.

For these reasons, we’re announcing and publicly releasing Escalating Esqueleto, a collection of exercises aimed at helping beginners practice with the library. Our goal was to get our engineers to be able to see through any Esqueleto query to the SQL underneath. Call it esq-ray vision.

We’ve used these exercises internally with 15+ engineers so far, but expect them to continue to evolve as we change how we use Esqueleto, and as we update our teaching methods. If you run through the exercises yourself, we’d welcome your feedback at [email protected], or via issues in the GitHub repo.

Exercise structure

Each exercise comes with incomplete starter code, a test to check your answers, a hint providing more context, and an answer with explanatory comments. There are several sets of exercises organized into different modules, and each attacks the problem of learning Esqueleto from a different angle.

1. The first set EE1_Select only requires working with a single table in each query. It introduces WHERE clauses, unwrapping Values, Esqueleto queries that take arguments, and a common gotcha with the way SQL’s NULL relates to Haskell’s Nothing.
2. EE2_Join introduces JOIN clauses, and so requires keeping track of how multiple tables interact. Otherwise it just tries to ramp up the query difficulty a bit, introducing GROUP BY and so on.
3. EE3_TypeZoo switches things up to focus on another tough aspect of learning Esqueleto—the types. While I think it’s smart to first learn Esqueleto as a sort of "syntax translation" process from SQL to Haskell, having a grounded understanding of the types involved really helps with more difficult work, like debugging complex queries with inscrutable error messages. For example, an understanding of what SqlQuery and SqlExpr are (including how they work, and where they can be used) goes a long way.
4. Finally, EE4_Errors focuses directly on reading and understanding error messages. In this module, it’s important to slow down and attempt to fully understand what any given error is telling you, before fixing it. The biggest benefit I’ve seen people take away from this is in understanding code they didn’t write, where it takes some extra work to build up a correct mental model.

Why Esqueleto is tricky to learn

I’ve helped teach Haskell to nearly 50 of Mercury’s engineers at this point, mostly through one-on-one mentorship, and have noticed some patterns in the ways Esqueleto is difficult for new learners. It was important to me to address these as much as possible through the exercises themselves, but I still recommend going over your exercise answers with someone experienced if possible. They can help unlock deeper understanding by pinpointing where your confusions lie.

Note that one way to get a dedicated Haskell mentor is to join Mercury.

Nested do blocks

Esqueleto queries often have two parts, and I’ve found beginners can get lost as to which context they’re working in.

query :: DB [r]
query = do
  values <- select $ do
    someSqlQuery               -- part 1
  someValuesComputation values -- part 2

This is normally cleared up by asking "which monad is each do block for?"—the outer one is DB and the inner one is SqlQuery. If someone doesn’t know what SqlQuery is, it’s a good chance to explain (or have them look it up on Hoogle).

Functional dependencies

Esqueleto relies on functional dependencies, which can make certain type errors seem to be happening far away from their actual sources. The most important one shows up in SqlSelect:

class SqlSelect a r | a -> r, r -> a

The -> arrow syntax to the right of the | is telling us that the type a determines r, and also that the type r determines a. For beginners, I like to use the catchphrase “a and r depend on each other”.

runDB and monad transformers

Often our engineers are learning Esqueleto at an early-intermediate stage in learning Haskell generally, so aren’t totally comfortable with monad transformer stacks. Escalating Esqueleto uses a simplified, non-production-ready DB monad for pedagogical purposes.

We start with a Reader of a SqlBackend over IO. Transforming IO directly brings some simplicity, where a production version of DB might have more intermediate steps.

type DB = ReaderT SqlBackend IO

SqlPersistT is an existing synonym for ReaderT SqlBackend.

type DB = SqlPersistT IO

Finally, we add on the ability to do logging, so we can print out rendered SQL. Seeing the generated SQL output is helpful when you’re working through exercises.

type DB = SqlPersistT (LoggingT IO)

This implementation of a DB monad allows execution of arbitrary IO actions. Our code is also not thread-safe, and ideally would use a Pool of connections rather than just one. However, a simplified DB lets learners understand what’s going on more effectively, and usage of runDB with a more-complex real-world DB implementation looks identical.

Making sense of unknown types

A useful trick for understanding complex type signatures is to simplify them until they start making intuitive sense, then add complexity back in as needed. A typical function to start with is select, since it shows up quite often in queries.

select :: (SqlSelect a r, MonadIO m, SqlBackendCanRead backend)
       => SqlQuery a -> ReaderT backend m [r]

ReaderT backend is the same as SqlPersistT, which lets us hide the SqlBackendCanRead constraint from view.

select :: (SqlSelect a r, MonadIO m) => SqlQuery a -> SqlPersistT m [r]

SqlPersistT m is the same as DB, which lets us hide MonadIO.

select :: SqlSelect a r => SqlQuery a -> DB [r]

Referring back to the functional dependency here, we can remove the final constraint by replacing it with an intuition about how a and r are related.

select :: SqlQuery a -> DB [r] -- where a and r depend on each other

In English, select takes a SqlQuery and returns a list of rows in a DB action.

The zoo of types and functions

When you hoogle for functions like select, you see two versions of the library: Database.Esqueleto and  Database.Esqueleto.Experimental. We’re using the Experimental syntax in our codebase (and in these exercises), and this is usually cleared up by teaching people to look in the right place. Hoogle lets you scope down to just the experimental module with a query like Database.Esqueleto.Experimental.select is:exact.

However, even once someone is sure they’re finding the right type signatures, there are a lot of pieces to sift through. For example, the ToFrom typeclass does useful work inside Esqueleto’s machinery, but beginners don’t need to know how it works internally.

from :: ToFrom a a' => a -> SqlQuery a'

Because where is a Haskell keyword, there’s no way to use the exact same keywords as SQL. Esqueleto’s fix is lightweight—a postfix underscore—but this is yet another thing to remember.

where_ :: SqlExpr (Value Bool) -> SqlQuery ()

The semantics of where_ are also different from WHERE. The prime example of this is that a SqlQuery do block can contain multiple where_ lines!

Often, values inside a query need to be lifted into a SqlExpr context. Learning about val gets you most of the way there, but it’s surprising for where_ $ 1 == 1 not to work when you try it. For operators like ==, this means a . postfix is needed to get them to work in SqlExpr land.

val :: PersistField typ => typ -> SqlExpr (Value typ)

(==.) :: PersistField typ
      => SqlExpr (Value typ) -> SqlExpr (Value typ) -> SqlExpr (Value Bool)

In general, there is some unavoidable complexity when trying to port so many SQL features into another syntax.

Closing thoughts

Esqueleto presents a few unique teaching challenges. It’s fundamentally important for our backend engineers to be fluent in talking to the database. Writing these queries comes up regularly in tickets, even fairly early on. However, “early on” in learning Haskell is often a point where functional dependencies, monad transformer stacks, and maybe even typeclasses aren’t totally familiar yet.

These exercises are intended as a way to bridge that gap, and I’m looking forward to making them better in the future.