CRUD in Clojure

Clj-DBCP and SQLRat are recently created Clojure libraries to deal with relational databases. This post describes how to use them to carry out database CRUD (Create, Retrieve, Update, Delete) operations in Clojure without installing a database.

For this example we will use the in-memory instance of the H2 embedded database. Let us create a project using Leiningen.

lein new crud

Edit the project.clj file as follows:

(defproject crud "1.0.0-SNAPSHOT"
  :description "CRUD example"
  :dependencies [[org.clojure/clojure "1.2.0"]
                 [org.clojure/clojure-contrib "1.2.0"]
                 [org.bituf/clj-dbcp "0.1"]
                 [org.bituf/sqlrat   "0.2"]
                 [com.h2database/h2 "1.2.141"]]
  :dev-dependencies [[swank-clojure "1.2.1"]]
  :main crud.core)

and get the dependencies:

lein deps

Now we will edit the core.clj file as follows:

(ns crud.core
  (:use org.bituf.clj-dbcp)
  (:use org.bituf.sqlrat.entity)
  (:gen-class))


(def db (db-spec (h2-memory-datasource)))


(defrecord Employee [])


(def emp-type
  (entity-meta :emp :empid (from-row Employee.)
    :cols [[:empid   :int "NOT NULL PRIMARY KEY"]
           [:empname "VARCHAR(50)" "NOT NULL"]
           [:dob     "DATE" "NOT NULL"]]))


(extend-entity Employee emp-type)


(defn new-emp [id name dob]
  (Employee. {} {:empid id :empname name :dob dob}))


;; create the table
(defn create-all-tables []
  (println "Creating employee table")
  (in-txn db
    (create-table emp-type)))


(def ymd-fmt (java.text.SimpleDateFormat. "yyyy-MM-dd"))


(defn ymd
  "Accept y, m and d. Return a new date based on input."
  ([y m d]
    (ymd (format "%d-%d-%d" y m d)))
  ([ymd]
    (.parse ymd-fmt ymd)))


(defn print-all-emp
  ([msg]
    (println msg)
    (print-all-emp))
  ([]
    (println "All employee records")
    (in-db db
      (let [all (find-by-criteria emp-type)]
        (println "All employees")
        (print-entities all)))))


(defn crud []
  (let [e1 (new-emp 1 "Joe Walker" (ymd "1977-10-10"))
        e2 (new-emp 2 "Mary Rayle" (ymd "1983-06-15"))]
    ;; insert
    (println "Inserting employee records")
    (in-txn db
      (save e1)
      (save e2))
    (print-all-emp "After insert")
    ;; retrieve by ID
    (in-txn db
      (let [r (find-by-id emp-type 1)]
        ;; update
        (save (assoc r :empname "Derek Smith"))))
    ;; print after update
    (print-all-emp "After update")
    ;; delete
    (in-txn db
      (delete emp-type 1))
    ;; print after delete
    (print-all-emp "After delete")))


(defn -main [& args]
  (create-all-tables)
  (crud))

Breakdown of this file:
1. An in-memory data source instance is created using the H2 database and bound to the var db.
2. We define an entity Employee (meta data emp-type). For creating instances of Employee data type we use the factory function new-emp and function ymd helps create date instances.
3. We carry out the CRUD operations in the function crud.
4. Helper functions create-all-tables and print-all-emp contain commonly used functionality.
5. The -main function is the entry point when executed from an executable JAR.

Now we try to build the file:

lein uberjar

and run it

java -jar crud-1.0.0-SNAPSHOT-standalone.jar

The output should like the following:

Creating employee table
Inserting employee records
After insert
All employee records
Executing SQL...
["SELECT * FROM emp"]
All employees
empid | empname    | dob
----- | ---------- | ----------
1     | Joe Walker | 1977-10-10
2     | Mary Rayle | 1983-06-15
Executing SQL...
["SELECT * FROM emp WHERE empid=?" 1]
After update
All employee records
Executing SQL...
["SELECT * FROM emp"]
All employees
empid | empname     | dob
----- | ----------- | ----------
1     | Derek Smith | 1977-10-10
2     | Mary Rayle  | 1983-06-15
After delete
All employee records
Executing SQL...
["SELECT * FROM emp"]
All employees
empid | empname    | dob
----- | ---------- | ----------
2     | Mary Rayle | 1983-06-15