LJ Archive

At the Forge


Reuven M. Lerner

Issue #232, August 2013

In this article, Reuven shows how to connect a simple Clojure Web app to a PostgreSQL database.

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 jdbc.postgresql.org. 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 "http://example.com/FIXME"
  :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 '[clojure.java.jdbc :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]
            [clojure.java.jdbc :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.

Inserting Data

Let's say you also want to insert data into your appointment book. To do that, you need an HTML form that then submits itself to a URL on your site. Let's first create a simple form—as always, written as a function:

(defn new-meeting-form
  [ req ]
  (html [:form {:method "POST" :action "/create-meeting"}
         [:p "Meeting at (in 2013-06-28T11:08 format): " 
         ↪[:input {:type "text" :name "meeting_at"}]]
         [:p "Meeting with: " [:input {:type "text" 
          ↪:name "meeting_with"}]]
         [:p "Notes: " [:input {:type "text" :name "notes"}]]
         [:p [:input {:type "submit" :value "Add meeting"}]]]))

Notice how the Hiccup library again lets you define HTML tags easily. In this case, because it's a form, you need to tell the form to which URL it should be submitted. So in this example, that'll be the /create-meeting URL. Thus, you need to define both /new-meeting and /create-meeting in your defroutes macro call:

(defroutes app-routes
  (GET "/" [] "Hello World")
  (GET "/meetings" [] list-meetings)
  (GET "/new-meeting" [] new-meeting-form)
  (POST "/create-meeting" [] create-meeting)
  (GET "/fancy/:name" [name] say-hello)
  (route/resources "/")
  (route/not-found "Not Found"))

As you can see, the routes distinguish between GET and POST requests. Thus, a GET request to /create-meeting will not have any effect (that is, it will result in the “not found” message being displayed); a POST request is needed to make it work.

Everything comes together when you want to add a new meeting to your database. You get the parameters from the submitted form and then insert them into the database.

I'm still learning about Clojure and Compojure and continue to discover new libraries of functions that can make it easier to create HTML forms and work with databases. For example, I've recently discovered SQLKorma, a library that seems almost like Ruby's ActiveRecord, in that it provides a DSL that creates database queries.

The power of Clojure, like all Lisps, is partly based on the idea that you do everything in small steps and then combine those steps for the full power. Here, for example, is the function I wrote to add a new record (meeting) to the database:

(defn create-meeting
  (sql/with-connection db
    (let [form-params (:form-params req)
          meeting-at-string (get form-params "meeting_at")
          meeting-at-parsed (clj-time.format/parse 
          meeting-at-timestamp (clj-time.coerce/to-timestamp 
          meeting-with (get form-params "meeting_with")
          notes (get form-params "notes")]
   (sql/insert-values :appointments
                      [:meeting_at :meeting_with :notes]
                      [meeting-at-timestamp meeting-with notes]))

The first and final parts of the function are similar in many ways to the database row insertion that you executed outside Compojure. You use sql/with-connection to connect to a database, and within that use sql/insert-values to insert a row into a specific table.

The interesting part of this function is, I believe, what happens in the middle. Using the “let” form, which performs local bindings of names to values, I can grab the values from the submitted HTML form elements, preparing them for entry into the database.

I further take advantage of the fact that Clojure's “let” allows you to bind names based on previously bound names. Thus, I can set meeting-at-string to the HTML form value, and then meeting-at-parsed to the value I get after converting the string to a parsed Clojure value, and then meeting-at-timestamp to turn it into a data type that both Clojure and PostgreSQL can handle easily.

Much of the heavy lifting here is being done by the clj-time package, which handles a wide variety of different date/time packages.

In the end, you're able to go to /new-meeting, enter appropriate data into the HTML form and save that data to the database. You then can go to /meetings and view the full list of meetings you have set.


I always have loved Lisp and often have wished I could find a way to use it practically in my day-to-day work. (Not that I dislike Ruby and Python, mind you, but the brainwashing I received in college was quite effective.) Playing with Clojure as a language, and Compojure to develop Web applications, has been a refreshing experience—one that I intend to continue trying and that I encourage you to attempt as well.

Web developer, trainer and consultant Reuven M. Lerner is finishing his PhD in Learning Sciences at Northwestern University. He lives in Modi'in, Israel, with his wife and three children. You can read more about him at lerner.co.il, or contact him at reuven@lerner.co.il.

LJ Archive