What is object-relational mapping?

What an ORM is

Object-relational mapping is a technique for converting data between a relational database and the objects used in an application. An object-relational mapper — the library that performs the mapping — lets you work with rows as though they were ordinary objects in your language, and translates the operations you perform on those objects into SQL.

In practice that means writing something closer to this:

user = User.find(42)
user.email = "new@example.com"
user.save()

rather than composing the equivalent statements by hand:

SELECT * FROM users WHERE id = 42;
UPDATE users SET email = 'new@example.com' WHERE id = 42;

The mapper is responsible for generating that SQL, sending it, and turning the result back into objects. It typically also tracks which objects have changed so it knows what to write, and maintains an identity map so the same row loaded twice yields the same object rather than two copies that can disagree.

The impedance mismatch

The reason ORMs exist is that object models and relational models are built on genuinely different ideas. This gap is usually called the object-relational impedance mismatch.

• Identity. Objects have a reference identity; rows have a primary key. Two objects can be equal without being the same object.
• Inheritance. Object models have subclasses. Relational tables do not, so inheritance must be flattened into one table, a table per class, or a table per concrete type — each with different trade-offs.
• Associations. Objects hold direct references and those references have a direction. Relational associations are foreign keys and join tables, and are inherently bidirectional.
• Granularity. A single conceptual object often maps to several tables, and one table often supports several objects.

An ORM does not remove this mismatch. It moves it into a library where it has been thought about carefully, which is usually better than solving it ad hoc in every query you write.

The two main patterns

Almost every mapper is a variation on one of two designs, both catalogued by Martin Fowler in Patterns of Enterprise Application Architecture.

Active Record

The object carries both the data and the logic for persisting it. A User object knows how to save and delete itself. This is fast to learn and concise to write, and it couples your domain model directly to your schema. Rails’ ActiveRecord, Laravel’s Eloquent and Django’s ORM all follow this shape.

Data Mapper

The domain object knows nothing about the database. A separate mapper layer moves data between the two. This keeps the domain model independent of the schema — valuable in complex domains, more ceremony in simple ones. Hibernate, Doctrine, SQLAlchemy’s ORM layer and TypeORM’s data-mapper mode work this way.

What you actually get

• Less boilerplate. The repetitive work of reading rows into objects and writing them back disappears.
• Parameterisation by default. Values are bound rather than concatenated, which removes the most common route to SQL injection. This is a real security benefit, though it is not absolute — raw-SQL escape hatches reintroduce the risk.
• Schema migrations. Most mature ORMs ship a migration tool that versions schema changes alongside your code.
• Portability. Dialect differences are abstracted, so moving between databases is easier — though rarely as free as it first appears.
• Type safety. Modern mappers generate types from the schema, so a renamed column becomes a compile error rather than a runtime one.

The costs

An ORM is an abstraction over something you still need to understand. The usual costs:

• Hidden query cost. An innocuous property access can trigger a database round trip. The code gives no visual indication of expense.
• A second language to learn. You end up knowing both SQL and the mapper’s query API, and how the second becomes the first.
• A ceiling on expressiveness. Window functions, recursive CTEs, and sophisticated aggregation are often clearer written directly in SQL, and sometimes not expressible through the mapper at all.
• Leaky performance. When a query is slow, fixing it means reading the generated SQL and the query plan — so the abstraction stops helping at precisely the point you most need help.

The N+1 problem

This is the failure worth knowing by name, because it is the one that most often reaches production unnoticed.

Load 100 orders, then read each order’s customer in a loop. That is one query for the orders and one hundred more for the customers — 101 queries where two would do. It is invisible in the source, performs acceptably against a development database with twenty rows, and degrades sharply under real volume.

# N+1: one query, then one per order
for order in Order.all():
    print(order.customer.name)

# Fixed: the customers are fetched up front
for order in Order.all().prefetch("customer"):
    print(order.customer.name)

Every mapper provides a way to load associations eagerly — prefetch, include, joinedload, with. The fix is easy; noticing is the hard part. Log your queries in development and watch the count.

When not to use one

An ORM is a default worth departing from. Prefer SQL directly when:

• The work is analytical rather than transactional — reporting and aggregation are what SQL is for.
• You need bulk operations. Loading a million rows into objects to update a field is far slower than a single UPDATE.
• The query is genuinely complex. If expressing it through the mapper is harder to read than the SQL, that is your answer.
• The path is performance-critical and you need exact control over the plan.

These are not mutually exclusive. Most healthy codebases use a mapper for the ordinary majority of their access and drop to SQL where it pays. A mapper that makes that escape hatch awkward is a mapper to be wary of.

Choosing one

Worth checking before you commit:

• Does it show you the SQL? Easy query logging is not a nice-to-have; it is how you will debug.
• How good is the escape hatch? Raw SQL should be straightforward and should still map results into types.
• How does it handle migrations? Including the reverse direction.
• What is the eager-loading story? If avoiding N+1 is awkward, it will not be done.
• Does it generate types from your schema? On typed stacks this catches a whole category of error before it runs.