(Outdated) Tutorial

Tutorial

Note: This tutorial is outdated. Cooler and newer tutorial is available in the project website.

what is haskell-relational-record?

haskell-relational-record is a set of packages for accessing databases from Haskell. It has the following features:

• ability to create type-safe SQL from pure Haskell
• automatic creation of Haskell record types by Template Haskell

install

Note: links to install methods of project site - Debian, OpenSUSE, stack, cabal

Like other Haskell packages, haskell-relational-record is available on hackage and can be installed with cabal.

However, the package name is different from the repository name.

$ cabal install relational-record

A sandboxed environment can be installed with the following commands.

$ cabal sandbox init
$ cabal install relational-record

simple example

This tutorial will show how to use haskell-relational-record to access a database.

In order to follow along this tutorial, you will need to install all the packages provided by haskell-relational-record, along with HDBC-postgresql. This tutorial will use PostgreSQL.

define item table

The SQL to define the item table and insert sample data is below.

/* create.sql */

create schema TUTORIAL;

create table TUTORIAL.item (
  item_id integer not null,
  item_name varchar(255) not null,
  price integer not null,
  primary key (item_id)
);

insert into TUTORIAL.item (item_id, item_name, price) values (1, 'orange', 80);
insert into TUTORIAL.item (item_id, item_name, price) values (2, 'apple', 120);

The following command is used to create the testdb database and run the above SQL.

$ createdb testdb
$ psql -f create.sql testdb

connect to database and extract info for item table

We create a DataSource module, and define the connect function.

module DataSource
  ( connect
  ) where

import Database.HDBC.PostgreSQL (connectPostgreSQL, Connection)

connect :: IO Connection
connect = connectPostgreSQL "dbname=testdb"

Next, we create an Item module that will use Template Haskell to pull out the information for the item table. In order to avoid errors and namespace collisions while using Template Haskell, it is recommended to define a separate module for each table.

{-# LANGUAGE TemplateHaskell, MultiParamTypeClasses, FlexibleInstances #-}

module Item where

import Database.HDBC.Query.TH (defineTableFromDB)
import Database.HDBC.Schema.PostgreSQL (driverPostgreSQL)
import Database.Record.TH (derivingShow)

import DataSource (connect)

$(defineTableFromDB connect driverPostgreSQL "TUTORIAL" "item" [derivingShow])

With the above code, the Haskell record type corresponding to the item table is automatically generated.

The following Item data type will be generated.

data Item = Item
    { itemId   :: !Int32
    , itemName :: !String
    , price    :: !Int32
    } deriving (Show)

Additional helper functions are also created.

write a query

Below, we create a query lessThan100dollars that retrieves all items less than $100.

module Query
    ( lessThan100dollars
    ) where

import Database.Relational.Query

import Item (Item, item)
import qualified Item as I

lessThan100dollars :: Relation () String
lessThan100dollars = relation $ do
    i <- query item
    wheres $ i ! I.price' .<. value 100
    return (i ! I.itemName')

This Relation p r is an instance of the Show class, so if you show it you can see the raw SQL.

$ ghci
Prelude> :l Query.hs
*Query> lessThan100dollars
SELECT T0.item_name AS f0 FROM TUTORIAL.item AS T0 WHERE (T0.price < 100)

run the query

We can run the query with the runQuery method.

module Main where

import Database.Relational.Query (relationalQuery)
import Database.HDBC.Session (withConnectionIO, handleSqlError')
import Database.HDBC.Record.Query (runQuery)

import DataSource (connect)
import Query (lessThan100dollars)

main :: IO ()
main = handleSqlError' $ withConnectionIO connect $ \conn -> do
    names <- runQuery conn (relationalQuery lessThan100dollars) ()
    print names

When passing lessThan100dollars to the runQuery method, it returns IO [String].

Let's try running it.

$ runghc main.hs
PostgreSQL: getFields: num of columns = 3, not null columns = [0,1,2]
PostgreSQL: getPrimaryKey: primary key = ["item_id"]
["orange"]

This returns ["orange"], which is the only item less than $100.

# The lines starting with "PostgreSQL: ..." are the logs of fetching data from the item table.