Design patterns are one of those things where you have to go through the full cycle to really use it effectively. It goes through the stages:
no patterns. -> Everything must follow the gang of four's patterns!!!! -> omg I can't read code anymore I'm just looking at factories. No more patterns!!! -> Patterns are useful as a response to very specific contexts.
I remember being religious about strategy patterns on an app I developed once where I kept the db layer separated from the code so that I could do data management as a strategy. Theoretically this would mean that if I ever switched DBs it would be effortless to create a new strategy and swap it out using a config. I could even do tests using in memory structures instead of DBs which made TDD ultra fast.
DB switchover never happened and the effort I put into maintaining the pattern was more than the effort it would have taken me to swap a db out later :,) .
Haha you're describing implementing an ORM! It's essentially that: a unified interface over multiple DB adapters.
What about the productivity gains from in memory db for tests though? Hard to measure I guess
In my experience, there is no in-memory database replacement that correctly replicates the behavior of your database. You need to use a real database, or at least an emulator of one.
For example, I have an app that uses Postgres as the database. I have a lot of functions, schemas, triggers, constraints in Postgres for modifying database state, because the database is 100x faster at this than my application will ever be. If I have an in-memory version of Postgres, it would need to replicate those Postgres features, and at that point I really should just be standing up a database and testing against it.
I have worked with people claiming that unit tests need to hermetically run in-memory, because reasons. Ok, I don't disagree, but if my bug or feature requires testing that the database is modified correctly, I need to test against a real database! Your in-memory mock will not replicate the behavior of a database _ask me how I know_ ...
These days, Docker makes this so easy that it's just lazy to not standup a database container and write tests againts it.