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).

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.

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:

Given a connect string, the connect API will return  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:  and. You need to use the functions with s prefix when using, instead of. allows you to use strongly typed data if type safety is very important in your application; otherwise,  is more handy when dealing with lots of database queries. When you use, 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".

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

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