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)