What are the benefits of using database abstraction by ORM?

Question

I'm starting to use the ORM recommended by the framework I choose, and though I like the idea of the added layer of abstraction the ORM provides, I'm starting to realize what this really means. It means I'm no longer working with my database (mysql) and any mysql-specific features are gone, out the window, like they don't exist.

The idea the ORM has is that it's trying to help me by making everything database agnostic. This sounds great, but often there is a reason why I choose a specific database system. But by going the database agnostic route, the ORM takes the lowest common denominator, which means I end up with the smallest set of features (the ones supported by all databases).

What if I know that for the long term I won't be switching the underlying database? Why not access the database-specific features, too?

Answer 1

I see it the same way. Any abstraction that doesn't allow you to get underneath it when necessary is evil, because it leads to all sorts of ugly abstraction inversions in your code.

At work we've got a homegrown ORM that works fairly well, but for the times when we need something that its features don't explicitly provide for, there's a method that takes a string and drops it directly into the query it's generating, allowing for the use of raw SQL when it's necessary.

IMO any ORM that doesn't have this feature isn't worth the bits it's compiled from.

Answer 2

the ORM takes the lowest common denominator

I must disagree with your statement. I will take nHibernate as an example. This framework supports lots of database, including the most popular ones, regardless the version (and supported features), just like most ORMs.

Quick note: you only mention database abstraction. That's one of the many benefits, but I really think that object oriented features are more powerful (eg: inheritance). And You design your domain model first...

...Back to the abstraction. It's not the lowest common denominator. In nHibernate, you have dialects. It allows you to use a same code to query different databases. Dialects take care of feature management for you. The correct statement is that a given dialect will try to use all the power of a given database system.

As an exemple take the SqlServer2005 dialect that when introduced was taking advantage of the new SQL Server 2005 paging features. Using that dialect instead of SqlServer2000 just boosted your performances.

Of course there are exceptions, but I didn't encounter a single one in many years of working with nHibernate, and my applications are very data centric.

Answer 3

The idea is neat, but I've never been moved to the point to use an ORM tool. Its just not been a problem for me to write the SQL myself (or handle whatever storage is used). But, I do have the luxury of working on smaller numbers of projects with longer product lives, so once the hand coded SQL and DB manipulation is in place, its in place. Plus, you obviously then can handle any direct SQL queries you need without having to fit your process into the ORM tool's way of thinking.

So, I guess the questions should be before you jump into one:

Are you actually gaining anything from using them? That is, are you saving a sufficient amount of effort by implementing an ORM tool to make it worth using, and to make it worth adding an extra complication to your system? (this is particularly true of commercial apps, where that extra 'piece' is now an extra vector for potential support problems)

And then, will the extra abstraction layer have a negative impact on the app? Is it going to be slower than hand coded SQL, etc.

Answer 4

O/R-M has been criticized regularly. Ted Neward called it the "Vietnam of Computer Science" a few years ago. O/R-M

starts well, gets more complicated as time passes, and before long entraps its users in a commitment that has no clear demarcation point, no clear win conditions, and no clear exit strategy.

Unless you just write you own ad-hoc mapping (which is a good solution in many cases, as other have already said), you could look at alternatives such as using non-relational database. Some say that a truly object database is better, other buzzwords mentioned in this context are LINQ, Ruby, and Tutorial D. I suppose, in contrast to truly relational databases, there are truly object databases. But in practice I found that conceptual data modeling - that is to find out, what real world objects you want to store data about, and how these objects relate to each other - is more important that fiddling out how to express you conceptual model in any programming or database language.

Answer 5

One thing that hurts using ORMs is that many of their fans tend to claim we no longer need to know SQL and RDB theory. The results vary from funny to utterly catastrophic. And this is despite the million times that ORM' manufactures and pundits state that we must know SQL and RDB theory well to properly use and configure a ORM.

Why people take a tool that has its uses on many, but not all contexts into a magical, universally applicable silver bullet is beyond me.

Answer 6

What's the alternative?

This is an interesting notion. By abstracting away the features of the underlying database we definitely lose some of the features of the specific database implementation. (Whether or not we should depend on specific database features is another argument) I guess this could be solved by database-specific ORMs which allow you to access the specific features, but that might just be more trouble than it's worth and a step in the wrong direction.

At the end of the day, you have to ask yourself - what's the alternative?

Should we stop using ORMs and go back to writing all database access ourselves? Sure we might lose access to a few database features through the ORM, but you can always access those using old-school techniques. In the end the gains you get in productivity completely outweigh the disadvantages.

Answer 7

An ORM basically amounts to "Unstored Procedures". There, I coined a term.

Everything an ORM does, you could replicate with Views, Triggers and Stored Procedures. They help abstracting raw SQL away and can keep normalized databases consistent. But it only works well for simple cases. If there is too mach data to process they can become a performance drain. (ORMs in PHP often take the data script-side, because the SQL builder chains cannot abstract everything.)

But as usual, it all depends on the application and task at hand.

Answer 8

My (simple) two cents:

1: There are ORMS and there are ORMS. Some ORMS are based on Active Record, others based on Data Mapper - that in itself is a relevant consideration, but one that requires some investigation to understand the implications. In general - my experience in PHP is that ORMS support the former, few support the latter. Active Record based ORMs seem to have one of two effects - they either de-normalise the database, or invoke a plethora of classes to support object interactions.

2: The advantage of ORMs diminish in direct correlation to the complexity of the query you need to run. When you have a nice simple relationship - i.e. Users -> Posts, they can work very well. This (IMO) is why most ORMs/frameworks use examples like this. When you need to run complex queries, however, the amount of ORMQL you need to generate is comparable to the string length of a regular SQL query - but less performant owing to the object graph running the query invokes, which kind of defeats the point of abstraction. So, in a nutshell - ORM's are brilliant for the first 30-50% of your database tasks - but terrible for the last. I think this is another reason why AR is so prevalent.

3: Why hide from the database? Would you use a server side language to abstract javascript?

Personally I mix those approaches:

• use ORM for the basics, loading "users"
• use a DAO containing several pre-baked SQL queries (i.e. selectLike, selectWhere).
• extend the DAO where necessary to add more specific but re-usable custom queries
• Provide simple database access through the DAO - i.e. $data = Dao->query(your sql string) to execute one off queries.

Having said all that, Doctrine 2 is coming out soon, which looks really interesting.

Answer 9

Last year I worked on an in-house app that changed frequently, and I was very happy we used an ORM. The app was a supporting app for a change management team, and as the larger project evolved, our little app got new requirements for situations that did not exist and could not be foreseen a few months before.

Thanks to the ORM (Propel, in PHP), we split up some of the logic for the code, so one function added the "is record valid" part of the query, another the security part ("show these records only if the user has these capabilities"), ... If the underlying structure changed (an extra field that should be taken into consideration for "record validness"), we only had to change one function, not twenty SQL clauses.

We came from a situation where all (well, most) SQL clauses were stored in a separate XML file, so "database changes would be easy to handle". Believe me, they were not. One part was so horrible that I had to submit it to The Daily WTF.

If a query was too complicated to express with Propel Criteria, or we needed some vendor-specific features (mostly full-text search, since we were using MySQL), this was no problem with Propel. You can add custom criteria, or when really needed we used raw SQL (now even more easy to combine with actual Propel objects). You can easily add your vendor-specific add-ons to the generated classes, so you have the best of both worlds: no SQL in your app code, but with all the capabilities of your database engine.

Answer 10

But the point is you get to program away from the database, meaning you don't need to think like you are in the database.

I work in C# now and use LINQ a lot, so lots and lots of fluent methods. I don't need to think about how my objects are stored or found, or even saved at the program level, and very little at the business layer level.

Quite often the methods provided by the Specific Databases themselves are used in the DB Connector, so you get those optimizations without needing to know what they are.

You can still write functions DB-side. Often you can just map them through.

And above all, separation like that allows testability and forces you (a little) to write better structured code

Answer 11

ORMs can give you access to database specific features as well, it depends on which ORM you're using and what features it provides.

For an example, in the current project I'm working on, we're using the Java Persistence API and Hibernate. Even so, we use several database specific features, such as searching in tables with soundex.

For me, the main benefit of using an ORM is not necessarily that it makes an application database agnostic (although that is a good thing as well), but that I for the most part don't have to think about how things are stored and retrieved.

However, at some point you most likely have to think about this anyhow, depending on the requirements for your application. The ORM is not magic.

Answer 12

I wrote my own which helps me do selects and inserts easier.

Evey db specific thing is available. I just need to write the query myself which the library assist me with (i can use '?' and params instead of manually name a parameter and use .Add)

I guess mine half sucks half useful. I get some help in the ORM world and do significant parts in the query world.

Answer 13

Writing code to insert and select, update inline or with stored procs and mapping them back through the DAL is longer the norm rather only beneficial in the exceptional case. The available ORM's, the supporting databases and languages the question you should be asking why aren't you using ORM?

They're standardised, universial, either specified or generic. furthermore its faster and easier to implement. I've used subsonic, linq-to-sql and the entity framework. It allows the developer to focus on the logic and the business rules instead database mapping.

Answer 14

You can use SQL mapper frameworks like myBatis if you want to utilize sql features.