A Registration Record (3)

You'll of course remember from the intro that I'm going to be collecting registration info for a scout camp via a web form. In the last couple of posts I put some basic stuff in place on the web facing side of things. In this post, I'm going to build a bit of the database side of things.

For development, I'm only going to ask for a three text fields: first name, last name and date of birth. (you can go read this thing about names, but this is a nice middle class English suburb where everyone conforms, and I'm replicating a paper based form that does names this way) (and yes, I am indeed collecting date of birth as a text field).

While I'm writing this in Haskell, I don't want to exclude other languages and environments. For example, there's a reasonable chance someone might want to point Microsoft Access at this database for building reports in an environment that is familiar to them, rather than using an environment that is familiar to me.

So I'm going to define the persistent data as SQL first, and then afterwards build some code to get it to/from Haskell.

Just like you all know some HTML, I'll assume you all know basic SQL.

I'll also assume that you have the sysadmin chops to install and configure PostgreSQL on your local machine. Although here's a clue:

# apt-get install -y postgresql-9.5 postgresql-server-dev-9.5
# echo "local all postgres trust" | sudo tee /etc/postgresql/9.5/main/pg_hba.conf 

In a file called tables.sql, I'll create a single table called registrations:

CREATE TABLE registration (

    id SERIAL PRIMARY KEY,

    firstname TEXT DEFAULT '' NOT NULL,
    lastname TEXT DEFAULT '' NOT NULL,
    dob TEXT DEFAULT '' NOT NULL
);

... and put that in the default database like this:

psql --user=postgres < tables.sql

Now I can insert some registrations and query them back out again, but only in the land of SQL:

postgres=# insert into registration (firstname, lastname, dob) values ('Ben', 'Clifford', '1900-01-01');
INSERT 0 1
postgres=# insert into registration (firstname, lastname, dob) values ('John', 'Smith', '2017-12-25');
INSERT 0 1
postgres=# select * from registration;
 id | firstname | lastname |    dob     
----+-----------+----------+------------
  1 | Ben       | Clifford | 1900-01-01
  2 | John      | Smith    | 2017-12-25
(2 rows)

Next I'll build a Haskell representation of this. This is going to get messy if we do everything in one file, so it's time to make a second Haskell source file, called src/Registration.hs. It goes in src which is the directory for source shared between different components, rather than app which is where the web server now lives.

module Registration where

data Registration = Registration {
  firstname :: String,
  lastname :: String,
  dob :: String
}

I'd like to be able to at least print this out for debugging purposes, show I'd like registration to be an instance of the Show typeclass. So on the end of the last line } add deriving Show.

I want to be able to go between PostgreSQL registrations and Haskell registrations. Haskell has a bunch of different database libraries around. In this case, I'm going to use postgresql-simple because it is not too abstracted from SQL, and is one of the most recent ones I've used.

So add a dependency in package.yaml for postgresql-simple. Because these changes are now in the library code for this project (which potentially will be shared between multiple executables), that dependency needs to be added to the library section, not the executables/reg-exe section. There will probably not be a dependencies sub-section there already, so add it in so that the library section looks like this:

library:
  source-dirs: src
  dependencies: postgresql-simple

Get an interactive Haskell prompt by running stack repl. You can query postgres and get back some lists of data like this:

*Main Lib Registration> import Database.PostgreSQL.Simple
*Main Lib Registration Database.PostgreSQL.Simple> c <- connectPostgreSQL "user='postgres'"
*Main Lib Registration Database.PostgreSQL.Simple> query c "SELECT firstname, lastname, dob FROM registration" () :: IO [[String]]
[["Ben","Clifford","1900-01-01"],["John","Smith","2017-12-25"]]

There is the same data inserted into the database earlier, as a list of list of Strings. That query line has to specify a type at the end (IO [[String]]) because query will try to convert your data into whatever type you ask it to.

It's possible, then, to get query to return a list of Registration records. The first bit of doing that (which won't work yet) is to change that IO [[String]] at the end of the query line to IO [Registration] - note only one pair of enclosing [brackets] now.

This will fail with an error something like No instance for (FromRow Registration). Which is true. FromRow is a typeclass in postgresql-simple that describes how to convert a row of SQL data into a particular type.

Luckily for a straightforward record like Registration, there is already code in postgresql-simple to figure this out mostly automatically, if rather bureaucratically.

First we need to use GHC generics: this feature lets library code (in this case in postgresql-simple) look at a datatype that is defined in an application (in this case Registration) and do interesting stuff with it (in this case, figure out how to make a FromRow instance).

Add a couple of language extensions to the library section of package.yaml (which won't exist yet): DeriveGeneric and DeriveAnyClass.

Now modify the deriving statement for the Registration data type. First add these imports:

import qualified GHC.Generics as G
import qualified Database.PostgreSQL.Simple as PG

and then add a couple of classes to the deriving clause of Registration to derive a Generic instance for that type, and then in turn derive FromRow.

...
} deriving (Show, G.Generic, PG.FromRow)

Now reload stack repl and try that query again:

*Main Lib Registration> import Database.PostgreSQL.Simple
*Main Lib Registration Database.PostgreSQL.Simple> c <- connectPostgreSQL "user='postgres'"
*Main Lib Registration Database.PostgreSQL.Simple> query c "SELECT firstname, lastname, dob FROM registration" () :: IO [Registration]
[Registration {firstname = "Ben", lastname = "Clifford", dob = "1900-01-01"},Registration {firstname = "John", lastname = "Smith", dob = "2017-12-25"}]

So there we've successfully read in some Registration values from the database.

Next, I'll talk about getting access to registrations in a web browser.

commit a9b48e0f gives the changes for this post.