Named after logician Haskell Curry, Haskell is a standardised,general-purpose, purely functional programming language, with non-strict semantics and strong static typing. This tenth article on Haskell explores 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
Lets 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.