What is an ORM (object-relational mapper)?

updated 2026-10-20

tool: SQLAlchemy
versions: SQLAlchemy 2.x
os: any

An ORM (object-relational mapper) is a library that maps database tables to classes and rows to objects. You define a User class, create User(name="ana") objects and ask for select(User).where(...); the ORM writes the SQL, runs it and turns the results back into objects. SQLAlchemy, the Django ORM, Hibernate, Entity Framework and Prisma are ORMs.

An ORM at work

bash

python -c '
from sqlalchemy import String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column

class Base(DeclarativeBase): pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))

engine = create_engine("sqlite://", echo=False)
Base.metadata.create_all(engine)
with Session(engine) as session:
    session.add_all([User(name="ana"), User(name="ben")])
    session.commit()
    stmt = select(User).where(User.name.startswith("a"))
    print("SQL:", str(stmt).replace("\n", " "))
    print("rows:", [u.name for u in session.scalars(stmt)])'

Output

SQL: SELECT users.id, users.name  FROM users  WHERE (users.name LIKE :name_1 || '%')
rows: ['ana']

Trade-offs

Benefit	Cost
Queries in the host language, checked by the type system and editor	generated SQL can be inefficient if you do not look at it
Parameters are bound automatically, which prevents SQL injection	another abstraction to learn on top of SQL
Schema changes through migrations (Alembic, Django migrations)	complex reports are often clearer in plain SQL
Portable across databases	database-specific features may need raw SQL

Watch for the N+1 query problem: loading a list of objects and then touching a relationship on each one runs one query per row. Eager loading (selectinload in SQLAlchemy, select_related / prefetch_related in Django) fixes it.

Sources

commands · djangopython manage.py makemigrations and migrate: create and apply migrationsmakemigrations turns model changes into migration files and migrate applies them to the database. Preview SQL with sqlmigrate, check state with showmigrations.compare · flaskFlask vs FastAPI: differences for building Python web APIsFlask is a minimal WSGI framework where you add validation and docs yourself; FastAPI is async ASGI, validates with type hints and generates OpenAPI docs.codes · djangoDjango HttpResponse subclasses and their status codesDjango HttpResponse subclasses and their status codes: Redirect 302, PermanentRedirect 301, NotModified 304, BadRequest 400, Forbidden 403, NotFound 404.

An ORM at work

Trade-offs

Sources

Related pages