I am using Firebase on a project and I regret it. There are some Firebase specific annoyances to put up with, like the local emulator is not as nice and "isomorphic" as say running postgresql locally. But the main problem (and I think this is shared by what I call loosely "distributed databases") is you have to think really hard about how the data is structured. You can't structure it as nicely from a logical perspec…
I think you're describing platform-as-a-service? It does exist, but it didn't eat cloud's lunch, rather the opposite I expect.
It's hard to sell a different service when most technical people in medium-big companies are at the mercy of non-technical people who just want things to be as normal as possible. I recently encountered this problem where even using Kubernetes wasn't enough, we had to use one of the big three, even though even sustained outages wouldn't be very harmful to our business model. What can I say, boss want cloud.