Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question

closed as not constructive by Mark Trapp, bigown Dec 18 '10 at 20:25

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

2  
+1: Very interesting. I've generally been a proponent of ORMs, but I have usually considered RDBMS' equal (which I've recently started to see as wrong). –  Steve Evers Sep 23 '10 at 5:02
    
It sounds like you just want confirmation of your own opinion; a blog might suit you better than a Q&A site. –  user8 Sep 25 '10 at 22:59
    
@Mark Trapp: Other people (16 ATM) think this is a good question. –  JBRWilkinson Dec 17 '10 at 15:27
    
You probably shouldn't need anything more than what the ORM provides. You just want to store your object data in a database and that's what the ORM will do. You shouldn't need any other 'features'. –  CJ7 Dec 14 '11 at 5:56

14 Answers 14

up vote 14 down vote accepted

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.

share|improve this answer
    
drops it directly into the query it's generating Sounds interesting. Do you know how long it took you to write this homegrown ORM? I'd like to see something like this in one of the open source ORMs out there. –  jblue Sep 23 '10 at 3:48
    
+1. Working with the most important aspect of my application (data) is the last thing I want to abstract and lose connection to. –  Fosco Sep 23 '10 at 3:53
1  
I like being able to dive under when necessary, as long as that diving-under involves crossing a metaphorical fence: "Here be dragons. Watch your step." –  Frank Shearar Sep 23 '10 at 12:04
    
@Jblue: No idea. It was all written years ago, long before I got hired. They set up an ORM before anyone was using the term. It's usually just called "the Object Model" in our codebase. –  Mason Wheeler Sep 23 '10 at 12:04
2  
I agree. For the basic and usual stuff ORMs are very helpful. But sometimes you need to go a little deeper, and if the ORM doesn't give that ability, then it's one more source of problems for you. –  Nikos Steiakakis Dec 17 '10 at 13:48

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.

share|improve this answer
    
+1 for advocating NHibernate. Bit of a learning curve compared to other ORM's, but the effort is well worth it. –  richeym Sep 23 '10 at 9:57
1  
I'd like to hear from some DBA's on this. At my last job, Hibernate was nothing but trouble (the SQL it generated was absolutely disgusting.) –  Fosco Sep 23 '10 at 13:50
    
+1 for this comment. It's spot on. When you start to compare the power of a good ORM like nHibernate (with caching, lazy-loading, etc) then you see why this abstraction is good and why your database-specific needs are less important. –  Chris Holmes Sep 23 '10 at 16:00
1  
Fosco, give nHibernate another chance. Like any other technoloy, you have to understand what is going on inside, to use it properly. –  user2567 Sep 23 '10 at 16:07
    
+1, ORM is a maximum intersection of what Java can do with what a particular JDBC driver can do. You're free to choose the amount of investments you want to make in persistence layer of your application –  bobah Dec 17 '10 at 17:26

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.

share|improve this answer

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.

share|improve this answer
2  
Great read there. I have come full-circle in career and have re-embraced the relational model with complete success. –  Xepoch Oct 6 '10 at 5:20
1  
+1 @Xepoch - I've had a recent about face re:ORMs - why is SQL being left out in the cold? Abstraction, sure - but ORM seems to promote SQL phobia. –  sunwukung Mar 10 '11 at 9:50
1  
@sunwukung, I did not always agree, but I do now believe that SQL should be considered a 1st-class logic layer, not just something that is used as a wire protocol. –  Xepoch Mar 10 '11 at 15:46

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.

share|improve this answer
    
I would question the need to use a rational database at all. It bothers me that people immediately jump to RDBMSes even if they aren't the right solution. Object databases, for example, provide a very elegant solution to many problems. Are they perfect? No, but people rarely bother to evaluate them. There is a disturbing trend of, "I need to store data, I'll use SQL!" –  Matt Olenik Sep 23 '10 at 15:16
5  
@Matt: RDBMSes are very tried and proven and well-understood technology. There's tons of people with experience with them, and large numbers of third-party tools. From an enterprise point of view, there's a lot of value in that when you're dealing with something exceedingly valuable like your data. On smaller projects, there's still value in being able to start up a project (like a LAMP web service) and have most of the software right there and well documented. –  David Thornley Sep 23 '10 at 17:01

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.

share|improve this answer
2  
"Unstored Procedures" - the proper term is "Parameterized SQL". You're only scratching the surface of what a mature ORM can do for you (batching, lazy-loading etc) –  richeym Sep 26 '10 at 11:21
    
@richeym: Most ORMs in PHP don't use prepared statements, nor parameterized placeholders. It's SQL escaping and concatenation all the way. Sure they provide higher level advantages over tedious manual SQL queries. Sementically however they take processing logic out of the database, hence "unstored procedures". –  mario Sep 26 '10 at 14:26

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.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer
2  
Again, why do you need to program away from the database? Why not make the database an integral part of your development as you've chosen to use one in the first place. If you didn't need a RDBMS why shove an ORM atop it? –  Xepoch Oct 6 '10 at 4:47
1  
It is an integral part. A database does very well at maintaining and enforcing relations and retrieving raw data. However, the things you want to do with this data have most likely already been handled in the ORM wrapper. And besides critical things, why take the logic out of the business layer? –  burnt_hand Oct 6 '10 at 9:44
    
@burnt_hand - "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." What are the universal advantages? I can see it as an advantage when your app is simply looking for a way to persist objects. But for relational data, OLTP and the like, programming away from the database is one way to screw yourself up big time. –  luis.espinal Oct 13 '10 at 16:58
    
@luis.espinal - what do mean screw your self up big time? i'm genuinely curious. Do you have an example? –  burnt_hand Oct 14 '10 at 9:02
    
@burnt_hand - actually I do have several real examples. Most recent one involves a very large domain model and as a matter of performance, the project must upload all hibernate mappings on start up. The resulting session factory ends up eating up to 30% of the used memory... just for the bindings. The code base is too committed now with the ORM, and it is impossible to rewrite it out. This now have actual implications since the application is approaching the physical limits on the hardware that it was supposed to run. Bummer. –  luis.espinal Oct 14 '10 at 11:45

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
1  
There are lots of reason for using, or NOT using a ORM. ORMs are not a panacea and very few people actually know how to use them effectively. Furthermore, in many cases, the business logic is already reflected (in part) in very natural manner in the relations already existing in a RDBMS (this has been the most common scenario I've encountered, past and present). A well-designed RDBM has a strong, well-understood, tried-and-true mathematical underpinning. Not everything should be modeled with a RDMB of course, but equally so, a ORM is not a universal solution either. –  luis.espinal Oct 13 '10 at 17:02

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.

share|improve this answer

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

share|improve this answer
    
That's not really an answer to jblue's question about the benefits of using such a mapper –  Lukas Eder Dec 12 '10 at 10:03

Not the answer you're looking for? Browse other questions tagged or ask your own question.