Your code stays clean, because nothing on the consumer side needs to change if in prod/test or postgres/sqlite. Better still, your code doesn't even need awareness that prod/test environments even exist. Keeps things simpler in the long run.
However I completely agree with what what's really important is the different types of services you run based on the implementation.
So for example when I develop my applications completely off grid, I use express + reaper to mimic S3 / cloud storage. When I deploy just swap it out with the AWS S3 service instead.
I also got inspired to use this pattern when building deepstream.io, it's a node server that can take any database/cache/message bus/etc and pretty much run with it (with a small library adaptor). In that case we basically say 'the minimum requirement for functionality is XYZ and if the service provides is, we are in. Super useful.
- Refrain from using any Postgres features that don’t work [the same way] in SQLite (so much for your code not knowing about your test environment!)
- Maintain both in tandem and, presumably, test that they have the same behavior or… accept that they may not; in either case you have to accept that you might not know if your SQLite implementation is correct
I don’t think the alternative is having code which knows about the test environment. Instead, it’s: test with the real service or set of services you use, and limit the surface area under test.
That doesn’t mean you can’t use DI! It just means you have:
- data access layer tests that hit Postgres (and any other supported provider)
- unit tests of anything consuming the data access layer, injecting a provider which only produces results consistent with the behavior already tested in the data access layer
At that point, why inject SQLite? You already ~know the results you can expect from the injected service… just return them.