Access Redis and PostgreSQL Databases with Haskell

The Hackage website at https://hackage.haskell.org/packages/#cat:Database provides a vast number of database packages that you can use, a couple of which will be covered here.
You will need to install the cabal-install tool on Fedora, for example, using the following command:

$ sudo yum install cabal-install

Connecting to the Redis database
Let’s use the hedis package to connect to the Redis server. Install the Fedora dependency package alex, and the Redis server as shown below:

$ sudo yum install alex redis

You can then install the hedis package using the following commands:

$ cabal update
$ cabal install hedis

This installs the latest hedis version 0.6.5. You can now start the Redis server on Fedora using the service command:

$ sudo service redis start

You can then test connectivity to the Redis server using the redis-cli command by issuing the PING command as follows:

$ redis-cli

127.0.0.1:6379> PING
PONG

You can also test the same using the hedis package inside the GHCi interpreter as illustrated below:

$ ghci
GHCi, version 7.6.3: http://www.haskell.org/ghc/ :? for help
Loading package ghc-prim ... linking ... done.
Loading package integer-gmp ... linking ... done.
Loading package base ... linking ... done.

ghci> :m Database.Redis

ghci> conn <- connect defaultConnectInfo

Loading package array-0.4.0.1 ... linking ... done.
Loading package base-unicode-symbols-0.2.2.4 ... linking ... done.
Loading package deepseq-1.3.0.1 ... linking ... done.
Loading package old-locale-1.0.0.5 ... linking ... done.
Loading package time-1.4.0.1 ... linking ... done.
Loading package transformers-0.3.0.0 ... linking ... done.
Loading package bytestring-0.10.0.2 ... linking ... done.
Loading package text-0.11.3.1 ... linking ... done.
Loading package stm-2.4.2 ... linking ... done.
Loading package primitive-0.5.0.1 ... linking ... done.
Loading package vector-0.10.0.1 ... linking ... done.
Loading package hashable-1.1.2.5 ... linking ... done.
Loading package transformers-base-0.4.1 ... linking ... done.
Loading package monad-control-0.3.2.1 ... linking ... done.
Loading package containers-0.5.0.0 ... linking ... done.
Loading package attoparsec-0.10.4.0 ... linking ... done.
Loading package mtl-2.1.2 ... linking ... done.
Loading package BoundedChan-1.0.3.0 ... linking ... done.
Loading package bytestring-lexing-0.4.3.2 ... linking ... done.
Loading package unix-2.6.0.1 ... linking ... done.
Loading package network-2.6.0.2 ... linking ... done.
Loading package resource-pool-0.2.3.2 ... linking ... done.
Loading package hedis-0.6.5 ... linking ... done.

ghci> runRedis conn ping
Right Pong

I would recommend that you use defaultConnectInfo to connect to the database, and its type is ConnectInfo:

ghci> :t defaultConnectInfo
defaultConnectInfo :: ConnectInfo

The different options that can be used in defaultConnectInfo are as follows:

connectHost = “localhost”
connectPort = PortNumber 6379 -- Redis port
connectAuth = Nothing -- No authentication
connectDatabase = 0 -- SELECT database 0
connectMaxConnections = 10 -- Up to 10 connections
connectMaxIdleTime = 20 -- Keep connection open for 20 seconds

The types of conn, connect, runRedis and ping are given below:

ghci> :t conn
conn :: Connection

ghci> :t connect
connect :: ConnectInfo -> IO Connection

ghci> :t runRedis
runRedis :: Connection -> Redis a -> IO a

ghci> :t ping
ping :: RedisCtx m f => m (f Status)

If the Redis server was not started, and you tried to issue the ping command, the following exception would be automatically thrown by the package:

ghci> runRedis conn ping
*** Exception: connect: does not exist (No route to host)

You can automate the above code snippets into Haskell code with a main function, as demonstrated below:

{-# LANGUAGE OverloadedStrings #-}
import Database.Redis
main :: IO (Either Reply Status)
main = do
conn <- connect defaultConnectInfo
runRedis conn ping

The OverloadedStrings extension allows string literals to be polymorphic for the IsString class. You can compile and run the above code inside GHCi, as follows:

$ ghci ping.hs

GHCi, version 7.6.3: http://www.haskell.org/ghc/ :? for help
Loading package ghc-prim ... linking ... done.
Loading package integer-gmp ... linking ... done.
Loading package base ... linking ... done.
[1 of 1] Compiling Main ( ping.hs, interpreted )
Ok, modules loaded: Main.

ghci> main
...
Right Pong

The echo Redis command is used to print a message that is passed as an argument to it. The equivalent hedis echo command expects the message to be of type ByteString. For example:

{-# LANGUAGE OverloadedStrings #-}
import Database.Redis
import qualified Data.ByteString as B

bytes :: B.ByteString
bytes = “Hello, World” :: B.ByteString

main :: IO (Either Reply B.ByteString)
main = do
conn <- connect defaultConnectInfo
runRedis conn $ echo bytes

Loading the above code in GHCi produces the following output:

ghci> main
Right “Hello, World”

The type signature of the echo function is as follows:

echo
:: RedisCtx m f =>
Data.ByteString.Internal.ByteString
-> m (f Data.ByteString.Internal.ByteString)

You can set a value to a key using the set function in hedis. An example is shown below:

{-# LANGUAGE OverloadedStrings #-}
import Database.Redis

main :: IO (Either Reply Status)
main = do
conn <- connect defaultConnectInfo
runRedis conn $ set “a” “apple”

Loading the above set.hs code in GHCi and testing the same produces the following output:

ghci> :l set.hs
[1 of 1] Compiling Main ( set.hs, interpreted )
Ok, modules loaded: Main.

ghci> main
Right Ok

The type signature of the set function is shown below:

ghci> :t set
set
:: RedisCtx m f =>
Data.ByteString.Internal.ByteString
-> Data.ByteString.Internal.ByteString -> m (f Status)

You can verify the value of the key ‘a’ from the redis-cli command, and it must return the value “apple”:

127.0.0.1:6379> get a
“apple”

You can also retrieve the value of a key using the get function. For example:

{-# LANGUAGE OverloadedStrings #-}
import Database.Redis
import Control.Monad.IO.Class

main :: IO ()
main = do
conn <- connect defaultConnectInfo
runRedis conn $ do
result <- get “a”
liftIO $ print result

Executing the above code in GHCi gives the expected result:

ghci> :l get.hs
[1 of 1] Compiling Main ( get.hs, interpreted )
Ok, modules loaded: Main.

ghci> main
Right (Just “apple”)

The liftIO function transforms an IO action into a Monad. Its type signature is shown below:

ghci> :t liftIO
liftIO :: MonadIO m => IO a -> m a
The type signature of the get function is as follows:
ghci> :t get
get
:: RedisCtx m f =>
Data.ByteString.Internal.ByteString
-> m (f (Maybe Data.ByteString.Internal.ByteString))

You are encouraged to read the Database.Redis documentation page that contains a comprehensive list of commands and their usage at https://hackage.haskell.org/package/hedis-0.6.5/docs/Database-Redis.html.

Accessing the PostgreSQL database
We shall now explore accessing a PostgreSQL database using the postgresql-simple (0.4.10.0) package. You will need to install and configure PostgreSQL for your GNU/Linux distribution. Please follow your distribution documentation to do so. On Fedora, for example, you can install the database server using the following command:

$ sudo yum install postgresql-server postgresql-contrib

You can then start the database server using the following service command:

$ sudo service postgresql start

You can now install the postgresql-simple package using the cabal command:

$ cabal install postgresql-simple

Let us first create a database and a schema using the Postgresql command-line utility psql:

$ psql -U postgres
Password for user postgres:
psql (9.3.5)
Type “help” for help.

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+----------+-------------+-------------+--------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/ postgres + postgres=CTc/postgres
template1 | postgres | UTF8| en_US.UTF-8 | en_US.UTF-8 |
=c/postgres + postgres=CTc/postgres
(3 rows)

postgres=# CREATE DATABASE test;
CREATE DATABASE

postgres-# \c test

You are now connected to database ‘test’ as user ‘postgres’.

test=# create schema social;
CREATE SCHEMA

test=# \dn
public | postgres
social | postgres

We can then create a users’ table with an ID, first name and last name using the postgresql-simple package:

{-# LANGUAGE OverloadedStrings #-}

import Database.PostgreSQL.Simple

main :: IO ()
main = do
conn <- connect defaultConnectInfo
{ connectUser = “postgres”
, connectPassword = “postgres123”
, connectDatabase = “test”
}

execute conn “create table social.users (id INT, fname VARCHAR(80), lname VARCHAR(80))” ()
close conn

Loading the above code in GHCi creates the table social.users as shown below:

$ ghci create.hs

GHCi, version 7.6.3: http://www.haskell.org/ghc/ :? for help
Loading package ghc-prim ... linking ... done.
Loading package integer-gmp ... linking ... done.
Loading package base ... linking ... done.
[1 of 1] Compiling Main ( create.hs, interpreted )
Ok, modules loaded: Main.

ghci> main

Loading package array-0.4.0.1 ... linking ... done.
Loading package deepseq-1.3.0.1 ... linking ... done.
Loading package bytestring-0.10.0.2 ... linking ... done.
Loading package containers-0.5.0.0 ... linking ... done.
Loading package text-0.11.3.1 ... linking ... done.
Loading package attoparsec-0.10.4.0 ... linking ... done.
Loading package blaze-builder-0.3.1.1 ... linking ... done.
Loading package dlist-0.5 ... linking ... done.
Loading package hashable-1.1.2.5 ... linking ... done.
Loading package transformers-0.3.0.0 ... linking ... done.
Loading package mtl-2.1.2 ... linking ... done.
Loading package old-locale-1.0.0.5 ... linking ... done.
Loading package syb-0.4.0 ... linking ... done.
Loading package pretty-1.1.1.0 ... linking ... done.
Loading package template-haskell ... linking ... done.
Loading package time-1.4.0.1 ... linking ... done.
Loading package unordered-containers-0.2.3.0 ... linking ... done.
Loading package primitive-0.5.0.1 ... linking ... done.
Loading package vector-0.10.0.1 ... linking ... done.
Loading package aeson-0.6.2.1 ... linking ... done.
Loading package random-1.0.1.1 ... linking ... done.
Loading package scientific-0.2.0.2 ... linking ... done.
Loading package case-insensitive-1.0.0.1 ... linking ... done.
Loading package blaze-textual-0.2.0.8 ... linking ... done.
Loading package postgresql-libpq-0.9.0.2 ... linking ... done.
Loading package binary-0.7.4.0 ... linking ... done.
Loading package cereal-0.3.5.2 ... linking ... done.
Loading package entropy-0.2.2.1 ... linking ... done.
Loading package tagged-0.6 ... linking ... done.
Loading package crypto-api-0.11 ... linking ... done.
Loading package cryptohash-0.9.0 ... linking ... done.
Loading package network-info-0.2.0.5 ... linking ... done.
Loading package uuid-1.3.8 ... linking ... done.
Loading package postgresql-simple-0.4.10.0 ... linking ... done.

You can verify the created table from the psql prompt:

test=# \d social.users
id | integer |
fname | character varying(80) |
lname | character varying(80) |

You can also list the databases in the PostgreSQL server using the query_ function as illustrated below:

{-# LANGUAGE OverloadedStrings #-}
import Database.PostgreSQL.Simple
main :: IO ()
main = do
conn <- connect defaultConnectInfo
{ connectUser = “postgres”
, connectPassword = “postgres123”
, connectDatabase = “test”
}
databases <- query_ conn “SELECT datname FROM pg_database”
print (databases :: [Only String])

close conn

Executing the above code in GHCi produces the following output:

$ ghci show.hs
GHCi, version 7.6.3: http://www.haskell.org/ghc/ :? for help
Loading package ghc-prim ... linking ... done.
Loading package integer-gmp ... linking ... done.
Loading package base ... linking ... done.
[1 of 1] Compiling Main ( show.hs, interpreted )
Ok, modules loaded: Main.

ghci> main
[Only {fromOnly = “template1”},Only {fromOnly = “template0”},Only {fromOnly = “postgres”},Only {fromOnly = “test”}]

You can now insert a record into the databases using the execute function:

execute conn “insert into social.users (id, fname, lname) values (?, ?, ?)” [“1” :: String, “Edwin” :: String, “Brady” :: String]

After executing the above code, you can verify the database entry from the psql prompt:

test=# select * from social.users;
id | fname | lname
----+-------+-------
1 | Edwin | Brady
(1 row)

You can also do batch inserts using the executeMany function. For example:

executeMany conn “insert into social.users (id, fname, lname) values (?, ?, ?)” [(“2” :: String, “Simon” :: String, “Marlow” :: String), (“3” :: String, “Ulf” :: String, “Norell” :: String)]

After running the above code, you can check the newly added rows in the database from the psql command-line tool:

test=# select * from social.users;
id | fname | lname
----+-------+--------
1 | Edwin | Brady
2 | Simon | Marlow
3 | Ulf | Norell
(3 rows)

You can also change a record entry using the UPDATE statement as shown below:

execute conn “update social.users SET lname = ‘Peyton Jones’ where fname = ‘Simon’” ()

The corresponding entry is updated as seen from the psql prompt:

test=# select * from social.users;
id | fname | lname
----+-------+--------------
1 | Edwin | Brady
3 | Ulf | Norell
2 | Simon | Peyton Jones
(3 rows)

It is recommended that you catch exceptions when running database commands. Consider the following example, where the number of arguments passed does not match with what is expected:

{-# LANGUAGE OverloadedStrings #-}

import Database.PostgreSQL.Simple
import Control.Exception
import GHC.Int

main :: IO ()
main = do
conn <- connect defaultConnectInfo
{ connectUser = “postgres”
, connectPassword = “postgres123”
, connectDatabase = “test”
}
result <- try (execute conn “insert into social.users (id, fname, lname) values (?, ?, ?)” [“4” :: String, “Laurel” :: String]) :: IO (Either SomeException Int64)
case result of
Left ex -> putStrLn $ “Caught exception: “ ++ show ex
Right val -> putStrLn $ “The answer was: “ ++ show val
close conn

The error is observed when the main function is executed as shown below:

ghci> main
Caught exception: FormatError {fmtMessage = “3 ‘?’ characters, but 2 parameters”, fmtQuery = “insert into social.users (id, fname, lname) values (?, ?, ?)”, fmtParams = [“4”,”Laurel”]}

You can also retrieve multiple records from the database and use the results, with the help of a map function. An example is illustrated below:

{-# LANGUAGE OverloadedStrings #-}

import Database.PostgreSQL.Simple
import Control.Monad
import Data.Text as Text

main :: IO ()
main = do
conn <- connect defaultConnectInfo
{ connectUser = “postgres”
, connectPassword = “postgres123”
, connectDatabase = “test”
}
users <- query_ conn “SELECT fname, lname FROM social.users”
forM_ users $ \(fname, lname) ->
putStrLn $ Text.unpack fname ++ “ “ ++ Text.unpack lname
close conn

The output after executing the above code in GHCi returns the actual data:

ghci> main
Edwin Brady
Ulf Norell
Simon Peyton Jones

Please refer to the Database.PostgreSQL.Simple documentation for more examples and usage at https://hackage.haskell.org/package/postgresql-simple-0.4.10.