
Meet Korma, a SQL DSL for Clojure - ibdknox
http://sqlkorma.com
======
tom_b
I am attracted to these types of DSLs but . . .

I work with a number of legacy data sources that often have models that are
not ORM-friendly.

I fall into SQL code patterns that seem left behind by tools like this on a
regular basis, particularly using SQL functions without default grouping
because I'll select other stuff higher up.

A simple example:

    
    
      select
        col1,
        col2,
        some_function() over (partition by col1,col2 order by col1,col2) as some_ck
      where <some conditions>
    

This is often part of a larger query (e.g., this is embedded in bowels of a
some longer "chain" of SQL where I will be using some_ck to decide on which
tuples I want in the end).

I get pretty significant mileage out of this - meaning I write much _less_
other code around it to handle the data.

I guess my complaint is that I often find the SQL DSLs lacking in
expressiveness that I use regularly enough to miss.

Of course, this could be because I've spent too much time hacking around in
legacy data in "real soon now" mode rather than in "hmm . . . let's clean up
this data model" mode.

~~~
jwr
I think writing DSLs for SQL makes little sense.

SQL is already a declarative query DSL, why not just use it? Putting another
declarative layer on top buys you little (syntactic sugar for most libraries,
this one additionally seems to deal with one-to-many relations, and I hope
that support is read-write, not just read-only) — and isolates you from the
database below.

In every serious application I've worked on there was a need to use something
which the DSL did not support. So why do it at all?

~~~
technomancy
Raw SQL is not very composable, while Korma emphasizes composability on the
second paragraph of the page.

~~~
hosh
And composability lets you create business DSLs and macros on top of that.
I've used this to good effect with Rails' Arel, but since it is still Ruby,
you can't go as far unless you're willing to parse the Ruby into something
more legible, like S-expressions.

------
sirn
Dear author of Clojure library, please always put a Leiningen dependency
string in your home page ([korma "0.2.0"] in this case).

Apart from that, I like what I see, especially how it use c3p0 for connection
pooling by default.

~~~
ibdknox
Sorry :) It's on the readme for the source. I'll add it to the website once
things calm down a bit.

------
lukev
Not saying this isn't a great, useful library.

But - what do people have against SQL? As far as I can see, it's a pretty good
language for relational data manipulation. But lots of people act like
actually writing SQL is dirty work. It's not. It's an elegant, expressive
language, for what it does.

Of course, use something like this if you have a documentable need for the
flexibility/composability. If you're generating lots of custom queries, it may
be justified.

But in nine out of ten projects I've seen which use a RDBMS, the system could
be made substantially simpler, more performant _and_ more flexible by just
writing raw SQL queries instead of screwing around with ORMs.

~~~
ibdknox
Korma was actually designed to be used to do exactly that: write SQL. You
don't have to use any of the ORM-y features and you're essentially just
writing SQL in a lisp syntax. The advantage, as others have said, is simply
composability, which leads to reusable SQL parts.

It's up to you whether you use korma to "just write SQL" or you use the magic
:)

~~~
lukev
Absolutely, and I completely grant you that there are projects where the
composability is a huge win. The library looks great, by the way.

I just think a lot of people will have a kneejerk reaction to use something
like this because they have a distaste for raw SQL, rather than a considered
analysis of the pros and cons.

Because there _are_ cons - every time you use something like this, however
well designed, you introduce another layer of abstraction into your software,
one that is bound to have some leaks. Maybe it's justified, maybe it isn't.

Just do the analysis to see if this can benefit you, instead of avoiding SQL
by default just because it's SQL.

------
moomin
See also ClojureQL

<http://clojureql.org/documentation.html>

Which doesn't have as nice a website but may have superior semantics.

~~~
ibdknox
The issue I had with ClojureQL is that it seems like the wrong abstraction to
me. Myself and others I've talked to have found ourselves fighting with how it
tries to model data and more specifically the kind of queries it generates.
For example, it is very quick to use rather inefficient sub-selects.

~~~
moomin
I'll be checking out korma. :)

------
timmorgan
Man this makes me want to use Clojure for something.

~~~
untog
If that's all it takes, check out LINQ-to-SQL in C#- it does similar stuff. I
could take or leave much of C#, but I miss LINQ every time I don't have access
to it.

~~~
boyter
Totally agree. LINQ to SQL is the one feature from C# I wish I had in many
other languages. The power you get from the chaining and lazy loading makes
implementing many complex joins and the like very easy, testable and compile
time checked.

~~~
gtrak
the thing is, to implement something like linq in C# or any language requires
compiler changes, with clojure or any lisp, this is library-level. You can't
make korma or linq in something like java, it's too conservative. Macros!

------
spariev
Nice looking DSL, but .. how could I do things like SELECT TOP 10 (MSSQL) or
SELECT DISTINCT (just trying to use it for queries I need at work) ? Haven't
found any clues on this in the docs (nice site BTW).

Also, it would be nice to allow some raw SQL in where clause - so one won't
have to wrestle with DSL just to occasionally use proprietary features or db-
specific functions.

------
moizsyed
I love the name! Its a spicy south asian curry
[https://www.google.com/search?gcx=w&q=korma&um=1&...](https://www.google.com/search?gcx=w&q=korma&um=1&ie=UTF-8&hl=en&tbm=isch&source=og&sa=N&tab=wi&biw=1472&bih=1319&sei=%20gK6xTr_DIePhiALpkMT4Dw)

~~~
r4vik
you mean mild English curry?

------
alpb
People liked this may also like Rogue
[http://engineering.foursquare.com/2011/01/21/rogue-a-type-
sa...](http://engineering.foursquare.com/2011/01/21/rogue-a-type-safe-scala-
dsl-for-querying-mongodb/) which is Scala DSL for MongoDB. It is not a SQL
database but can be compared in terms of semantics.

------
scarface548
There seems to something similar for Ruby in terms of C# linq
<https://github.com/suryagaddipati/linqr>

~~~
thom
Back in the day, Ambition was pretty cool:

<http://defunkt.io/ambition/>

It used ParseTree to parse idiomatic Ruby code against Enumerable APIs into
SQL.

~~~
dkubb
I wrote an Ambition-like API for DataMapper a couple of years ago and still
keep it updated: <https://github.com/dkubb/dm-ambition>

It uses sourcify (which uses ParseTree on 1.8 and Ripper on 1.9). The
semantics are identical to standard Enumerable API. In fact, it's not
recommended, but you can turn off dm-ambition and the expressions will still
work as-is -- only instead of generating a query from the block it iterates
over the results and filter in-memory.

~~~
thom
Great stuff! I haven't really kept up with the Ruby world, and didn't realise
there was a solution on 1.9.

~~~
dkubb
Well, on 1.9 it's not quite as nice as 1.8. The ruby-core team decided to
change ruby so that after parsing the source the AST would be thrown away as
part of some kind of optimization. That means things like ParseTree don't work
anymore, and you can't inspect the AST of a Proc at runtime.

However, with sourcify you can sort of emulate the 1.8 behaviour, but it's not
perfect. It uses ripper to parse the file where the Proc is defined, and then
provides the same callbacks that ParseTree does. It mostly seems to work, but
it does run into problems if two Procs are defined on the same line, eg:
users.select { ... }.select { ... }.

I'm actually a little disappointed that ruby-core decided to go in this
direction. I think that decision will end up _costing_ the community because
it makes it harder for runtime introspection of code. It means less tools will
be written on top of the runtime, and things like Ambition become harder if
not impossible to do reliably. I'm glad to see Rubinius is going in the
opposite direction though.

~~~
scarface548
Not if i understand your comment completely but ripper in 1.9 seems to what
ParseTree was doing. Am i missing something?

~~~
dkubb
Ripper can only parse static source, while ParseTree can give you the AST as
it is at runtime. So with Ripper you give it the name of a file/line number
and it can parse it, but it can't get any contextual information available at
runtime, or anything that might've changed at runtime. With ParseTree I can
give it a Proc object and it can parse it in whatever state it is at that
point in time.

In a nutshell, how sourcify works is it gets the source_location for the Proc,
which gives you the file name and line number. It uses Ripper to parse the
file, and the Proc starting at that line number. It provides an object that
can be fed into SexpProcessor to extract information about the block of code
within the Proc.

Since it's bound to Proc#source_location one of the limitations is it can't
distinguish between two or more Proc objects defined on one line (eg
users.select { |u| u.id == 1 }.select{ |u| u.name == 'John Doe' }). However,
that doesn't seem to be too much of a limitation.

------
joeyespo
"takes the pain out of working with your favorite RDBMS"

The way that's worded just sounds a little odd.

Aside from that, this looks really cool.

------
reflexer
Nice page colors.

