I've seen a lot of the problems that ORM creates with big projects. The most egregious is lack of control. You'll run into some problem caused by some quirk of your ORM system and you'll dig down into the SQL and learn precisely what's causing it, but you still won't be able to fix it because you don't know the magic voodoo incantations to change your config or the ORM client code in the right way to fix it.
When ORM starts to get in the way like that it really makes you wonder whether it's worthwhile.
Not true. Any acceptable ORM system (and there's a lot of those nowadays) allows you to drop into pure SQL and several levels of abstraction between that and pure object code.
Really, I don't know which library you're using, but I have never seen such an issue with neither Django nor SQLAlchemy, LinqToSql, or Yii's ORM.
I would love to hear about an example of this kind of case.
My experience is that ORM queries, used properly, deliver predictable results. If they don't because of a bug in the ORM, can't you just drop down to the native query and move on?
Dropping down to raw SQL is equivalent to rewriting the affected components. This might be trivial with simple CRUD apps but with large, complex systems that can be a big undertaking. Not to mention that if you end up spending a significant amount of time using straight SQL instead of the ORM that draws into question the value of using the ORM at all. Not to mention causing maintenance and development headaches down the road.
Also, the "you can always drop down to SQL" defense of ORMs is extremely weak. It's great that most ORMs are not so intensely broken as to prevent you from not using it, but in general the "you can just stop using it" argument is not a strong one. Imagine if C++ was defended based on the idea that you can just drop down to raw machine code if you run into problems with the language.
All code has bugs. In my experience ORMs don't save you from having to drill all the way down to native SQL to debug. Then you figure out what SQL changes you'd need to fix the bug and from there you work your way back up to how you'd need to change your ORM config or client code to effect the same thing. ORMs are typically some of the most leaky abstractions of any sort. If people spent as much time examining and tweaking machine code as most ORM users do with SQL code people would question the usefulness of their "high level" programming languages.
Why not patch the ORM then? Personally, I suspect most of the issues people experience with ORMs starts with poor data modeling.
Also, I'm not sure what "large, complex systems" means, but it doesn't sound like the use-case for an ORM to begin with. ORM is best suited for small, flexible, simple systems – which ought to be 99% of what people and companies build.
I just don't see the benefit of complaining about how its an "antipattern".
Unfortunately yes. It was 9350 lines of string concatenation that called sp_executesql at the end (T-SQL/SQL2005). I had to remove all the injection attacks...
My main experience with Hibernate but I could give you a nice long list of examples. The big problem is that there is a pretense of transparency and simplicity when it doesn't actually exist.
For example, Hibernate tries to optimise how much it loads and saves. This means it doesn't load foreign keys automatically and it has its own logic for exactly when and how it flushes data.
If you are loading an object it will by default not load the collections of objects that are related to it - that will happen lazily when those properties are accessed. Sounds fine, right? Exactly what a good developer would do by default anyway. Until you pass one of those objects around to a naive code module that knows nothing of the database and it tries to access the property - which looks like a nice in-memory collection to them - and it now executes an expensive query on the database that you weren't expecting. In fact, the naive code module might get a whole list of these things to work with and it might sequentially query each one. The best case here is that it's thousands of times more expensive than it should be, but the worst case is far worse - the database transaction might have ended or have been rolled back (which generates a giant exception+stack trace), or the rows might have been locked (which might just make things slow but could result in deadlocks since this naive code doesn't know anything about the database).
The way I see it in the end is that ORMs try to hide some of the most important parts of your software design from plain sight. You feel like they are hiding trivial stuff that is boring and you don't want to waste time on. But in the end that stuff is absolutely critical to your design and some of the most core stuff you should be thinking about. Having it all hidden away makes you not think about it at all until its too late and you've built a whole giant application on shaky ground.
After being through this enough times I am now expert enough to confidently build applications using Hibernate and save time over plain SQL. But the body of knowledge I had to accumulate just about Hibernate is equal to all the knowledge I ever had to learn about plain old relational databases. These days, if I start a new project using an ORM I try to minimize its use down to just being a convenient utility for loading rows and map them into an object's properties.
The one thing I will say in their defense is that when time is your most critical factor - eg: you're a startup with 3 months of runway and your end product is going to be mainly demo ware to get your next round of funding - an ORM like ActiveRecord, etc. is absolutely the right thing.
It's mainly due to the fact that MORE abstraction means MORE complexity. I currently have to deal with an NHibernate mess of over 1000 domain objects (!) at the moment and it's an absolute nightmare on the performance and maintainability front.
To be fair, 1,000 (or however many you need) stored procedures probably wouldn't be all that much better. I've seen systems taking both approaches and I've seen both go right and wrong. It has more to do with the team than the technology, I think.
It's not worthwhile if you don't understand what the function of the ORM code is. ORM might be an abstraction, but it doesn't mean it doesn't take some elbow grease to learn. Like anything worthwhile, once you get over the learning curve hump, it becomes a tool that you enjoy using precisely because you understand it.
Even when you have to sometimes write SQL inside your ORM!
When ORM starts to get in the way like that it really makes you wonder whether it's worthwhile.