Advertisement

A Clojure App with Interaction with MySQL

P.Madanasekaran

Introduction:  Storing and retrieving application data in and from a database is a common task for developers. As in every programming language, there are a number of drivers and libraries to interact with databases in Clojure. Rich Hickey, its creator, has also created a logical/NoSQL database- Datomic- in Clojure and Java. But now we will focus on the interaction of Clojure Apps with Relational databases. By virtue of running on the Java Virtual Machine, Clojure works with any database that can be accessed via Java Database Connectivity (JDBC). With it, we can easily access a large number of RDBMS databases, such as MySQL, MS SQL Server, PostgreSQL, and Oracle. The core library for dealing with relational databases is clojure.data.jdbc. When using this library we have to write custom SQL for each type of database we intend to use.

Other libraries: There are many Clojure database libraries built upon clojure.data.jdbc.  Each one serves a different purpose and we can choose one according to our need. If we don’t want to mix SQL in our Clojure code and want to keep our SQL in a separate file, we can use the Yesql or HughSQL library. If we want to avoid writing SQL altogether and want our library to generate SQL from Clojure data structures, we can use a library like “HoneySQL”. Or if we want our library to generate SQL from a Clojure DSL we can use “Korma”. For a simple application like ours, the clojure.data.jdbc library is sufficient, as it provides all the functionality we need without any additional complexity. We’ll use MySQL as our database.

Clojure.java.jdbc
It provides a simple abstraction for CRUD (create, read, update, delete) operations on a SQL database, along with basic transaction support. Basic DDL operations are also supported (create table, drop table, access to table metadata).Maps are used to represent records, making it easy to store and retrieve data. Results can be processed using any standard sequence operations.

 Create a sample
Create a new Clojure app using “Leiningen”. Leiningen can generate an application skeleton (or scaffolding) from a plethora of different templates. There’s a template for nearly everything such as Clojurescript projects, web applications and much more. To generate a new application, we use the new Leiningen task whose  basic syntax is as follows:

Lein new [$TEMPLATE_NAME] [app] $PROJECT_NAME

The new task expects at-least a name for the project ($PROJECT_NAME).Optionally, we can provide a specific ($TEMPLATE_NAME) and/or  the key-word “app”  .If we don’t specify a template and the key-word “app”, then lein  tool will use the default template, which is   a general template for developing libraries. The use of key word “app” indicates that we want lein to generate an application using the default template rather than a library.

lein new app myapp
After lein generates the project, if you open the folder in LightTable editor you can see something similar to what is found under in Figure-1

You can see “myapp”  project folder contains a number of sub-folders and files . The important files are:

  1. /src/myapp/core.clj –it contains the main() function which is the entry-point for starting  the application.
  2. “project.clj” –the configuration file.

As Leiningen uses a Clojure configuration file, unlike Maven which uses “XML”, it is easy to add the dependencies. In the “project.clj” add the following dependencies:
[org.clojure/java.jdbc "0.7.0-alpha1”]
                [mysql/mysql-connector-java "5.1.18"]
The first is the Clojure wrapper for java drivers for SQL databases and the second is the java driver for MySQL.

Code for database connection

To interact with a database using “clojure.java.jdbc”, all you need is a connection specification.  This specification takes the form of a plain Clojure map with values indicating the connection parameters. It is given below:

(def db {
:subprotocol "mysql"
:subname "//127.0.0.1:3306/employeedb"
:user "root"
:password "mysql"})

 We’ve defined a  variable ‘db’ passing the connection parameters in a map. The ‘subprotocol’ is the protocol to be used for the connection. We will be using the mysql protocol. ‘subname’ is the url to connect to the mysql db along with the database name. ‘user’ is the username used to connect to the database. ‘password’ is the password to be used to connect to the database. A connection is returned and it can be used in subsequent mysql operations.

For the above code to work, we require an instance of the MySQL database located on localhost using schema called  employeedb along with the username and password. So create a database “employeedb”  in your local MySQL installation .If your username and password  differ, you can change the above code accordingly.

Code for fetching database records:

Unlike object oriented languages, Clojure does not require us to define a separate model in code and map it to the one defined in the database. Instead, the query results are represented by sequences of maps where the keys correspond to the column names in the tables being queried. For fetching the records we can use the query function provided by our library.  The query function takes two parameters.1) database connection name 2) vector containing SQL. It returns a result-set after performing a simple database query. A sample query is given below:

(sql/query db
["select * from emp"]
)

The result set by default comes with ‘doall’ function to force the complete realization of lazy sequence returned. We need not write any code to iterate the result set.
The vector passed to query can take, apart from SQL, also optional parameters which specify how to construct the result-set.

Run the APP:  Before running the application, ensure that you create a table ‘emp’. The schema for the table can be as under:

 CREATE TABLE emp ( id SMALLINT UNSINGNED NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, department VARCHAR(50) NOT NULL, PRIMARY KEY(id) );

Insert a few records making use of a GUI tool like HeidiSQL, as shown in Figure-1 below:

If you execute “lein run” , the necessary dependencies will be downloaded. You may see in the console the records in the database displayed in the form of a sequence of maps as under:
{{:id 1, :name Ram, :department “ECE”}, {:id 2, :name Samy :department “ECE”}, {:id 3 :name Madhan :department CS}}

Code for insertion of a record

For insertion of a record we can use insert!() function in the library as shown below:

(sql/insert! db
:emp { :name "Sekaran" :department "CS"})

The insert!()  function in the clojure.java.jdbc library takes the following parameters.

  1. Db Connection var  2)table into which the data has to be inserted  3) a map representing the columns as keys and values for the same.

The complete code

            The full code for the core.clj of the application is given hereunder. You can see all the functions and variables for connecting to database and getting and inserting a record are enclosed within main function .

(ns lrn.core
(:require [clojure.java.jdbc :as sql]))
(defn -main [] ;;entry point of the application
(def db {
:subprotocol "mysql"
:subname "//127.0.0.1:3306/employeedb"
:user "root"
:password "mysql"})

(sql/insert! db
:emp { :name "Sekaran" :department "CS"})

(println(sql/query db ;;println() used to print data to the console
["select * from emp"]
)))

Conclusion

Clojure treats even code as data. If you overcome your inhibition for LISP syntax, you can see that Clojure is a powerful language with a concise syntax which can also make use of the rich set of libraries available on JVM. The lein tool comes in handy and you can specify the dependencies in Clojure (unlike Maven which expects “XML” ) and this goes a long way in simplification of our code. Our next article will cover how a Clojure web application can interact with a database using the above clojure.java.jdbc library. Many popular platforms, such as Rails or Django, take the approach of providing a monolithic framework for building web applications. The Clojure community has traditionally shunned this approach in favour of using composable components that the user can assemble in a way that best fits his particular application. But in this approach there is a certain amount of boilerplate to set up for any given project. The Clojure community tackles this problem by using Leiningen templates that generate the necessary boilerplate for specific types of projects. As in the earlier article Developing Web Apps in Clojure,  we will use compojure-app template to create our web application. But for a bigger web app one can use Luminus template. Luminus like any other template removes the burden or having to find the libraries, configure middleware, and add the common boiler-plate. But as it makes use of a number of libraries for different functionalities, the application generated by the template is ready for deployment out of the box. Hence this can be considered as a mini web framework. When compared to Rails or Phoenix, the only part that’s missing is the tasks for generating domain logic for the application.








Added on February 12, 2017 Comment

Comments

#1

Saibal Das commented on February 14, 2017 at 7:23 p.m.

Thanks a lot for your hint, very useful.

#2

Rahul commented on February 18, 2017 at 1:02 p.m.

Hello, I am Rahul Pandy and I study Electrical and Computer Engineering at Bangalore University. Computer programming has recently become one of my hobbies and I started programming by teaching myself C (ouch!). This blog is broadly about my fascinations with functional programming but specifically about my progress in attempting to learn a new functional language: Clojure. My goal is to learn Clojure and develop a web app using Noir and deploy it on the Google App Engine.

#3

Madanasekaran commented on February 18, 2017 at 3:17 p.m.

Noir framework has been deprecated.For a bigger web app one can use Luminus template now.

Post a comment