Our instinct as Haskell programmers is to follow the types around and make sure the way we’re composing things works. Normally, this makes sense.
However, in the case of translating SQL to Esqueleto syntax, I find it’s often easier to view the Haskell code as just the end product of some indifferent translation mechanism (translate :: SQL -> Haskell), which doesn’t have very helpful types until it’s completed the translation. This definitely has drawbacks, such as making it harder to iteratively add things, but if you already have your SQL query handy, then translating it to Esqueleto becomes a purely syntactic game of shuffling terms around.
For convenience, I’ll be using the example schema from https://hackage.haskell.org/package/esqueleto-3.3.3.2/docs/Database-Esqueleto.html, but hopefully the exact schema won’t matter too much here. It looks something like this:
Person
name String
age Int Maybe
deriving Eq Show
BlogPost
title String
authorId PersonId
deriving Eq ShowI’m also using some example queries taken directly from that page. Mostly what I’ve done here is just adding a bit more description to how they get translated, and color-coding for clarity. Hopefully, this means that if I’ve made any mistakes, the Esqueleto docs can clear them up.
Let’s start with a basic, yet common, query. I’ve color-coded each of the elements so it’s easier to follow along with where they move to.
SELECT * FROM Person;
SELECT becomes select, and FROM becomes from, of course. But the order is weird, as both come at the start in esqueleto. We’ve also inserted some $, and added a hole for what to add next.
select $ from $ _
What comes next is the relevant tables. In this case, Person is the only table we care about. We make a function from the relevant tables, to the result. This means making a lambda from person to the result. Right now, our result is just an empty do block.
select $ from $
\person -> do
_
What should our result be? In this case, our SQL query returns *. But what does * really mean? We can think of it as Person.*, or “every column of the Person table”. In Haskell, we represented the Person table with person (the argument to our lambda), so our result here is just returning person again.
select $ from $
\person -> do
return person
Here’s a similar query, but with an added WHERE clause. Since it’s fairly straightforward to know what keywords like SELECT and FROM map to in Esqueleto, I’ll save on continuing to color them.
SELECT * FROM Person WHERE Person.name = “John”;
This query has the same general outline as the last one, but we’ve added a _ hole for the where clause.
select $ from $
\person -> do
_
return person
Let’s focus on the where clause specifically.
WHERE Person.name = “John”
WHERE translates to where_, and = translates to ==., with two holes to fill in now
where_ ( _ ==. _ )
For the orange hole, we’re looking for the exact string "John". However, Esqueleto’s values are not quite the same as Haskell values. To lift the text "John" into an Esqueleto context, we need to use val "John"
where_ ( _ ==. val “John”)
There are other ways to lift Haskell values into an Esqueleto context. For example, we can use just x as a convenient replacement for val (Just x) when lifting a Maybe.
This is a nice segue into filling in the green hole. We know that we want to translate Person.name. This is a “projection” of a Person to a name.
As with just and val, Esqueleto tries to be nice, and provides different projection operators for when something may exist (?.) and when something is known to exist (^.), because in SQL queries we often have to deal with possibly-nonexistent data.
We can resolve that here by looking at the schema.
Person
name String
age Int MaybeBecause name is not a Maybe, we use the operator ^.. If instead we were looking for Person.age, we would use ?..
Our final translation of Person.name is person ^. PersonName. We get person from our lambda introducing the tables we’re using, and we get PersonName from the database schema, which says that we have a projection from Person to a String called name.
where_ (person ^. PersonName ==. val “John”)
Now we can slot the where clause back into our overall query, to get this final translation:
select $ from $
\person -> do
where_ (person ^. PersonName ==. val “John”)
return person
Now let’s try a query involving more than one table.
SELECT * FROM Person INNER JOIN BlogPost ON Person.id = BlogPost.authorId;
Again, the general query structure is very similar. Note that the second hole is in the same place as the hole for the WHERE clause was in the last example, inside the do block. This is also where we’d put other clauses, like orderBy and groupBy, though we won’t need those two here. Here, we’ll be using on.
select $ from $
\ _ -> do
_
return _
Remember that the first hole is where our “table information” goes. Because we have more than one table now, we’ll need to express that our multiple tables are joined somehow. In this case, we can do that with InnerJoin. There’s also LeftOuterJoin, FullOuterJoin, etc., corresponding to the various types of joins in SQL.
select $ from $
\(person `InnerJoin` blogPost) -> do
_
return _
* in SQL can represent quite a few different things, so it can be hard to translate. In this example, it represents all the information from both the person and blogPost tables, which we can represent by the tuple (person, blogPost).
select $ from $
\(person `InnerJoin` blogPost) -> do
_
return (person, blogPost)
Now we’re left with just the ON clause to translate.
ON Person.id = BlogPost.authorId
As with WHERE, let’s translate this clause separately and then paste it back into the big query. ON becomes on, and = becomes ==.
on ( _ ==. _ )
We have Person.id and BlogPost.authorId. Checking the schema, and because we’re using InnerJoin, neither of these should be Maybe. So we can use ^. to project these fields.
on (person ^. PersonId ==. blogPost ^. BlogPostAuthorId)
Now we put this on clause back where it belongs in the query
select $ from $
\(person `InnerJoin` blogPost) -> do
on (person ^. PersonId ==. blogPost ^. BlogPostAuthorId)
return (person, blogPost)