Clojure - Databases

In order to use the database functionality, please ensure to first download the jdbc files from the following url − https://codeload.github.com/clojure/java.jdbc/zip/master
You will find a zip file which has the necessary drivers for Clojure to have the ability to connect to databases. Once the zip file is extracted, ensure to add the unzipped location to your classpath.

The main file for database connectivity is a file called jdbc.clj in the location clojure/java.
The clojure jdbc connector supports a wide variety of databases, some of which are the following.

• H2Database
• Oracle
• Microsoft SQL Server
• MySQL
• PostgreSQL

In our example, we are going to use MySQL DB as an example.
The following operations are possible in Clojure with regards to Databases.

Database Connection

Before connecting to a MySQL database, make sure of the following −

• You have created a database TESTDB.
• You have created a table EMPLOYEE in TESTDB.
• This table has fields FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
• User ID "testuser" and password "test123" are set to access TESTDB.
• Ensure you have downloaded the ‘mysql jar file’ and added the file to your classpath.
• You have gone through MySQL tutorial to understand MySQL Basics.

Syntax

Following is the syntax to create a connection in Clojure.

(def connection_name {
   :subprotocol “protocol_name”
   :subname “Location of mysql DB”
   :user “username” :password “password” })

Parameters − ‘connection_name’ is the name to be given to the connection. ‘subprotocol’ is the protocol to be used for the connection. By default 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.
Return Value − This will provide a connection string, which can be used in subsequent mysql operations.
The following example shows how to connect to the tables in the information schema and retrieve all the data in the table.

Example

(ns test.core
   (:require [clojure.java.jdbc :as sql]))
(defn -main []
   (def mysql-db {
      :subprotocol "mysql"
      :subname "//127.0.0.1:3306/information_schema"
      :user "root"
      :password "shakinstev"})
   (println (sql/query mysql-db
      ["select table_name from tables"]
      :row-fn :table_name)))

Querying Data

Querying data on any database means to fetch some useful information from the database. Once a database connection is established, you are ready to make a query into this database. Following is the syntax by which data can be queried using Clojure.

Syntax

clojure.java.jdbc/query dbconn
["query"]
   :row-fn :sequence

Parameters − ‘dbconn’ is the name of the connection used to connect to the database. ‘query’ is the query string used to fetch data from the database. ‘:sequence’ is by default all the rows of data fetched from the database and is returned as a sequence. The necessary operations on the sequence can then be done to see what data has been fetched.
Return Value − This will return a sequence, which will have the rows of data from the query operation.
The following example shows how to connect to the employee table and fetch the first_name column of the rows in the table.

Example

(ns test.core
   (:require [clojure.java.jdbc :as sql]))
(defn -main []
   (def mysql-db {
      :subprotocol "mysql"
      :subname "//127.0.0.1:3306/testdb"
      :user "root"
      :password "shakinstev"})
   (println (sql/query mysql-db
      ["select first_name from employee"]
      :row-fn :first_name)))

From the above code, we can see that

• The query of “select first_name from employee” is passed as the query string.
• The :first_name is the sequence, which is returned as a result of the fetch operation.

If we assume that there is just one row in our database which contains a first_name value of John, following will be the output of the above program.

(John)

Inserting Data

It is required when you want to create your records into a database table. Following is the syntax by which data can be inserted using Clojure. This is done by using the ‘insert!’ function.

Syntax

clojure.java.jdbc/insert!
   :table_name {:column_namen columnvalue}

Parameters − ‘:table_name’ is the name of the table in which the insertion needs to be made. ‘{:column_namen columnvalue }’ is a map of all the column names and values, which need to be added as a row in the table.
Return Value − This will return nil if the insertion is made successfully.
The following example shows how to insert a record into the employee table in the testdb database.

Example

(ns test.core
   (:require [clojure.java.jdbc :as sql]))
(defn -main []
   (def mysql-db {
      :subprotocol "mysql"
      :subname "//127.0.0.1:3306/testdb"
      :user "root"
      :password "shakinstev"})
   (sql/insert! mysql-db
      :employee {:first_name "John" :last_name "Mark" :sex "M" :age 30 :income 30}))

If you now check your MySQL database and the employee table, you will see that the above row will be successfully inserted in the table.

Deleting Data

Rows can be deleted from a table by using the ‘delete!’ function. Following is the syntax on how this operation can be performed.

Syntax

clojure.java.jdbc/delete!
   :table_name [condition]

Parameters − ‘:table_name’ is the name of the table in which the insertion needs to be made. ‘condition’ is the condition used to determine which row needs to be deleted from the table.
Return Value − This will return the number of rows deleted.
The following example shows how to delete a record from the employee table in the testdb database. The example deletes a row from the table based on the condition that the age is equal to 30.

Example

(ns test.core
   (:require [clojure.java.jdbc :as sql]))
(defn -main []
   (def mysql-db {
      :subprotocol "mysql"
      :subname "//127.0.0.1:3306/testdb"
      :user "root"
      :password "shakinstev"})
   (println (sql/delete! mysql-db
      :employee ["age = ? " 30])))

If you had a record which had a row with age equal to the value of 30, that row will be deleted.

Updating Data

Rows can be updated from a table by using the ‘update!’ function. Following is the syntax on how this operation can be performed.

Syntax

clojure.java.jdbc/update!
   :table_name
{setcondition}
[condition]

Parameters − ‘:table_name’ is the name of the table in which the insertion needs to be made. ‘setcondition’ is the column which needs to be updated as mentioned in terms of a map. ‘condition’ is the condition which is used to determine which row needs to be deleted from the table.
Return Value − This will return the number of rows updated.
The following example shows how to delete a record from the employee table in the testdb database. The example updates a row from the table based on the condition that the age is equal to 30 and updates the value of income to 40.

(ns test.core
   (:require [clojure.java.jdbc :as sql]))
(defn -main []
   (def mysql-db {
      :subprotocol "mysql"
      :subname "//127.0.0.1:3306/testdb"
      :user "root"
      :password "shakinstev"})
   (println (sql/update! mysql-db
      :employee
      {:income 40}
      ["age = ? " 30])))

If you had a record which had a row with age equal to the value of 30, that row will be updated wherein the value of income will be set to 40.

Transactions

Transactions are mechanisms that ensure data consistency. Transactions have the following four properties −

• Atomicity − Either a transaction completes or nothing happens at all.
• Consistency − A transaction must start in a consistent state and leave the system in a consistent state.
• Isolation − Intermediate results of a transaction are not visible outside the current transaction.
• Durability − Once a transaction was committed, the effects are persistent, even after a system failure.

Example

The following example shows how to implement transactions in Clojure. Any operations which needs to be performed in a transaction needs to be embedded in the ‘with-dbtransaction’ clause.

(ns test.core
   (:require [clojure.java.jdbc :as sql]))
(defn -main []
   (def mysql-db {
      :subprotocol "mysql"
      :subname "//127.0.0.1:3306/testdb"
      :user "root"
      :password "shakinstev"})
   (sql/with-db-transaction [t-con mysql-db]
      (sql/update! t-con
         :employee
         {:income 40}
         ["age = ? " 30])))