In my last article, I started discussing Compojure, a Web framework written in the Clojure language. Clojure already has generated a great deal of excitement among software developers, in that it combines the beauty and expressive elegance of Lisp with the efficiency and ubiquity of the Java Virtual Machine (JVM). Clojure has other traits as well, including its famous use of software transactional memory (STM) to avoid problems in multithreaded environments.

As a Web developer and a longtime Lisp aficionado, I've been intrigued by the possibility of writing and deploying Web applications written in Clojure. Compojure would appear to be a simple framework for creating Web applications, built on lower-level systems, such as "ring", which handles HTTP requests.

In my last article, I explained how to create a simple Web application using the "lein" system, modify the project.clj configuration file and determine the HTML returned in response to a particular URL pattern ("route"). Here, I try to advance the application somewhat, looking at the things that are typically of interest to Web developers. Even if you don't end up using Clojure or Compojure, I still think you'll learn something from understanding how these systems approach the problem.

Databases and Clojure

Because Clojure is built on the JVM, you can use the same objects in your Clojure program as you would in a Java program. In other words, if you want to connect to a PostgreSQL database, you do so with the same JDBC driver that Java applications do.

Installing the PostgreSQL JDBC driver requires two steps. First, you must download the driver, which is available at Second, you then must tell the JVM where it can find the classes that are defined by the driver. This is done by setting (or adding to) the CLASSPATH environment variable—that is, put the driver in:

export CLASSPATH=/home/reuven/Downloads:$CLASSPATH

Once you have done that, you can tell your Clojure project that you want to include the PostgreSQL JDBC driver by adding two elements to the :dependencies vector within the defproject macro:

(defproject cjtest "0.1.0-SNAPSHOT"
  :description "FIXME: write description"
  :url ""
  :dependencies [[org.clojure/clojure "1.5.1"]
                 [compojure "1.1.5"]
                 [hiccup "1.0.3"]
                 [org.clojure/java.jdbc "0.2.3"]
                 [postgresql "9.1-901.jdbc4"]]
  :plugins [[lein-ring "0.8.5"]]
  :ring {:handler cjtest.handler/app}
  {:dev {:dependencies [[ring-mock "0.1.5"]]}})

Now you just need to connect to the database, as well as interact with it. Assuming you have created a database named "cjtest" on your local PostgreSQL server, you can use the built-in Clojure REPL (lein repl) to talk to the database. First, you need to load the database driver and put it into an "sql" namespace that will allow you to work with the driver:

(require '[ :as sql])

Then, you need to tell Clojure the host, database and port to which you want to connect. You can do this most easily by creating a "db" map to build the query string that PostgreSQL needs:

(def db {:classname "org.postgresql.Driver" 
	 :subprotocol "postgresql"
	 :subname (str "//" "localhost" ":" 5432 "/" "cjtest")
	 :user "reuven"
	 :password ""})

With this in place, you now can issue database commands. The easiest way to do so is to use the with-connection macro inside the "sql" namespace, which connects using the driver and then lets you issue a command. For example, if you want to create a new table containing a serial (that is, automatically updated primary key) column and a text column, you could do the following:

(sql/with-connection db 
    (sql/create-table :foo [:id :serial] [:stuff :text]))

If you then check in psql, you'll see that the table has indeed been created, using the types you specified. If you want to insert data, you can do so with the sql/insert-values function:

(sql/with-connection db (sql/insert-values 
 ↪:foo [:stuff] ["first post"]))

Next, you get back the following map, indicating not only that the data was inserted, but also that it automatically was given an ID by PostgreSQL's sequence object:

{:stuff "first post", :id 1}

What if you want to retrieve all of the data you have inserted? You can use the sql/with-query-results function, iterating over the results with the standard doseq function:

(sql/with-connection db
    (sql/with-query-results resultset ["select * from foo"]
        (doseq [row resultset] (println row))))

Or, if you want only the contents of the "stuff" column, you can use:

(sql/with-connection db
    (sql/with-query-results resultset ["select * from foo"]
        (doseq [row resultset] (println (:stuff row)))))

Databases and Compojure

Now that you know how to do basic database operations from the Clojure REPL, you can put some of that code inside your Compojure application. For example, let's say you want to have an appointment calendar. For now, let's assume that there already is a PostgreSQL "appointments" databases defined:

CREATE TABLE Appointments (
       id SERIAL,
       meeting_at TIMESTAMP,
       meeting_with TEXT,
       notes TEXT

INSERT INTO Appointments (meeting_at, meeting_with, notes) 
      VALUES ('2013-july-1 12:00', 'Mom', 'Always good to see Mom');

You'll now want to be able to go to /appointments in your Web application and see the current list of appointments. To do this, you need to add a route to your Web application, such that it'll invoke a function that then goes to the database and retrieves all of those elements.

Before you can do so, you need to load the PostgreSQL JDBC driver into your Clojure application. You can do this most easily in the :require section of your namespace declaration in handler.clj:

(ns cjtest.handler
  (:use compojure.core)
  (:require [compojure.handler :as handler]
            [compojure.route :as route]
            [ :as sql]))

(I did this manually in the REPL with the "require" function, with slightly different syntax.)

You then include your same definition of "db" in handler.clj, such that your database connection string still will be available.

Then, you add a new line to your defroutes macro, adding a new /appointments URL, which will invoke the list-appointments function:

(defroutes app-routes
  (GET "/" [] "Hello World")
  (GET "/appointments" [] list-appointments)
  (GET "/fancy/:name" [name] say-hello)
  (route/resources "/")
  (route/not-found "Not Found"))

Finally, you define list-appointments, a function that executes an SQL query and then grabs the resulting records and turns them into a bulleted list in HTML:

(defn list-appointments
   [:h1 "Current appointments"]
     (sql/with-connection db
         (sql/with-query-results rs ["select * from appointments"]
            (map format-appointment rs))))]))

Remember that in a functional language like Clojure, the idea is to get the results from the database and then process them in some way, handing them off to another function for display (or further processing). The above function produces HTML output, using the Hiccup HTML-generation system. Using Hiccup, you easily can create (as in the above function) an H1 headline, followed by a "ul" list.

The real magic happens in the call to sql/with-query-results. That function puts the results of your database call in the rs variable. You then can do a number of different things with that resultset. In this case, let's turn each record into an "li" tag in the final HTML. The easiest way to do that is to apply a function to each element of the resultset. In Clojure (as in many functional languages), you do this with the map function, which transforms a collection of items into a new collection of equal length.

What does the format-appointment function do? As you can imagine, it turns an appointment record into HTML:

(defn format-appointment [one-appointment]
 (html [:li (:meeting_at one-appointment)
	 " : "
	 (:meeting_with one-appointment)
	 " (" (:notes one-appointment) ")" ]))

In other words, you'll treat the record as if it were a hash and then retrieve the elements (keys) from that hash using Clojure's shorthand syntax for doing so. You wrap that up into HTML, and then you can display it for the user. The advantage of decomposing your display functionality into two functions is that you now can change the way in which appointments are displayed, without modifying the main function that's called when /appointments is requested by the user.


Reuven M. Lerner, Linux Journal Senior Columnist, a longtime Web developer, consultant and trainer, is completing his PhD in learning sciences at Northwestern University.


Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Fully in keeping with the

sollen's picture

Fully in keeping with the tradition of the brand, the name of the Veneno originates from a legendary fighting bull. Veneno is the name of one of the Tractor Work Lights strongest and most aggressive fighting bulls ever.