Thursday, November 30, 2017

Haskell to MySQL via YeshQL (Part 1.)

As I was looking for an easy way to talk to Postgres from Clojure, I discovered Yesql. I wanted to find something similar in Haskell, and I found YeshQL. It's a template parsing library on top of HDBC, exactly what I needed to keep SQL and Haskell code separate.

This blog post will show you how you can easily get YeshQL up and running and run queries against MySQL. I'll build a simple CRUD console app that you can follow along, I'll list commit points at the end of each section.

The Clojure tutorial I created for my blog posts is named Kashmir, I'll name this project Hashmir.

You will need stack and ghc installed, I have stack Version 1.5.1 x86_64 hpack-0.17.1 and ghc version 8.0.2. MySQL is also needed, I have version 5.7.20, but I won't use anything fancy as far as the database goes, if you have MySQL installed, I am sure that will do it.

Generate the project with stack

Generate the project with this command: stack new hashmir. Go inside the directory, build and deploy the app with stack build && stack install. Run the skeleton app:

% ~/.local/bin/hashmir-exe
someFunc

Commit point

Using hpack

Since I learned about hpack, I never touch a Haskell project's cabal file any more. This blog post assumes you are familiar with this tool, feel free to learn about hpack more before you proceed.

Add this package.yaml to the project's root directory:

name: hashmir
version: 0.1.0.0
author: Attila Domokos <adomokos@gmail.com>
maintainer: adomokos@gmail.com
copyright: 2017 Attila Domokos
category: Console App
homepage: https://github.com/adomokos/hashmir#readme

ghc-options: -Wall

dependencies:
  - base >= 4.7 && < 5

library:
  source-dirs: src/
  exposed-modules:
    - Lib

executables:
  hashmir-exe:
    source-dirs: app/
    main: Main.hs
    dependencies:
      hashmir
    ghc-options: -threaded -rtsopts -with-rtsopts=-N

Feel free to use your name and Github repo in this file.
Delete and regenerate the project's cabal file with this command: rm -f hashmir.cabal && stack build. stack install should produce the same executable file.

Commit point

Setting up the Database

We will need a MySQL user and a database we can use in this project. Let's add a Makefile to script that out for us. You might need the master or root user in MySQL to create a role and grant access. If the Makefile target does not work, just log on to the MySQL console with your root account and add the role and grant access with the scripts you see in the Makefile.

This is the schema file we will work with. I won't include it in this post, but this script should drop and recreate the tables. Put it into the ./resources directory.

These targets in the Makefile will create the role and will rebuild the database:

create-db-user: ## Creates a DB user with the root MySQL user
    mysql -u root --host $(HOST) -e "CREATE USER '$(DBUSER)'@'$(HOST)' IDENTIFIED BY '$(DBPASSWD)';" > /dev/null 2>&1
    mysql -u root --host $(HOST) -e "GRANT ALL PRIVILEGES ON `$(DBNAME)`.* TO '$(DBUSER)'@'$(HOST)';" > /dev/null 2>&1

build-db: ## Builds the DB
    @echo "Dropping and rebuilding database $(DBNAME)"
    @mysql -u $(DBUSER) --password='$(DBPASSWD)' --host $(HOST) -e "DROP DATABASE IF EXISTS $(DBNAME);" > /dev/null 2>&1
    @mysql -u $(DBUSER) --password='$(DBPASSWD)' --host $(HOST) -e "CREATE DATABASE $(DBNAME);" > /dev/null 2>&1
    @mysql -u $(DBUSER) --password='$(DBPASSWD)' --host $(HOST) $(DBNAME) < resources/schema.sql > /dev/null 2>&1

You should be able to execute make build to rebuild the app, and make run to rebuild the DB and run the app:

% make run
Dropping and rebuilding database crud_yeshql_test
time ~/.local/bin/hashmir-exe
someFunc

real    0m0.011s
user    0m0.002s
sys 0m0.007s

Commit point

Writing the First Query

There are two parts of using YeshQL's code:

  1. The SQL templates
  2. Code that uses the generated functions from the template

Modify the app/Main.hs file like this:

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

module Main where

import Database.YeshQL
import Database.HDBC.MySQL

[yesh|
    -- name:countClientSQL :: (Int)
    SELECT count(id) FROM clients;
|]

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

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

main :: IO ()
main = countClient

When you try to build the project (I conveniently use make build) one of these errors are displayed:

Failed to load interface for 'Database.YeshQL'

I started referencing Database.YeshQL, however, I did not add that library to the project. This is where hpack is helpful, we only have to add it to the package.yaml file, that will generate a cabal file with the correct references.

Let's modify the dependencies section of package.yaml file like this:

...

dependencies:
  - base >= 4.7 && < 5
  - yeshql
  - HDBC
  - HDBC-mysql

...

When I try to build the project, I get the following error:

Error: While constructing the build plan, the following exceptions were encountered:

In the dependencies for hashmir-0.1.0.0:
    yeshql must match -any, but the stack configuration has no specified version (latest applicable is 3.0.1.3)
needed since hashmir-0.1.0.0 is a build target.

Recommended action: try adding the following to your extra-deps in ~/hashmir/stack.yaml:
- yeshql-3.0.1.3

By modifying the extra-deps: [] array like this in stack.yaml

extra-deps: [
    yeshql-3.0.1.3
]

will download YeshQL for the project and build it successfully.

When I run the app (use make run to do it), this is what I see:

% make run
Dropping and rebuilding database hashmir_test
time ~/.local/bin/hashmir-exe
There are 0 records.

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

YeshQL generated a function on-the-fly, named countClientSQL. That's the function I invoked in the countClient function. Since there are no records in the table, 0 was returned from the function.

Commit point

We set up the project, ran the first query against MySQL from Haskell via YeshQL templates. This brings us to the end of part 1 of this series. In the next article, we'll start adding more SQL queries to insert and query various records.