ORMs. I didn’t “buy” Hibernate but we adopted it and the whole JavaEE thing wholesale. What a disaster. Learned a lot of lessons from that, #1 being, don’t use an abstraction layer (ORM) to abstract away another abstraction layer (SQL).
The performance of Hibernate relative to plain SQL was abominable, and this directly caused us to lose at least one contract. It turns out - on reflection, of course - that it’s not even theoretically possible to get into the same performance ballpark.
After years of doing battle with the tools, I eventually kicked them all out, decided to work hand-in-glove with the database, and suddenly things became both straightforward and performant. I now think that ORMs are a code smell.
I'm not sure I agree that ORMs are a code smell. I've used NHibernate (the C# equivalent to Hibernate) and more recently, Django ORM, both extensively. It's possible to get out the SQL generated by each, and 80% of the time, it's roughly what I'd write if I were writing SQL, and performs comparably.
The other 20% of the time there are performance differences, but only about half of those are big enough and in hot enough spots to warrant fixing. For those cases, some of them can be fixed by doing things a bit differently in the ORM, but often it's easiest to dip out of the ORM and write a little SQL. The mistake I see people making here is "fighting with the tools": if you find yourself fighting the ORM, just dip into SQL: the ORM doesn't make this hard to do.
What emerges from this strategy is that the ORM handles all your trivial cases, while you dip into SQL for your more complex queries.
It doesn't have to be all-or-nothing. The ORM can co-exist peacefully with a little SQL. If I had to choose "everything ORM" or "everything SQL", I'd certainly choose the latter, but we have other options.
All-SQL has its own problems. As your number of queries grows, you start to find duplication between parts of the code that moves tabular data into more user-palatable object forms. If you create abstractions to remove that duplication... slowly a half-baked in-house ORM emerges. Some projects aren't large enough or long-running enough for that to happen, but it isn't usually the small, short-term projects which have non-trivial datasets that ORM's can't handle.
Some of our differing ORM experiences might come from the different ORMs: maybe Hibernate is just bad and you'd have had a better time with one of the ORMs I've used. I wouldn't know as I haven't used Hibernate.
I can’t comment on your experience either but I can say that IME Hibernate doesn’t play well with others. You end up with all these cache problems that you have to mop up, and interop either becomes super fragile or you disable the cache and the app becomes very slow.
Maybe that’s hibernate, maybe we were just Holding It Wrong, but whatever the reason - we spent a lot more time writing Hibernate code and a lot less time working on domain logic than I was comfortable with (the “blue flame” of a couple of recent comments on other posts)
For me the clincher came when I started using stored functions for business logic. There is so much less duplication (entity objects etc) and you can call the logic from any language, including the SQL CLI.
FWIW I am not at all opposed to libraries that help me work with SQL, but these days I want any tool I use to be very close to the database.
Libraries like Jooq and SQLDelight include what I'm talking about and then build on top of it with codegen which is even nicer since it adds compiler safety
But even without codegen you'd still a much nicer interface than manually hacking together strings as the Golang example others have linked shows: https://github.com/Masterminds/squirrel
Constructing sql by concat strings has a few issues, its repetitive and hard to assemble certain queries conditionally, and at least in golang its easy to write code vulnerable to sql injection and you can avoid that by using types
sql == "INSERT INTO users (name,age) VALUES (?,?),(?,? + 5)"
That said, I will happily agree that that SQL statement composition is not the same as an ORM, and I can see the benefit of Squirrel for those rare times you do need to conditionally build SQL statements.
What are the use cases for an ORM like Hibernate? I've worked in fairly complex domains, an I'd rather just use a lightweight library and write the SQL myself. Typing out SQL queries is easier than, for example, using the JPA mappings and Spring Data JPA query format.
That is the right approach. We were sucked in by the claims that Hibernate would make life “easier”, with the idea that we could represent everything in Java. This was very appealing.
I think another factor was that for a while, databases were seen as a necessary evil, rather than the beating heart of our application (see also: NoSQL). Working in a “real” language was seen as better than working with this messy data thing. The whole “impedance mismatch” nonsense.
(I’m not defending our decision, which in hindsight was wrong and stupid. just trying to explain it.)
Seconding: databases as an "implementation detail" is pretty wrong. If you're writing a basic CRUD app you can get away with it, and in fairness this is a lot of cases. But as soon as you start saying things like "how do we lock rows" or "let's put 10M rows in this table" you really need to write the SQL yourself, because at this point you don't have a software application, you have a data application.
I've always thought the caching Rails added was invaluable most of the time. Granted I've also worked with plenty of people who don't even look at the query logs and wonder why their API's are slow
The performance of Hibernate relative to plain SQL was abominable, and this directly caused us to lose at least one contract. It turns out - on reflection, of course - that it’s not even theoretically possible to get into the same performance ballpark.
After years of doing battle with the tools, I eventually kicked them all out, decided to work hand-in-glove with the database, and suddenly things became both straightforward and performant. I now think that ORMs are a code smell.