ORM

An ORM (object-relational mapper) lets your code work with database data as ordinary objects, and writes the SQL for you. It saves you hand-writing the same queries over and over. The SQL still runs, though, and knowing what it looks like is how you avoid the slow cases.

Two worlds that don't match

A relational database stores data in tables: rows and columns, linked by keys, and you talk to it in SQL. Your application code thinks in objects: a User with a name and a list of orders. Getting from one to the other by hand means, for every feature:

1. Writing a SQL string.
2. Sending it to the database with the right values.
3. Turning each row that comes back into an object, column by column.

Do that for every read and write in an app and you end up with hundreds of near-identical queries. Joins get copied and tweaked, a renamed column breaks queries you forgot about, and a typo in a SQL string only shows up when that line runs. The gap between the two models has a name: the object-relational impedance mismatch.

An ORM closes most of that gap. You describe your data once, as classes, and the ORM handles the SQL and the conversion in both directions.

How the mapping works

Every ORM works from the same three rules:

Relationships come along too. A foreign key from orders.user_id to users.id becomes a property on each object, so user.orders gives you that user's orders as a list, and order.user gives you the user back.

When you ask for data, the ORM goes through the same steps every time. Here is one request for every user, from your code to the database and back:

A worked example

Every popular language has ORMs: SQLAlchemy and the Django ORM for Python, Entity Framework Core for .NET, Hibernate for Java, Prisma and TypeORM for TypeScript, and Active Record in Ruby on Rails. They look different but do the same job. Here is a small model in Python with SQLAlchemy:

from sqlalchemy import ForeignKey, String
from sqlalchemy.orm import (
    DeclarativeBase, Mapped, mapped_column,
    relationship,
)
 
class Base(DeclarativeBase):
    pass
 
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    orders: Mapped[list["Order"]] = relationship(
        back_populates="user"
    )
 
class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    item: Mapped[str] = mapped_column(String(50))
    user_id: Mapped[int] = mapped_column(
        ForeignKey("users.id")
    )
    user: Mapped["User"] = relationship(
        back_populates="orders"
    )

User maps to the users table, each Mapped attribute to a column, and orders to the rows in orders whose user_id points at this user. Now two everyday tasks, getting all users and creating a new order:

from sqlalchemy import select
from sqlalchemy.orm import Session
 
with Session(engine) as session:
    users = session.scalars(select(User)).all()
 
    order = Order(item="tuna", user=users[0])
    session.add(order)
    session.commit()

engine is the database connection, made once with create_engine. Behind the scenes, the ORM sends SQL along these lines:

SELECT users.id, users.name FROM users;
 
INSERT INTO orders (item, user_id) VALUES (?, ?);

The ? marks are parameters (the exact placeholder style depends on the database driver). The ORM sends the values, 'tuna' and the user's id, separately from the SQL text, and that separation is what protects you from SQL injection, covered below.

You never wrote a column name into a string and never built an object from a row by hand. If you rename name to display_name, you change it in one place (plus a database migration), and your editor can find every use.

What else an ORM does for you

Most ORMs do more than translate:

• Values go in as parameters by default. Because they travel separately from the SQL, user input can't change what a query does. That shuts out most SQL injection, which is easy to let in when you build SQL by gluing strings together.
• The session tracks changes. It remembers which objects you added or changed, and writes them all in one transaction when you commit.
• They usually come with, or pair with, a migration tool (Alembic for SQLAlchemy, EF Core's own migrations) that turns changes to your classes into versioned scripts that update the schema.
• Some of your code becomes portable: the same models can run against SQLite in tests and PostgreSQL in production, as long as you avoid features only one database has.

What it costs

Because the SQL is out of sight, its costs are easy to miss.

The N+1 query problem

The best-known ORM performance trap starts with a loop that looks harmless:

users = session.scalars(select(User)).all()
for user in users:
    print(user.name, len(user.orders))

By default SQLAlchemy loads a relationship lazily, the first time you touch it. So this code sends one query for the users, then one more query per user for their orders. With 1,000 users, that is 1,001 round trips to show one page. The fix is to ask for the related rows up front:

from sqlalchemy.orm import selectinload
 
stmt = select(User).options(
    selectinload(User.orders)
)
users = session.scalars(stmt).all()

Now it is two queries in total, however many users there are. Other ORMs have the same tool under another name, such as Include in Entity Framework Core and prefetch_related in Django.

Other trade-offs

• One innocent-looking property can be a query, so slow queries hide behind tidy code. Turn on SQL logging while developing (echo=True on a SQLAlchemy engine) so you can see what runs.
• Complex queries fight the abstraction. Reports with several joins, window functions or heavy aggregation are often clearer, and faster, in plain SQL. Mainstream ORMs let you drop down to raw SQL for those.
• Bulk work is slow object by object. Loading 100,000 rows as objects to change one column costs memory and time, where a single UPDATE statement does it in one go.
• You still need SQL. The ORM writes the queries, but when one is slow you will be reading the SQL it generated and the database's query plan.

When to use one

An ORM suits apps whose work is mostly create, read, update and delete: web apps, APIs and admin tools, where queries are simple and the same shapes repeat. It also suits teams who want the schema defined in one place, next to the code.

Reach for something lighter when most of the work is reporting or analytics, when you need tight control over every query, or when the data doesn't fit objects well. In between sit query builders, such as SQLAlchemy Core and Knex, which let you build SQL in code without mapping objects, and micro-ORMs such as Dapper for .NET, which map results onto objects while you write the SQL yourself.

Many projects mix them: the ORM for everyday reads and writes, hand-written SQL for the few queries where it matters. If tables and keys are new to you, database normalisation covers how a well-designed schema is laid out, and the SQL quiz checks the SQL basics an ORM builds on.

Key takeaways

• An ORM (object-relational mapper) translates between your code's objects and a relational database, and writes the SQL for you.
• Classes map to tables, objects to rows and properties to columns; foreign keys become references between objects.
• It cuts repetitive SQL and sends values as parameters, which blocks most SQL injection.
• It can hide expensive queries: watch for N+1 and load related data up front.
• Use it for everyday reads and writes, and plain SQL where a query needs full control.