Tuesday, December 12, 2017

Haskell to MySQL via YeshQL (Part 2.)

In the previous blog post, we built the skeleton of a console app in Haskell that talks to MySQL via YeshQL. We used a Makefile to rebuild the database, compile the code and run the app. This is the commit point where we left it at the end of Part 1.

In this article we will add functionality to create clients records, we will extract the logic that acquires, commits and closes the connection, and finally, we will move the database related code into its own module.

Insert a Client record

We can query the clients table, however, there are no records in that table. Let's add one script to the SQL template.

Modify the YeshQL templates by adding the following code:

 [yesh|
     -- name:countClientSQL :: (Int)
     SELECT count(id) FROM clients;
     ;;;
     -- name:insertClientSQL
     -- :client_name :: String
     -- :subdomain :: String
     INSERT INTO clients (name, subdomain) VALUES (:client_name, :subdomain);
 |]

Three semicolons are used to separate SQL statements. The -- name is used to provide a function name that we can refer to in our code. The two lines are the input arguments to the generated function. Those arguments are the values in the INSERT statement on the last line.

Let's add the following Haskell function to insert a new Client record:

insertClient :: String -> String -> IO ()
insertClient name subdomain = do
    conn <- getConn
    clientId <- insertClientSQL name subdomain conn
    commit conn
    disconnect conn
    putStrLn $ "New client's id is " ++ show clientId

The generated insertClientSQL (note the name in the SQL template) is invoked with the two specified arguments plus the connection. The uncommitted connection will not write the record to the table, without that statement the countClientSQL function would return 0 records.
Disconnecting a connection is a good practice (if you can't pool those connections), free up resources when you don't need them.

Invoke the created insertClient function from main function like this:

main = do
    insertClient "TestClient" "testclient"
    countClient

When I try to build the app (use make build for it), I get the following errors:

/Haskell/hashmir/app/Main.hs:34:5: error:
    Variable not in scope: commit :: Connection -> IO a0

It turns out that Database.HDBC library is needed to invoke commit and disconnect.
Add the following import statement to the top of the file:

import qualified Database.HDBC as H

Update the function to use the qualified names for the commit and disconnect calls.

insertClient :: String -> String -> IO ()
insertClient name subdomain = do
    conn <- getConn
    clientId <- insertClientSQL name subdomain conn
    H.commit conn
    H.disconnect conn
    putStrLn $ "New client's id is " ++ show clientId

The project should successfully build, when you run the app (use make run to do it), you should see this output:

% make run
Dropping and rebuilding database hashmir_test
time ~/.local/bin/hashmir-exe
New client's id is 1
There are 1 records.

real 0m0.020s
user 0m0.009s
sys 0m0.006s

The database got rebuilt, one client record was inserted and the count function counted that record.

Commit point

Introducing: withConn

Committing and disconnecting a connection is generally a good practice. Let's match the countClient function with the insertClient function and call commit and disconnect the connection there as well.

countClient :: IO ()
countClient = do
    conn <- getConn
    Just (clientCount) <- countClientSQL conn
    H.commit conn -- added line
    H.disconnect conn -- added line
    putStrLn $ "There are " ++ show clientCount ++ " records."

Now both the countClient and the insertClient has the same duplicated logic:

    conn <- getConn
    ...
    H.commit conn -- added line
    H.disconnect conn -- added line

This reminds me of the use of withFile from the IO module. withFile accepts a lambda where the handle is passed to it and the code in the lambda can use the provided handle. We need the same thing here, withConn would accept an active connection. Consider this function:

withConn :: (Connection -> IO b) -> IO b
withConn f = do
    conn <- getConn
    result <- f conn
    H.commit conn
    H.disconnect conn
    return result

Our refactored insertClient function would look like this:

insertClient :: String -> String -> IO ()
insertClient name subdomain = do
    clientId <-
        withConn (\conn -> do
            insertClientSQL name subdomain conn
        )
    putStrLn $ "New client's id is " ++ show clientId

When you build the project and run it, it should work without errors.

Commit point

Thanks to Haskell's currying, this function can be further simplified. No need to provide the input argument in the lambda:

insertClient :: String -> String -> IO ()
insertClient name subdomain = do
    clientId <-
        withConn (do insertClientSQL name subdomain)
    putStrLn $ "New client's id is " ++ show clientId

This looks much better, but we can further simplify this code:

insertClient :: String -> String -> IO ()
insertClient name subdomain = do
    clientId <- withConn $ do insertClientSQL name subdomain
    putStrLn $ "New client's id is " ++ show clientId

Now that function is easy to read!

Commit point

Let's refactor the countClient function similarly.

countClient :: IO ()
countClient = do
    Just (clientCount) <- withConn countClientSQL
    putStrLn $ "There are " ++ show clientCount ++ " records."

Commit point

Extract Data Access Logic

Having code that prints information on the screen from the functions that talks with the database makes them mixed with responsibilities. They should only return primitives, and the caller main function should print the reports. Let's make those functions a bit more clean:

insertClient :: String -> String -> IO Integer
insertClient name subdomain = do
    withConn $ do insertClientSQL name subdomain

countClient :: IO (Maybe Int)
countClient = do withConn countClientSQL

I really like how Haskell makes the functions that uses IO impure or dirty: once they have been tainted, they are tainted. You should always try to isolate functions that are tainted from the pure ones as the pure ones are easier test - they have no side effects.

The main function is now responsible for reporting the result:

main :: IO ()
main = do
    clientId <- insertClient "TestClient" "testclient"
    putStrLn $ "New client's id is " ++ show clientId
    Just clientCount <- countClient
    putStrLn $ "There are " ++ show clientCount ++ " records."

Commit point

Move Data Logic Into a Library

Our Main.hs file has all the application logic. It looks pretty solid, let's move it into a library module. Copy all database access related code from app/Main.hs into src/Hashmir/Data.hs file like this:

{-#LANGUAGE TemplateHaskell #-}
{-#LANGUAGE QuasiQuotes #-}

module Hashmir.Data where

import Database.YeshQL
import qualified Database.HDBC as H
import Database.HDBC.MySQL

[yesh|
    -- name:countClientSQL :: (Int)
    SELECT count(id) FROM clients;
    ;;;
    -- name:insertClientSQL
    -- :client_name :: String
    -- :subdomain :: String
    INSERT INTO clients (name, subdomain) VALUES (:client_name, :subdomain);
|]

getConn :: IO Connection
getConn = do
    connectMySQL defaultMySQLConnectInfo {
        mysqlHost     = "localhost",
        mysqlDatabase = "hashmir_test",
        mysqlUser     = "hashmir_user",
        mysqlPassword = "shei7AnganeihaeF",
        mysqlUnixSocket = "/tmp/mysql.sock"
    }

withConn :: (Connection -> IO b) -> IO b
withConn f = do
    conn <- getConn
    result <- f conn
    H.commit conn
    H.disconnect conn
    return result

insertClient :: String -> String -> IO Integer
insertClient name subdomain =
    withConn $ insertClientSQL name subdomain

countClient :: IO (Maybe Int)
countClient = withConn countClientSQL

This is the same logic we had in the app/Main.hs file, but now it is in the Hashmir.Data module.

The Main module becomes small once we remove all the code we just moved out of it:

module Main where

import qualified Hashmir.Data as D

main :: IO ()
main = do
    clientId <- D.insertClient "TestClient" "testclient"
    putStrLn $ "New client's id is " ++ show clientId
    Just clientCount <- D.countClient
    putStrLn $ "There are " ++ show clientCount ++ " records."

We also have to tell Cabal where it can find this code. Change the Lib directive to Hashmir.Data in the package.yaml:

library:
  source-dirs: src/
  exposed-modules:
    - Hashmir.Data

The project should build and when you run the app it should insert and return the number of clients records:

% make run
Dropping and rebuilding database hashmir_test
time ~/.local/bin/hashmir-exe
New client's id is 1
There are 1 records.

real 0m0.022s
user 0m0.010s
sys 0m0.007s

Commit point

This last change wraps up our Part 2 in this series. We can now create clients records, count them with a simple withConn function that properly opens, commits and closes the connection.

In the third post in this series, I will show you how we can insert two records in one transaction, how we can deal with errors and how this logic can be tested.