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))
SQLAlchemy was already mentioned. In the JavaScript world, there's node-sql. https://github.com/brianc/node-sql
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.
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).
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")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.
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.
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.
I believe that the phrase you're looking for is "lack of compositionality".
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.
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.
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.