Exporting as CSV (8)

So far we've built a pretty lame way for people to fill out their registration forms (as long as they know the URL their a pre-created registration...).

Once that's all happened, it would be nice to do something with the data. One of the main ways I want to do this with the real life version of this booking system is send it on to various other non-geek humans to fiddle with as they please in their favourite spreadsheet app.

The cheap and cheerful way to do this is to dump everything out in one big CSV file (and leave it to some other poor sucker to deal with the inevitable encoding problems etc). It turns out there really isn't much to build on top of what already exists to make that happen - there is already cassava to handle CSV in Haskell, and the almost inevitable servant-cassava that does what it says in the package name.

Add servant-cassava and cassava as dependencoes in package.yaml and:

import Servant.CSV.Cassava as SC

...
type CSVAPI = "admin" :> "csv" :> S.Get '[SC.CSV] [Registration]

This declares a URL path /admin/csv which we can GET - so use as a regular URL in a browser or wget. What's different?

Multiple literal path components - subdirectories if you want to pretend the URL space is backed by a posix-like file system. Just string a bunch of those in a row.

The two type parameters for Get are different:

Previously we've always had SB.HTML as the first type parameter, meaning that the wire-side content type will be text/html . This is how a browser knows that it should try rendering the response as HTML. This has changed to a new content type text/csv provided by servant-cassava, which will tell your browser that what is coming over the wire is a CSV file. That's how it knows to do whatever it does: perhaps offer you the chance to open in a spreadsheet app or save to disk, rather than trying to render it as HTML.

The second type parameter has changed too: previously we specified B.Html meaning that a Haskell handler for this API will return a B.Html value. This is because we've been generating the structure of the HTML ourselves; all servant(-blaze) has had to do is convert that into a wire format and send it. In the admin/csv endpoint, we' going to return a list of Registrations from our handler - something much closer to our application - and we're not going to write any "rendering" / "conversion" code inside the handler. Instead that conversion will happen inside servant-cassava.

import qualified Data.Csv as CSV
[...]
handleCSV :: S.Handler [Registration]
handleCSV = 
 liftIO $ bracket 
    (PG.connectPostgreSQL "user='postgres'")
    PG.close
    $ \conn -> do
      PG.query
        conn
        "SELECT firstname, lastname, dob FROM registration" ()

instance CSV.ToNamedRecord Registration
instance CSV.DefaultOrdered Registration

This will SELECT all the rows in the database (like previous SELECT queries but without a WHERE clause to filter, and return a list of those rows.

cassava needs to know how to turn a Registration into a CSV row, but there is an "obvious" way to do it and those two instance declarations says "do it the obvious way".

And that's all there is to it. Hit localhost:8080/admin/csv and you'll get a CSV file back...

... although it might have a bit of a rubbish filename (like csv without a .csv on the end like you're probably used to.

Next post I'll fix that.