babysteps in using Persistent to interact with PostgreSQL


as I mentioned I want to try some real-life Haskell and of course this means using some kind of database.
I looked around a bit and found the really great Persistent package from Micheal Snoyman.
Not only is this wonderfuly documented – no you get an yesod integration and a online-book(chapter) explaining it for free – thank you Micheal!

This post will only detail the setup and installation I had to do on top of my PostgresSQL installation and it’s more or less my notes I wrote along the way (I hope you enjoy this experiment).

So here we go – with opened SublimeText and console we start…

Set up the playground

  • made a directory and initialized a git repository in it git init
  • copied my generic haskell .gitignore into it
  • initialized cabal cabal init setting up the basic names and stuff
  • added persistent, persistent-sqlite and persistent-postgresql as dependencies
  • shamelessly copied the code from here into main.hs:
{-# LANGUAGE EmptyDataDecls    #-}
{-# LANGUAGE FlexibleContexts  #-}
{-# LANGUAGE GADTs             #-}
{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE QuasiQuotes       #-}
{-# LANGUAGE TemplateHaskell   #-}
{-# LANGUAGE TypeFamilies      #-}
import           Control.Monad.IO.Class  (liftIO)
import           Database.Persist
import           Database.Persist.Sqlite
import           Database.Persist.TH

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
    name String
    age Int Maybe
    deriving Show
    title String
    authorId PersonId
    deriving Show

main :: IO ()
main = runSqlite ":memory:" $ do
    runMigration migrateAll

    johnId <- insert $ Person "John Doe" $ Just 35
    janeId <- insert $ Person "Jane Doe" Nothing

    insert $ BlogPost "My fr1st p0st" johnId
    insert $ BlogPost "One more for good measure" johnId

    oneJohnPost <- selectList [BlogPostAuthorId ==. johnId] [LimitTo 1]
    liftIO $ print (oneJohnPost :: [Entity BlogPost])

    john <- get johnId
    liftIO $ print (john :: Maybe Person)

    delete janeId
    deleteWhere [BlogPostAuthorId ==. johnId]
  • obviously wanting a sandbox: cabal sandbox init
  • get the stuff from the net and write this here in the meantime 😉 cabal install --dependencies-only

the usual cabal oddysee starts.. (but do not fear – Penelope does not have to wait long)

So this was the first thing that failed – persistent-sqlite went like a charm but persistent-postgresql had a issue.

Ok – comment out the postgres part for now – the code does not need it.

But as cabal build showed I had a missing module Database.Persist.TH – and as I could not find the right package at once, let’s comment it out for now.

Yeah that got me another missing module Control.Monad.IO.Class – but this time ghc (and me) knows where to find it: transformers – so in it.

Now I get some more information – as you could guess the .TH stands for TemplateHaskell and sure enough ghc complains about things like share and mkPersist.

And sure enough there is a persistent-template package with those functions and the missing module – so another cabal install later, we finally have a compiling program.

Yes there are a few warnings (unused constructors and so forth) but let’s forget about this right now.

let’s run

a cabal run yields

Migrating: CREATE TABLE "blog_post"("id" INTEGER PRIMARY KEY,"title" VARCHAR NOT NULL,"author_id" INTEGER NOT NULL REFERENCES "person")
[Entity {entityKey = Key {unKey = PersistInt64 1}, entityVal = BlogPost {blogPostTitle = "My fr1st p0st", blogPostAuthorId = Key {unKey = PersistInt64 1}}}]
Just (Person {personName = "John Doe", personAge = Just 35})

Which looks like just what the copied code should do … great!

back to postgres

But of course I want to run this in my local postgresql db – so there’s still one thing left before I could call it a day – so remove let’s get it back in there.

Retrying the install I saw this nice little helper-message:

setup: The program pg_config is required but it could not be found.

And indeed – I did not have this on my system – running openSUSE it’s no big deal to find out where the bugger lives: cnf pg_config – voila postgresql92-devel so give it to me sudo zypper install postgresql92-devel (note: of course you might have to do some sudo apt-get or something similar if you are on a debian based system – but I’m sure you get it – if you are on windows … well let’s say I keep this task for another day and propably ms-sql or mysql instead of postgres).

After this little intermezzo persistent-postgresql builds/installs and we can look at how to use it with our “copy&paste” example.

Turns out that this too is already mentioned in the book chapter I linked earlier – right at the bottom of the doc.
The changes are very slight:

  • import Database.Persist.Postgresql instead of .Sqlite
  • get a connection string for the database:
        connStr = "host=localhost dbname=test user=??? password=**** port=5432"

(fill in whatever you need – obviously you should use a known user/password).

  • run withPostgresqlPool with runSqlPersistMPool instead of just runSqlite like this:
main = withPostgresqlPool connStr 10 $ pool -> do 
    flip runSqlPersistMPool pool $ do

The rest may stay untouched.

And YEAH – cabal run yields

HINWEIS:  CREATE TABLE erstellt implizit eine Sequenz »person_id_seq« für die »serial«-Spalte »«
HINWEIS:  CREATE TABLE / PRIMARY KEY erstellt implizit einen Index »person_pkey« für Tabelle »person«
Migrating: CREATe TABLE "blog_post"("id" SERIAL PRIMARY KEY UNIQUE,"title" VARCHAR NOT NULL,"author_id" INT8 NOT NULL)
HINWEIS:  CREATE TABLE erstellt implizit eine Sequenz »blog_post_id_seq« für die »serial«-Spalte »«
HINWEIS:  CREATE TABLE / PRIMARY KEY erstellt implizit einen Index »blog_post_pkey« für Tabelle »blog_post«
Migrating: ALTER TABLE "blog_post" ADD CONSTRAINT "blog_post_author_id_fkey" FOREIGN KEY("author_id") REFERENCES "person"("id")
[Entity {entityKey = Key {unKey = PersistInt64 1}, entityVal = BlogPost {blogPostTitle = "My fr1st p0st", blogPostAuthorId = Key {unKey = PersistInt64 1}}}]
Just (Person {personName = "John Doe", personAge = Just 35})

Checking my test database with pgAdminIII I see that indeed I now have a person and a blog_post table with the right columns set up. The first having an “John Deo” entry and the second beeing empty.

Rerunning again adds another “John Doe” with id “3” as it should.
As a last test I removed the “delete” so I can check for a blog post … and yes I now have yet another “John 5” who has 2 posts … so yes it’s working as it should.

This should wrap up the first tests – rather painless which is great!

  • Matthew Parsons

    Thanks for the awesome post! It’s really helpful. The HTML formatting makes the code a bit difficult to read though, you may want to revisit your post preprocessing.

    • thanks – I have no clue what went wrong there – I migrated to markdown formatting a while ago and parts of this seemed to have to failed – indeed I only load/save as is and it seems to be fixed for me

      • Matthew Parsons

        Weird! It looks great now. Thanks!