A new release of postgresql-simple is now available. Version 0.1 introduces some breaking changes as well as some significant improvements to the interface. I’ll cover the biggest and best change in this post, for other changes you should read the changelog.

A few months ago, Ozgun Ataman suggested that I add some convenience code to simplify writing instances of QueryResults . I ended up liking his suggestion so much I decided to dig deeper into postgresql-simple and rebase the implementation around his interface. This allowed me to eliminate some intermediate data structures, and has some other advantages I’ll get to later. I’m rather pleased with the end result; here is a contrast of the old and the new:

class QueryResults a where convertResults :: [Field] -> [Maybe ByteString] -> Either SomeException a instance (Result a, Result b) => QueryResults (a,b) where convertResults [fa,fb] [va,vb] = do !a <- convert fa va !b <- convert fb vb return (a,b) convertResults fs vs = convertError fs vs 2 -- RowParser has instances for Applicative, Alternative, and Monad class FromRow a where fromRow :: RowParser a -- field is exported from Database.PostgreSQL.Simple.FromRow field :: FromField a => RowParser a instance (FromField a, FromField b) => FromRow (a,b) where fromRow = (,) <$> field <*> field

The new interface not only eliminates a significant amount of syntactic overhead, it also allowed me to automatically force the converted values to WHNF, which eliminates the old caveats about writing QueryResults instances. (Note that there are (still) caveats to writing FromField instances, even if they weren’t documented before.) And perhaps more interestingly, it enables new forms of composition. For example, the Types module defines a pair type for composing FromRow instances as follows:

data a :. b = a :. b infixr 3 :. instance (FromRow a, FromRow b) => FromRow (a :. b) where fromRow = (:.) <$> fromRow <*> fromRow

Here’s an example of how you might use this. Let’s pretend we have a simple schema for that might be used for a (small) website where people can post stories and rate them from 0-9:

CREATE TABLE users (uid int not null, username text not null); CREATE TABLE posts (pid int not null, uid int not null, content text not null); CREATE TABLE ratings (pid int not null, uid int not null, score float4 not null);

Then, here’s some example boilerplate that could be used to represent (part of) the schema in Haskell:

data User = User { u_uid :: Int, username :: Text } instance FromRow User where fromRow = User <$> field <*> field data Post = Post { p_pid :: Int, p_uid :: Int, content :: Text } instance FromRow Post where fromRow = Post <$> field <*> field <*> field type Score = Float

And finally, here is an example of how one could get the data out of the database in a form almost ready to generate a web page:

rows :: [User :. Post :. Only Score] <- query conn [sql| SELECT u.*, p.*, avg(r.score) as avg_score FROM users as u JOIN posts as p JOIN ratings as r ON u.uid = p.uid AND p.pid = r.pid GROUP BY p.pid ORDER BY avg_score DESC |] () forM rows $ \(user :. post :. Only score) -> do -- generate the HTML for a single post here

Now of course, this isn’t meant to be a serious sketch of such a post/rating system; there are plenty of performance problems that are perfectly obvious to me, and maybe to you too. This is just to give you some ideas about how this functionality might be used: for example, to deal with (a limited class of) joins, or to deal with a computed column that’s been affixed to the table.

Some unresolved issues at this point would be dealing with natural joins and outer joins. I suspect that an adequate solution to these problems may require more breaking changes to this interface. In any case, multiple people have been asking for this kind of functionality, and I do think this interface is a step in the right direction.

For questions, comments, or suggestions relating to postgresql-simple, I suggest joining database-devel, a new mailing list pertaining not only to postgresql-simple, but also database development in Haskell in general. Also I’m often willing to answer questions if you happen to catch me on Freenode.