I still fail to see how anybody who actually knows sql and works with “Big Kid” datasets would use an ORM.
In my recent projects that's essentially what I do - I use SQLAlchemy for very basic CRUD and then anything analytical gets handcrafted.
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.
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.
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.
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 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).
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.