ORM is an anti-pattern (seldo.com)

What web host in the world doesn't run MySQL? What platforms don't support Sqlite3? What PaaS doesn't give you easy access to Postgres or another database.

Compare that to support and sysadmin knowledge of Mongo, Redis, Couch, whatever: there's no comparison.

It's only "overkill" in the very limited sense of, what, CPU cycles? Unused relational algebra potential?

The story is slightly different for open source RDBMSes which have inferior cost-based optimizers and multi-version concurrency control implementations that fail on a wider range of corner cases like:

select count(*) from Billing_And_Accounts_Receivable.Transaction

which is slow in PostgreSQL for reasons I'd rather not take the time to teach about here.

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.

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.

Even the data-types supported are different.

It's not uncommon for consumer and enterprise products to have to work with different databases. In the case of a consumer product, it'd be a client having only one database type installed and requiring that your software use it. You benefit by writing in an ORM because you can have the same codebase for multiple db installs.

With enterprise customers it'll be having multiple databases installed at once, and having your code interface between all of them. You benefit with the ORM by not having to remember and handcode all of the query differences.

Perhaps as a result of it being so exceptionally boring that it's so error-prone. If you forget to wire something up by hand (especially if its nullable) and don't notice while you're coding, it can be one of those subtle little bugs that lingers in the background until it really bites you.

Also, you forgot:

D) Difficult and time-consuming to create test automation for.

I agree with this.

I think you can look at this two ways, over time you incur technical debt for a variety of reasons (if your app is being used or growing in any way). The articles author thinks it is best to start at the bottom and build up your "model layer" from scratch.

People who use an ORM start from above and almost certainly will have to modify their entire software stack to address things like scaling and unusual features along the way. As long as you know your ORM well and can do the rearcitecting this really is not a problem.

Basically whichever approach you take a good developer would end up with the app in roughly the same state.

Personally I would prefer to start with an ORM and work down, just because it is a hell of lot quicker.

A) I'm not sure why its execeptionally boring. Solving problems with sql is interesting and elegant in it's own way (like using recursion or functional programming to create an new algorithm).

Lots of people posting who don't know so much about the specifics of the database the are using. Why spend thousands on Oracle or SqlServer then abstract all those features out?

Phil

I don't find that to be true. You write the SQL, you've already written your Objects, you write a 2-3 line row -> object(s) mapper (supported by your own utils, or something like Spring). You have clear control and mapping between SQL and Object.

This said, I like what I've seen and used of the Django ORM so far, but then my application has not been anywhere close to as complex as the "day job" RDBMS.

This doesn't give me the same information as the SQL. This, for example, doesn't tell me it's doing an Inner Join on two tables. Being that I know the query you are replicating here, I can assume either it's automatically joining all tables that are related to the table I'm calling. In which case, how do I get a result while only joining specific tables (or none), or the more likely case, it's not doing any join, and waiting until I actually call the object, whereby it performs the query on the table.

Simply put, the SQL query tells me exactly what is being called. The ORM does not, and hides away what it's doing. Granted, this is your example, but it's also easy to find this same style in ORMs in the wild.

I'm on the bench about ORMs myself. I use one, and enjoy it's use. However, it's also annoying because I'm comfortable with SQL and can whip up a query faster than I can wrestle the ORM to do what I want.

I wrote an "ORM" for MongoDB which adds functionality such as transparent relationships.[1] Basically, even though it's not a relational db, it lets you do things like this:

    foreach ($author->books as $book)
    //                ^         ^ this is a Book object
    //                ^ this is an iterator, it loads a
    //                  Book object lazily every iteration.
        echo $book->author->name;
    //              ^ this is an Author object, auto-
    //                matically & lazily loaded & cached.

Among other many cool features. The point of highlighting that though, was to illustrate that giving up traditional RDBMS doesn't mean giving up on awesome relationships. The only thing missing is subqueries—but honestly, I don't think that's a very big loss.

http://dev.mysql.com/doc/refman/5.5/en/where-optimizations.h...

(The columns currently all have to be numeric for this to be true, but that's surprisingly often the case)

Longer answer: Any relational database will read the entire page the row resides on when you select a row.

This means that tables with more columns will have less efficient pages which will need to be read/buffered more frequently per row.

This also means there is an exception: columns that are stored in overflow pages away from the rest of the row like blob or text columns or (in some db's) very long varchars may not necessarily be read if you don't select them.

There is an additional, very important exception: if you use a covering index then the db will not need to read the data page the row resides on. For example, if you have an index on (username, user_id) and you select "select user_id from table where username=xxxxx" then it will be able to read the user_id from the leaf node of the index and no bookmark lookup to the data pages will be needed. In some db's the primary key is always "covered" and you never need a bookmark lookup to get it.

(looking over my database, less than 1% of the 30-40GB size is in overflow tables so unless you routinely store large amounts of data it won't affect you).

(I used to think the "toast" tables were temporary tables and wondered when Postgres would delete them).

Everyone likes to think they're writing the next google, Facebook,etc, but the reality is far more humble and boring.

The whole point of an ORM is to abstract the data from application code. Business Logic can be built on top of it with minimal knowledge of the underlying data storage system except in exceptional cases. ORM frameworks aim to simplify the process of writing these boilerplate code and continue to fulfil most common use cases.

The problem I have with your post is that you are repeatedly mistaking the high level idea of an ORM with the (seemingly) Spartan implementations that you have used.

Here is an ORM that provides support for automatically profiling the performance of queries over a period of time:

http://squeryl.org/performance-profiling.html

Sample output:

http://squeryl.org/profileOfH2Tests.html

I cannot begin to tell you how much time this has saved me when optimising performance of my webapp.

Note that that particular ORM also has the advantage of having type safe queries - i.e. it can tell at compile time if there's a syntax error in your query (subject to bugs in the ORM :)) - even in dynamically generated queries. In practice this is a fantastic feature as it is so much safer than building up SQL queries with string manipulation and dealing with multiple code paths that depend on user input. The test paths alone in such code (even if you have a "query builder" layer) are the stuff of nightmares.

There are many features missing from Squeryl though that I've had in other ORMs because it makes different trade-offs. But this is what you do when you choose a library, and it's important to understand what trade-offs you're making upfront... otherwise you might find yourself writing off an entire approach to software development as an anti-pattern because you picked the wrong library.

Then I have static methods to do the special queries that the post author talks about, which may return direct result sets, an integer, generated objects with fewer columns, or large object trees depending on the case.

ORM would be faster (developer time) for a prototype site, but do-it-yourself DAOs work just fine.

Hibernate does manage relationships - but that is a misfeature. It's doing the wrong thing well. The right thing is not to try to model relations as objects, but to model queries as methods. The relationships exist only in the database - they are not duplicated into the data objects.