Hacker News Reader

Comment by lukev | Hacker News Reader

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.

View on HN

SQL is fantastic, but the annoying bit is that you have to create strings to send to the database, and generating these strings correctly is hard. One of the key features of libraries like Korma and SQLAlchemy is that they provide a very eloquent way of robustly creating these strings. Yes, things like prepared statements help, but dealing with strings and prepared statements is not relevant to the problem that you want to solve. Korma et al. shave the yaks for you.

Without something like Korma, your process looks something like:

structural-description-of-query --(string operations)--> query-string-to-db --(careful unpacking of response)--> structured-results

With Korma and its ilk, this process becomes:

structural-description-of-query --(somebody else's problem)--> structured-results

Expressive, yes. But SQL is rarely elegant. Declarative languages can be elegant; even modern regexen, with their named groups and whitespace tolerant syntax, allow the ideal of "small, composable methods". You can't do composability in SQL (in fact, if you Google, you'll discover that the very term "composable" is an unrelated feature of, IIRC, Sybase.)

The SQL community doesn't spend much time talking about writing elegant code in the way the functional, concurrent, OOP, and whatever-Ruby-is communities do. When programmers talk about "SQL Style" or "SQL Idioms", they mean whether you make keywords uppercase and where you indent and wrap to a new line. There is an entire book on the SQL equivalent of "K&R braces, or GNU style?", and should you or should you not alias long tables to short aliases.

There's a book on "SQL Refactoring"; it's about database design, not coding.

The pain goes on. I've been wishing for something like "CoffeeScript for SQL" for a while; I'm not a lisphead, but this may just have to do, especially if it supports PG-isms like SQL procedures and user-defined types.

SQL is not a bad language. However, it's far from flexible and reusable. The biggest issue I always have with SQL is the fact that I can hardly reuse any piece of my code.

Let me give you an example.

select vendor, model, avg(price) as price from allCars group by vendor, model;

So far, this is simple and clear. Now let's imagine someone has a requirement to calculate this for different set of cars:

select vendor, model, avg(price) as price from allCars where vendor = 'BMW' group by vendor, model;

select vendor, model, avg(price) as price from allCars where year > 1990 group by vendor, model;

This approach only works if I'm always selecting from the same table - allCars, and I know all the condition in advance. To reuse the actual code which calculates the average prices (select ... group by) I'd need something like this:

select vendor, model, avg(price) as price from $cars -- $cars is a variable with at least three attributes (vendor, model, price) group by vendor, model;

This way I could write my own relational operator which calculates avg prices of cars per vendor and a model no matter where the cars are coming from.

Does anyone else have the same problem as me or am I just not smart enough to figure out how to do it in SQL?

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 :)

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.

Reply on news.ycombinator.com