Haskell/Database
From Wikibooks, the open-content textbooks collection
[edit] 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. As 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). Although MySQL is the most popular open-sourced database, there is no specific driver for MySQL. MySQL users can use the ODBC driver on any MySQL-supported platform, including Linux. The 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. Same argument of perferring ODBC applies for other commercial databases, such as Oracle and DB2.
[edit] Installation
[edit] SQLite
See here for more information.
[edit] PostgreSQL
See here for more information.
[edit] ODBC/MySQL
Instruction how to install ODBC/MySQL. It is somewhat involved to make HDBC work with MySQL via ODBC, especially if you do not have root privilege and have to install everything in a non-root account.
- 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.
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
[edit] General Workflow
[edit] 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 program 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
[edit] 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".
[edit] Running SQL Statements
[edit] Select
[edit] Insert
[edit] Update
[edit] Delete
[edit] 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.
