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.
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.