SQLAlchemy 2.0 Query Style Is Awful

I write code in Python, and I use SQLAlchemy to connect and query the database.

I do this because after 20 years of writing Perl and connecting to the database with DBI and writing hand-rolled SQL, I decided that I wanted a slightly easier life. 99% of all web apps are CRUD apps and my users do not really care if I hand crafted that update statement or not.

I also use Flask, which means I use Flask-SQLAlchemy.

After creating my models, if I want to retrieve the first matching Richard in my Person table, it looks something like this:

first_richard = Person.query.filter(Person.name == 'Richard').first()

Or if I want to count how many people there are:

total_people = Person.query.count()

This is incredibly easy and very intuitive!

BUT.

This style of querying is a Flask-SQLAlchemy construct, and if you were using SQLAlchemy v1.x directly, they would actually want you to do this:

first_richard = session.query(Person).filter(Person.name == 'Richard').first()

And:

total_people = db.session.query(Person).count()

So right away if I was to use the "official" version, I will be writing many more characters every time I want to query the database. Which is a bit counterintuitive when the point of an ORM is to make my life easier.

But then along comes SQLAlchemy 2.0. Which has decided that making your life easy should no longer be top of their list of concerns. Instead, you have to write code like this:

first_richard = session.scalars(select(People).filter_by(name="Richard").limit(1)).first()

And:

total_people = session.scalar(select(func.count()).select_from(People))

WTF? Why do I now need to use scalar? But also scalars? And why is count now in the middle, rather than at the end? And now there is select_from as distinct from select in the other example?

But their docs also have examples next to those ones like:

session.execute(
  select(User).
  join(Address).
  where(
    Address.email == "e@sa.us"
  )
).scalars().all()

So that is sometimes scalars as a method at the end of an execute, and sometimes scalars as the method of session?

How does something like this happen? How did a bunch of smart people sit down and decide that the best thing the project needed was to make every single interaction with it worse?

It does not help that the SQLAlchemy docs are terrible. They have always been terrible. They are incredibly detailed, but they are written by the kind of people who also like writing database libraries. Which is not to denigrate the team behind SQLAlchemy - it is just that writing for humans does not appear to be their strongest attribute. The new quickstart is an improvement, but it does not change the fact that the new querying approach is so confusing. Nor does it change the fact that every single existing StackOverflow question, example and online tutorial is now wrong - because they are all designed for 1.x style.

Maybe it is time to try peewee, which has a querying approach which looks logical, sensible, and does not require the kind of documentation that would scare newbies off programming for life.