I strongly believe in this principle, but I've also seen colleagues try to future-proof the database (via interfaces) in the wrong place.
If your DBUserStore happens to know directly about SQL, that class is the wrong place to try to introduce flexibility around different DBs, ORMs, SQL dialects, etc. Just hard-code it to PostgresUserStore and be done with it.
Instead, put the interface one level up. Your PostgresUserStore is just a place to store and retrieve users, so it can implement a more general UserStore interface. Then UserStore could just be a HashMap or something for the purposes of unit tests.
Also, if you have some autowiring nonsense that's configured to assume "one service has one database", that's bad. Singletons used to be an anti-pattern and now they've been promoted to annotation and/or basic building block of Java microservices.
When it comes time to live-migrate between databases, your service will need to stand up connections to both at once - two configurations, not one, so architect accordingly.
Singleton was never the antipattern, it was the GoF implementation using the class to manage the singleton instance that everyone eventually ran from. Object lifecycles like Singleton are managed these days by a module system or a DI container.
If your DBUserStore happens to know directly about SQL, that class is the wrong place to try to introduce flexibility around different DBs, ORMs, SQL dialects, etc. Just hard-code it to PostgresUserStore and be done with it.
Instead, put the interface one level up. Your PostgresUserStore is just a place to store and retrieve users, so it can implement a more general UserStore interface. Then UserStore could just be a HashMap or something for the purposes of unit tests.
Also, if you have some autowiring nonsense that's configured to assume "one service has one database", that's bad. Singletons used to be an anti-pattern and now they've been promoted to annotation and/or basic building block of Java microservices.
When it comes time to live-migrate between databases, your service will need to stand up connections to both at once - two configurations, not one, so architect accordingly.