Best way to unit test database access?

I keep hitting up against this, and I keep thinking that there must be a solution to this. So, here goes. What are the recommended ways to unit test your DAO code?Specifically, how do you test that your code is executing the correct SQL, getting back the expected values from the database, and generally doing the right thing?The examples that I keep finding all have problems with them:Don't bother - acceptance tests will cover it. All well and good if you have good acceptance test coverage, but even then you're leaving until the last minute something that could be done earlierRewire / Proxyquire / etc the database module (e.g. pg). This works, but it often assumes that you're using pg directly in your DAO code and not going through some intermediate layer. (I've got a module of my own that exposes a single query method which does logging and returns the result as a Promise). Yes, this module could be Rewire-d but then how do you test this layer? This also means that you are asserting on the exact SQL string, which has problems if you are using some tool like Squel.js that writes it for you.Run a real database to test against. This implies that every developer has the database running locally, and if you're not careful you get leakage between tests which is very bad. I could do this using Docker - I do that already for acceptance tests - but it then means that unit tests can only be run from Grunt, not from the IDE.My current thinking is that 2b is the best that can be achieved, but it feels like it should be possible to do better.

Submitted July 17, 2017 at 05:14PM by sazzer