Haskell/Database

Introduction

Haskell's most popular database module is HDBC. HDBC provides an abstraction layer between Haskell programs and SQL relational databases. This lets you write database code once, in Haskell, and have it work with a number of backend SQL databases.

HDBC is modeled loosely on Perl's DBI interface, though it has also been influenced by Python's DB-API v2, JDBC in Java, and HSQL in Haskell. Like how DBI requires DBD in Perl, HDBC requires a driver module beneath it to work.

These HDBC backend drivers exist: PostgreSQL, SQLite, and ODBC (for Windows and Unix/Linux/Mac). MySQL is the most popular open-sourced databases, and there are two drivers for MySQL: HDBC-mysql (native) and HDBC-odbc (ODBC). MySQL users can use the ODBC driver on any MySQL-supported platform, including Linux.

An advantage of using ODBC is that the syntax of the SQL statement is insulated from the different kinds of database engines. This increases the portability of the application should you have to move from one database to another. The same argument for preferring ODBC applies for other commercial databases (such as Oracle and DB2).

Installation

PostgreSQL or SQLite

See the HDBC FAQ for more information.

Native MySQL

The native ODBC-mysql library requires the C MySQL client library to be present.

You may need to wrap your database accesses to prevent runtime errors.

ODBC/MySQL

Making HDBC work with MySQL via ODBC is somewhat involved, especially if you do not have root privileges.

• If your platform doesn't already provide an ODBC library (and most do), install Unix-ODBC. See here for more information.
• Install MySQL-ODBC Connector. See here for more information.
• Install Database.HDBC module
• Install Database.HDBC.ODBC module
• Add the mysql driver to odbcinst.ini file (under $ODBC_HOME/etc/) and your data source in $HOME/.odbc.ini.
• Create a test program

Since the ODBC driver is installed using shared library by default, you will need the following env:

export LD_LIBRARY_PATH=$ODBC_HOME/lib

If you do not like adding an additional env variables, you should try to compile ODBC with static library option enabled.

The next task is to write a simple test program that connects to the database and print the names of all your tables, as shown below.

You may need to wrap your database accesses in order to prevent runtime errors.

  module Main where
  import Database.HDBC.ODBC
  import Database.HDBC
  main =
    do c  <- connectODBC "DSN=PSPDSN"
       xs <- getTables c
       putStr $ "tables "++(foldr jn "." xs)++"\n"
    where jn a b = a++" "++b

General Workflow

Connect and Disconnect

The first step of any database operation is to connect to the target database. This is done via the driver-specific connect API, which has the type of:

 String -> IO Connection

Given a connect string, the connect API will return Connection and put you in the IO monad.

Although most programs will garbage-collect your connections when they are out of scope or when the program ends, it is a good practice to disconnect from the database explicitly.

 conn->Disconnect

Running Queries

Running a query generally involves the following steps:

• Prepare a statement
• Execute a statement with bind variables
• Fetch the result set (if any)
• Finish the statement

HDBC provides two ways for bind variables and returning result set: [ SqlValue ] and [ Maybe String ]. You need to use the functions with s prefix when using [ Maybe String ], instead of [ SqlValue ]. [ SqlValue ] allows you to use strongly typed data if type safety is very important in your application; otherwise, [ Maybe String ] is more handy when dealing with lots of database queries. When you use [ Maybe String ], you assume the database driver will perform automatic data conversion. Be aware there is a performance price for this convenience.

Sometimes, when the query is simple, there are simplified APIs that wrap multiple steps into one. For example, Run and sRun are wrappers of "prepare and execute". quickQuery is a wrapper of "prepare, execute, and fetch all rows".

Running SQL Statements

Transaction

Database transaction is controlled by commit and rollback. However, be aware some databases (such as mysql) do not support transaction. Therefore, every query is in its atomic transaction.

HDBC provides withTransaction to allow you automate the transaction control over a group of queries.