Object-relational mapping, ORM for short, simplifies access to relational databases. Such frameworks help with developing applications without writing SQL. SQL was supposed to be easy to use for non-programmers. That’s part of the reason why SQL is so verbose. However, writing complex joins by hand is hard. Also, typically, once you fetch data from your database, you immediately translate it to objects. So why not build a universal framework for such mapping? Like, object-relational mapping?

ORM tools assume relations in the database map easily to objects. You know, a table has rows and columns. For example, a customers relation has one row per customer. And one column per each property of the said customer. Like, ID, first name, date of birth, etc. It sounds very reasonable to implement a simple object Customer with the same properties as columns. When you ask for a customer by ID, the ORM framework returns a prepopulated object. That object has fields and accessors for first name, date of birth and so on. The framework generates appropriate SQL for you.

In the same vein, let’s say you modified the Customer object. The ORM framework will issue an UPDATE statement. Some ORMs will even discover changes automatically and update your database behind the scenes. This is sometimes referred to as dirty checking.

But the biggest revolution is how ORM handles relationships between tables. For example, your customers table has a one-to-many relationship to orders. And orders has a one-to-many relationship with items. Which in turn has a many-to-one relationship with products. There are dozens more of such relationships. Writing a correct SQL query to find all products purchased by a customer is tedious. ORMs have a deceptively genius way of handling that. One of the Customer’s object properties is a collection of orders. Order object, on the other hand, has a collection of items. Item object has a reference to a Product object. And so on.

When you query for a Person, the ORM framework will silently query for associated orders, items, etc. Just so you can say person.getOrders(). What looked simple, becomes a multi-join performance nightmare, fetching thousands of records. Alternatively, ORM framework can lazy-load relationships. Which has another downside of possibly making hundreds of tiny SQL queries. Also known as an N+1 problem.

There are many bad practices and patterns that ORM frameworks may introduce. For example in object-oriented programming, inheritance is common. However, there’s no such thing in relational databases. So ORMs try to fake it, sacrificing performance. Or denormalize, duplicating data.

From a larger perspective, ORMs sometimes lead to poor architecture. Objects returned from the framework are used everywhere throughout the application. This, among other things, introduces coupling and leaks the abstraction.

That being said, ORM tools like Hibernate, Active Record or Entity Framework, aren’t bad per se. Quite the opposite! They allow rapid application development. Some even generate the database schema from code for you! However, in the long run, it’s probably easier to learn SQL than to master ORM tools. Or you can consider document databases. Which, of course, have their own set of quirks.

That’s it, thanks for listening, bye!

More materials

• Java
  • Hibernate for Java
  • MyBatis
• Ruby
  • Active record
• .NET
  • Entity framework
• PHP
  • Doctrine ORM