3 ms·

While I somewhat agree that a lot of these articles are people who just don't actually try/use the full feature set of ORMs, I don't agree with the overall prem

While I somewhat agree that a lot of these articles are people who just don't actually try/use the full feature set of ORMs, I don't agree with the overall premise you're presenting that they really do more than write SQL for you. The other things they provide are largely just abstractions around how the queried data is returned and some additional metadata tracking of the relationships. Your estimation of those parts being 60% of the value added is probably generally wrong for most users of SQLAlchemy. Not having to consider or use another language syntax while writing your code is probably closer to 70-80% of the value for most people. Your example gist is essentially just "ORMs excel at this one thing, anything else is worthless" and also effectively hides code ("the benefit of the library!!!") to make it a very much disingenuous comparison. > But why would anyone really want to, writing SQL for CRUD is really repetitive and tedious. Computer can do that for you. Performance. I wanted to love SQLAlchemy but even in some relatively simple things it generates really asinine queries that take insane amounts of time. It's just usually not noticeably insane amounts of time at first. So you query for some data and wind up with a query that returns in 20ms when there's 20 rows in the database but falls apart when there's 200,000. It's also bad at projecting analytics type queries onto a transactionally-normalized database, but then most ORMs are not great at that. I had both of these issues enough times that I instead just opted to start handcrafting queries in those cases and using a single "ResultSet" type class that projects as both a SimpleNamespace and a dict.

TX81Z 3y ago
Agree, my datasets have multiple billions of rows and if I don’t know the details of the query, or have the ability to tune it, it’s utterly insufficient for my needs. I still fail to see how anybody who actually knows sql and works with “Big Kid” datasets would use an ORM.
- ubercore 3y ago
  When I've worked with datasets that size, the common operations tend towards the analytic or bulk operations, IME. That's not the sweet spot for an ORM, and SQL will often make a lot of sense. But, and again this is my experience, you often want to work with some more traditionally sized slice of that data, doing CRUDish things, and ORM can exist alongside the nicely tuned bulk or analytic operations. If you have the pure data operations side of a DB that size, I agree ORM isn't it.
  - BeefWellington 3y ago
    Yeah I agree here. If you're doing mostly transactional work then it can work for the 99%. As soon as you step towards analytics queries you start to see things fall apart and it's better to hand-tune your queries. In my recent projects that's essentially what I do - I use SQLAlchemy for very basic CRUD and then anything analytical gets handcrafted.
    - TX81Z 3y ago
      Doesn’t that increase complexity? I just have a database wrapper class and keep all my db code segregated there. In my case also using an ORM would blow up that segregated approach.
      - ubercore 3y ago
        It's very very easy to keep the sql alongside models and other business logic. SqlAlchemy lets you drop down to fairly complex sql while remaining in Python, and will even take in a direct sql query as well. The added complexity has always been a non-issue for me. Even a simple `sql.py` next to `models.py` works really well, for a pretty straightforward solution.
zzzeek 3y ago
Would need to know what versions of SQLAlchemy you spent time with and what exactly were these "asinine" queries you refer towards. SQLAlchemy generates very good queries these days for the information its given. Years ago there was heavy use of large complicated subqueries for many cases and those days are long gone. Also my 40% / 60% breakdown is based on the SQLAlchemy source code itself regarding what parts of the code deal with generating SQL and what parts deal with all the rest. 40% is likely a large overestimate.
- BeefWellington 3y ago
  40% LOC != 40% of the value, which is what your comment stated. My most recent experience of needing to hand-craft queries because SQLAlchemy was doing something truly hilarious was on 1.4.40-something from 2022 (I think possibly .41 or .42?). I don't have the specifics in front of me as it was for a prior job but rough outline of what I observed: A simple select including several related tables with filters on each table resulted in an individual subquery for each *filter* being applied. The resulting SQL was incredibly large, selected data in a poor way, and when replaced with a simplified single query that let the DB do its thing sped up about 100x. Those days may be gone now with 2.0 but I don't consider barely a year ago "long gone". I think there's a great place for Create/Update/Delete, and generally when doing trivial Reads. But the lesson I've learned with basically every ORM is that once a third table becomes involved you probably want to write your own SQL because they'll break in some weird way. This is essentially how I use SQLAlchemy in current projects and it's been great in that regard.
  - zzzeek 3y ago
    this sounds like the select() object was being used incorrectly, using a pattern that currently emits very prominent deprecation warnings which is: stmt = select(...) stmt = stmt.where(stmt.c.foo == 'bar') stmt = stmt.where(stmt.c.bar == 'xyz') That above pattern is one I've seen people do even recently, using the "select().c" attribute which from very early versions of SQLAlchemy is defined as "the columns from a subquery of the SELECT" ; this usage began raising deprecation warnings in 1.4 and should be fully removed by 2.1 as it was a remnant of a much earlier version of SQLAlchemy. it will do exactly as you say, "make a subquery for each filter condition" and will produce disastrous queries. people have started tripping over it recently as a result of 1.4 / 2.0's move away from the ORM `Query` object back towards `select()`. the moment you see SQLAlchemy doing something you see that seems "asinine", send an example to https://github.com/sqlalchemy/sqlalchemy/discussions https://github.com/sqlalchemy/sqlalchemy/discussions and I will clarify what's going on, correct the usage so that the query you have is what you expect, and quite often we will add new warnings or documentation when we see people doing things we didn't anticipate (such as in this case, I thought we had removed .c by 2.0 but apparently it's still just deprecated. it will be gone in 2.1).

TX81Z 3y ago

Agree, my datasets have multiple billions of rows and if I don’t know the details of the query, or have the ability to tune it, it’s utterly insufficient for my needs. I still fail to see how anybody who actually knows sql and works with “Big Kid” datasets would use an ORM.

ubercore 3y ago
When I've worked with datasets that size, the common operations tend towards the analytic or bulk operations, IME. That's not the sweet spot for an ORM, and SQL will often make a lot of sense. But, and again this is my experience, you often want to work with some more traditionally sized slice of that data, doing CRUDish things, and ORM can exist alongside the nicely tuned bulk or analytic operations. If you have the pure data operations side of a DB that size, I agree ORM isn't it.
- BeefWellington 3y ago
  Yeah I agree here. If you're doing mostly transactional work then it can work for the 99%. As soon as you step towards analytics queries you start to see things fall apart and it's better to hand-tune your queries. In my recent projects that's essentially what I do - I use SQLAlchemy for very basic CRUD and then anything analytical gets handcrafted.
  - TX81Z 3y ago
    Doesn’t that increase complexity? I just have a database wrapper class and keep all my db code segregated there. In my case also using an ORM would blow up that segregated approach.
    - ubercore 3y ago
      It's very very easy to keep the sql alongside models and other business logic. SqlAlchemy lets you drop down to fairly complex sql while remaining in Python, and will even take in a direct sql query as well. The added complexity has always been a non-issue for me. Even a simple `sql.py` next to `models.py` works really well, for a pretty straightforward solution.

zzzeek 3y ago

Would need to know what versions of SQLAlchemy you spent time with and what exactly were these "asinine" queries you refer towards. SQLAlchemy generates very good queries these days for the information its given. Years ago there was heavy use of large complicated subqueries for many cases and those days are long gone. Also my 40% / 60% breakdown is based on the SQLAlchemy source code itself regarding what parts of the code deal with generating SQL and what parts deal with all the rest. 40% is likely a large overestimate.

BeefWellington 3y ago
40% LOC != 40% of the value, which is what your comment stated. My most recent experience of needing to hand-craft queries because SQLAlchemy was doing something truly hilarious was on 1.4.40-something from 2022 (I think possibly .41 or .42?). I don't have the specifics in front of me as it was for a prior job but rough outline of what I observed: A simple select including several related tables with filters on each table resulted in an individual subquery for each *filter* being applied. The resulting SQL was incredibly large, selected data in a poor way, and when replaced with a simplified single query that let the DB do its thing sped up about 100x. Those days may be gone now with 2.0 but I don't consider barely a year ago "long gone". I think there's a great place for Create/Update/Delete, and generally when doing trivial Reads. But the lesson I've learned with basically every ORM is that once a third table becomes involved you probably want to write your own SQL because they'll break in some weird way. This is essentially how I use SQLAlchemy in current projects and it's been great in that regard.
- zzzeek 3y ago
  this sounds like the select() object was being used incorrectly, using a pattern that currently emits very prominent deprecation warnings which is: stmt = select(...) stmt = stmt.where(stmt.c.foo == 'bar') stmt = stmt.where(stmt.c.bar == 'xyz') That above pattern is one I've seen people do even recently, using the "select().c" attribute which from very early versions of SQLAlchemy is defined as "the columns from a subquery of the SELECT" ; this usage began raising deprecation warnings in 1.4 and should be fully removed by 2.1 as it was a remnant of a much earlier version of SQLAlchemy. it will do exactly as you say, "make a subquery for each filter condition" and will produce disastrous queries. people have started tripping over it recently as a result of 1.4 / 2.0's move away from the ORM `Query` object back towards `select()`. the moment you see SQLAlchemy doing something you see that seems "asinine", send an example to https://github.com/sqlalchemy/sqlalchemy/discussions https://github.com/sqlalchemy/sqlalchemy/discussions and I will clarify what's going on, correct the usage so that the query you have is what you expect, and quite often we will add new warnings or documentation when we see people doing things we didn't anticipate (such as in this case, I thought we had removed .c by 2.0 but apparently it's still just deprecated. it will be gone in 2.1).