For example, I’m pretty sure I would like every app to automatically handle cases where the underlying file gets renamed or removed. (AFAIK apps that use the Cocoa API automatically does these.) However there really isn’t any POSIX API that allows handling these kind of situations — and to do these now you need to do all of these nitty-gritty things that SQLite does.
In fact, I think that the reason why SQLite needs all of these nitty-gritty details that does everything is because of an insufficient abstraction. If the file APIs were better and didn’t require everyone to consider whether you have to call fsync or not, SQLite wouldn’t have needed any of these. But well POSIX have flied and here we are.
I don’t think the POSIX API is perfect by any stretch, but there is a real downside to going lower level, in terms of productivity, in terms of mis-use and accidents, in terms of security, and in terms of ability to support old hardware, heterogeneous storage devices, networked storage devices, etc. If there was a huge and unanimous demand for the low level features SQLite uses, the API would already expose those features.
APIs sometimes are too high level, and sometimes are too low level. Getting it wrong in either direction has negative consequences. And getting it exactly right is hard. In any case, I’m unconvinced that the Linux filesystem API is abstracted at the wrong level or represents a good example of an API that is too high level.
On the other hand, the OS filesystem APIs are detailed enough for most people (and even for most of the OS internals too). I’ve never wanted to know sector sizes or power loss characteristics for any reason other than curiosity. The needs of SQLite aren’t very common. Most developers writing code to use the filesystem don’t need (or even want) to know if a file was renamed or what happened to it before last reboot or whether it can be mmap()ed with other processes. I can barely manage to use rsync, and if I was burdened with these things whenever I need to open or write a file, I’d use SQLite as my API instead of fopen(), which puts me right back at using a high level API (https://www.sqlite.org/whentouse.html).
All APIs present an interface over the resources they manage, so an abstraction is inherent, even if the API is low level. But usually part of the goal of API design is to make the interface easier to use than the resources it manages. If we expose an API that is too low level, then we compromise on usability and safety for everyone in favor of performance and detailed control for a very small minority of users.
So personally, I think I agree with your general concern in the sense that API designers should be aware of the needs of who they’re designing for, but I also think SQLite and the Linux filesystem aren’t a good example.