I personally think that ORMs are good for management and simple CRUD cases, QueryBuilders are good for managing more complex queries while still being secure / type-safe and for everything else a thin database abstraction layer for native SQL queries with parameters / prepared statements is still required especially for performance use cases.
I for one think that "simple CRUD cases" is bullshit, those applications don't exist. In practice, System-of-Records systems are rare. (and should be, their value are inversely proportional of how many of those you have in your overall system).
Because if it was "just simple CRUD", one would use the database directly? Databases are already capable of handling CRUD and much more with way less implementation bugs.
Even assuming your application "is a system-of-record", how is it giving any more value that directly using a ready-made solution like Oracle REST Data Services, or PostgREST?
Ideally models are generated from SQL schemas, which you map to domain models.
I think ORMs do too much. I want to control the querying, or, more precisely, I want to control the SQL that goes to the planner. The good ones largely do allow for this, but I can't think of one that has innate support for vendor-specific features.
What I do appreciate is that they handle the boilerplate like managing connections, preparing statements, setting parameter values, and mapping database types back to client types.
No, because if the schema is the only reference for data models, developers on any sufficiently large team will come up with extremely widely varied queries to access equivalent information. Those are more likely to be incorrect (someone with domain expertise on one set of tables might miss that authoritative data needs to be joined/queried from elsewhere), harder to update when schemas change (more client code changes to alter and test), and more likely to miss performant techniques to query data.
Those can all be addressed with disciplined use of views or common utility SQL snippets or functions, but ORMs also get you to that point without requiring as much ongoing discipline, care, and feeding.
Totally agree. Views as a data API is the best way to take advantage of the facilities that the database itself offers and guarantees enforces consistency across disparate clients.
Ah yes, the famous database integration anti-pattern.
> but ORMs also get you to that point without requiring as much ongoing discipline, care, and feeding.
[citation needed]
The fact that you have being practising "database integration" won't suddenly disappear just because you used a ORM. In fact I expect even worse database integration from your average ORM user, as people that uses ORM blindly often don't care (to their own detriment) about "silly issues" like data provenance or persistence mechanical sympathy.
At some point I expect the DBAs of such database integration nightmares will have to start handling stuff like column-level security and row-level security to prevent naive users from shooting themselves in the foot.