PostgreSQL Data Access with Haskell

Introduction

PostgreSQL is a very popular relational database which has quite a few different data access libraries available for the Haskell programming language.

Today’s article aims to get you up and running, executing queries against PostgreSQL from your Haskell environment with the least amount of hassle.

postgresql-simple

The first library that we’ll go through is postgresql-simple. This library has a very basic interface, and is really simple to get up an running.

A mid-level client library for the PostgreSQL database, aimed at ease of use and high performance.

Prerequisites

Before you get started though, you’ll need libpq installed.

pacman -S postgresql-libs

Now you’re ready to develop.

You’ll need to add a dependency on the postgresql-simple library to your application. The following code will then allow you to connect to your PostgreSQL database, and ru a simple command.

Hello, Postgres!

{-# LANGUAGE OverloadedStrings #-}
module Main where

import Database.PostgreSQL.Simple

localPG :: ConnectInfo
localPG = defaultConnectInfo
        { connectHost = "172.17.0.1"
        , connectDatabase = "clients"
        , connectUser = "app_user"
        , connectPassword = "app_password"
        }

main :: IO ()
main = do
  conn <- connect localPG
  mapM_ print =<< (query_ conn "SELECT 1 + 1" :: IO [Only Int])

When your application successfully builds and executes, you should be met with the following output:

Only {fromOnly = 2}

Walking through this code quickly, we first enable OverloadedStrings so that we can specify our Query values as literal strings.

localPG :: ConnectInfo
localPG = defaultConnectInfo
        { connectHost = "172.17.0.1"
        , connectDatabase = "clients"
        , connectUser = "app_user"
        , connectPassword = "app_password"
        }

In order to connect to Postgres, we use a ConnectInfo value which is filled out for us via defaultConnectInfo. We just override those values for our examples. I’m running PostgreSQL in a docker container, therefore I’ve got my docker network address.

conn <- connect localPG

The localPG value is now used to connect to the Postgres database. The conn value will be referred to after successful connection to send instructions to.

mapM_ print =<< (query_ conn "SELECT 1 + 1" :: IO [Only Int])

Finally, we run our query SELECT 1 + 1 using the query_ function. conn is passed to refer to the connecion to execute this query on.

With this basic code, we can start to build on some examples.

Retrieve a specific record

In the Hello, World example above, we were adding two static values to return another value. As exampeles get more complex, we need to give the library more information about the data that we’re working with. Int is very well known already, and already has mechanisms to deal with it (along with other basic data types).

In the client database table we have a list of names and ids. We can create a function to retrieve the name of a client, given an id:

retrieveClient :: Connection -> Int -> IO [Only String]
retrieveClient conn cid = query conn "SELECT name FROM client WHERE id = ?" $ (Only cid)

The Query template passed in makes use of the ? character to specify where substitutions will be put. Note the use of query rather than query_. In this case, query also accepts a Tuple containing all of the values for substitution.

Using the FromRow type class, our code can define a much stronger API. We can actually retrieve client rows from the database and convert them into Client values.

We need FromRow first:

import Database.PostgreSQL.Simple.FromRow

The Client data type needs definition now. It’s how we’ll refer to a client within our Haskell program:

data Client = Client { id :: Int, name :: String }
  deriving (Show)

The Client data type now gets a FromRow instance, which allows postgresql-simple to use it.

instance FromRow Client where
  fromRow = Client <$> field <*> field

In order of the fields definitions, we give fromRow definition. The retrieveClient function only changes to broaden its query, and change its return type!

retrieveClient :: Connection -> Int -> IO [Client]
retrieveClient conn cid = query conn "SELECT id, name FROM client WHERE id = ?" $ (Only cid)

Create a new record

When creating data, you can use the function execute. The execute function is all about execution of the query without any return value.

execute conn "INSERT INTO client (name) VALUES (?)" (Only "Sam")

Extending our API, we can make a createClient function; but with a twist. We’ll also return the generated identifier (because of the id field).

createClient :: Connection -> String -> IO [Only Int64]
createClient conn name =
  query conn "INSERT INTO client (name) VALUES (?) RETURNING id" $ (Only name)

We need a definition for Int64. This is what the underlying SERIAL in PostgreSQL will translate to inside of your Haskell application.

import Data.Int

We can now use createClient to setup an interface of sorts fo users to enter information.

main :: IO ()
main = do
  conn <- connect localPG
  putStrLn "Name of your client? "
  clientName <- getLine
  cid <- createClient conn clientName
  putStrLn $ "New Client: " ++ (show cid)

We’ve created a data creation interface now.

Name of your client?
Ringo
New Client: [Only {fromOnly = 4}]

Update an existing record

When it comes to updating data, we don’t expect much back in return aside from the number of records affected by the instruction. The execute function does exactly this. By measuring the return, we can convert the row count into a success/fail style message. I’ve simply encoded this as a boolean here.

updateClient :: Connection -> Int -> String -> IO Bool
updateClient conn cid name = do
  n <- execute conn "UPDATE client SET name = ? WHERE id = ?" (name, cid)
  return $ n > 0

Destroying records

Finally, destroying information out of the database will look a lot like the update.

deleteClient :: Connection -> Int -> IO Bool
deleteClient conn cid = do
  n <- execute conn "DELETE FROM client WHERE id = ?" $ (Only cid)
  return $ n > 0

execute providing the affected count allows us to perform the post-execution validation again.

Summary

There’s some basic operations to get up and running using postgresql-simple. Really looks like you can prototype software all the way through to writing fully blown applications with it.

Really simple to use.