Felipe Lessa

Esqueleto now supports IN and EXISTS

Felipe Lessa — Thu, 29 Nov 2012 15:57:04 +0000

Since version 0.2.9 that I’ve just released, esqueleto supports IN and EXISTS operators (and their negated counterparts). For example:

select $
from $ \person -> do
where_ $ exists $
from $ \post -> do
where_ (post ^. BlogPostAuthorId ==. person ^. PersonId)
return person

Enjoy! =)

PS: I’ll try to post more in the future, so keep tuned =).

Announcing esqueleto, a type-safe EDSL for SQL queries

Felipe Lessa — Thu, 06 Sep 2012 22:51:09 +0000

I’m very pleased to announce a preview release of esqueleto, a bare bones, type-safe EDSL for SQL queries.

On the first part of this blog post I’ll talk about persistent and HaskellDB. You may jump right into where I talk about esqueleto if you want, though.

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.

One of those libraries is persistent. It fills the role of communicating with the database: serialization and deserialization of data types, insertions, updates, queries, schema migration, and so on. It currently has both SQL and NoSQL backends, such as persistent-mysql, persistent-postgresql, persistent-sqlite and persistent-mongoDB.

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:

Entity johnId john <- getBy (UniqueEmail john'sEmail)
posts <- selectList [BlogPostAuthorId ==. johnId] []

Or you could do it in one query using the ad hoc Database.Persist.Query.Join.Sql module:

runJoin $ SelectOneMany
{ somFilterOne = [ PersonEmail ==. john'sEmail ]
, somOrderOne = []
, somFilterMany = []
, somOrderMany = []
, somFilterKeys = (BlogPostAuthorId <-.)
, somGetKey = blogPostAuthorId
, somIncludeNoMatch = False
}

Compare to the equivalent SQL query:

SELECT BlogPost.*
FROM Person INNER JOIN BlogPost
ON Person.id = BlogPost.authorId
WHERE Person.email = ?

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

2. ORM
+ Type safety (ala persistent)
– No control over queries
– No proper join support(?, persistent)
+ Abstract
+- High-level

3. Relational algebra
+ Type safety (ala HaskellDB)
+ Great control over queries
+ Good control over abstractions
+ It’s algebra, therefore fits Haskell (tongue in cheek)
– Forgotten (HaskellDB)

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

Let’s remember the first query we did on the beginning of this post:

selectList [BlogPostAuthorId ==. johnId] []

You could write the same query in plain SQL as:

SELECT BlogPost.*
FROM BlogPost
WHERE BlogPost.authorId = ?

With esqueleto, you may say:

select $
from $ \b -> do
where_ (b ^. BlogPostAuthorId ==. val johnId)
return b

Arguably more verbose than persistent’s selectList, but extremely close to the handwriten SQL: just take the return and move it to the top!

Better still, the SQL that esqueleto generates is:

SELECT BlogPost.id, BlogPost.title
FROM BlogPost
WHERE BlogPost.authorId = ?

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

Remember the one-to-many join? Here’s the esqueleto version:

select $
from $ \(p `InnerJoin` b) -> do
on (p ^. PersonId ==. b ^. BlogPostAuthorId)
where_ (p ^. PersonEmail ==. val john'sEmail)
return b

The SQL esqueleto generates for this query is:

SELECT BlogPost.id, BlogPost.title
FROM Person INNER JOIN BlogPost
ON Person.id = BlogPost.authorId
WHERE Person.email = ?

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:

query db $ do
s <- table S.stock
e <- table E.end_of_day
restrict (s!S.stock_id .==. e!E.stock_id .&&.
s!S.ticker .==. constant ticker .&&.
e!E.trade_date .==. constant stockDate)
r <- project (closing_price << e!E.closing_price #
trade_date << e!E.trade_date)
return r

The SQL that HaskellDB generated on 2008 was (adapted):

SELECT closing_price2 AS closing_price,
trade_date2 AS trade_date
FROM (SELECT stock_id AS stock_id2,
trade_date AS trade_date2,
closing_price AS closing_price2
FROM end_of_day AS T1) AS T1,
(SELECT stock_id AS stock_id1,
ticker AS ticker1
FROM stock AS T1) AS T2
WHERE stock_id1 = stock_id2
AND ticker1 = 'FXJ'
AND trade_date2 = '2008-02-14 00:00:00';

His handwritten SQL was (adapted as well):

SELECT e.closing_price AS closing_price,
e.trade_date AS trade_date
FROM
stock s, end_of_day e
WHERE
s.stock_id = e.stock_id
AND s.ticker = 'FXJ'
AND e.trade_date = '2008-02-14 00:00:00';

The same query could be written using persistent and esqueleto as:

select $
from $ \(s, e) -> do
where_ (s ^. StockId ==. e ^. EndOfDayStockId &&.
s ^. StockTicker ==. val ticker &&.
s ^. EndOfDayTradeDate ==. val stockDate)
return (e ^. EndOfDayClosingPrice, e ^. EndOfDayTradeDate)

The generated SQL would be:

SELECT end_of_day.closing_price, end_of_day.trade_date
FROM stock, end_of_day
WHERE stock.stock_id = end_of_day.stock_id AND (stock.ticker = ? AND end_of_day.trade_date = ?)

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.

Thanks for reading this rather long blog post! =)

Abstracting permissions with Yesod

Felipe Lessa — Mon, 16 Jan 2012 11:55:04 +0000

Yesod is a terrific framework for web applications in Haskell. It has many, many built-in features. One of them is that there’s nice support for authentication and authorization. In this post I’m interested in talking about how you could write your authorization code such that it’s harder to make mistakes.

As shown on the recent example of creating a blog web app, Yesod’s approach to authorization lies within the isAuthorized function:

class ... => Yesod a where
...
isAuthorized :: Route a -> Bool -> GHandler s a AuthResult
...

So isAuthorized takes a Route a, such as EntryR 10, and a Bool telling if the request may do writes (e.g. POST or PUT) or not (e.g. GET or HEAD). It must return an AuthResult that decides if the user is Authorized, Unauthorized or if he needs to be authenticated first (AuthenticationRequired).

While keeping authorization code in a single place is nice, using isAuthorized alone makes it very difficult to test your authorization code. Using the blog example again, only the admin should be able to create blog posts. So let’s take a look at the example code that decides if the user should be authorized to post to the blog:

isAuthorized BlogR True = do
mauth <- maybeAuth
case mauth of
Nothing -> return AuthenticationRequired
Just (_, user)
| isAdmin user -> return Authorized
| otherwise -> unauthorizedI MsgNotAnAdmin

There are many things going on here:

There’s authentication code that checks if the current user is logged in via maybeAuth.
If the user is not logged in, it returns AuthenticationRequired.
Otherwise the user’s credentials are checked to see if he should be authorized or not.

Step 1 is pretty standard housekeeping-style code. Steps 2 and 3, however, are part of your business logic that decides who should be able to do what. This means that you should be able to create (a) an unit test that asserts that the admin can create blog posts and (b) an unit test that asserts that a non-admin can’t create blog posts. Unfortunately, with the code above writing unit tests is really difficult. You not only need to artificially run the GHandler monad (boring), but you need to fake the current session so that Step 1′s maybeAuth gets the right information (difficult). Even then, your isAuthorized function is allowed to do anything, since it’s inside GHandler.

Enter permissions. What we’re really trying to say in the code above is that (a) to create a blog post you need the “post” permission and (b) admin has “post” permission, non-admins don’t. So let’s split these things! First of all, we need a list of the permissions that we’ll need:

data Permission = Post | Comment

You should read these constructors names with “permission to” before their names. For example, the admin has permission to Post and any logged user has permission to Comment.

Each request needs to decide which permissions it requires. This is one of the most important pieces of your application’s security, since forgetting to ask for permissions could lead to catastrophic problems. Instead of having this core piece of your app diluted in isAuthorized, we use a simple, clear, pure function called permissionsRequiredFor. The idea is to make permissionsRequiredFor as simple as possible, such that with code review alone you could determine if you’re asking for the right permissions.

permissionsRequiredFor :: Route Blog -> Bool -> [Permission]
permissionsRequiredFor BlogR True = [Post]
permissionsRequiredFor (EntryR _) True = [Comment]
permissionsRequiredFor _ _ = []

We also need to decide if the currently logged user has the necessary permissions or not. This is the other important piece of your authorization puzzle, and a piece that we need to make easily testable. In order to do so, we avoid maybeAuth and take the user as an argument.

hasPermissionTo :: (UserId, User)
-> Permission
-> YesodDB sub Blog AuthResult
(_, user) `hasPermissionTo` Post
| isAdmin user = return Authorized
| otherwise = lift $ unauthorizedI MsgNotAnAdmin
_ `hasPermissionTo` Comment = return Authorized

isAuthorizedTo :: Maybe (UserId, User)
-> [Permission]
-> YesodDB sub Blog AuthResult
_ `isAuthorizedTo` [] = return Authorized
Nothing `isAuthorizedTo` (_:_) = return AuthenticationRequired
Just u `isAuthorizedTo` (p:ps) = do
r <- u `hasPermissionTo` p
case r of
Authorized -> Just u `isAuthorizedTo` ps
_ -> return r -- unauthorized

The hasPermissionTo function decides if the user has a given permission or not. While in this example hasPermissionTo could have been a pure function, in general you may need to access the database. The isAuthorizedTo function then (a) decides if the user needs to be authenticated and (b) checks all permissions required from the list using hasPermissionTo.

Finally, we need to implement isAuthorized gluing everything together:

isAuthorized route isWrite = do
mauth <- maybeAuth
runDB $ mauth `isAuthorizedTo` permissionsRequiredFor route isWrite

Ok, so what did we gain by writing three more functions?

You can easily review permissionsRequiredFor to see if you didn’t leave a restricted route in the open.
If you don’t use the wildcards on the last line of permissionsRequiredFor and instead list all of your routes one by one, then you’d get a compiler warning and a runtime error every time you forgot to set the permissions of a newly added route.
If you have many routes that needed the same permissions, you don’t need to recode the permission code everywhere. You just need to code it once on hasPermissionTo and then ask for that permission on each of your routes. In my experience, the set of permissions (i.e. the Permission data type) is a lot smaller than the set of possible routes.
You may easily create unit tests for hasPermissionTo, increasing your confidence on your code’s correctness.

I should also note that this approach is easily extendable. For example, suppose that you wanted to restrict the visibility of some of your blog posts. You could change the Permission data type into:

data Permission = Post | CommentOn EntryId | View EntryId

Now your hasPermissionTo function is able to give a different answer depending on which blog post we’re talking about.

So far this approach has been successfully used on my day job’s Yesod application. It looks like a cousin of Yesod’s i18n support using data types.

Thanks for reading along this far! Please use the comment section below to say what think of this approach. =)