I'll start by saying that I've done 95% of my database work using SQL. Recently, I did some investigation of various ORMs, such as NHibernate and Doctrine.

I can see the advantages of not needing to know much SQL and the database portability that an ORM provides. But I can also see that knowing SQL will make working with an ORM more effective and I can only think of one time in my career that an application's biggest change would be the database vendor.

Because I'm very comfortable writing SQL and am apparently not realizing the often taught benefits of using an ORM, my question to heavy ORM users is this:

What kind of Web development projects benefit the most from using ORM?

The answer is "All of them." But that's probably not what you want to know. You might want to refine your question to ask some more specific information.
–
S.LottApr 18 '11 at 20:03

1

For me, SQL is currently easier to produce but that is only because of my acclimation (or ignorance). I've read that ORM is not always the best choice because it is slower than normal SQL; however, many developers have stated that this is usually not important. I'm mostly curious about projects that are best suited for ORM. I can see most answers will probably be "all of them", and that's fine too. I'm not looking for a specific answer. :)
–
Fred WilsonApr 18 '11 at 20:31

If you don't ask for more details, you're not going to learn much.
–
S.LottApr 18 '11 at 20:37

6 Answers
6

Using ORM doesn't necessarily mean you don't need to know SQL. A knowledge of SQL will help understand what the ORM tool is actually doing, which is especially useful during debugging. Moreover, SQL may actually be required to develop complex queries that are beyond the capability of your chosen ORM.

And as you say, portability is rarely a concern in real life.

Instead, the real benefits of ORM are:

ORM saves programmer time because it saves writing tons of CRUD logic in SQL

many ORMs include complex caching logic etc. that is difficult to write and debug. As well as saving time this can enhance reliability and maintainability of your application (or at least save you the time it would take you to achieve the same results)

the best ORMs have a community of users who actively develop, maintain and support the product. The community around custom SQL is, at best, somewhat less focused on the problems we need to solve.

As you comment, one down side of ORM is a loss of performance. However, this can usually be offset by spending more hardware.

Typically, programmer time is more expensive that hardware, so ORM is genrally a good option instead of hand-coding SQL.

ORM is best for applciations with a lot of fairly simple CRUD database logic. ORM is less effective for:

Applications that need little / no database access.

Applications that are largely dependent on complex queries and very little simple CRUD logic

Situations where performance is critical, but where there is no possibility of deploying faster hardware

"Using ORM doesn't necessarily mean you don't need to know SQL." - So true. One of the advantages I see in a team environment is that we can concentrate the SQL skillset so that not every developer working on the business layer has to be an expert.
–
Fred WilsonApr 18 '11 at 22:03

1

For complex queries, you can always revert back to SQL and write a SQL query.
–
harsimranbJan 6 at 0:37

I am comfortable writing SQL as well. I am also a lot more comfortable not having to write any SQL at all, as well as not worrying about connecting, disconnecting, pooling, etc to a database.

So.. I'll answer the negative of your question. The only web development projects that do NOT benefit from an ORM are the one not talking to a database at all. Which I believe are the minority (if any).

+1: I also consider myself to be very proficient at sql, but still prefer an OR/M. In fact I would say you definitely need to have a handle on sql to make good use of an OR/M, otherwise when the abstraction leaks, you'll never find the problem. "not needing to know much SQL" is not a feature. To me it's all about productivity gains and having the compiler find the problems, not the runtime, and OR/M's (especially newer generics/linq based ones are great at that).
–
BrookApr 18 '11 at 20:38

@Brook - I wholeheartedly agree. Using an OR/M does not mean that SQL knowledge is optional.
–
Otávio DécioApr 18 '11 at 20:50

Based on my experience with ASP.NET WebForms I would propose that web projects using stateful web frameworks do benefit the most from using an ORM.

With a stateful framework the markup is generated automatically behind the scenes, based on the hierarchy of active server controls. It's tempting to make those controls load and persist their state automatically to the database. This is where ORM helps.

You sort of abstract away the end of the pipeline (HTML output) and it's naturally inviting to deal with the beginning (data source) in the same manner so that you only stay within your business logic in the application code.

Not that I'm not saying this is a good way to do things. It's just where ORM fits naturally.

The concept which you are referring to is a Database Abstraction Layer (DBAL) which allows you to write portable "sql" which is not dependent on the underlying database system.

An ORM on the otherhand (which is (almost?) always built on top of a DBAL):

Object-relational mapping (ORM, O/RM, and O/R mapping) in computer software is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language. (Wikipedia)

Simply put, a ORM allows you to convert data from a flat database into an inflated object representation.

Thank you for the clarification. The main reason for me to move to ORM is to focus more on OOP and move away from writing as much SQL if it makes sense for some projects.
–
Fred WilsonApr 18 '11 at 21:03