news

[Published in Open Source For You (OSFY) magazine, April 2015 edition.]

In this article we shall explore access to Redis and PostgreSQL databases using Haskell modules. 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

It is recommended 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 will 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 databaes 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 to catch exceptions when running database commands. Consider the following example, where the number of arguments passed does not match with the 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 using 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.0/docs/Database-PostgreSQL-Simple.html.