Shakthimaan's Blog | Introduction to Haskell

November 19, 2015

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

In this ninth article on Haskell, I shall cover access to Sqlite, and MySQL databases using Haskell modules. A number of packages are available from the https://hackage.haskell.org/packages/#cat:Database website, but, I will illustrate a few of them with examples.

You first need to install the cabal-install tool on Fedora, for example, using the following command:

$ sudo yum install cabal-install

You can then install HDBC.Sqlite3 using the following command:

$ cabal install HDBC-sqlite3

This installs the latest 2.3.3.0 version from https://hackage.haskell.org/package/HDBC-sqlite3. You can also install the Sqlite3 package on Fedora for testing, as follows:

$ sudo yum install sqlite

To initiate a connection to a database, you can test it out in the GHCi prompt using the connectSqlite3 function, as shown 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> :module Database.HDBC Database.HDBC.Sqlite3

ghci> conn <- connectSqlite3 "students.db"

Loading package array-0.4.0.1 ... 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 old-time-1.1.0.1 ... linking ... done.
Loading package containers-0.5.0.0 ... linking ... done.
Loading package utf8-string-0.3.7 ... linking ... done.
Loading package mtl-2.1.2 ... linking ... done.
Loading package convertible-1.1.0.0 ... linking ... done.
Loading package HDBC-2.4.0.0 ... linking ... done.
Loading package HDBC-sqlite3-2.3.3.0 ... linking ... done.

The signature of the connectSqlite3 function is as follows:

ghci> :t connectSqlite3
connectSqlite3 :: FilePath -> IO Connection

The type of conn is a Connection.

ghci> :t conn
conn :: Connection

If you already have an existing Sqlite3 database, you can give the full path to the database and connect to it, or else you can now create a table using the Sqlite CREATE TABLE syntax as shown below:

ghci> run conn "CREATE TABLE names (id INTEGER NOT NULL, fname VARCHAR(80), lname VARCHAR(80))" []
0

The type signature of run is as follows:

ghci> :t run
run
  :: IConnection conn => conn -> String -> [SqlValue] -> IO Integer

It takes three arguments as input and performs an IO computation that returns an integer indicating the status of the execution. The first argument to run is the connection, the second argument is the Sqlite command to be executed, and finally is the array of SqlValues that provide a mapping between Haskell values and SQL databases.

Both Haskell and SQL databases have types, and different databases may have different representations of the types. In order to provide a consistent mapping between the two, each HDBC driver implements the relation using SqlValue.

You can now insert a record into the database using the following command:

ghci> run conn "INSERT INTO names (id, fname, lname) VALUES(1, 'Edwin', 'Brady')" []
1

ghci> commit conn

The type signature of commit is given here:

ghci> :t commit
commit :: IConnection conn => conn -> IO ()

It takes a connection and completes the pending IO actions. To read the result from Haskell you can use the quickQuery function from the GHCi prompt, as follows:

ghci> quickQuery conn "SELECT * from names" []
[[SqlByteString "1",SqlByteString "Edwin",SqlByteString "Brady"]]

The type signature of the quickQuery function is as follows:

quickQuery
  :: IConnection conn =>
     conn -> String -> [SqlValue] -> IO [[SqlValue]]

You can also verify the result of the above actions using the sqlite3 executable in the command prompt as illustrated below:

$ sqlite3 students.db 

SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.

sqlite> .schema
CREATE TABLE names (id INTEGER NOT NULL, fname VARCHAR(80), lname VARCHAR(80));

sqlite> select * from names;
1|Edwin|Brady

You can also do batch processing for inserts by preparing the statements and executing them:

ghci> batch <- prepare conn "INSERT INTO names VALUES (?, ?, ?)"

ghci> execute batch [toSql (2 :: Int), toSql "Simon", toSql "Marlow"]
1

ghci> execute batch [toSql (3 :: Int), toSql "Ulf", toSql "Norell"]
1

ghci> commit conn

The type signatures of the prepare and execute functions are given below:

ghci> :t prepare
prepare :: IConnection conn => conn -> String -> IO Statement

ghci> :t execute
execute :: Statement -> [SqlValue] -> IO Integer

You can once again check the records in the database using the quickQuery function:

ghci> quickQuery' conn "SELECT * from names" []

[[SqlByteString "1",SqlByteString "Edwin",SqlByteString "Brady"],[SqlByteString "2",SqlByteString "Simon",SqlByteString "Marlow"],[SqlByteString "3",SqlByteString "Ulf",SqlByteString "Norell"]]

You can also run an update query to the database. For example:

ghci> run conn "UPDATE names set lname = 'Peyton Jones' WHERE fname = 'Simon'" []
1

ghci> commit conn

Verifying the output from the Sqlite3 command prompt, you get:

sqlite> select * from names;

1|Edwin|Brady
2|Simon|Peyton Jones
3|Ulf|Norell

The HDBC driver provides many functions to retrieve information regarding the database and the drivers. A few examples are illustrated below:

ghci> hdbcDriverName conn
"sqlite3"

ghci> hdbcClientVer conn
"3.8.4.3"

ghci> dbTransactionSupport conn
True

ghci> getTables conn
["names"]

ghci> describeTable conn "names"

[("id",SqlColDesc {colType = SqlIntegerT, colSize = Nothing, colOctetLength = Nothing, colDecDigits = Nothing, colNullable = Nothing}),("fname",SqlColDesc {colType = SqlUnknownT "varchar(80)", colSize = Nothing, colOctetLength = Nothing, colDecDigits = Nothing, colNullable = Nothing}),("lname",SqlColDesc {colType = SqlUnknownT "varchar(80)", colSize = Nothing, colOctetLength = Nothing, colDecDigits = Nothing, colNullable = Nothing})]

It is considered good practice to use handleSqlError before running any HDBC commands to catch errors that may arise during the database transactions. For example:

ghci> handleSqlError $ quickQuery conn "SELECT * from namesaaa" []

*** Exception: user error (SQL error: SqlError {seState = "", seNativeError = 1, seErrorMsg = "prepare 23: SELECT * from namesaaa: no such table: namesaaa"})

To disconnect from the database, you can use the disconnect function provided by HDBC as shown below:

ghci> disconnect conn

Let’s now look at how to access a MySQL database using the mysql-simple package. You will also need to create or grant privileges for an existing user to use the MySQL database server. Please follow your GNU/Linux distribution manual on how to install and configure a MySQL server. On Fedora, for example, you must have mysql and mysql-server installed:

$ sudo yum install community-mysql community-mysql-server

You can install the mysql-simple Haskell package using:

$ cabal install mysql-simple

Create a test database using the mysql command line tool as shown below:

$ mysql -u user -p

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.03 sec)

Let us create a users’ table that has an id as well as first name and last name fields, using the mysql-simple package, as follows:

{-# LANGUAGE OverloadedStrings #-}

import Database.MySQL.Simple

main :: IO ()
main = do
  conn <- connect defaultConnectInfo
    { connectUser = "user"
    , connectPassword = "password"
    , connectDatabase = "test"
    }

  execute conn "create table users (id INT, fname VARCHAR(80), lname VARCHAR(80))" ()

  close conn

The OverloadedStrings extension allows string literals to be polymorphic for the IsString class. The defaultConnectInfo is of type ConnectInfo:

ghci> :t defaultConnectInfo
defaultConnectInfo :: ConnectInfo

ConnectInfo can take many parameters to describe the connectivity to the MySQL server. For example:

connectInfo :: ConnectInfo
connectInfo = ConnectInfo { connectHost = "localhost",
                            connectPort = 3306,
                            connectUser = "user",
                        connectPassword = "password",
                        connectDatabase = "test",
                         connectOptions = [],
                            connectPath = "",
                             connectSSL = Nothing }

The above code to create a table can be compiled directly in GHCi and the main function can be executed as given 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 old-locale-1.0.0.5 ... linking ... done.
Loading package time-1.4.0.1 ... linking ... done.
Loading package bytestring-0.10.0.2 ... linking ... done.
Loading package text-0.11.3.1 ... linking ... done.
Loading package blaze-builder-0.3.1.1 ... linking ... done.
Loading package primitive-0.5.0.1 ... linking ... done.
Loading package vector-0.10.0.1 ... linking ... done.
Loading package pcre-light-0.4 ... linking ... done.
Loading package containers-0.5.0.0 ... linking ... done.
Loading package attoparsec-0.10.4.0 ... linking ... done.
Loading package blaze-textual-0.2.0.8 ... linking ... done.
Loading package base16-bytestring-0.1.1.6 ... linking ... done.
Loading package mysql-0.1.1.7 ... linking ... done.
Loading package mysql-simple-0.2.2.4 ... linking ... done.

You can check with the mysql command line utility for the created table:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc users;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| fname | varchar(80) | YES  |     | NULL    |       |
| lname | varchar(80) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

You can now list the databases available in the MySQL server using the query_ function as illustrated below:

{-# LANGUAGE OverloadedStrings #-}

import Database.MySQL.Simple

main :: IO ()
main = do
  conn <- connect defaultConnectInfo
    { connectUser = "user"
    , connectPassword = "password"
    , connectDatabase = "test"
    }
  databases <- query_ conn "SHOW databases"
  print (databases :: [Only String])

  close conn

You can compile the above code directly with GHCi and execute the main function, as follows:

 $ ghci show-databases.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-databases.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.
...
[Only {fromOnly = "information_schema"},Only {fromOnly = "mysql"},Only {fromOnly = "performance_schema"},Only {fromOnly = "test"}]

You can try inserting a record into the database using the execute function:

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

After running the code, you can check the database entry using the mysql client program as shown below:

mysql> select * from users;

+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
|    1 | Edwin | Brady |
+------+-------+-------+
1 row in set (0.00 sec)

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

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

You can verify the execution of the code from the mysql utility:

mysql> select * from users;
+------+-------+--------+
| id   | fname | lname  |
+------+-------+--------+
|    1 | Edwin | Brady  |
|    2 | Simon | Marlow |
|    3 | Ulf   | Norell |
+------+-------+--------+
3 rows in set (0.01 sec)

You can change a record entry using the UPDATE MySQL command:

execute conn "update users SET lname = 'Peyton Jones' where fname = 'Simon'" ()

Executing the code in GHCi, and checking the results with the mysql prompt gives the following changed output:

mysql> select * from users;
+------+-------+--------------+
| id   | fname | lname        |
+------+-------+--------------+
|    1 | Edwin | Brady        |
|    2 | Simon | Peyton Jones |
|    3 | Ulf   | Norell       |
+------+-------+--------------+
3 rows in set (0.00 sec)

It is important to catch any exceptions that may arise on executing the database commands. Consider the following example, where the number of arguments passed does not match with the expected:

{-# LANGUAGE OverloadedStrings #-}

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

main :: IO ()
main = do
  conn <- connect defaultConnectInfo
    { connectUser = "user"
    , connectPassword = "password"
    , connectDatabase = "test"
    }
  result <- try (execute conn "insert into 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 caught when the main function is executed inside GHCi:

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

You can also map through the results returned from the database and use them for your needs. The following is an illustration of the same:

{-# LANGUAGE OverloadedStrings #-}

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

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

The resultant output when executing the main function in GHCi is given below:

ghci> main

Edwin Brady
Simon Peyton Jones
Ulf Norell

You are encouraged to read Database.MySQL.Simple documentation from https://hackage.haskell.org/package/mysql-simple-0.2.2.4/docs/Database-MySQL-Simple.html for more information.

2015 haskell