You still need to supply the local variables to your ORM-like so that it can perform interpolation into the string (a feature which is actually best done by the database driver, NOT your ORM-like). Perhaps we can suggest this syntax:
s.add "WHERE foo > ?", bar
Or even this:
s.Where "foo > ?", bar
At that point, you've reinvented ActiveRecord or hundreds of other query builders, it also avoids you having to remember the ordering rules of sql as to which part of the query must be built first. There's a reason these query builders have converged on similar syntax.
I see where you're coming from - you don't want to learn two sets of syntax, but many query builders nowadays are very well thought out, and have a simple syntax which just echoes SQL while avoiding sqli and statement order issues, they also usually have an sql() option which lets you just send SQL if you wish for a complex query.
There are very good reasons people keep inventing layers over SQL:
They help avoid sqli
They centralise db access so you can optimise in one place by say adding caching layers to queries
They mean you don't have to deal with the vagaries of SQL for simple queries unless you want or need to
"You still need to supply the local variables to your ORM"
That can be done automatically in many languages. At least in the sense you can do:
"where value=$(bar)"
and not:
"where value = ?", bar
OpenACS was an old web framework for the Tcl language, but it even had that 20 years ago!. The company backing it failed in the .com era, and the lack of types was a turnoff for many - but it made SQL much much more readable. And of course the queries were turned into prepared statements. A query looked like this:
select foo
from table
where age>:min_age
and name != :user_name
Things like this can also be done in node.js (disclaimer: I wrote the blog post below):
Perhaps we can suggest this syntax: s.add "WHERE foo > ?", bar
I'd say stick with keeping it a string and use:
s.add "WHERE foo > $(bar)"
but many query builders nowadays are very well thought out, and have a simple syntax which just echoes SQL
And that's the problem. Everyone "echos" SQL, just slightly differently, each with their own idiosyncraticies.
If all you do is echo anyway, why don't you just let me write plain SQL, for christ's sake?
It's much easier for humans to memorize a slight SQL dialect than a different god-awful, nested ORM API boilerplate for every language they're working with.
I can write this without thinking, for queries of almost any complexity:
s.add "SELECT id from users WHERE"
s.add "name == $(name)" unless name.nil?
s.add "AND age > $(age)" unless age.nil?
s.add "GROUP BY age"
A parser/query-builder doesn't need amazing smarts to infer exactly what I want from this sloppy syntax; e.g. drop the "AND" if the first condition was false and present me with meaningful error messages when something is ambiguous.
Ask me to formulate the same query in any ORM and I'll have to refer to the documentation. And almost every time I'll waste a stupendous amount of time on getting some irrelevant, syntactic detail just right. Only because the ORM insists on an autistic API.
They mean you don't have to deal with the vagaries of SQL for simple queries unless you want or need to
After having worked with Hibernate, SQLAlchemy (the one-eyed amongst the blind), ActiveRecord, DataMapper, Sequel and a few others that I don't even remember, I conclude with passion: They have it backwards.
It's not the vagaries of SQL that I don't want to deal with. It's the vagaries of the ORM du jour that I don't want to deal with.
If all you do is echo anyway, why don't you just let me write plain SQL, for christ's sake?
They usually do more. SQL is not very pretty or friendly IMO, ORMs clean it up a bit (for example allowing arbitrary ordering, sqli, construction of multiple queries from one base). The example I gave was an arel query - very similar to yours if you move the WHERE out into the function name.
Your ORM proposal above looks fine (and very similar to existing ones but with more strings), but personally I find it uglier than the many which are available, and you haven't actually tackled the protection against sqli or explained how that would work. Normally you'd pass that through to the db driver as a $n param, rather than trying to escape a string at the ORM level. If your ORM is going to infer details from the sql, it basically has to parse that sql first and handle all the same things that current ORMs do by using named functions to add SQL - you'd find the same issues with consistency and edge cases I imagine, and of course have to be very careful about parameters and sqli.
So if you don't like ORMs, don't use them, but you do have to be sure that your own ad-hoc home-grown ORM that you're writing instead covers all the bases current ones do. I prefer the syntax of the ones you're denigrating for simple queries, even if for complex queries it might be better to write sql directly (via the functions they provide), but would be interested to see it in action - when are you going to start :)
s.add "WHERE foo > ?", bar
Or even this:
s.Where "foo > ?", bar
At that point, you've reinvented ActiveRecord or hundreds of other query builders, it also avoids you having to remember the ordering rules of sql as to which part of the query must be built first. There's a reason these query builders have converged on similar syntax.
I see where you're coming from - you don't want to learn two sets of syntax, but many query builders nowadays are very well thought out, and have a simple syntax which just echoes SQL while avoiding sqli and statement order issues, they also usually have an sql() option which lets you just send SQL if you wish for a complex query.
There are very good reasons people keep inventing layers over SQL:
They help avoid sqli
They centralise db access so you can optimise in one place by say adding caching layers to queries
They mean you don't have to deal with the vagaries of SQL for simple queries unless you want or need to