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
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.
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
Writing the First Query
There are two parts of using YeshQL's code:
- The SQL templates
- 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.
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.
