Close

Download the code from GitHub: current version 1.0.1

SQL Finder

When it comes to using SQL in Clojure there are a few options. Korma seems the most popular, and ClojureQL has some interesting features, but hasn’t been maintained some time. On a recent project building a RESTful web service backed by MySQL, I was using clojure.java.jdbc for some simple DB interactions, and decided to clean it up a bit with some language for constructing queries I could easily create from incoming data.

The result is Finder. It’s not meant to be an ORM, or provide a new abstraction. Its purpose is just to allow easy construction of queries from data when working with clojure.java.jdbc.

Installation

If you’re using Leiningen (which you are) then you can get Finder easily through Clojars (check here for the latest version). Add something like this to your project.clj

[sql-finder “1.0.0]

Then import it in your namespace…

(ns my.project
  (:require [finder.core :as f]))

 and you’re good to go.

Basic Usage

To run a simple query to find all users with a given name…

(ns my.project
  (:require [clojure.java.jdbc :as sql]
            [finder.core :as f]))
 
(sql/with-connection cnn
  (sql/with-query-results res
    (f/where :users {:name “john”})))

You can see that we’re still using the jdbc library, but the actual query is now created by Finder.

A few more examples…

Finding a user by id:

(f/by-id :users 123)

Or finding all, urr…  parcels of a few particular types:

(f/where :parcels {:type #{1 2 3}})

Finder isn’t meant to be a behemoth that covers all possible SQL features like aggregate queries, joins, derived tables, sub-selects, etc. – none of that is supported. Its only purpose is to be useful when covering the 95% cases.

And/Or Matching

By default, matched fields will be and’d together, but you can also specify or’d groups using a vector.

(f/where :users [{:first_name “john”}
                 {:last_name “evans”}]

 This will find all users where the first name is ‘john’OR the last name is ‘evans’.

Limits, Offsets, and Ordering

You can also do the usual ordering:

(f/all :cars {:order :name})

The default order is descending (as vendor order is ascending by default, so if you specify any order it’s probably because you want it descending), but you can specify it if you need to with multiple fields:

(f/all :tweets {:order {:name :asc
                          :date_posted :desc}})

Then limit, and offset are simple too:

(f/where :posts {:author “rich”}
                {:limit 10 :offset 20})

As you can see, it’s just meant to be super-simple. If you’re doing complex queries you’ll need to specify these as normal for clojure.java.jdbc.

Comparison Operators

By default, all comparisons are made with = (ie. field = 10). But if you’d like to use a different operator then you can do this by using a vector with the value to compare against. This is probably more easily explained with a simple example…

(f/where :users {:age [< 50]})

The < needs to be quoted, as will any other symbols.

Conclusion

And that’s it! There’s not a massive amount to it really. As I said at the start, it isn’t aimed at being the one ORM abstraction for your application. If you’re looking for that, then Korma is probably a much better bet. But if you’re using clojure.java.jdbcand want some help cleaning up your queries then I hope Finder will be useful for you.

As usual, code is on GitHub, along with the full documentation and unit tests.

About the author

Box UK

Box UK

Box UK's team of simply brilliant thinkers, consultants and application developers mastermind simply brilliant solutions to the world's toughest, performance-critical web and software assignments.

Related content

Tech round-up: Oct 23rd

By Ian Jenkins

Tech round-up: Sep 18th

By Steffan Harries

We're hiring. Let's talk. View available roles