Cloud Parts

Object-relational mapping

A library that turns rows in relational tables into objects in your program and back, so ordinary code can read and save data without hand-writing every SQL statement.

9 technologies on this page ↓

How it works

Programs hold data as objects that point at each other; a relational database holds it as rows in tables joined by keys. Moving data between the two by hand means writing the same SELECT, the same loop that copies columns into fields, and the same INSERT or UPDATE for every class. An object-relational mapper (ORM) does that work from a mapping: each class maps to a table, each field to a column, each reference to a foreign key. A full ORM adds several patterns that Martin Fowler named in 2002. An identity map keeps one object per row in a session, so loading order 42 twice returns the same object. Change tracking remembers what each loaded object looked like, and a unit of work turns every change made during the session into the right INSERT, UPDATE and DELETE statements when you save or commit. Lazy loading leaves related objects as placeholders and fetches them the first time code touches them. Most full ORMs also come with migrations: scripts that change the database schema to match the classes. Two styles exist. In Active Record each object loads and saves itself (book.save()); in Data Mapper plain objects know nothing about the database and a separate mapper or session moves them. The best-known trap is the N+1 query problem: load N rows with one query, touch a lazy relation on each, and the ORM runs N more queries. The fix is to say up front what to load, with a join or a second batched query. Every ORM also lets you drop down to plain SQL for reports, bulk updates and queries the mapping can’t express.

Think of it as

A translator who sits between you and a records office that only accepts filled-in forms. You say “change Ada’s address”; the translator remembers which form she is on, fills in only the changed box, and files all your changes in one batch at the end of the day. It saves a lot of paperwork, but if you ask about 50 people one at a time, the translator walks to the office 50 times unless you hand over the whole list at once.

Reach for it when

  • Business applications with many tables and lots of create, read, update and delete screens, where hand-written mapping code would be repetitive
  • A domain model with real behaviour, where changes to several related objects should be saved together in one transaction
  • Teams that want schema changes versioned as migrations next to the code that depends on them
  • Code that must run on more than one database, where the ORM generates each dialect’s SQL

Think twice when

  • Reporting, analytics and bulk updates: set-based SQL over millions of rows beats loading objects one by one
  • Read-mostly services that just return query results: a query builder or micro-ORM is simpler and faster
  • The team doesn’t know SQL: the ORM hides queries until production traffic shows the N+1 loops and missing indexes
  • Data that isn’t relational in shape, such as documents or graphs, where a matching database avoids the mapping altogether

A real example

A bookshop page lists 10 books with each author’s name. With the author relation lazy-loaded, the ORM runs 1 query for the books and then 1 per book for its author: 11 queries. Asking for the authors up front (includes(:author) in Rails, selectinload in SQLAlchemy, Include in EF Core) makes it 2 queries: one for the books, one SELECT ... FROM authors WHERE id IN (1, 2, ..., 10). On a list of 500 books the lazy version sends 501 queries and the fixed one still sends 2.

Kinds of object-relational mapping

Specific products, and what each does differently.

Full ORMs →

Map classes to tables and manage the objects for you: one object per row in a session, change tracking, saving a whole unit of work at once, lazy loading and migrations.

Active Record (Ruby on Rails)Open sourceThe model layer of Ruby on Rails and the best-known implementation of Fowler’s Active Record pattern: each Ruby class wraps a table, each object a row, and objects load, validate and save themselves.Entity Framework CoreOpen sourceMicrosoft’s object-relational mapper for .NET: you query C# classes with LINQ, a `DbContext` tracks the entities it loads, and `SaveChanges` writes every change back as SQL through a pluggable database provider.Hibernate ORMOpen sourceThe standard object-relational mapper for Java: it maps annotated classes to tables, tracks changes to loaded objects in a session and writes them back as SQL, and implements Jakarta Persistence (JPA).Prisma ORMOpen sourceA TypeScript ORM driven by a schema file: you declare models once, Prisma generates a fully typed client and SQL migrations from them, and queries return plain JavaScript objects.SQLAlchemyOpen sourceA Python database toolkit in two layers: a SQL expression layer (Core) that builds and runs SQL, and an ORM on top of it that maps classes to tables with an identity map and a unit of work.Tortoise ORMOpen sourceAn async-native Python ORM with a Django-style API: models as classes, `await Model.filter(...)` queries, and built-in migrations since 1.0.

Active Record or Data Mapper#

Fowler’s Patterns of Enterprise Application Architecture describes both. An Active Record is “an object that wraps a row in a database table or view, encapsulates the database access, and adds domain logic on that data”: the class is the table, and book.save() writes it. Ruby on Rails is built on this pattern and adds conventions on top, so a class named Book maps to a table named books with no configuration. It is quick to write and easy to read, and it fits applications whose objects look like their tables.

A Data Mapper is “a layer of mappers that moves data between objects and a database while keeping them independent of each other”. The objects are plain classes with no SQL and no knowledge of the schema; a session or context loads them, tracks their changes and writes them back. Hibernate, Entity Framework Core and the SQLAlchemy ORM work this way, and Prisma describes itself as a Data Mapper too, though it returns plain objects without a session. The extra layer pays off when the object model and the schema differ, for example with inheritance or value objects, and it lets you test domain code without a database.

What each kind does for you#

The line between the kinds is how much state the library keeps between your code and the database.

Full ORMQuery builderMicro-ORMPlain SQL driver
You writeClasses and a mapping; queries in an object query language or method chainsQueries in a typed API that mirrors SQLSQL stringsSQL strings
Maps rows to objectsYes, including related objectsYes, to typed rows or recordsYes, by matching column names to propertiesNo: you copy each column yourself
Identity map and change trackingYes: changes are saved by a unit of workNo: every write is an explicit statementNoNo
Lazy loadingUsually, the source of N+1 queriesNo: you join or batch explicitlyNoNo
MigrationsUsually built in or a companion toolOften a companion toolNoNo
ExamplesHibernate, EF Core, SQLAlchemy ORM, Active Record, PrismajOOQ, Drizzle, SQLAlchemy Core, KyselyDapperJDBC, ADO.NET, Python DB-API drivers

See it in a real stack

Architectures that use object-relational mapping

GraphQL API in PythonUvicorn runs a FastAPI app that mounts Strawberry’s GraphQL router; graphql-core parses, validates and executes each query, and resolvers load rows through SQLAlchemy from PostgreSQL.FastAPIStrawberry GraphQLGraphQL-corePydanticSQLAlchemyPostgreSQL

In code · python

# pip install sqlalchemy  (2.x; runs against an in-memory SQLite database)
from sqlalchemy import ForeignKey, create_engine, event, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship, selectinload


class Base(DeclarativeBase):
    pass


class Author(Base):
    __tablename__ = "authors"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]


class Book(Base):
    __tablename__ = "books"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))
    author: Mapped[Author] = relationship()  # lazy-loaded by default


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
queries = []
event.listen(engine, "before_cursor_execute", lambda *args: queries.append(args[2]))

with Session(engine) as s:
    s.add_all([Book(title=f"Book {i}", author=Author(name=f"Author {i}")) for i in range(10)])
    s.commit()

with Session(engine) as s:  # N+1: one SELECT for books, then one per author
    queries.clear()
    for book in s.scalars(select(Book)):
        book.author.name
    print(len(queries))  # 11

with Session(engine) as s:  # fix: load all authors in one extra SELECT ... WHERE id IN (...)
    queries.clear()
    for book in s.scalars(select(Book).options(selectinload(Book.author))):
        book.author.name
    print(len(queries))  # 2

Often confused with

Relational databasesData transfer objectsCRUDObject-oriented programmingService layerCQRS
Where it came from2001–2012

Object-oriented programs and relational databases grew up separately. Objects form graphs that point at each other and use inheritance; relational tables hold rows linked by keys and are queried as sets. The friction between the two became known as the object-relational impedance mismatch, borrowing a term from electrical engineering. Through the 1990s, Martin Fowler recalls, project after project wrote its own mapping framework and found it much harder than expected. Commercial products appeared: TopLink, built in Smalltalk by The Object People, was ported to Java between 1996 and 1998 and bought by Oracle in 2002.

Two things in the early 2000s gave the field its shape. Gavin King started Hibernate in 2001 out of frustration with EJB 2 entity beans; by its developers’ account it became the most popular persistence tool for Java, and it was the model for the Java Persistence API, finalised in May 2006. In 2002 Fowler’s Patterns of Enterprise Application Architecture named the building blocks: Active Record, Data Mapper, Identity Map, Unit of Work and Lazy Load. Ruby on Rails, released in 2004, built its model layer on Active Record. Microsoft shipped the Entity Framework with .NET 3.5 SP1 in August 2008; the first version drew a “vote of no confidence” signed by at least a thousand developers.

The backlash came early. Ted Neward called object-relational mapping “the Vietnam of Computer Science”: early success draws a project in, and the painful trade-offs show up much later. In 2011 Stack Overflow wrote Dapper because its ORM was burning CPU, and jOOQ (2009) went the other way, making SQL itself a typed Java API. Fowler answered the critics in 2012: an ORM handles 80 to 90% of the mapping, and the rest needs someone who understands relational databases. Newer TypeScript tools lean the same way: Prisma returns plain objects instead of tracked ones, and Drizzle models its API on SQL.

Why it’s built this way

Two patterns: objects that save themselves, or a separate mapper
Active Record is the most obvious approach and works when objects look like rows. When the object model diverges, with collections and inheritance that tables lack, changes on one side ripple into the other, so Data Mapper puts a layer between them that neither side knows about.
Track changes and write them at the end
Writing every change as it happens means many small, slow database calls and a transaction held open for the whole interaction. A unit of work records what changed during a business transaction and works out the writes once, at commit.
Load related objects only when touched
Loading an object together with everything it refers to can pull in a huge graph when only a few objects are needed. Lazy loading leaves a placeholder and fetches on first use, at the price of N+1 queries when code walks a list.
Leave a way down to SQL
No mapping covers every query. David Heinemeier Hansson held that developers on a relational database should know how it works, so, in Fowler’s words, Active Record “takes care of boring stuff, but provides manholes” down to SQL. SQLAlchemy built its ORM on a public SQL layer so applications can “move down” a level when they need to.

Timeline

  1. 2001

    Gavin King starts Hibernate as an alternative to EJB 2 entity beans

  2. 2002

    Fowler’s Patterns of Enterprise Application Architecture names Active Record, Data Mapper and Unit of Work

  3. 2004

    Ruby on Rails is released with Active Record

  4. 2006

    Java Persistence API 1.0 is finalised, modelled on Hibernate

  5. 2008

    Microsoft ships the Entity Framework in .NET 3.5 SP1

  6. 2011

    Stack Overflow builds and open-sources Dapper, a micro-ORM

  7. 2012

    Fowler’s “OrmHate” defends ORMs as an 80% solution to a hard problem

Sources15

Improve this page on GitHub