Background

Yesod is very modular, and each of its components may be used separately. You may also take libraries from other web frameworks. However, it’s more convenient to use Yesod with its standard set of libraries.

Persistent’s weakness

Even though persistent’s use of Template Haskell has been criticized by some, it’s almost consensual that its largest drawback lies on its query API. For example, if you want to see all of John’s posts, and you know that John’s key on the database is johnId, then you may write:

posts <- selectList [BlogPostAuthorId ==. johnId][]

Even though the meaning of those lists is somewhat cryptic, it’s not a bad line of code. However, what if you didn’t know John’s key, just its e-mail (assuming that there is an uniqueness constraint on e-mails)? Unfortunately, with persistent you’ll need either two queries:

But it gets even worse: you have support only for simple queries (using selectList) and for simple one-to-many joins (using the ad hoc SelectOneMany). If you need anything else then you’re on your own. For instance, there’s no support for doing many-to-many joins.

For these reasons I’ve created the rawSql function 8 month ago. It was nice to be able to write raw SQL queries and still be able to use persistent to deserialize the results, but we still get all the drawbacks of using raw SQL queries:

No compile-time checks whatsoever. You could alleviate this problem using hssqlppp, for example, but you’d still not get compile-time checks for the types of your entities—there would still be plenty of ways of shooting yourself in the foot.

No composability. Suppose you have a query for people and their latest blog post. You can’t reuse this query to reorder the results or filter them.

Zero coolness factor. C’mon, we’re not using Haskell for nothing! =)

HaskellDB: a solution?

HaskellDB is a type-safe EDSL that allows you to write SQL queries using relational algebra. It’s as old as Parsec, having been introduced in 1999!

Recently some people have been showing interest in using it with Yesod. Last month Mats Rauhala wrote the following summary about his opinion at the time on Yesod’s mailing list:

1. Direct sql
– No, or very little type safety
– No, or very little compile time checks
– Raw queries are ugly
+ Full power of SQL

Although it’s rather painful to setup HaskellDB’s tables, I’ll won’t count that as a big drawback since some Template Haskell could certainly solve this shortcoming. Its lack of migration capabilities is not a huge problem, too, since you may use persistent just for the migrations (although I’m not sure if anyone has ever put something like this into production).

HaskellDB’s biggest drawback is being a relational algebra library. “What?”, I hear you say. I like relational algebra as much as the next functional programmer, but it comes with two drawbacks, one of them being a major one:

Being something different than what we’re used to means that it takes some time to learn how to use it and get productive. This is a minor drawback, but nevertheless it is a drawback.

However, the biggest drawback is that it’s very hard to map into efficient SQL. Back in 2008, Geoff Wilson wrote a blog post about HaskellDB’s performance. A simple INNERJOIN was taking between 40x and 160x more time to execute when using HaskellDB and comparing against a handwritten SQL query.

While I’m sure that some work has been done on HaskellDB’s optimizer since that blog post, Chris Done found out last November that it still isn’t very good:

Don’t expect good performance from HaskellDB if you’re using MySQL.

Even if I started using PostgreSQL, I wouldn’t want to rely on its optimizer when doing, say, a join between five tables on HaskellDB. (If you didn’t already know, I’m persistent-mysql‘s author.)

Please don’t get me wrong, HaskellDB is amazing! But it won’t work for my production systems.

Esqueleto rises

Last Sunday my co-worker was bitten by a nasty bug due to a raw SQL query. He changed an entity’s field so that it would be optional. After fixing all type errors, he found out that some parts of the application were not working, but no error messages were to be found anywhere. Turns out an implicit join in a raw SQL query started dropping rows from the result since the value was NULL. After we found the bug, he proceeded to show me Squeryl. My initial thought after seeing the examples was: how could I write this in Haskell?

Thus esqueleto was born. It’s a bare bones, type-safe EDSL for SQL-queries. Like HaskellDB, it has composable, type-checked queries. Unlike HaskellDB, it’s not relational algebra, it’s SQL. I was inspired by Squeryl but created esqueleto from scratch.

It sits on top of persistent and requires no further setup: if you’re already using persistent then you already have everything it takes to use esqueleto. Although I haven’t tested, yet, it should work on any SQL backend.

I’m not kidding! The only difference from the handwritten query is the explicit list of columns, which is needed for correctness (the order your database returns the columns may not be what persistent expects).

Let’s take Geoff Wilson’s post as an example again. Since this is a preview release of esqueleto, it does not have support for IN yet, so I’ll rewrite his code slightly. The HaskellDB code he wrote was:

Conclusion

The full power of raw SQL. Type-checked queries, no type signatures required. Complete control over the resulting SQL. The robustness and performance of persistent. And with only 800 source lines of code (+ 400 SLOC for the test suite). What’s not to like about esqueleto? =D

This is just a preview release. I’m eager to hear what you have to say about it. Send pull requests, open issues, comment about it on reddit or send e-mails to Yesod’s mailing list. Or, even better, give it a spin a let me know how it went! Its Haddock documentation should get you started.

8 Responses to Announcing esqueleto, a type-safe EDSL for SQL queries

It’s very important that these operators are short and readable or whole thing is a quasiquote, if language is a very restricted EDSL then quasiquote is often a LOT more readable than EDSL that invents new ‘==’. I know it might sound like nitpicking but that’s one of the reasons I didn’t use HaskellDB.

Operators matter a lot. I don’t know if we can just use some TH $macro that would process our normally written AST that has normal == and such. I think $tosql(… AST) would be more readable than code sprinkled with ^.

Other ones were that it didn’t support pgsql stored procedures and that there was lots of code to write. Little was maintained, nothing was documented and I found -th package a lot later.

It is possible to write TH code that translates an even more SQL-like language into esqueleto’s operators. However, I’m not sure if that would be a good idea in practice, we’d have to try it out. Perhaps we could provide both interfaces as well. I’ve opened an issue on GitHub but don’t hold your breath https://github.com/meteficha/esqueleto/issues/3 =).