Using PostgreSQL with Compojure

Jump to Comments

I am writing a Compojure application that will host my journal on EricLavigne.net. In a previous article, I showed how to setup Compojure to run on a Slicehost VPS. The next step is to setup a database to store the journal articles. In this article I will show how to setup a PostgreSQL database, store some example articles in that database, and display those articles in a Compojure application.

Setting up PostgreSQL

The first step is to setup PostgreSQL. This includes installing the postgresql package, starting the database server, and creating a database. The database administration commands used below are in /usr/lib/postgresql/8.3/bin.

apt-get install postgresql

sudo -u postgres initdb -D /var/lib/postgresql/data
sudo -u postgres mkdir /var/lib/postgresql/log
sudo -u postgres pg_ctl -D /var/lib/postgresql/data   \
     -l /var/lib/postgresql/log/postgres.log start
sudo -u postgres createdb -O postgres production

Adding articles to the database

We need some example articles for testing whether the application is working correctly.

First we login to PostgreSQL.

psql -U postgres production

Next, from the PostgreSQL prompt, we create a new table for storing articles.

create table article (
     id           serial primary key,
     title        text not null unique check (trim(title)  ''),
     description  text not null default '',
     body         text not null default '',
     created      timestamp not null default now(),
     updated      timestamp not null default now(),
     published    timestamp null default null
);

Next, create two example articles.

insert into article (title, description, body) values
     ('Article 1', 'My first article',
      '<p>Paragraph 1 in article 1</p><p>Another paragraph</p>'),
     ('Article 2', 'My second article',
      '<p>This article also has a paragraph</p><p>And another</p>');

We’re finished with the PostgreSQL prompt, so we quit the prompt.

\q

Accessing PostgreSQL from Clojure

In order to access PostgreSQL from Clojure, we need a new library.

cd /root/install
wget http://jdbc.postgresql.org/download/postgresql-8.3-604.jdbc4.jar

In order for that library to be accessible to our application, it needs to be added to the classpath in /root/site/run. The new library can be added as the second-to-last line as shown below.

java -cp \
/root/install/compojure/compojure.jar:\
/root/install/compojure/deps:\
/root/install/compojure/deps/clojure.jar:\
/root/install/compojure/deps/clojure-contrib.jar:\
/root/install/compojure/deps/jetty-6.1.14.jar:\
/root/install/compojure/deps/jetty-util-6.1.14.jar:\
/root/install/compojure/deps/servlet-api-2.5-6.1.14.jar:\
/root/install/postgresql-8.3-604.jdbc4.jar:\
. clojure.lang.Repl  site.clj

Finally, the interesting part. I’ll show the full contents of the new /root/site/site.clj and explain the new code later.

; Indicates which libraries we need, and the
; shorter names by which they will be called
(ns site
  (:require [compojure.http.servlet :as servlet])
  (:require [compojure.http.routes :as routes])
  (:require [compojure.http.helpers :as http-helpers])
  (:require [compojure.html :as html])
  (:require [compojure.html.page-helpers :as page-helpers])
  (:require [compojure.server.jetty :as jetty])
  (:require [clojure.contrib.sql :as sql]))

; Information about the PostgreSQL database
(def db {:classname    "org.postgresql.Driver"
         :subprotocol  "postgresql"
         :subname      "production"
         :user         "postgres"})

; Performs a database query and returns the results as a list
(defn sql-query [query]
  (sql/with-connection db
    (sql/with-query-results res
      ; query is string that may contain "?"
      ; which are replaced with later elements
      ; in this array
      [query]
      (into [] res))))

; Fetches all articles
(defn articles []
  (sql-query "select * from article"))

; Converts "My Article" to "my-article" for use in URL
(defn article-title-to-url-name [title]
  (.replaceAll
    (.toLowerCase title)
    "[^a-z0-9]+" "-"))

; Converts map of article attributes to a URL
(defn article-url [article]
  (str
    "/articles/"
    (article-title-to-url-name
      (article :title))))

; Fetch an article with the given title
(defn article [title]
  (first
    (filter
      (fn [art]
        (=
          (article-title-to-url-name
            title)
          (article-title-to-url-name
            (art :title))))
      (articles))))

; HTML page for an article
(defn render-article [article]
  (html/html
    [:head [:title (article :title)]]
    [:body
      [:h1 (article :title)]
      [:p [:em (article :description)]]
      (article :body)]))

; Search for article by title, return HTML or redirect
(defn view-article [title]
  (try
    (render-article (article title))
    (catch Exception ex
      (http-helpers/redirect-to "/articles/"))))

; HTML link to an article
(defn render-article-link [article]
  (page-helpers/link-to
    (article-url article)
    (article :title)))

; HTML page that lists all articles
(defn view-article-list []
  (html/html
    [:head [:title "Articles"]]
    [:body
      [:dl (mapcat
             (fn [article]
               (list
                 [:dt (render-article-link
                        article)]
                 [:dd (article
                        :description)]))
             (articles))]]))

; Mapping between URLs and view functions
(servlet/defservlet journal-servlet
  "Eric Lavigne's Journal"
  (routes/ANY "/articles/"
    (view-article-list))
  (routes/ANY "/articles/:title"
    (view-article (route :title)))
  (routes/ANY "/*"
    (http-helpers/redirect-to "/articles/")))

; Server settings
(jetty/defserver journal-server
  {:port 80}
  "/*" journal-servlet)

; Command to start the server
(jetty/start journal-server)

This first section creates a namespace for my code called “site” and indicates which libraries I am using. I indicate that this file requires code from the compojure.http.servlet namespace, which will be referred to as servlet later in the code. The compojure.http.servlet namespace is defined in compojure/http/servlet.clj of the compojure library, just as the site namespace is defined in site.clj of this project. This naming convention comes from Java packages and enhances Java interoperability.

; Indicates which libraries we need, and the
; shorter names by which they will be called
(ns site
  (:require [compojure.http.servlet :as servlet])
  (:require [compojure.http.routes :as routes])
  (:require [compojure.http.helpers :as http-helpers])
  (:require [compojure.html :as html])
  (:require [compojure.html.page-helpers :as page-helpers])
  (:require [compojure.server.jetty :as jetty])
  (:require [clojure.contrib.sql :as sql]))

Next is a description of the PostgreSQL database, in the format expected by the clojure.contrib.sql library.

(def db {:classname    "org.postgresql.Driver"
         :subprotocol  "postgresql"
         :subname      "production"
         :user         "postgres"})

The following code performs a query against the database and returns the results of that query as a list.

; Performs a database query and returns the results as a list
(defn sql-query [query]
  (sql/with-connection db
    (sql/with-query-results res
      ; query is string that may contain "?"
      ; which are replaced with later elements
      ; in this array
      [query]
      (into [] res))))

The articles function returns a list of all articles from the database.

; Fetches all articles
(defn articles []
  (sql-query "select * from article"))

The URL of an article is based on its title, with spaces replaced by dashes and all letters converted to lowercase. For example, “The green snake” would have a URL of “/articles/the-green-snake”. The function article-title-to-url-name would convert “The green snake” to “the-green-snake”, and the function article-url would convert “The green snake” to “/articles/the-green-snake”.

; Converts "My Article" to "my-article" for use in URL
(defn article-title-to-url-name [title]
  (.replaceAll
    (.toLowerCase title)
    "[^a-z0-9]+" "-"))

; Converts map of article attributes to a URL
(defn article-url [article]
  (str
    "/articles/"
    (article-title-to-url-name
      (article :title))))

The article function retrieves an article from the database based on the title of the article. This function is very inefficient because it retrieves all of the articles and checks which of them match the given title. The database is capable of doing this job much more efficiently, so this function will need to be rewritten later.

; Fetch an article with the given title
(defn article [title]
  (first
    (filter
      (fn [art]
        (=
          (article-title-to-url-name
            title)
          (article-title-to-url-name
            (art :title))))
      (articles))))

The render-article function uses the compojure.html library to produce an HTML page for an article. It should be easy to understand if you’ve used HTML before.

; HTML page for an article
(defn render-article [article]
  (html/html
    [:head [:title (article :title)]]
    [:body
      [:h1 (article :title)]
      [:p [:em (article :description)]]
      (article :body)]))

The view-article function tries to produce an HTML page for an article with a certain title, redirecting to the list of articles if an article with that title cannot be found.

; Search for article by title, return HTML or redirect
(defn view-article [title]
  (try
    (render-article (article title))
    (catch Exception ex
      (http-helpers/redirect-to "/articles/"))))

The render-article-link function produces an HTML snippet to represent a link to an article.

; HTML link to an article
(defn render-article-link [article]
  (page-helpers/link-to
    (article-url article)
    (article :title)))

The view-article-list function produces an HTML page that lists all of the articles with link and description for each.

; HTML page that lists all articles
(defn view-article-list []
  (html/html
    [:head [:title "Articles"]]
    [:body
      [:dl (mapcat
             (fn [article]
               (list
                 [:dt (render-article-link
                        article)]
                 [:dd (article
                        :description)]))
             (articles))]]))

The servlet controls how this application responds to different URLs. The application responds to the “/articles/” URL by listing the available articles, responds to the “/articles/:title” URL (where :title is replaced by the title of an article) by showing that article, and redirects all other URLs to “/articles/”.

; Mapping between URLs and view functions
(servlet/defservlet journal-servlet
  "Eric Lavigne's Journal"
  (routes/ANY "/articles/"
    (view-article-list))
  (routes/ANY "/articles/:title"
    (view-article (route :title)))
  (routes/ANY "/*"
    (http-helpers/redirect-to "/articles/")))

; Server settings
(jetty/defserver journal-server
  {:port 80}
  "/*" journal-servlet)

; Command to start the server
(jetty/start journal-server)

Getting a database setup was a big step in the right direction, but we still need a good way to add new articles. This Compojure application will need an admin panel with forms for easily creating or editing articles, as well as password authentication and SSL for security. These will be the topics of future articles as I prepare for transfering my journal from WordPress to EricLavigne.net.

9 Comments

Filed under Programming