I don't disagree that it's tremendously overpowered. You can use SQLModel with FastAPI if you want a more minimal API surface that uses SQLA under the hood. As a user I just don't have to deal with most of its complexity on a day to day basis so it doesn't bother me and I don't think it's a bad end state for a swiss-army knife of a query library.
Well, neither SQLModel nor FastAPI existed at the time. So those weren't really options. And SQLAlchemy was still many years away from v2. So whatever it has done in the meantime to improve (I honestly have no idea) was not part of the library at the time.