More Generic SQL (12)

In the last post, we made a bunch of changes throughout the codebase to support an additional field.

Some of those changes are fairly mechanical: for example, when we SELECT a Registration out of the database, we always need to request all of the fields in a Registration (else we can't give sensible values for every parameter of the constructor).

Some of this work is done already in postgresql-simple. As long as we include the all the fields in the right order in a SELECT, then the FromRow typeclass can figure out how to turn those fields into a single Registration value. The code already does that at every SELECT.

But we still need to include all the fields. This should be automatable. And indeed it is, using postgresql-simple-sop.

There isn't much to this: it gets the list of fields in a specified datatype, and sticks them in a SELECT statement for you.

This is the first package we're going to encounter that is neglectware: last updated on hackage in 2015, and until a few days ago, the git version wouldn't build against the stack snapshot that I'm building. That last problem is fixed as of last weekend, and so we can get the package from github (rather than from a curated stack snapshot). (actually there's been a bunch of other development buried in a branch of a fork in a previous employer's github repo, but it never got merged to release/master)

To do that, add a dependency for postgresql-simple-sop for the reg-exe executable. This will give a build error about the stack configuration has no specified version - because there isn't one in the stack snapshot I'm using.

So tell stack to download the dependency from github, in extra-deps in stack.yaml, alongside the two existing extra dependencies:

extra-deps:
- digestive-functors-0.8.3.0
- digestive-functors-blaze-0.6.2.0
- git: https://github.com/openbrainsrc/postgresql-simple-sop
  commit: d1834bda124081ad6a39fd4849a40d4b2fa74b3e

So in addition to (previously) telling stack that yes we want it to use those specific versions of two digestive functor packages, we've specified which package we want for postgres-simple-sop as a specific git version. (you could also write master there instead of a commit, to use the latest on a master branch. But magically updating dependencies is troublesome in the long run.

Now instead of this:

PG.query
  conn
  "SELECT firstname, lastname, dob, swim FROM registration WHERE id = ?"
  [identifier]

... we can instead write:

We can now write things like this:

import Database.PostgreSQL.Simple.SOP as PGS
...
PGS.gselectFrom conn "registration where id = ?" [identifier]

... although to allow this new library to work we have to add some more instance declarations on Registration:

import qualified Generics.SOP as GS
import qualified Database.PostgreSQL.Simple.SOP as PGS

...

instance GS.Generic Registration
instance GS.HasDatatypeInfo Registration
instance PGS.HasFieldNames Registration

... with generics-sop added as a dependency in package.yaml.

generics-sop is a different implementation of generics (different from GHC.Generics) that is nicer to use if you're writing generic code (rather than using it, as we are here).

HasFieldNames is a typeclass that says we can get an SQL field name for each entry in the Registration type. Because we don't specify any actual implementation code, the default will use generics-sop to generate them from the names used in the Haskell declaration. We don't have to do it that way though, and can specify a different implementation, if the names don't align. (I was expecting this all along so I was secretly careful to call my SQL field names the same as my Haskell field names).

There are three instances of SELECT to be replaced in the above style.

It would be nice if you could do this for the single UPDATE too, but the master version of postgres-simple-sop doesn't provide that. So next post, I'll dig into what is happening inside a bit more, and write my own UPDATE variation of gselectFrom.