ORM for clojure?

Question

I was reading this site about the clojure web stack:

http://brehaut.net/blog/2011/ring_introduction

and it has this to say about ORM for clojure:

"There are no SQL/Relational DB ORMs for Clojure for obvious reasons."

The obvious reason I can see is that the mapping to object happens automatically when you do a clojure.contrib.sql or clojureql query. However it seems some extra work is needed to do one-to-many or many-to-many relations (although maybe not too much work).

I found this write up for one-to-many: http://briancarper.net/blog/493/

Which I'm not sure I agree with; it appears to be assuming that both tables are pulled from the database and then the joined table is filtered in memory. In practice I think the sql query would specify the where criteria.

So I'm left wondering, is there some fairly obvious way to automatically do one-to-many relations via clojureql or clojure.contrib.sql? The only thing I can think of is something like this (using the typical blog post/comment example):

(defn post [id] 
    @(-> (table :posts)
        (select (where :id id))))
(defn comments [post_id]
    @(-> (table :comments) 
         (select (where :post_id post_id))))
(defn post-and-comments [id]
    (assoc (post id) :comments (comments id)))

Is there any way to sort of automate this concept or is this as good as it gets?

Answer 1

I asked this question quite a while ago, but I ran across the following and decided to add it as an answer in case anyone is interested:

http://sqlkorma.com/

Answer 2

The "obvious" reason you don't need ORM as such in Clojure is because idiomatic Clojure doesn't have objects, per se.

The best way to represent data in a Clojure program is as lazy seqs of simple data strutures (maps & vectors). Mapping these to SQL rows is much less complex, and has much less of an impedance mismatch, than full-blown ORM.

Also, regarding the part of your question relating to forming a complex SQL query... reading your code, it doesn't really have any clear advantages over SQL itself. Don't be afraid of SQL! It's great for what it does: relational data manipulation.

Answer 3

There's still no high-level library to create complex relational queries that I know of. There's many ways to tackle this problem (the link you provided is one way) but even if ClojureQL provides a really nice DSL you can build upon, it still miss some important features. Here's a quick and dirty example of a macro that generate imbricated joins:

(defn parent-id [parent]
  (let [id (str (apply str (butlast (name parent))) "_id")]
    (keyword (str (name parent) "." id))))

(defn child-id [parent child]
  (let [parent (apply str (butlast (name parent)))]
    (keyword (str (name child) "."  parent "_id"))))

(defn join-on [query parent child]
  `(join ~(or query `(table ~parent)) (table ~child)
         (where
          (~'= ~(parent-id parent)
               ~(child-id parent child)))))

(defn zip [a b] (map #(vector %1 %2) a b))

(defmacro include [parent & tables]
  (let [pairs (zip (conj tables parent) tables)]
    (reduce (fn [query [parent child]] (join-on query parent child)) nil pairs)))

With this you could do (include :users :posts :comments) and get this SQL out of it:

SELECT users.*,posts.*,comments.*
  FROM users
  JOIN posts ON (users.user_id = posts.user_id)
  JOIN comments ON (posts.post_id = comments.post_id)

There's one major issue with this technique though. The main problem is that the returned columns for all tables will be bundled together into the same map. As the column names can't be qualified automatically, it won't work if there's similarly named column in different tables. This also will prevent you from grouping the results without having access to the schema. I don't think there's a way around not knowing the database schema for this kind of things so there's still a lot of work to do. I think ClojureQL will always remain a low-level library, so you'll need to wait for some other higher-level library to exist or create your own.

To create such a library, you could always have a look at JDBC's DatabaseMetaData class to provide information about the database schema. I'm still working on a database analyzer for Lobos that use it (and some custom stuff) but I'm still far from starting to work on SQL queries, which I might add in version 2.0.

Answer 4

At the risk of swimming in the waters with some of the SO heavy hitters (to mix my metaphors, thoroughly ;) - surely one of the best features of ORM is that, for the vast majority of cases, the pragmatic programmer has to never use or even think about SQL. At worst some hacky programming with the results of a couple of queries may be required, on the basis that this will be converted in to raw SQL when that optimisation is required, of course, ;).

To say that ORM is not required for the 'obvious' reason, is somewhat missing the point. Further to start using a DSL to model SQL is compounding this mistake. In the vast majority of web frameworks, the object model is a DSL used to describe the data being stored by the web app, and SQL merely the declarative language required to communicate this to the database.

Order of steps when using ROR, django or Spring:

1. Describe your models in an OOP format
2. Open REPL and make some example models
3. Build some views
4. Check results in web browser

Ok, so you might use a slightly different order, but hopefully you get the point. Thinking in SQL or a DSL that describes it is a long way down the list. Instead, the the model layer abstracts away all the SQL away allowing us to create data objects that closely model the data we wish to use in the web site.

I would fully agree that OOP is no silver bullet, however, modelling data in a web framework is something it is definitely good for, and exploiting clojure's ability to define and manipulate Java classes would seem to be a good match here.

The examples in the question clearly demonstrate how painful SQL can be, and DSLs like Korma are only a part solution: "Let's suppose that we have some tables in a database..." - er, I thought my DSL was going to create those for me? Or is this just something an OOP language does better? ;)

Answer 5

Have you checked out the Korma library http://sqlkorma.com/ ? It allows you to define table relationships and abstract over joins. I think a major reason there aren't any ORM's for clojure is because they go against Rich Hickey's ideas of simplicity that the language was founded on. Check out this talk: http://www.infoq.com/presentations/Simple-Made-Easy

Answer 6

The library known as aggregate can take care of most of the itches you have here. It's not a full-scale ORM, but if you tell it the relationship graph for your database schema, then it provides CRUD implementations that automatically walk the relation graph. It's useful if you are using something like Yesql or raw SQL queries already, because it plugs in easily to an implementation that is using simple result maps.

Answer 7

Whether you want to use them or not, there was already aggregate and there is toucan now (and they apparently read the same link as you did).

Answer 8

ORM is premature optimization. Walkable is the new sql library for Clojure that takes holistic approach. Check it out here https://github.com/walkable-server/walkable.

A real world example for those skeptical of fancy readmes: https://github.com/walkable-server/realworld