Yesterday we saw how we can use
digestive-functors to perform form validation. However, most of the time we want to persist that form data, often to a database. In todays entry of 24 Days of Hackage, we’ll look at how we can use Leon Smith’s
postgresql-simple to store data in a PostgreSQL database.
postgresql-simple describes itself as a “mid-level PostgreSQL client library, forked from mysql-simple” - and I think this is a great description. It’s not an ORM-style library that will write SQL for you, for that there are tools such as persistent. Rather,
postgresql-simple gives you a rich API to interact specifically with a PostgreSQL database - and as we’ll see in this post, that lets us do some rather neat things.
Let’s start with the basics first though.
Santa’s trying to get organized for Christmas 2012, and has decided that he’s fed up with all the problems in his existing Christmas management software, and is writing some new software to hopefully get a better handle on things. Like any other great mythical figure, he’s chosen to write it in Haskell. Here are the data types he has to work with:
No surprises there. Santa needs to be able to get lists of children and presents out of the database though, and in order to map an SQL row to a Haskell data-type, we can use the
FromRow class has only one associated function -
fromRow :: RowParser a.
RowParser is a
Functor, which makes it a breeze to construct data values. In this case we use the
Monad instances to consume fields of a row (from left-to-right), converting them into the appropriate values. The
field combinator takes a single field from a row, and tries to convert it to a data-type. This means that an SQL
varchar can be mapped to
text, but is also expressive enough to guarantee a field is not null (such as
Armed with our new
FromRow instances, we can pluck things out of the database:
FromRow, there is also a
ToRow type class. As you’d expect, this lets us turn Haskell values into SQL rows, for insertion. I’ll leave these instances as an exercise for the reader!
As I said at the start of the article,
postgresql-simple has a few PostgreSQL specific features. I don’t have time to go over all of them, but one overlooked feature is the
NOTIFY are two PostgreSQL specific queries which let you communicate between server and clients by pushing to clients, rather than having clients poll. In this hypothetical example, we’ll assume we have a
notifySanta :: Text -> IO () function, which lets Santa know that a new Present has been added to a Child’s wishlist via SMS. We could write a daemon that polls the database, but using notifications, we can be much elegant, and performant!
I’ve only scrated the surface of
postgresql-simple - there’s a lot more that it’s capable of. To whet your appetite, there’s also support for PostgreSQL large-objects, sane parameter substitution, joins, rich error exception handling, flexible transaction support (with isolation level and automatic retrying for serializable transactions), and a left fold for incrementally streaming results.
If only delivering all those presents was as easy as writing this code…
You can contact me via email at firstname.lastname@example.org or tweet to me @acid2. I share almost all of my work at GitHub. This post is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License.
I accept Bitcoin donations:
14SsYeM3dmcUxj3cLz7JBQnhNdhg7dUiJn. Alternatively, please consider leaving a tip on