[drdaeman]

The only problem I see, is we can't rewrite SQL.

I.e., in pseudocode:

    query = SQL("SELECT * FROM entities WHERE owner = ?", owner=me)
    ...
    if some_condition:
        query = query + SQL.WHERE("OR public = TRUE")
    ...
    if other_condition:
        query = query + SQL("LEFT JOIN things AS t"
                            " ON t.entity_id = entities.id") \
                      + SQL.WHERE("things.value > 0")
    ...
    my_nice_list_of_results = run(query + SQL("LIMIT ?", count))

This should be technically possible, but I haven't seen any library that does it.

[lucian1900]

SQLAlchemy lets you do precisely that. Give it a try, I haven't written a line of SQL since I've been using it.

[drdaeman]

Yes, I know and use it, but SQLAlchemy is not SQL. It's completely another (although, SQL-inspired and compiled-to-SQL) language, which rises learning barriers. I find myself frequently thinking SQL then mentally transforming the queries to SQLAlchemy syntax, which is somehow pointless activity, considering the fact computers excel at transforming formal languages.

I believe, If someone'll take an SQL SELECT statements parser and create a library that'd generate SQLAlchemy query/statement object from them, such library will make development more productive.

[zzzeek]

this is more or less what HQL does (http://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html...). However the huge advantage to composing SQL as an actual object construct is that you get reusable constructs which serve as components to composing a larger structure. String-based SQL OTOH means you're going to be concatenating strings together which is error-prone, verbose, and even hazardous from a security point of view as it discourages the usage of bound parameters.

[memset]

I have always wondered why people have an aversion to stored procedures. They are a programming language in themselves, so you could pass all of your parameters (owner, some_condition, other_condition) into a proc and, depending the logic, return a different cursor to a different query.

[zzzeek]

it's because the stored procedure development model lacks the tools in order to make integrating with an application-level domain model simple. You end up needing to write not just one persistence layer, that of marshaling your object model to and from SQL statements, but two - all the SQL statements behind your stored procedure layer, and a second to move all the data between the SPs and your object model. To make matters worse, the stored procedures must be written completely by hand without the benefit of any in-application schemas to help compose statements.

One reason for the variety of opinion on this is that different developers make more or less use of domain models in the first place. Those who are accustomed to writing all SQL completely by hand with no helpers at all, and not working with a domain model tend to view the stored procedure approach as equivalent. Those who are accustomed to having at least some simple marshaling layers like a construct that generates an INSERT statement given a list of column names see the SP approach as more tedious since simple techniques like that are usually not easily available, at least in more old school SP languages like TRANSACT-SQL and PL/SQL.

All of that said, I do think this is a problem that can possibly be solved. Postgresql allows SPs to be written in many languages, including Python. I have an ongoing curiousity about the potential to integrate a Python-based object relational system into a stored procedure system. But it might end up looking like EJBs.

[hcarvalhoalves]

The problem with stored procedures is managing it. You have to maintain another, separate codebase. You need to make sure those procedures are "installed" and up to date. Plus, DBMSs don't have a concept of "versions", and those procedures are treated as data. In short, it's hell.

That's one of the pain points RethinkDB is trying to solve, since you write your queries in whatever application language you use and it's parsed and executed in the cluster.

[PommeDeTerre]

This sounds like a situation where it'd just be better to use separate, yet similar, queries, with each handling a particular case or set of conditions.

Views, stored procedures and functions can be used to help isolate duplication, parameterize the queries, or otherwise hide the SQL.

Code like you've posted is the result of taking DRY too far, to the point where avoiding a small amount of repetition ends up bringing in far more complexity and problems than the repetition might cause.

[nemoniac]

CLSQL has a functional interface which can do this (http://clsql.b9.com/manual/).

[gngeal]

"The only problem I see, is we can't rewrite SQL."

I believe that the phrase you're looking for is "lack of compositionality".

[drdaeman]

I fail to see why one can't manipulate parsed SQL's AST. SQLAlchemy shows thas manipulating SQL-inspired objects is perfectly possible.

It's only bridge between sqlparse and SQLAlchemy that's missing. I guess, just because nobody had a wish, will and time to finish and share one.

[gngeal]

I fail to see why one can't manipulate parsed SQL's AST.

I think that this is the "every problem in CS can be solved by another layer of indirection" part. You're basically sidestepping the issue of SQL not providing the functionality in the first place.

[neilk]

There are libraries which build up a model of a query - selections, constraints, etc, and only turn it into a string at the point of executing it.

SQLAlchemy was already mentioned. In the JavaScript world, there's node-sql. https://github.com/brianc/node-sql

[PommeDeTerre]

That kind of code often ends up being worse to deal with than the SQL it is replacing.

I've always found it kind of odd how there are some people who despise SQL merely for its syntax, yet they'll turn around and advocate the use of libraries which mimic a SQL-like syntax in some other programming language (but do an absolutely terrible job at it).

The node-sql examples are atrocious, for example. It's even more obvious with the SQL so close by. The SQL statements are clear and concise, while the JavaScript version is nowhere near as easy to read.

At least LINQ gives the option of not having to directly deal with the method calls, which makes it marginally nicer to work with. Anything less than that, like we see with basically all other systems, is far less usable.

[j-kidd]

> I've always found it kind of odd how there are some people who despise SQL merely for its syntax, yet they'll turn around and advocate the use of libraries which mimic a SQL-like syntax in some other programming language (but do an absolutely terrible job at it).

No, I use ORM because I love SQL. ORM doesn't replace SQL. ORM helps to generate the exact SQL I want with much less code.

I have seen application with thousands of stored procedures, most of them boilerplates, and only supports one particular flavor of RDBMS. I have seen too much hand-crafted SQL in the form of "@param_xxx IS NULL OR field_xxx = @param_xxx".

I used to think that Tom Kyte was right, that everything should be in stored procedures. Now, I am thankful for ORM (more specifically, SQLAlchemy).

[neilk]

The complaint was not about SQL's syntax, but that SQL statements as strings are inflexible.

One often wants to have several variants of a SQL statement, beyond simple placeholders for arguments. I've seen several projects that grow a lame templating syntax on top of their SQL strings, to the point that the SQL then becomes incomprehensible.

If this really bugs you, perhaps the ultimate solution would be to actually parse SQL.

   query = sqlParse("SELECT foo FROM bar WHERE quux = 1")
   query2 = query.clone().constraint("quux = 2")

[Daishiman]

Some ORMs support the Query Builder pattern which does the exact thing you want. http://www.yiiframework.com/doc/guide/1.1/en/database.query-...