Use a database with Clojure

Question

What methods to use a database from Clojure are there?

I know from Clojure you can do anything you can with Java, but that means that I may end up using something overly complicated (like Hibernate) which clashes with Clojure simplicity. Any recommendations or comments?

Solution 1

clojure-contrib has an sql library which is a thin wrapper around JDBC (java.sql.DriverManager). The test file that comes with it has some examples of its usage.

Solution 2

I would now (as of late 2011) recommend Korma - "Tasty SQL for Clojure"

It's a beautiful little SQL DSL, here's an example from the website:

(select users
  (aggregate (count :*) :cnt)
  (where (or (> :visits 20)
             (< :last_login a-year-ago))))

Solution 3

I'd like to add an as-of-Nov-2011 answer for the sake of anybody coming here from Google.

The current core SQL access library in Clojure 1.3 is clojure.java.jdbc. There are some very good libraries built on top of this like ClojureQL and Korma.

Solution 4

If you are open to using a Java library but want something that embraces simplicity, perhaps you'll like Persist. It'll only take you 10 minutes to have a look and see if it fits your needs.

Solution 5

The latest and greatest for SQL databases seems to be HoneySQL and Yesql.

HoneySQL is a quite elegant DSL to generate SQL queries. There are rumours it can even modify the statements to be highly optimized, see the clojure-group thread "Current best-of-breed JDBC libraries?" from Feb 24 2015.

Niels van Klaveren says in the above-mentioned thread:

"Basically, it [HoneySQL] generates SQL scripts to relink foreign key references to clean up duplicates in a database. It takes a honeysql select query with (at least) a from table, a group-by and an order-by clause as a base definition what are to be considered doubles, and in which order records should be preserved. In combination with JDBC metadata that query effectively gets rewritten to generate:

• A temporary replacement table
• Queries to unify unique indexes, to prevent clashes when foreign key references are updated
• Queries to update all foreign key references
• Delete statements to remove all duplicates

To create the best performing, but still database independent SQL, I had to extend honeysql with extra clauses like OVER and PARTITION BY. I wouldn't say it was a breeze, but seemed to work very well.

...

That cut down SQL to (sometimes) GB's of script to around a few 100 lines of SQL, and on one occasion, a runtime from 19 hours to 1.5 minutes."

Yesql, on the other hand, aim for total simplicity. It defines some functions to load parameterized .sql-files.

It's webpage mentions the following "USPs":

• No syntactic surprises. Your database doesn't stick to the SQL standard - none of them do - but Yesql doesn't care. You will never spend time hunting for "the equivalent sexp syntax". You will never need to fall back to a (raw-sql "some('funky'::SYNTAX)") function.
• Better editor support. Your editor probably already has great SQL support. By keeping the SQL as SQL, you get to use it.
• Team interoperability. Your DBAs can read and write the SQL you use in your Clojure project.
• Easier performance tuning. Need to EXPLAIN that query plan? It's much easier when your query is ordinary SQL.
• Query reuse. Drop the same SQL files into other projects, because they're just plain ol' SQL. Share them as a submodule.

View more solutions

Comments

[Rollo Tomazzi]

clojure-contrib has moved. It's here now: code.google.com/p/clojure-contrib

[Ben Richardson]

Actually it moved twice since I posted this. It's now here: github.com/richhickey/clojure-contrib :)

[nickik]

you should look at clojure ql too.

[dave_thompson_085]

And moved (better: deprecated) again. Get your libraries directly from github.com/clojure

[kristianlm]

hi @jartur, your link is a 404.

[Richard Miskin]

Looks like this is now in the Clojure java.jdbc repository on Github.

[claj]

Korma seems to be abandoned: groups.google.com/d/msg/clojure/SgqL_zDvPdw/ZJtfvEucJQYJ