I honestly do not understand ActiveRecord and ORMs in general. There is the obvious advantage of mapping, for which one merely needs microORMs. Even by reading this article one realizes that for anything complex, it is much simpler to directly write SQL queries rather than use byzantine techniques whose only raison d'etre is to coerce the ORM into writing those SQL queries. I mean -- why not write SQL directly? :-)
There's the not-so-common case of needing database independence as well, but at that point DB perf becomes a really hard problem to solve generally...
If you're encountering these problems at scale where scale is up to the reader, I can promise you that you're already writing SQL. You might have already started from the beginning by writing direct SQL, because that's something that rails also allows you to do against a model. Apart from juniors, I don't think I've ever met anyone who actually said that they wanted to avoid writing SQL in all cases. The reality is that it's far more useful and efficient to burn cpu time than dev time, and ORMs general tend towards the latter than the former in the favor of generally being a fair bit more legible (depending on the writer).
As a semi-counter-point, learning some data mapper libraries where you learn a DSL that seems better-structured than SQL definitely helped me understand SQL better.
SQL is ultimately a bit dysfunctional and takes a lot of getting used to. Not arguing it's worth learning but since most of my career I was not truly exposed to it, I preferred the bespoke DSL.
I'll agree using ORMs is indeed going to ridiculous lengths to avoid learning even a smidgen of SQL but there's a nice middle ground.
Say you have a SQL database on one side, and a Python/Ruby/Java/Golang webserver on the other. If you wrote raw SQL, you'd have to also write the ORM (the object-relational mapper) yourself as well to map database results into objects or whatever data structure your webserver expects.
In the sense that in Java (almost) everything is an object, a jOOQ query must of course return an object (such as a Record2<String, String>), but unlike Hibernate or AR, jOOQ doesn't enforce a 1:1 mapping between domain objects and tables, especially if you turn off POJO generation - at my last company, we had some custom mapping from a table to several subclasses of a sealed class depending on certain fields in the table - and you can even split a single class into two tables etc.
> In the sense that in Java (almost) everything is an object,
no, this is misleading. There are different kinds of objects. if you use JDBC to run a query you get back something like a Row object (sorry I havent done JDBC since the 1990s) - the "Row" like object does not declaratively define the fields of the row, the fields and the data of each field are all data. however when you get back POJOs, as you say, these declaratively define the fields that are "mapped" to a column. if jOOQ does this, it's an ORM. ORM has nothing to do with writing SQL - that's called a "SQL builder". the ORM is about marshalling data from POJO-style objects to and from relational database rows.
There are different ways of using jOOQ, some of them being more like an ORM (but still more low-level than Hibernate) and others less so. You can use the API in such a way that it just returns a general tuple object holding the result from your query (called Record1<T>, Record2<T1, T2>, etc.). This is especially useful when fetching from multiple tables. You can also use codegen to auto-create POJOs and mapping code, but this is not required.
"Unlike ORM frameworks, MyBatis does not map Java objects to database tables but Java methods to SQL statements." https://en.wikipedia.org/wiki/MyBatis
"Jdbi is not an ORM. It is a convenience library to make Java database operations simpler and more pleasant to program than raw JDBC." https://jdbi.org/
"While jOOQ is not a full fledged ORM (as in an object graph persistence framework), there is still some convenience available to avoid hand-writing boring SQL for every day CRUD. That's the UpdatableRecord API [which is only one part of it and you don't have to use it]" https://blog.jooq.org/how-to-use-jooqs-updatablerecord-for-c...
ORMs do not require SQL builders. Textual, hand constructed SQL to rows based on tables mapped to objects and you get objects back. "Why don't people learn SQL?" as a retort for ORMs is incorrect.
This is about ActiveRecord, which tries very hard to convince you that a relational database works like objects in memory, especially when it comes to associations, saving etc. Hibernate is similar in that regard (although it at least enforces repositories, but you can still have surprises when it comes to when it loads associated tables). Both allow you to drop down to SQL but then you lose syntax checking / type safety and the conveniences of the library.
With something like jOOQ, the query language is basically SQL, just made type-safe. You write a query that maps to an SQL query 1:1 and then you map the results to whatever you need. No implicit saving, auto-loading of associations in the background etc.
So it's not about "people should use the SQL syntax instead of query builders", it's "people should write relational queries explicitly instead of relying on the framework to save and load stuff from the database when it deems it necessary". Your domain objects do not need to know that they're persisted and they don't need to carry a DB connection around at all times (looking at you, ActiveRecord).
Speed to market. One person or a very small team can build a feature-rich app in Rails (or equivalent framework) faster than any other approach.
Plenty of companies end up building the wrong product altogether and need to pivot when they realize customers don't care about feature X and need feature Y instead. In cases like these you hope you get the right product out there and survive long enough to regret building with the fast framework instead of the finely tuned ultra performant version.
It’s really handy to have a composable API for building SQL queries where different elements are contributed by different parts of the code. From example, having your authorisation code apply restrictions to queries through where clauses, joins, etc. to ensure a user only sees the records they are allowed to see.
I currently spend a large proportion of my time working in a Java code base that uses JDBC directly. There are many places where the complexity of the work to be done means code is being used to assemble the final SQL query based on conditionals and then the same conditional structure must be used to bind parameter values. Yes, in some places there are entire SQL statements as String literals, but that only really works for simple scenarios. There are also many bits of code that wrap up common query patterns, reimplementing some of what an ORM might bring.
I recently implemented soft deletion for one of the main entities in the system, and having to review every query for the table involved to see whether it needed the deleted_at field adding to the where clause took a significant amount of time. I think better architecture supported by a more structured query builder would have made this much easier. For me that’s the main benefit of an ORM.
A well-designed ORM can help you with performance, for example, by reducing the amount of effort to do things like fetch related records in a single query. The really good ones also handle migrations. And finally, all of the popular ones I'm aware of allow developers to use them just for mapping results to objects. I would say that in general the further an ORM strays from SQL concepts the more troublesome it is likely to be. If the query syntax looks like Mongo, then you probably won't have a good time when doing more advanced things.
Yeah … for want of easy serializing to classes and what not I hate the ORM approach. The database is already a black box. Stats might be out of whack. The indexes might be bad. Etc etc. now add magic that the ORM does.
There's the not-so-common case of needing database independence as well, but at that point DB perf becomes a really hard problem to solve generally...