---
layout: default
title: Tutorial
---

### Preparing

This is a tiny tutorial of Haskell Relational Record (HRR). This tutorial assumes that SQLite version 3 and HRR are already installed. If not, please install them first (see [quick start](quickstart.html)).

Also, please download "relational-record-examples" as follows:

    % cabal unpack relational-record-examples
    % cd relational-record-examples-<VERSION>

If you prefer stack, run as follows:

    % stack unpack relational-record-examples
    % cd relational-record-examples-<VERSION>

Replace `cabal unpack` with `stack unpack` also in the following example like above if you are a stack user.

If you want to use previous LTS Haskell releases of stackage
( [LTS-9.x](https://www.stackage.org/lts-9)
please download the previous version of "relational-record-examples" as follows:

    % cabal unpack relational-record-examples-0.3.2.1
    % cd relational-record-examples-0.3.2.1

If you want to use older LTS Haskell releases of stackage
( [LTS-8.x](https://www.stackage.org/lts-8), [LTS-7.x](https://www.stackage.org/lts-7) and [LTS-6.x](https://www.stackage.org/lts-6) are available ),
please download the previous version of "relational-record-examples" as follows:

    % cabal unpack relational-record-examples-0.3.1.5
    % cd relational-record-examples-0.3.1.5

### Creating tables in a DB

We use the bank example in [Learning SQL](http://shop.oreilly.com/product/9780596007270.do). Its support page provides a script to create the tables of the bank examples for MySQL. We modified [it for SQLite](https://github.com/khibino/haskell-relational-record/blob/master/relational-record-examples/sql/add.sql) and created a DB file called "examples.db" in the top directory of "relational-record-examples". We deeply thank Alan Beaulieu, the author of "Learning SQL".

Note that HRR does not have a feature to create tables at this moment. This is another reason why we provide the DB file.

### Defining record types in Haskell

Now we map the type of rows of a table to a Haskell record type. Here is the schema of the "Account" table:

    % sqlite3 examples.db
    sqlite> .schema Account
    CREATE TABLE account
     (account_id integer primary key autoincrement not null,
      product_cd varchar(10) not null,
      cust_id integer not null,
      open_date date not null,
      close_date date,
      last_activity_date date,
      status text not null,
      open_branch_id integer,
      open_emp_id integer,
      avail_balance float(10,2),
      pending_balance float(10,2),
      check(status = 'ACTIVE' or status = 'CLOSED' or status = 'FROZEN')
      constraint fk_product_cd foreign key (product_cd)
        references product (product_cd),
      constraint fk_a_cust_id foreign key (cust_id)
        references customer (cust_id),
      constraint fk_a_branch_id foreign key (open_branch_id)
        references branch (branch_id),
      constraint fk_a_emp_id foreign key (open_emp_id)
            references employee (emp_id)
     );

We don't want to have to define `data Account` for this by hand. HRR accesses our DB at compile time and automatically generates Haskell record types. To avoid the conflict of record field names, we recommend making one module per table. (This limitation would be solved by `OverloadedFieldRecord` in the future.)

Here is the content of "Account.hs":

{% highlight haskell %}
{-# LANGUAGE TemplateHaskell, MultiParamTypeClasses, FlexibleInstances #-}

module Account where

import Database.Record.TH.SQLite3 (defineTable)

$(defineTable "examples.db" "account")
{% endhighlight %}

This code generates the `Account` data type as follows:

{% highlight haskell %}
data Account
  = Account {accountId :: !Int,
             productCd :: !String,
             custId :: !Int,
             openDate :: !Day,
             closeDate :: !(Maybe Day),
             lastActivityDate :: !(Maybe Day),
             status :: !String,
             openBranchId :: !(Maybe Int),
             openEmpId :: !(Maybe Int),
             availBalance :: !(Maybe Double),
             pendingBalance :: !(Maybe Double)}
  deriving (Show)

-- Relation type corresponding to Table
account :: Relation () Account
account =  ...

-- Column selectors for This DSL
accountId' :: Pi Account GHC.Int.Int64
accountId'
  = ...
productCd' :: Pi Account String
productCd'
  = ...
custId' :: Pi Account GHC.Int.Int64
custId'
  = ...
....
{% endhighlight %}


### Defining relations

Next we define a simple relation (SELECT statement) in "src/examples.hs":

{% highlight haskell %}
account_4_3_3a :: Relation () Account
account_4_3_3a = relation $ do
  a  <- query account
  wheres $ #productCd a `in'` values ["CHK", "SAV", "CD", "MM"]
  return a
{% endhighlight %}

`Relation` takes two type parameters. The first one is the type of placeholder. This example does not use placeholder, so its type is `()`. The second one is the type of the value in `Relation`.

Let's look at the signature of 'relation':

{% highlight haskell %}
relation :: QuerySimple (Record Flat r) -> Relation () r
{% endhighlight %}

So, the type of the `do` should be `QuerySimple (Projection Flat r)`. `query` has the following type (note that this signature is simplified):

{% highlight haskell %}
query :: Relation () r -> QuerySimple (Record Flat r)
{% endhighlight %}

`account` is the variable which refers to the "Account" table. This is automatically generated by `defineTableFromDB` and its type is `Relation () r`. So `a <- query account` binds the variable `a` to each row of the "Account" table.

`wheres` is corresponding to the SQL 'where' clause. In this example, rows whose `productCd` is one of "CHK", "SAV", "CD", and "MM" are filtered.

### Connecting to the DB

Let's define a wrapper function to execute our relation on "examples.db":

{% highlight haskell %}
run :: (Show a, IConnection conn, FromSql SqlValue a, ToSql SqlValue p)
       => conn -> p -> Relation p a -> IO ()
run conn param rel = do
  putStrLn $ "SQL: " ++ show rel
  records <- runRelation conn rel param
  mapM_ print records
  putStrLn ""
{% endhighlight %}

`run` shows the generated SQL statement first and then the results of the query. Here are the signatures of the important function above:

{% highlight haskell %}
runRelation :: (IConnection conn,
                ToSql SqlValue p,
                FromSql SqlValue a) =>
               conn -> Relation p a -> p -> IO [a]
{% endhighlight %}

OK. Let's execute our relation on "examples.db":

    % cabal configure -f binary
    % cabal build
    % cabal repl executable:examples
    > conn <- connectSqlite3 "examples.db"
    > run conn () join_5_1_3
    SQL: SELECT ALL T0.account_id AS f0, T0.cust_id AS f1, T0.open_date AS f2, T0.product_cd AS f3 FROM (MAIN.account T0 INNER JOIN MAIN.employee T1 ON (T0.open_emp_id = T1.emp_id)) INNER JOIN MAIN.branch T2 ON (T1.assigned_branch_id = T2.branch_id) WHERE (T1.start_date <= '2004-01-01') AND ((T1.title = 'Teller') OR (T1.title = 'Head Teller')) AND (T2.name = 'Woburn Branch')
    Account3 {a3AccountId = 1, a3CustId = 1, a3OpenDate = 2000-01-15, a3ProductCd = "CHK"}
    Account3 {a3AccountId = 2, a3CustId = 1, a3OpenDate = 2000-01-15, a3ProductCd = "SAV"}
    ...

Great!

For stack users:

Copy and paste this as `stack.yaml`:

{% highlight yaml %}
resolver: lts-11.5
packages:
- '.'
extra-deps:
- HDBC-sqlite3-2.3.3.1
flags:
  relational-record-examples:
    binary: true
extra-package-dbs: []

# Uncomment here if you put sqlite3.h and other required sqlite3 library
# in a non-standard path.
# THIS IS REQUIRED ON WINDOWS!!
#extra-include-dirs:
#- 'C:\lib\sqlite'
#extra-lib-dirs:
#- 'C:\lib\sqlite'
{% endhighlight %}

Uncomment and edit `extra-include-dirs` and `extra-lib-dirs` for your environment.
    - THIS IS REQUIRED ON WINDOWS!!

Then run as follows:

    % stack build
    % stack ghci :examples
    > conn <- connectSqlite3 "examples.db"
    > run conn () join_5_1_3
    SQL: SELECT ALL T0.account_id AS f0, T0.cust_id AS f1, T0.open_date AS f2, T0.product_cd AS f3 FROM (MAIN.account T0 INNER JOIN MAIN.employee T1 ON (T0.open_emp_id = T1.emp_id)) INNER JOIN MAIN.branch T2 ON (T1.assigned_branch_id = T2.branch_id) WHERE (T1.start_date <= '2004-01-01') AND ((T1.title = 'Teller') OR (T1.title = 'Head Teller')) AND (T2.name = 'Woburn Branch')
    Account3 {a3AccountId = 1, a3CustId = 1, a3OpenDate = 2000-01-15, a3ProductCd = "CHK"}
    Account3 {a3AccountId = 2, a3CustId = 1, a3OpenDate = 2000-01-15, a3ProductCd = "SAV"}
    ...

Great!

To understand how to express more complicated relations and how to update tables, please read [Examples](examples.html).