Cool, the term "object-relational mapping" does indeed sound broad, as if it could be applied to merely the act of mapping tuples into something more structured, but that doesn't matter. It has a definition.
If people started using the term "data serialization" to mean taking parallel data and making it serial (for an English speaker, a perfectly reasonable meaning) would you say that this is what data serialization also was?
The term object-relational mapping refers to the very specific concept of taking relational databases and letting you access them as if they were a database of objects. Specifically, in which you had objects which held one-to-one or one-to-many or many-to-one references to other objects etc. This is a graph. The object-relational mismatch deals with the fact that relational databases and graphs are fundamentally different such that there isn't a well defined way to represent all kinds of one as the other and vice versa. Moreover, there is a performance penalty to attempting to pretend that your relational database is a graph database, and querying the relational data in ways which would make sense for a graph database.
In the case of SQLAlchemy ORM (not Core) when I worked with it back in 2018 now you could select all users from a table, or all orders. But if you wanted to select the most recent order for each user, it's much harder and requires breaking more abstractions than if you were to do it using a query generator. This is because SQLAlchemy ORM expected you to represent your data as a graph:
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String, nullable=False)
orders: Mapped[list["Order"]] = relationship(back_populates="user")
If you _just_ use the ORM you would write something like:
users = session.query(User).all()
most_recent_orders = []
for user in users:
if user.orders:
most_recent = max(user.orders, key=lambda o: o.created_at)
most_recent_orders.append((user, most_recent))
This is the n+1 query problem, and the performance would tank.
To avoid this, you have a few options, the simplest seems to be to add a
virtual fiend to your User object which holds the most recent order:
User.most_recent_order = relationship(
Order,
primaryjoin=Order.user_id == User.id,
order_by=Order.created_at.desc(),
uselist=False,
viewonly=True
)
session.query(User).options(selectinload(User.most_recent_order)).all()
But this still isn't performant, as there's going to be a double-select, one for the users, and one for the orders (filtered by the users).
If you want to do this performantly, with one final query, you end up needing an alias:
RankedOrder = aliased(Order,
select(
Order.id, ...,
func.row_number()
.over(partition_by=Order.user_id, order_by=desc(Order.created_at))
.label("rnk")
).subquery()
)
session.execute(
select(User, RankedOrder)
.join(RankedOrder, RankedOrder.user_id == User.id)
.where(RankedOrder.rnk == 1)
)
All this and you don't get users with their corresponding order contained within, you get an abstraction leaking sequence of tuples and their corresponding orders.
This is just one single mildly non-trivial example. All this extra boilerplate just to get performance.
Meanwhile if you use just Core:
ranked = select(
orders.c.id, ...,
func.row_number().over(
partition_by=orders.c.user_id,
order_by=desc(orders.c.created_at)
).label("rnk"),
).subquery()
query = (
select(users, ranked)
.join(ranked, ranked.c.user_id == users.c.id)
.where(ranked.c.rnk == 1)
)
Which looks like literally the final "ORM" code.
This is what I mean when I say ORMs are either not actually ORMs or their users aren't using the ORM part for the most part.