February 12, 2015

Using a Clojure REPL for everyday SQL tasks.

I've described previously how I've been trying to improve my tooling and move away from using GUI tools so much. As well as this I've also had a more general move away from Windows to OSX.

Since I'm spending most of the day doing Windows based development on the .NET Framework there are a couple of tools I keep using in a VM. I've managed to get rid of most of these but I've not really found a good replacement for SQL Server Management Studio. There is Razor but I'm not very into that.

I've been spending more and more time doing Clojure development lately and consequently I've always got a Clojure REPL open. I thought it would be reasonably useful to setup a project I could use in the REPL to run the ad-hoc SQL queries I typically run throughout the day. I also get the added benefit of having query results as Clojure sequences and maps, which makes me happy. :)

The first thing I did to get started was create a new Clojure project. I called it sqlrpl but thats not important.

lein new sqlrpl
After that I added the java.jdbc dependency to project.clj.
[org.clojure/java.jdbc "0.3.6"]
java.jdbc also has a dependency on the JDBC driver for the plaform you are using. I added the TDS driver for Microsoft SQL Server.
[net.sourceforge.jtds/jtds "1.2.4"]

Once you've done this you can run a basic query by starting a clojure REPL:

lein repl
and then executing (after changing server details):

This will return a sequence of maps, each map represents a single row returned by the query and will have keys matching the column names.

Obviously, doing this in the REPL isn't very friendly but since we have a lot of control we can write functions around this and make it easy to run queries.

I started building up a small DSL to compose queries, its very simple and functions follow a similar pattern. Each function returns a string generating function and combines it with the results of the next-fn.

This makes it easy to write queries like:

I also make use of two other functions:

This means we can run the following to look at the generated SQL:

And the following to execute the query:

I think this is pretty nice, and its relatively easy to implement. You also don't have to compose queries like this, you can just do something like:

I also thought it would be nice to have completion for table/column names. So I came up with this (no doubt horrible) bit of Clojure. This defs symbols that match table/column names and return the table/column names.

This means we don't have to use the string representation for our table/column names, we use the symbols defined for us, so we get completion in the REPL and our queries become:

What I've put together is pretty rubbish but its simple and I'm finding it really useful. If you want to look at a really good project that gives you "Tasty SQL for Clojure" check out Korma.

I also have to give a hat tip to a plugin that has made working in the REPL even more awesome, Ultra. The colorized REPL is awesome when you are doing stuff like this.

While I'm in no way trying to suggest this is a replacement for all the stuff I can do in SQL Server Management Studio, its a start. It covers about 90% of the stuff I do on a day to day basis.

If you want to have a play, the source is here.

A couple of extra bits:

  • Generate a HTML table of our map sequence.

  • Generate an ASCII style string of our map sequence.

Tags: clojure repl sql