http://seancorfield.github.io for newer blog posts." />

An Architect's View

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

An Architect's View

Real World Clojure - SQL and noSQL

October 30, 2011 · 10 Comments

Persistence is core to pretty much every real world application.

Side Story: One of the strengths of CFML is that it has a <cfquery> tag built-in that makes it drop-dead simple to work with SQL. Only recently has CFML gotten a built-in ORM (based on Hibernate) so that what Java programmers have taken for granted for years is finally an option for CFML developers. That's not to say there weren't ORM-like data mappers in CFML for years but they were written in CFML and had a lot of limitations. At World Singles, we were using one such ORM called Reactor which provided Active Record capabilities along with generated Table Data Gateways and (single-object) Data Access Objects. By trying to emulate a number of Java-like design patterns, Reactor ends up being pretty heavyweight and... slow. Early on in the project, I created a much more lightweight data mapper that worked more closely with CFML "structs" (maps) but Reactor, with its Active Record pattern, was fairly intrusive and migrating off it would be a big task. It was one of those "we'll do it when we have to" tasks.

As we started to move low-level code into Clojure, we very quickly hit situations where we needed to do persistence in Clojure. We were committed to the Clojure 1.3.0 branch at World Singles so using clojure.contrib.sql was problematic since it was clear that the old, monolithic contrib was being abandoned (in that format) and new, modular contrib libraries were being created. Since I needed some form of SQL library compatible with Clojure 1.3.0, I pushed hard to get clojure.contrib.sql promoted and found myself to be the new maintainer! With the new name of clojure.java.jdbc, I was able to start using SQL directly from Clojure and, whilst not quite as simple as what I'd been used to in CFML, it was a lot better than native Java (or many other languages!). What I really wanted was an API that operated directly on maps or just on primary keys - something like:

  • save-row - insert or update a map (depending on whether the map contains a primary key or not)
  • get-by-id - retrieve a record by primary key
  • delete-by-id - remove a record by primary key
  • find-by-keys - retrieve one or more records based on an example map
  • execute - run arbitrary SQL to retrieve one or more records, or perform update operations

So that's exactly what we built, on top of the new Clojure contrib library. This served us well in Clojure, being able to load and save native maps, and it also served us well in CFML which mutable maps with case-insensitive keys (behind the scenes CFML converts keys to uppercase so structs are really maps with all uppercase keys):

(save-row :table {:firstname "Sean" :lastname "Corfield"})

returns the newly generated primary key, whereas:

(save-row :table {:lastname "Bangle" :id 4321})

will update the lastname column for primary key 4321 (assuming the primary key is the column id). All of our "id" functions take an optional argument to specify the primary key column name. The functions that can return multiple rows all take a function that gets applied to the results of the SQL - since they are wrappers around with-query-results. I'm not going to show this CRUD wrapper code because it's both too basic to matter and too long to be worth adding to this blog post (mostly mapping name/value pairs to strings of SQL).

We'd been evaluating "noSQL" datastores for a while at World Singles and finally settled on MongoDB as a suitable solution for most of our needs. That meant looking at the Clojure wrapper for MongoDB, CongoMongo. At the time, CongoMongo had not been updated for Clojure 1.3.0 so I pushed for that and found myself to be one of the committers! With CongoMongo updated to support both Clojure 1.2.1 and 1.3.0, I started integrating it into the CRUD wrapper we'd built - so that our application code could just use maps and the API shown above, whilst the wrapper determined whether to interact with clojure.java.jdbc or CongoMongo based on just the table / collection name.

As an example, our find-by-keys function either delegates to fetch in CongoMongo or to with-query-results in the JDBC library (with the appropriate SQL). The result in either case is a sequence of maps; our save-row function either delegates to fetch-and-modify in CongoMongo or to update-values or insert-record in the JDBC library.

Back in CFML-land, we still have a lightweight object wrapper around the Clojure maps so when you load a "membership event" - which comes from MongoDB - and you say event.getUser(), the CRUD wrapper knows to fetch a user map from MySQL based on the userId foreign key in the event map. This allows us to migrate data between MySQL and MongoDB by simply adjusting which tables / collections live where (specified by a map in the CRUD wrapper configuration). The nice thing about being able to transparently save and load data from MongoDB is that we can add structured data to the mix so, for example, when logging an exception, we can store the stack trace as a vector of maps (containing the file and line number).

Tags: clojure

10 responses so far ↓

  • 1 Si // Oct 30, 2011 at 3:56 AM

    It's great to hear about your experiences - I'm very much enjoying your recent series of posts.
  • 2 angel // Oct 30, 2011 at 1:16 PM

    Hi..I've a question...you used in the past CFML and now u're migrating to clojure...has clojure better performance than cfml..I make the question because cfml is very popular for be a very scalable and it's a static language, its performance would be similar to java , clojure is dynamic, and despite than clojure is fast, it's far from be as faster as static lang...

    in LOC...comparing clojure with cfml...do u see any inprovement?...

    thanks so much for the article...really appreciate
  • 3 Sean Corfield // Oct 31, 2011 at 1:44 PM

    @angel, CFML is not a static language - it's dynamic and its performance is generally a lot worse than Java. Clojure is designed for performance and can usually be made to perform as fast as hand-crafted Java code.

    As for lines of code, Clojure is very concise so, yes, for LOC it's generally going to beat CFML (it beats Java by a long way!). A lot depends on exactly what problem you're trying to solve tho'...
  • 4 Nando // Nov 6, 2011 at 9:05 AM

    Sean, Thanks very much for this post. What resources would you recommend to learn more about using Clojure for persistence? To be specific, some example code would help me considerably to get started, but any pointers would be very much appreciated.
  • 5 Sean Corfield // Nov 7, 2011 at 11:43 PM

    @Nando, does this help?

    http://clojure.github.com/java.jdbc/

    If not, I'm really not sure what you'd want...
  • 6 Nando // Nov 8, 2011 at 4:30 AM

    Sean,

    Yes, it does, as a start. Is there example code somewhere yet?
  • 7 Sean Corfield // Nov 8, 2011 at 10:08 AM

    @Nando, that site has example code - follow the links under "Related Documentation". In particular, "Manipulating data with SQL" shows examples of most of the main functions.
  • 8 Nando // Nov 8, 2011 at 11:37 PM

    Excellent Sean. Thank you. Noob question: At a simple level, how does one manage the database connection? I see a variety of connection functions in the spec, but I'm completely new to this. (and do you mind me asking beginner questions here?)
  • 9 Sean Corfield // Nov 9, 2011 at 9:47 AM

    @Nando, as shown on the Connection Pooling page in the docs (via that link again), you just need a db-spec:

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

    If you don't want to bother with connection pooling, that's all you need. Then wrap (with-connection db-spec ..) around your database operations.
  • 10 walt // Oct 8, 2012 at 10:30 PM

    excellent stuff.
    I've taken to learning Clojure, and your blog has been invaluable for this, sir.
    Thanks.

Leave a Comment

Leave this field empty