[Arch-TK]

SQLAlchemy Core isn't an ORM, it's just a very good query generator.

Although nobody seems to use the term ORM correctly any more so it's entirely possible that neither is peewee or sqlorm.

The story behind why ORM is nowadays no longer used correctly is kind of funny:

1. Query generator sounds primitive, like cavemen banging rocks together. Software engineers are scared of primitive technologies because it makes their CVs look bad.

2. Actual ORMs attempt to present a relational database as if it was a graph or document database. This fundamentally requires a translation which cannot be made performant automatically and often requires very careful work to make performant (which is a massive source of abstraction leaks in real ORMs). People don't realise the performance hit until they've written a chunk of their application and start getting users.

3. Once enough people encountered this problem, they decided to "improve" ORMs by writing new "ORMs" which "avoid" this problem by just not actually doing any of the heavy mapping. i.e. They're the re-invention of a query generator.

[adastra22]

I don’t know if I buy that. Object-relational mapping can in principle be a broad spectrum of possibilities. SQLAlchemy (the original, not Core) is an ORM that still exposes some of the underlying relational aspects.

It is still basically a query generator, just with the helpful step of converting selected tuples into objects, and tracking changes to those objects. This means that it is often possible to solve ORM-related performance issues without too much work.

It’s been a long time since I worked with SQLAlchemy though (or even touched Python), so my memory or knowledge of the current ecosystem might be off.

[Arch-TK]

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.

[instig007]

> orders.c.id

you don't need .c. in recent releases, you can just use the same model attributes as in orm.

[reactordev]

This is complete nonsense.

I’ve written a few ORMs and you have the same performance executing a select and getting rows back and translating them into objects than you do my ORMs. It’s literally the same. Are you going to return back a Row* from your function? No. You’re going to return an object or an array. Building that from an array of rows is no different than an ORM mapping those rows for you using instructions on what field goes where.

Just like you do in your function to build an object. “Oh but it’s at compile time!” You’ll shout. So are mine. CodeGen exists. The real issue you experience is that a certain style of ORMs confuse you. Unit of work or ActiceRecord pattern style ORMs can literally be codegen’ed into your binary to make mapping as fast as new() {}.

ORMs provide you with objects and relationships. Some of them even do validation should you choose. It’s about correctness and not going Wild West on your database with no regard to schema or normalization. If you’re properly normalized, you’ll be thankful for ORMs saving you from the JOIN hell you so desperately hang on to.

[Arch-TK]

You seem to be disagreeing on what the term ORM means and using the new, not very useful, and very far from the original definition. I do recommend you look at the literature of the time when ORMs and the "Object Relational Mismatch" became a hot topic and look at how ORMs worked and how people used them. Because you would be surprised to find that it's nothing like what you describe.

I didn't say you didn't want a query generator, or to have some way of mapping things automatically to native types so you are not effectively dealing with native database types.

What you don't want, which is what object relational mapping means / was designed to solve, is to force your relational data into being represented as a graph of objects when you operate on that data within your application.

To finalize:

ORMs use query generation, but they are not the originators or the only source of query generators

ORMs often provide additional validation, but they are not the originators or the only source of validation

ORMs by their definition map your SQL data to your native types, but fundamentally they do it in a way which cannot be made performant at scale. You can map things to your native types in a way which is performant but this would definitionally not be an ORM

[adastra22]

You seem to be the odd one out with a very particular, niche, and nonperformant definition of ORM.

[reactordev]

Agree to disagree.

Your last sentence negates anything you claim.