I did once in my last job, because we were told AWS was sunning down SimpleDB. We were able to swap to DynamoDB with relatively little fuss (though obviously not none, no migration is entirely painless) because we abstracted it so well. Turns out that several years later, SimpleDB is still up and running, but it was probably a good migration anyway.

Probably a bigger benefit though, is being able to use a different DB in production (probably big scale) vs on your personal machine while developing.

I did it once in my career. We've had one legacy PHP project designed to work for MySQL. I was in charge of bringing it back to live and I decided to host in on Heroku for the sake of simple deployment. While Heroku does have MySQL addons I opted for using Postgres.

So we've just switched underlying connection and fixed a dozen of (mostly reports) places with plain SQL queries with MySQL-specific syntax. ORM library handled rest just perfectly.

I think it's fairly common to swap out your core datastore when you take your prototype / MVP and bring it to production, or the first time your product experiences hypergrowth. At point the codebase, schema and team are still small enough that this is feasible. In fact I suspect every Firestore app that hits any degree of scale swaps out the DB for at least a subset of the app.

So I agree with you with this one important exception.

I've seen it with postgres-compatible DBs and Cassandra-compatible DBs. Lots of databases build their frontends this way so that people can switch from postgres/cassandra to their product when they need a bigger DB or better performance. I've never seen anyone swap any other kind of database.

I've been on a team that ported a production system from MySQL to PostgreSQL, because we wanted the ability to add columns without downtime (this was a few years ago before MySQL gained the ability) and we wanted to build features using trigram indexes.

Heh, of course you get a bunch of responses about database swaps...

But I think the point is still valid. You probably don't want your early design to focus on an edge case.

That said, a clear API between your DB and app is worthwhile for a lot of reasons (in general).

My company did, when the DB vendor quoted as an astronomical price per server when the original licensing agreement expired.

AWS swapped famously swapped out Oracle DB2