A somewhat related question that's fun to think about is why we don't put databases directly in our clients very often (especially SPAs). The ClojureScript community has been doing some good work exploring what it's like to put your client state in a client DB type of structure. There was also a compilation of SQLite to JavaScript done, I wonder how much usage that has?
If your API is SQL against some tables, you're promising you won't make any breaking changes to that -- or else you're promising you can roll out an update to all the servers and clients at the same time.
SQL against tables has a very large surface area, and there are many details that are different depending on the version of your database software. So, you'll find you can't update your database software without also updating (and testing) all of your clients. The more complex and more widely distributed your software is, the more time-consuming and expensive it will be to update anything. If you have clients outside your direct control and running on the same release cycle, you'll find you can't update your database software at all without breaking your clients.
So you only offer an ANSI SQL compatible parser, not specific to any database (and it would then be compatible / compile-able to postgres, sqlite, whatever), and you only expose specific views into the database.
Once you create such a public view, you treat it as immutable, and all API changes require new views. Each old view must function forever.
This is not actually all that different from REST apis today; we version them, old versions must exist forever (or all clients must be updated), you can't update your server software that serves the rest API without being darn sure it's backwards compatible...
The only real difference is we encode our CRUD operations in a non-sql language right now (ad-hoc json schemas usually) vs encoding them as the ANSI subset of SQL against some fictional database views.
All of your complaints apply equally well to REST apis I think.
It’s a much larger commitment than you need to be making for many, many APIs.
Why are we doing data schema type erasure at the Application layer? The "flexibility" and "encapsulation" aren't worth the manual hell of hand coding it back in all the way to the front end.
Why not expose SQL schemas all the way to the front end? Even if the Application layer makes a view that the data store doesn't know about, everything downstream could benefit through automation.
From there you get into issues mostly having to do with APIs that can be paginated, and how to optimize those connections to balance caching with network latency.