> Will you actually need to change between fundamentally different database technologies?

Yes, use sqlite locally for development and run PG in prod. Unit tests can now use the db and finish in milliseconds. You get unit tests that have the power of integration tests and don't have to ever stub out your db. I use Redislite for the same thing.

I'm of the opinion that SQLite is the musl of the SQL world. By deciding that you'll support it first-class you'll avoid the sharp edges of database specific behavior and extension hell and write better more maintainable code.

"Sorry we can't actually put that logic the database, SQLite doesn't support spooky action at a distance."