4 ms·

sqlorm is a new orm developed as part of hyperflask. I use sqlalchemy daily, it's an amazing library, but I wanted something more lightweight and straightforwa

• Induane 1y ago
  Considered peewee?
• globular-toast 1y ago
  I'm surprised an ORM is in scope for this project, but in any case I would have thought a framework would make unit of work not cumbersome. For example you could just tie the unit of work to the request cycle.
• instig007 1y ago
  > but I wanted something more lightweight it's called "sqlalchemy core" https://docs.sqlalchemy.org/en/20/core/ https://docs.sqlalchemy.org/en/20/core/
  • Arch-TK 1y ago
    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 1y ago
      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 1y ago
        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.