The problem is that people who end up writing most of those libs are:
1. People who have very superficial knowledge about range of features, semantics, and algorithms behind SQL.
2. People who are motivated by an irrational yucky feeling about code in strings (as if their Lisp/Python/Java/etc. source isn't just code in a string itself).
3. People who are typically very averse towards learning new technologies. They are blissfully unaware of all the ways they can persist their objects without "mapping" them. They know in depth and favor one application language, and they've stored data in SQL a few times, so let's do everything in SQL, because learning is hard, but writing abstractions is cool. It feels like an achievement.
> 2. People who are motivated by an irrational yucky feeling about code in strings (as if their Lisp/Python/Java/etc. source isn't just code in a string itself).
I won't claim anything about people who write ORMs, but wanting to avoid a mess of concatenated strings in your code, which will blow up unexpectedly because your forgot a ',' in an if somewhere is a sane attitude to have. SQL strings are unsafe and compose very badly.
As for claiming that "all code is string anyway", this is a strawman. Sure, "all code is string", except it can be statically checked for basic syntax errors.
If we'll be talking about strawmen, the idea every SQL query is composed of many concatenated string pieces is the biggest of them all.
You both talk about the perils of dynamically building queries, yet proclaim the benefits of static syntax checks. You can't "statically syntax check" a dynamically built query. Even with ORMs. So pick one. Either you're building it dynamically, or if it's static there won't be "string pieces" to worry about.
Most of queries are defined statically: a query with several anonymous (?) or named (:foobar) parameters to insert literals. So none of those scary string concatenation problems even occur.
Regarding stray commas... please: listOfIdents.join(','). It's not rocket science. Plus, I'm not necessarily advocating you use string functions to build queries, there are many small helpers to be had when you need to build a complex query, but the point is to know when to stop, and not try to completely hide the resulting SQL from the user of such libraries.
It's one thing to use a query builder for those rare complicated dynamic queries, but an entirely different thing to lock down and hide the connection and have your hands at the mercy of an abstraction layer for your every query.
If you can't control when and how your database gets called, if you can't control select locking, and transaction isolation levels, if the vast majority of its functions and syntax is out of reach, you can only do the most basic of CRUD operations on SQL.
It's just ugly that we have a complex piece of machinery like a modern SQL database, with all those advanced querying and MVCC features packed in for people to use them in order to build fast and safe applications, and watch them use it like a little more than a file system.
It's like buying yourself a million dollar supercomputer so you can play Solitaire on it.
> You can't "statically syntax check" a dynamically built query.
In a language with any sort of decent static type system, you most assuredly can. There should be no way to build and use an invalid query, the same as you can use the type system to enforce any other constraints.
> It's one thing to use a query builder for those rare complicated dynamic queries, but an entirely different thing to lock down and hide the connection and have your hands at the mercy of an abstraction layer for your every query.
Almost any SQL I've written has needed to be composable, for the same reasons that any other code should be composable. I need to be able to get <X> complex set of data from the database, given an identifier which could be an ID, or an URL slug, or any number of other things. I may also need to get that either for one row or for many.
It's nice to be able to split that up into the bit of the query that selects the data, and the bit of the query that chooses which row(s) to select it from, without writing out the SQL twice or developing "stringly-typed" code. It's a standard across most of the software development world that when dealing with source code (which SQL is), you almost always work with ASTs.
The frameworks I use for this do allow me complete access to the database, including all the things you've mentioned, and the ability to extend my queries with arbitrary syntax.
> "There should be no way to build and use an invalid query"
Well the SQL server won't actually run an invalid query, so what's your point?
I'm not afraid of invalid queries. I'm rather afraid of valid queries like these:
"DELETE FROM Table"
Because depending on how abstract your ORM is, it's far easier to run something you really don't want to run on your SQL server. But at least it'll be valid, hey!
> Well the SQL server won't actually run an invalid query, so what's your point?
To avoid runtime errors, I suppose.
As for "DELETE FROM table", I would say it's not any likelier to happen with an ORM than without. I haven't experienced issues of this sort, at least. The problem is more to build efficient, complex SELECT queries without getting a bad surprise performance-wise once in production.
> If we'll be talking about strawmen, the idea every SQL query is composed of many concatenated string pieces is the biggest of them all.
I'm usually careful about using such words as "never" and "always". However, I have yet to encounter a non-trivial application built on top of raw SQL where this doesn't come and bite you. Of course you can't statically check your ORM-built query, but just like with static typing, you can still avoid many problems this way.
> Regarding stray commas... please: listOfIdents.join(','). It's not rocket science. Plus, I'm not necessarily advocating you use string functions to build queries, there are many small helpers to be had when you need to build a complex query, but the point is to know when to stop, and not try to completely hide the resulting SQL from the user of such libraries.
That's just one example. I've done complex dynamic queries with joins and aliases, and frankly, that's ugly with an ORM. Without it, it's an abomination. And I'll point that most (all?) ORMs let you use raw SQL.
> If you can't control when and how your database gets called, if you can't control select locking, and transaction isolation levels, if the vast majority of its functions and syntax is out of reach, you can only do the most basic of CRUD operations on SQL.
I'm not the biggest ORM advocate there is, but even I have to admit that persisting a complex object graph using an ORM is way easier than without it. The problems arise (usually) when you combine ORMs with naive developers attempting to get a lot of data out of the database.
I think you dismiss too easily the advantages you get from a good ORM (not that there are many of them), but I agree they're full of performance pitfalls as well (lazy collections being the most prominent one).
I've noticed that as my SQL skills evolved, I became more and more repulsed by ORMs. Now when I write a schema, I design a remote facade and map that 1:1 to a service object in client code. It is the best way I have contrived in a commercial setting (e.g. constrained in every way) to get testing isolation between database and client code to be useful.
This is something I've also noticed, and ironically it took writing an ORM to get it. I still think however that using Crane is worthwhile, even if I only use the migrations and connection management and write all my SQL by hand.
I recommend to anyone who thinks they want to use an ORM to start out by writing one, so they'll understand the product space and use cases better. It's my own little subversion of a crappy paradigm.
It's true, they don't have to be useless.
The problem is that people who end up writing most of those libs are:
1. People who have very superficial knowledge about range of features, semantics, and algorithms behind SQL.
2. People who are motivated by an irrational yucky feeling about code in strings (as if their Lisp/Python/Java/etc. source isn't just code in a string itself).
3. People who are typically very averse towards learning new technologies. They are blissfully unaware of all the ways they can persist their objects without "mapping" them. They know in depth and favor one application language, and they've stored data in SQL a few times, so let's do everything in SQL, because learning is hard, but writing abstractions is cool. It feels like an achievement.