ORM (Object-Relational Mapping)

ORM (Object-Relational Mapping) is a foundational concept in system design that bridges the gap between object-oriented programming (OOP) and relational databases.

The Core Problem

Applications are typically written in objects (classes, instances), while databases store data in table (rows & columns).

This mismatch is called the Object-Relational Impedance Mismatch.

What ORM Actually Does

An ORM:

1. Maps classes <-> tables
2. Maps objects <-> to rows
3. Maps attributes <-> columns
4. Handles relationships (1-1, 1-N, N-N)
5. Generates SQL queries automatically

How ORM Works Internally

Metadata Mapping

ORM maintains a mapping definition:

User -> users table
Post -> posts table
User.id -> users.id

This can be defined via:

• Annotations (Java, Python)
• XML configs (older systems)
• Code-first / schema-first approaches

Query Translation

When we write:

users = User.objects.filter(name="TheJat")

ORM converts it to:

SELECT * FROM users WHERE name = 'TheJat';

Object Hydration

After DB returns rows:

id | name
1  | TheJat

ORM converts them into objects:

User(id=1, name=TheJat)

This is called hydration.

Change Tracking (Unit of Work Pattern)

ORM tracks changes:

User.name = "MaK"

Instead of updating immediately, it:

• Marks object as “dirty”

• Executes SQL later:

  UPDATE users SET name='MaK' WHERE id=1;

Advantages of ORM

Developer Productivity

• No need to write raw SQL
• Faster development

Abstraction

• DB-agnostic (PostgreSQL, MySQL, etc.)

Security

• Prevents SQL injection (parameterized queries)

Maintainability

• Centralized data logic

Disadvantages

Performance Overhead

ORM adds:

• Query generation cost
• Object creation cost

N+1 Query Problem

Example:

users = User.objects.all()
for user in users:
    print(user.posts)

This results in:

1 query for users
+ N queries for posts

Total = N + 1 queries -> kills performance at scale

Lack of Control

• Hard to optimize complex queries
• Generated SQL may be inefficient

Leaky Abstraction

You still need SQL knowledge:

• Indexing
• Joins
• Query plans