Where do ORMs fall through?

Question

I often hear people bashing ORMs for being inflexible and a "leaky abstraction", but you really don't hear why they're problematic. When used properly, what exactly are the faults of ORMs? I'm asking this because I'm working on a PHP orm and I'd like for it to solve problems that a lot of other ORMs fail at, such as lazy loading and the lack of subqueries.

Please be specific with your answers. Show some code or describe a database schema where an ORM struggles. Doesn't matter the language or the ORM.

Reminiscence

Answer 1

One of the bigger issues I have noticed with all the ORMs I have used is updating only a few fields without retrieving the object first.

For example, say I have a Project object mapped in my database with the following fields: Id, name, description, owning_user. Say, through ajax, I want to just update the description field. In most ORMs the only way for me to update the database table while only having an Id and description values is to either retrieve the project object from the database, set the description and then send the object back to the database (thus requiring two database operations just for one simple update) or to update it via stored procedures (which is the method I am currently using).

Jeaz

I agree that this is an issue, and this is my #1 pet peeve with ORMS. I hate that you have to SELECT the object just to update one of its fields, resulting in multiple queries. This is something I am going to address via lazy loading. – Reminiscence

Answer 2

Objects and database records really aren't all that similar. They have typed slots that you can store stuff in, but that's about it. Databases have a completely different notion of identity than programming languages. They can't handle composite objects well, so you have to use additional tables and foreign keys instead. Most have no concept of type inheritance. And the natural way to navigate a network of objects (follow some of the pointers in one object, get another object, and dereference again) is much less efficient when mapped to the database world, because you have to make multiple round trips and retrieve lots of data that you didn't care about.

In other words: the abstraction cannot be made very good in the first place; it isn't the ORM tools that are bad, but the metaphor that they implement. Instead of a perfect isomorphism it is is only a superficial similarity, so the task itself isn't a very good abstraction. (It is still way more useful than having to understand databases intimately, though. The scorn for ORM tools come mostly from DBAs looking down on mere programmers.)

Armor

Answer 3

ORMs also can write code that is not efficient. Since database performance is critical to most systems, they can cause problems that could have been avoided if a human being wrote the code (but which might not have been any better if the human in question didn't understand database performance tuning). This is especially true when the querying gets complex.

I think my biggest problem with them though is that by abstracting away the details, junior programmers are getting less understanding of how to write queries which they need to be able to to handle the edge cases and the places where the ORM writes really bad code. It's really hard to learn the advanced stuff when you never had to understand the basics. An ORM in the hands of someone who understands joins and group by and advanced querying is a good thing. In the hands of someone who doesn't understand boolean algebra and joins and a bunch of other basic SQL concepts, it is a very bad thing resulting in very poor design of database and queries.

Relational databases are not objects and shouldn't be treated as such. Trying to make an eagle into a silk purse is generally not successful. Far better to learn what the eagle is good at and why and let the eagle fly than to have a bad purse and a dead eagle.

Thymic

But wouldn't you say that given the appropriate usage of them, ORMs generate code just fine? In my experience the only time they generate "bad code" is when they're being used incorrectly, like fetching relationships in a loop. – Reminiscence

But that's exactly what I mean, when people who know what they are doing use them, they are good, when people use them as a crutch to avoid learning SQL, they can create a mess. – Thymic

I agree, but couldn't you say that about any tool? Inexperienced programmers can write poor SQL just as easy as inexperienced ORM users can write poor queries using the ORM itself. – Reminiscence

Yes and I did say that above, but what really bothers me is that they never learn basic sql concepts that they will need when it is time to do the advanced, complex stuff the ORMs don't handle very well. – Thymic

Answer 4

The way I see it is like this. To use an ORM, you have to usually stack several php functions, and then connect to a database and essentially still run a MySQL query or something similar.

Why all of the abstraction in between code and database? Why can't we just use what we already know? Typically a web dev knows their backend language, their db language (some sort of SQL), and some sort of frontend languages, such as html, css, js, etc...

In essence, we're trying to add a layer of abstraction that includes many functions (and we all know php functions can be slower than assigning a variable). Yes, this is a micro calculation, but still, it adds up.

Not only do we now have several functions to go through, but we also have to learn the way the ORM works, so there's some time wasted there. I thought the whole idea of separation of code was to keep your code separate at all levels. If you're in the LAMP world, just create your query (you should know MySQL) and use the already existing php functionality for prepared statements. DONE!

LAMP WAY:

• create query (string);
• use mysqli prepared statements and retrieve data into array.

ORM WAY:

• run a function that gets the entity
• which runs a MySQL query
• run another function that adds a conditional
• run another function that adds another conditional
• run another function that joins
• run another function that adds conditionals on the join
• run another function that prepares
• runs another MySQL query
• run another function that fetches the data
• runs another MySQL Query

Does anyone else have a problem with the ORM stack? Why are we becoming such lazy developers? Or so creative that we're harming our code? If it ain't broke don't fix it. In turn, fix your dev team to understand the basics of web dev.

Joby

Your answer is quite specific to PHP or other languages/platforms which have no tool support. In C# I would simply use a statement like var query = from c in db.Customers from o in db.Orders where o.CustomerId = c.ID select new {c.ID, c.Name, o.Quantity}; – Orthodox

Answer 5

ORMs are trying to solve a very complex problem. There are edge cases galore and major design tradeoffs with no clear or obvious solutions. When you optimize an ORM design for situation A, you inherently make it awkward for solving situation B.

There are ORMs that handle lazy loading and subqueries in a "good enough" manner, but it's almost impossible to get from "good enough" to "great".

When designing your ORM, you have to have a pretty good handle on all the possible awkward database designs your ORM will be expected to handle. You have to explicitly make tradeoffs around which situations you are willing to handle awkwardly.

I don't look at ORMs as inflexible or any more leaky than your average complex abstraction. That said, certain ORMs are better than others in those respects.

Good luck reinventing the wheel.

Lester

This is what I'm talking about. You denounce ORMs, yet you give no clear explanation. How exactly are subqueries only implemented "good enough" in some ORMs? "...awkward for solving situation B" - which situation? I understand that ORMs are tradeoffs, but in which situations are you hindered by these tradeoffs? Which types of queries, specifically? – Reminiscence

@ryeguy: I did not denounce ORMs. I don't know PHP and I don't know the ORMs that you know, so I don't know how to give you more specific examples. One well known ORM tradeoff is that lazy loading exposes the SELECT N+1 problem. – Lester

I stated in my original post that it can be orm agnostic, though. I guess I'll make it more clear. – Reminiscence