Do you test methods that return queries from the database?

Question

I just want to know if you test the output of a query with unit testing. Is this considered good practice?

For example you have a method getUsers()

getUsers(){
    // Overly simplified
    return select users from table_users 
           where create_date between "day_a" and "day_b" and active = true
           or where create_date < "day_a" and active = false
}

It is a "complex" query and you want to make sure you get a certain output. Maybe you missed something, but not sure if you actually need to create a unit test. Maybe the query should be self explanatory...

I'm looking for opinions.

Answer 1

You can't really test the output of a query with unit testing. There are two broad options:

• You could write unit tests that validate the expected value of the query string for given inputs. However, that would be a test of implementation rather than behaviour; if you came up with a different way of writing the query, even if the overall result was identical, you'd need to rewrite all the tests too. Tests like this actually make it harder to improve your code, so don't tend to be valuable. It's also entirely possible that the expected query string doesn't end up giving the actual output you want when run against the database, and you don't find that out until later.

• You could integration test the output of the query, running it against e.g. an in-memory database for speed and isolation. This verifies the thing that you actually care about, that you get the right output from the database for a given input, and gives you the flexibility to refactor the implementation details.

Hopefully it's clear that I'd recommend the second option!

Answer 2

I am assuming you are running this on test data & you have some control over the database or input being used.

Along what jonrsharpe said, what if you seed one or two users that should always return with the query & then validate for them. No it isn't checking the entire return set, but it is a sanity check that it is not totally broken.

I see value in that, because if the query is totally wrong (I'm assuming the data is used downstream) you need to know that & fix it. As a downstream tester, knowing that type of unit test would give me a level of confidence in the code that it is ready for me to jump in & start doing some other testing.