You're coming at it from a slightly wrong angle. You are completely right that for queries, there's really no gain, you just end up having to learn both SQL and whatever your ORMs DSL is.
Where ORMs are useful is once you have your objects. The usefulness of an ORM is being able to say:
Also being able to work on your data in objects or structure native to your programming language means that you can leverage both the strengths of the language and the database server. Some operations are much simpler to do in Python or C#, compared to SQL. In other scenarios you really need to let the database do its thing. Again, you do have a point, because believing that you can skip learning SQL and just rely on the ORM will get you into trouble.
One other feature of ORMs is that they allow you to write code that will run on multiple databases, but at the cost of not being able to use the more advanced database features. For many CRUD applications that is a price worth paying though.
Sure you have to write the updateGroups method and use SQL to do it, but that's trival. On the otherhand when you want to do something more complex this is when ORM's inject all sorts of subtle and dangerous bugs into your code base.
For one, you're creating a hard coupling to a specific flavor of SQL. And that's not too mention the fact that you're taking an otherwise purely data object and embedding persistence logic into it - a horrifying abuse of OO.
> you're creating a hard coupling to a specific flavor of SQL
You're just trading one coupling (specific flavor of SQL) to another (your ORM.)
Assuming your application is layered correctly, when you write your own queries, all of your SQL queries are in a single place and can be updated.
BUT: If you're using an ORM, and you let your data bound objects leak into all layers, the coupling is much much much harder to fix if you chose to change your ORM. IE, if you do things like lazy loading, or construct your queries in business logic, switching ORMs will be extremely painful.
I've done it both ways (write my own SQL and use an ORM) and I would say the single biggest mistake is to assume that you absolutely should (or shouldn't) use an ORM.
Not at all - in most architectures, your data objects know nothing about how they are being stored, instead delegating that to a repository class or something similar.
Adding persistence logic to a data object adds all kinds of bloat - it has to have a connection to the database, which now makes unit testing a pain in the neck and introduces all kinds of weirdness around serialization. Now it has a bunch of CRUD methods, so developers have to know which methods are for business logic versus persistence. Also, data objects with persistence logic aren't really suited to be published in a shared library as clients should not access your database directly.
> Adding persistence logic to a data object adds all kinds of bloat
That's not what you do if you write your own queries. That's what a novice does after learning object oriented programming.
For a more accurate example, look at things like Hibernate / NHibernate / Entity Framework and lazy loading. They inherit from classes at runtime and will transparently run queries as business logic navigates relationships on an object graph. It's "not wrong," but it can lead to all kinds of problems and painful refactors. (But it's totally "worth it" in prototypes, one-offs, and throwaway code.)
If you don't do lazy loading, Entity Framework still requires that you pre-declare which relationships you will traverse. It's intended that your business logic uses Entity Framework APIs to say what part of the object graph it will use. (Thus tightly coupling your application to your ORM, which means your trading tightly coupling to a database to tightly coupling to a framework / library.)
But, keep in mind that the ORMs I mentioned tie the objects to the database connection. It isn't quite as intense as "Adding persistence logic to a data object", but they do track that, if you modify an object, it can only be saved on the transaction that it was loaded from; or if you're using lazy loading, lazy loading only works if the transaction is still open.
If you build a layer around your data access code that fully shields your business logic from your ORM, you've "done it right." But, at that point your ORM's value becomes negligible, because from the perspective of your business logic, it doesn't matter if your data access layer has hand-optimized SQL or an ORM; but you've lost one of the real selling points of an ORM, which is that you can easily do your data access from within your business logic. Which is why I say that the biggest mistake is assuming you should, or shouldn't, use an ORM.
Three things - first, it wasn't my example, I'm just responding to the terrible looking code in your previous comment.
Second, you just tried to argue for manually writing your own SQL rather than using an ORM by suggesting the way NHibernate (an ORM) does it is the right one. Might want to think up a new argument.
Finally, I used NHibernate in the past but it's gone out of favor because of the problematic things it does. Those proxies have to be runtime generated (startup cost + some extra overhead for each query) and are terrible for your data model. You have to mark properties virtual and suddenly GetType doesn't return the expected value. State tracking is actually not a great idea - causes many people to do dumb things like querying just to perform an update.
Entity Framework with state tracking turned off is better all around.
> you're creating a hard coupling to a specific flavor of SQL.
I fail to see why this is a problem. Switching databases is a costly move, and is pretty rare as far as I know. When it does happen, it is usually from one type of db to another type, not between two RDBMSs.
IMO it doesn't, by itself, justify sticking to an ORM rather than raw SQL.
Switching databases can be an easy move depending on how you design schemas, versioning and migration. We have a multi-cloud, multi-db offering - customers choose their DB. Without ORM, this would not be feasible.
Many/most ORMs (the ones that follow the ActiveRecord pattern) do this as well though. I prefer to avoid mixing concerns and use datamapper-based ORMs myself, but what GP wrote is fundamentally not that different than what a lot of ORMs do.
I'd dispute "most", as it does not match my experience across a number of languages. Yes, active record sorta does it but, as best I recall, those methods are all implemented generically as mixins, so not actually a part of the data object. This seems to be advocating for data specific queries to be added to each class.
Ummm... No. See my previous comment - at least in Rails, the queries aren't part of the data object themselves, but are instead mixins or default handlers (don't recall which offhand), so it's really just syntactic sugar that makes it appear as though they are methods on the data class.
Even if they did the same thing you're suggesting, by that logic, we should all use PHP because WordPress does, and it's probably more popular than those two combined.
Take a look at something like Spring or Entity Framework to see other takes on ORM patterns.
And again, you're trying to argue against ORMs, while citing that your suggested alternative is supported by the way ORMs do it.
I’m not the person that suggested handwriting object methods. As I’ve said, I agree with the point you’re making, I’m not arguing with you, or for any specific ORM design.
I don’t see what the nuance is you’re getting at with the mixin vs object method argument though. They both have the same issues around testability and violation of SRP.
Ahh, apologies misread the tree. In terms of the nuance around a mixin vs object - a mixin can be defined and tested in its own separate library independent of your data model, so it's at least cleaner in terms of separation of concerns.
That’s fair. My issue with the mixin approach is that then your app code can hit the database from anywhere you have access to the model object, which makes testing your own code in isolation much more difficult
Where ORMs are useful is once you have your objects. The usefulness of an ORM is being able to say:
Also being able to work on your data in objects or structure native to your programming language means that you can leverage both the strengths of the language and the database server. Some operations are much simpler to do in Python or C#, compared to SQL. In other scenarios you really need to let the database do its thing. Again, you do have a point, because believing that you can skip learning SQL and just rely on the ORM will get you into trouble.One other feature of ORMs is that they allow you to write code that will run on multiple databases, but at the cost of not being able to use the more advanced database features. For many CRUD applications that is a price worth paying though.