ORMs are nice but they are the wrong abstraction

> Most people never learn databases, and most people who do are idiots working on enterprise three-tier architectures

Tell me how you really feel :)

My biggest problem with ORM's is that it causes people to sprinkle the ORM code throughout their codebase but they'd never do that with SQL, they'd want to try and sequester it to a system whose responsibility is the retrieving and updating of data.

It puts people into a poor mindset around their data.

> ORMs suck, but raw SQL embedded in your code sucks too.

_strongly_ disagree and I bet anyone who has this opinion can't articulate a good, concrete, reason for it. The best they're going to come up with is FUD about changing table names, which is one of those theoretical things that very rarely manifests itself in reality.

You missed the point entirely.

Language like "require raw SQL today might be more elegantly handled with an ORM's language" suggests that you do not understand the elegance of SQL or the relational data model.

However, you are correct. I used ORMs less and less often as I became a better SWE. They can save time on toy projects, but for those, I usually use a NoSQL or KVS directly.

But one issue with ORMs is also that they can bait you to try more complex queries, where eventually you might run into one slight edge case that you spend huge amount of time finding a solution for because reverting to raw SQL will not feel elegant at that point - and feels like you've failed in some way. So you might run into these edge cases and then also you might have terrible joins without really knowing. Also different ORMs have different APIs and capabilities, which means more time learning those things and being uncertain whether this particular ORM even supports what you want to do.

I think generally a lot of time will be spent in analysis paralysis and overthinking. Is this query doable with ORM? How long should I google? How far into docs I have to go, do I need to go to ORM source code to figure out how to implement this? If it's a project with other developers, then will they disapprove of me using raw SQL here, and giving up on trying to go for an elegant ORM solution.

> What I generally want is:

> 1) Something translating SQL syntax into my native language, but maintaining SQL full semantics and expressiveness.

You have just described a good ORM used well.

> 2) This should allow me to be database-agnostic.

Meh, you sacrifice some powerful features if you demand total database agnosticism, and how often do you actually switch databases? Being database-agnostic is a side benefit of writing your logic simply against a good abstraction. The biggest benefit is composability. You can write (and optimize) one query against abstractions, and re-use that query in lots of different ways.

> 3) This should prevent things like injection attacks.

As all ORMs automatically already do.

> 4) It should not map onto objects. It should maintain 100% of the capability of SQL.

If it's not mapping onto objects, what is it doing? The problem is that your mental model of what "objects" means includes awful design decisions like deep inheritance trees, mutability, and lots of reference cycles (this.parent.child[0] == this, etc.) If your object model is already clean and following relational principles, then mapping into that model is exactly what you want.

It should not strive to maintain the capability of some bastardized pseudo-language which is despised by the progenitors of relational logic. It should strive to support the relational model. That's not the same thing.

> 5) This may and ideally should have added functionality, for example, around managing and organizing database migrations.

No, because your code versions and your database schemas advance together. To reliably run a database migration in code, you'd need to run it with the code version that exactly matches each step in the schema. That means for each step in the migration, you'd need to checkout the correct commit in git, compile, and run the ORM code in that version. Either that, or you're maintaining a code model that is compatible with every historical database schema, which is way worse.

But what ORMs should be able to do (and I haven't found one that does this well) is generate SQL migration scripts for you, which you store. Those would be frozen relative to the database schema version, so all the above problems go away.

> What most programmers fail to understand -- since universities don't teach -- is how powerful and elegant the underlying theory of databases is.

The underlying relational model is powerful and elegant. SQL itself is not. SQL is disliked by the founders of the relational model. Good ORMs let you incorporate the relational model into your code.