How does ClojureQL compare to clojure.contrib.sql?

Question

It looks like each one covers the basic cases like selecting certain columns and filtering by predicate pretty well, but I'm wondering how each compares for more advanced cases. Is it easier to express complex queries in one vis-à-vis the other? Is one library missing any functionality that the other covers?

Answer 1

ClojureQL and clojure.contrib.sql are two quite different libraries. The first aims to implement the primitives from relational algebra and compile those to SQL92. It also offer an extensible compiler that can be adapted to database specific SQL dialect. The second is a lightweight set of helpers for using JDBC from Clojure code.

Querying

clojure.contib.sql

With clojure.contib.sql, you'll have to use SQL to write your queries. Here's an example:

(sql/with-connection db
  (sql/with-query-results rs ["select * from customer"]
    (doseq [r rs] (println (:lastname r))))

ClojureQL

As ClojureQL is mostly a query language, it provides a rich Clojure-based DSL to create SQL queries. I'll skip advanced examples and only show you the ClojureQL equivalent to the above query:

(sql/with-connection db
  (cql/with-results [rs (cql/table :customer)]
    (doseq [r rs] (println (:lastname r))))

You can express queries of arbitrary complexity with both, but contrib.sql require you to write SQL code. Take note that ClojureQL DSL main advantage over standard SQL is composability. Its table function returns a RTable object representing a query on the specified table, you can chain other ClojureQL function over that object to create the query that you need, then dereference it to execute it. Refer to ClojureQL examples page and documentation for more information on how to create more complex queries.

Inserting, Updating and Deleting

clojure.contib.sql

clojure.contrib.sql provides a comprehensive set of functions to insert, update and delete rows.

• Inserting:
  • (insert-records table & records), where records are maps
  • (insert-rows table & rows), where rows are vectors
  • (insert-values table column-names & value-groups)
• Updating: (update-values table where-params record)
• Inserting or Updating: (update-or-insert-values table where-params record)
• Deleting: (delete-rows table where-params)

ClojureQL

ClojureQL provides three RTable methods to manipulate the specified table data:

• conj! which is a shorcut to contrib.sql's insert-records
• disj! which is a shorcut to contrib.sql's delete-rows
• update-in! which is similar to contrib.sql's update-or-insert-values

These have the advantage of using ClojureQL predicates syntax, but for now this part of ClojureQL is not generating database agnostic SQL as it's separated from the compiler. I intend to fix that by merging code from another library I've written in the more-or-less near future.

Schema Manipulation

clojure.contib.sql

clojure.contrib.sql only provides create-table and drop-table for creating and removing tables. Note that these are very simple functions that won't make your code portable. To alter a table you'll need to send SQL ALTER statements using the do-commands function.

ClojureQL

No schema manipulation helpers provided.

Lobos (shameless plug ;-)

This is a library I wrote to plug the hole left by these two libraries. It's a work in progress, but you already get a Clojure DSL to send any DDL statements in a database agnostic way.

Here's a basic example for creating a table:

(create (table :users (integer :id :unique)))

And altering it:

(alter :add (table :users (text :name)))

You can get more information on this library by visiting the website or the github page. It aims to provides higher-level functionality like migrations and declarative schema manipulation.

Others

clojure.contrib.sql has a couple extra lower-level helpers, see the complete documentation

There's more to say about how these libraries handle database connections but I'll leave that for another day!

P.S.: Note that both ClojureQL and Lobos are relatively young libraries that still need some work. Both descent from the original ClojureQL project which was a DSL covering the whole SQL language. ClojureQL already have a stable API, but only provide a SQL92 compatible compiler. Lobos has compiler support for multiple databases. but is still in active development and its API can still change.

Update: I've made some changes after a suggestion from Lau. ClojureQL itself doesn't aim to be database-agnostic, but provide the means for users to replace the compiler by a database-specific one. Note that the DML part of SQL is much more standardize than the DDL part.