ORMs are an antipattern. They save you a little time and effort up front, but when you inevitably need to do something that's not basic CRUD you end up wasting an immense amount of time fighting your tools.
Don't fight your tools, just write SQL.
I've been using ORM pattern throughout my career, and have even designed/built one 10 years ago because what was available at the time wasn't gonna cut it functionality- and performance-wise for the system we were building (a real-time bond trading system). Nowadays most mainstream ORMs do the job rather adequately.
Having said that, I have a fair share of gripes against most of them, but I still find that people who bitch the most about ORMs are those who understand it (and the problem it tries to solve) the least.
A big issue nobody is addressing here is that using "pure SQL" requires you to use crappy data structures (say, a dictionary, "associative array" or, most likely, a simple array in languages like C/C++) to access and push the data. So, instead of having something simple like:
user = User.objects.get(username='ecopoesis')
...
# 100 lines of business logic that uses the 'user' object.
# Methods like 'set_password' that calculates a hash of a
# password and stores it in the object make my life much easier
# by keeping the logic close to the data.
...
user.save()
Namely, what you can't avoid, if building an object-oriented system, is that you ultimately need to map those DB values to your live objects. Whether you end up using an off-the-shelf ORM or you hand-code the SQL yourself is less relevant.
Odds are if you eschew an ORM in favor of hand-coded SQL layer, you'll end up writing one anyways - only you're likely to do a shittier job of it than people who understand the problem area far more intimately (e.g. Hibernate committers).
I always get strange looks from my fellow programmers when I suggest that we just ditch the ORM and use straight SQL. With some complex queries, like generating reports, I had a much easier time using iBATIS many years ago than hacking through ActiveRecord's oddities. AR is good for simple select/insert/update, but once you get into UNIONs and nested subqueries, you're fighting AR more than you should be.
Is ActiveRecord running n+1 queries every time you call a method? You're doing it wrong. Ignorance of how an ORM works is not an indictment against ORMs.
Is ActiveRecord generating really bad SQL for the fifty-table report you want to generate? Fine, ditch it. Run your own custom-written SQL, or a stored procedure, or whatever. Just because an ORM can't write all the SQL you'll ever need does not mean that they're useless.
I have hand coded and maintained a lot of SQL. And you know what's not fun? Adding or removing a column and going through all the selects, updates, inserts and manually changing them all and still getting bit by the 'Table X has no corresponding column of type Y' for a piece of the system used once in a blue moon.
And every time you want to add a new class creating the table, creating all the boilerplate select, insert, update statements that are exactly the same as the thousands of times I've done this before.
All that pointless busy worked done away with in a second. In a couple of keystrokes.
You, in my opinion, do not know what you're missing.
That's what an ORM is for. It's not a 'little time'. It's only a 'little time' if you don't actually do serious business coding where there are often 100s of tables to effectively solve a problem domain.
An ORM will dissipate that entirely. And when you want to drop down to hand coded SQL, you can! Best of both worlds!
The only problem with ORMs is when novices don't understand what they're requesting the DB to do.
And guess what, SQL has exactly the same problem!
"Adding or removing a column and going through all the selects, updates, inserts..."
If you're adding a column, you're adding functionality to the program, so of course you're going to have to add the column everywhere it's used! And if removing, then likewise. Because you have code depending on the new/old column that needs to be changed.
There's nothing boilerplate about any of this. Every column has unique functionality and integrates into my code in its own unique way, and needs to be treated as such.
In fact, going through all the code manually makes sure you discover the once-in-a-blue-moon cases where you need to change the behavior.
Using an ORM isn't going to help any of this at all.
Or, if your new column is exactly the same as 80 other columns in the table, which are all used in the same way, then you probably shouldn't be using columns at all, but rather rows in an id-attribute-value format, which solves your whole problem of boilerplate code.
It tends to consist of a lot of classes of fairly shallow functionality with a lot of data access based on different criteria, meaning a lot of different SQL statements doing minor variations on a theme.
Business code is boilerplate a lot of the time. An organisation has a name, a phone number, an address, an added on date, an added by date, and on and on. An so does a Person, a Project, an Order, an Enquiry.
99% of columns are simple data stores that have no sort of 'unique functionality' you describe. They exist to store data that's only purpose is to be recorded and later re-displayed or updated. Hell most problems that require a DB don't have this 'unique functionality' you describe.
For example a bunch of static methods on an Order class or in a OrderDataAdapter:
Order.Get(id)
Order.Get(accountCoordinatorUsername)
Order.GetUnassigned()
Order.GetOrdersByPartner(id, (optional)boolIncludeCompleted)
etc., etc.
You then have to add the column to every method that uses this value. You have to go through all those other totally unrelated methods checking and changing each and every single one of them. And this table is probably accessed in other classes too, say like the QuarterlyReport class, so it's now a find and hunt scenario.
Unless you're relying on SELECT *, but no sane SQL programmer does that right?
In the bad old days you'd start rolling your own ORM to try and manage this mess. It would invariably suck and cause more problems than it solved. Today's ORMs are a god-send to this type of coding.
What I concluded was that we debug most languages line by line, like moving through a linked list. SQL is much more efficient. A query, if it is well structures (no inline views or UNIONs) is a small set of sections, each of which is broken down further. So instead of reading through a query line by line, we figure out which section to look at, which relations are involved and debug that way. It's like searching a btree instead.
I'll admit, though, that I'm a big fan of having a separate table that has the "front end view" (read: denormalized) of the data for the ORM to read from, especially in cases where the raw data tables are fairly complex.