I was raised in a part of web developmentland where the creation of websites is mostly synonymous with Wordpress, FTP, Joomla, and procedural code, instead of things like TDD, A/B testing, Doctrine, and design patterns. Having not traveled this area much, I'd like to know when object relational mapping and ORM tools are necessary to create and manage a site. Can you provide some generic (but real world examples) of when it should be used?

  • 6
    There are very few "necessary's" and "should's" in software development. – GrandmasterB Sep 16 '11 at 21:26
  • In addition to the other answers a major reason for ORM is spelling. When you get more programmers and more columns/tables we found basic typing mistakes in SQL statements where causing bugs. A nice ORM tool pulls the names from the syscatalog tables and enables the compiler to enforce the names and types. – jqa Sep 17 '11 at 2:52

Necessary? Never.

ORMs are an layer of abstraction over the database. They are intended as translators between object graphs and database tables/relationships. In this capacity they simplify simple data access and with a large object model/database can save a lot of time otherwise spent writing boilerplate code for data access (getting a record by ID, deleting it by ID, updating and inserting a record).

Useful? Almost always, for the points made above.

There are no rules, as such, but ORMs are best used for basic DB operations (Insert, Update, Delete, Select). When you need something more complex, it is best to use the standard API for your platform.

| improve this answer | |
  • I'd like a concrete example of when it is very useful. Thanks – JustChris Sep 16 '11 at 21:20
  • @Just Chris - They are very useful when you have a system of 100s of tables. The ORM is really a time saving tool. For a system I worked on, I built a mini ORM to generate C# code classes for me so I would spent weeks writing mundane SQL and mapping code. There only some much joy in writing the same procedure or SQL over and over when all that is changing is column names. – Jon Raynor Sep 17 '11 at 0:15
  • Thanks for the clarification. All the developers in my company write a lot of boilerplate code and don't know what ORM is. Our philosophy is just "no time for research, just get 'er done" ;) – JustChris Sep 19 '11 at 17:39
  • @JustChris - Yeah, just spend 3 weeks writing code that, even if you spend a day learning and using an ORM would be done for you. – Oded Sep 19 '11 at 17:40
  • @Oded I don't agree with the philosophy, I wish we can turn that around. We just gotta sharpen our convincing skills for our boss that a little bit of research goes a long way. You know how some development tools are black-boxed? Well my boss likes to black-box the entire dev team. He doesn't care how our development process should work. – JustChris Sep 19 '11 at 17:44

ORM tools should be used for CRUD, that 80% of code that is so tedious to write over and over, that you want to bang your head against the wall.

Feel free to use more "old school" methods like Stored Procedures for the remaining 20% (e.g. operations that require performance optimization).

For a simple example of how ORMs can be effectively used to create an organized and effective Repository class for retrieving and updating database data CRUD style, see here:


| improve this answer | |
  • Heh, at most places I've been at there are no stored procedures due to lack of specialization in the IT dept. So you probably mean any trivial queries that occur frequently. – JustChris Sep 16 '11 at 21:24
  • @Just: It doesn't have to be stored procedures, it can just be hand-rolled SQL. The SQL that ORM's produce isn't always the most efficient way. – Robert Harvey Sep 16 '11 at 21:26

ORM's serve two worthwhile goals;

Abstracting some of the database specific dialect details; and otherwise insulating developers from the weirdness that SQL can sometimes be.

Most ORM's are an 80-20 sort of tool, where they can significantly simplify about 80% of the work of persistence, compared to using SQL directly. For simple applications, 80% is really 100%, since the platform can often direct the design decisions.

Larger, more complex applications will always have problems that could not have been anticipated by any tool. The difference between good and bad ORM's shows in sharp relief when this happens.

A good ORM will get out of the way of developers when they need to issue custom SQL to get at their data, or even provide simple extension or introspection mechanisms to grow into the needed use. Poor ones just get in the way of this sort of thing; requiring developers to do these custom jobs at a lower layer.

Attaching business intelligence or other convenient behaviors to the persistent entities.

Getting the most out of the class mapping features of ORM's often requires a deep understanding on the part of the ORM's developers what role application objects play. The model objects fulfill a very different role from the persistent state they wrap.

Specifically, the rows and columns in a database represent the set of facts believed true, but the methods and inheritance graphs of the models are the rules and formulae that the application operates on.

When an ORM conflates the two ideas, they'll obstruct developers in a number of idiomatic ways. There will be no way to operate on relations that aren't part of a table (obvious counterexamples are the rows returned by a select containing joins or group by clauses). Another breakdown is that they assume that every table must have a primary key; or that the primary key must be an integer or a single column. A more subtle way ORM's can mess this up is by providing object features, like many-to-many properties, that somehow require a database connection to work properly (the entity relationship is not related to persistence).

| improve this answer | |

If your application is using a relational database, your code will contain a lot of SQL (or LINQ if you are using .NET).

The ideas is to simplify interacton between application code and the database by creating Data Access Layer and business objects quickly and without coding, yet allows the developer to extend the logic by adding special business rules (if any).

Object Relational Mapping tools are many. Not all tools are the same. Some are simple whereas some are complex enough to be an entire framework.

They rely on database already built (tables) and read the table definitions from your database then:

0-Creates classes that represent objects (usually 1 to 1), such classes contain methods to read, write, update and delete data from the database. Some support LINQ (.Net) and some have their own object query language.

1-Handle primary key generation and table to table relationships automatically

2-Can read and return data related to a table automatically with one method call

3-Allow you to switch the database without coding

4-Take care of data buffering to enhance performance

5-Generate necessary SQL to get and put data to database automatically

6-Some generate web services automatically (e.g. Oxygen)

7-Some can generate auditing code for you automatically

8-Some can create stored procedures

9-Some provide authorization support

10-Some support multiple tier architecture automatically

11-Some support .NET Entity Framework

So if you need any of the above services in your project, ORM may be for you.

It may be something for you to decide whether you want to use ORM or Entity Framework if you are a .NET user.

| improve this answer | |

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