An Architect's View

CFML, Clojure, Software Design, Frameworks and more...

An Architect's View

Clojure and SQL

August 30, 2010 ·

I spent quite a bit of time playing with Clojure over the weekend (including writing my first plugin and my first hook for Leiningen, Clojure's popular build tool) and I started experimenting with reading data from a database. I tweeted that I was pleased with myself for succeeding and Marc Esher asked "is reading data so hard in clojure that it warrants celebration?" so I figured I'd post my little example, so you could see how easy it is (or isn't, depending on your point of view).

(ns sean.core
  (:require [clojure.java.jdbc :as sql])
  (:gen-class))

This just declares a namespace for my code to live in - the file is sean/core.clj - and says I'm going to be using the library package clojure.java.jdbc (formerly known as clojure.contrib.sql) under the alias sql. :gen-class says I want the file compiled to a Java class.

(def db {:classname "com.mysql.jdbc.Driver"
         :subprotocol "mysql"
         :subname "//127.0.0.1:3306/mydb"
         :user "dbuser"
         :password "secret"})

This declares the datasource I'm going to use, MySQL, locally, database 'mydb' with the credentials I'm using to login.

(defn print-users
  [] (sql/with-connection db
       (sql/with-query-results rows
         ["SELECT * FROM user"]
         (doseq [record rows]
           (println record)))))

This declares a function print-users taking no arguments [] which reads all users from the 'user' table and prints them out (they are automatically formatted as records with key: value pairs for columns). I'll explain this in more detail below but it really is pretty straightforward.

(defn -main [] (print-users))

And that's my 'main' function. Like a regular Java main function. Update project.clj to have the correct dependencies and declare the main class:

(defproject sean "1.0.0-SNAPSHOT"
  :description "FIXME: write description"
  :dependencies [[org.clojure/clojure "1.2.1"]
                 [org.clojure/java.jdbc "0.0.3"]
                 [mysql/mysql-connector-java "5.1.6"]]
  :main sean.core)

We can compile and run that using Leiningen as follows:

lein uberjar
lein run -m sean.core

This will print every row of the user table to the screen.

So what exactly does it do? The key thing to remember with functional languages is that they often have expressions which are treated as functions that get applied to data in a context. The body of print-users is (sql/with-connection db expression) so it gets a connection for the specified db and then evaluates expression in that context. (sql/with-query-results rows [vector] expression) executes the SQL in the vector (subsequent elements of the vector are parameters substituted into the SQL - see below), binds the result to 'rows' and then evaluates the expression in that context. (doseq [record rows] expression) takes the sequence 'rows' and iterates over each element, binding the element to 'record' and then evaluating the expression in that context. So, it gets a connection, selects all users and prints each row.

If you wanted to just get users with a particular status, you'd say something like (sql/with-query-results rows [ "SELECT * FROM user WHERE status = ?", search-status ] expression) where search-status was a variable containing the status you were searching on.

Tags: clojure

5 responses

  • 1 Shantanu Kumar // Aug 31, 2010 at 12:38 AM

    Nice example. I have two points to make:

    1. There seems to be a typo in Marc Esher's Twitter URL.

    2. I'd suggest to check out SQLRat (will be available on Clojars very soon), which should make accessing databases further easier: http://bitbucket.org/kumarshantanu/sqlrat/src
  • 2 Marc Esher // Aug 31, 2010 at 3:34 AM

    Thanks for the example, Sean.
  • 3 Marko Simic // Aug 31, 2010 at 5:53 AM

    Interesting. Since I start learning Scala and paying more attention on functional languages in general, my "way of thinking" has significantly changed. Dare to say, it changed me for the better.

    Trivia question. Code snippets in post, look like they are over-formatted :) Why is that? Is that coding style adopted in Clojure community or syntax rules?
  • 4 Tony Piazza // Aug 31, 2010 at 10:36 PM

    Nice post! I'm a Java guy who has been learning Clojure for the past few weeks. Using embedded SQL is something I hadn't tried yet in Clojure. Seems reasonably straightforward. I'd be interested in a more complete example with full CRUD.
  • 5 Sean Corfield // Aug 31, 2010 at 10:50 PM

    @Shantanu, thanx. Marc's Twitter link is fixed. I'll check out SQLRat.

    @Marko, yes, I'm not surprised your way of thinking has changed. I did a lot of Functional Programming back in the 80's and it's very different to procedural and OO. Not sure what you mean about over-formatted in the code examples...? The code is as formatted by CounterClockWise in Eclipse.

    @Tony, yeah, I need to figure out how I feel most comfortable representing row data in an application with Clojure (defrecord or plain maps) and that will drive how I would do CRUD.