How to unit test a query that returns articles by a date range?

Question

Say I have a articles table, and I want to write a test for the method that returns all articles that are about to expire.

How would I go about writing a unit test for this? If I mock the collection of articles that the method returns, how is that a unit test?

I mean I want to make sure it returns the correct data range of articles?

Can someone clarify this?

Answer 1

If you're testing the data access layer itself, you will indeed probably need to access some data. Many people consider a test that hits an external dependency such as a database to be an "integration" test rather than a "unit" test -- which doesn't mean they aren't important to do.

One option is to use a lightweight or in-memory database such as SQLite. Seed the database with a set of known data as part of setting up your test. Then you can safely test the results in a controlled way.

Another factor you'll want to control: when you're creating something that depends on the current date (as in this instance) you'll want a way to inject a fake "current date" to make testing easier -- don't just use "SYSDATE" or the equivalent in your database or your testing will be much harder (your test data will have to change depending on the date).

Answer 2

I typically have some integration tests that are driven by the unit test framework, and that works on a determined set of data (including scripts to set up the data for each test run, as described here). That way you can know exactly how many (and which) articles that should be returned for a given date range, and then you can also author a test for it.

Answer 3

There is no way to "unit test" data access layer instead you can write an "integration test" that would setup a simple database query it and use assertions to check the result.

You also need to run a clean function after the test to make sure that the database is restored to it's initial state.

• if you use MSTest you can use deployment items to setup the database otherwise you'll have to write simple helper method instead.

In case you want to check business logic - i.e.erhow the results are handled then use mocks to create fake retuns from the query.

Answer 4

If you mock it, it's a unit test because you're testing the smallest amount of functionality possible.

If you don't mock it, and go against a live database, as Dror said, it's an integration test.

Answer 5

This type of test would indeed be an integration test as mentioned previously. You could either use MSTest or NUnit as the test runner, however a good rule of thumb would be to separate these tests out into their own respective assembly or category so they are not ran every time your fast unit tests are ran. To pull off this integration test should not be very difficult, you would would need to do something like the following:

1. Create the database/clear out an existing database
2. Seed the database with data from either SQL scripts or a framework like ndbunit, this step can be done in the Test Setup or similar setup routine
3. Run your integration test and assert that the known data seeded in the previous step matches the results returned from your code
4. Clean up the database and truncate/delete the records form the database so they do not adversely affect other tests.

Just as a word of caution though is that these types of tests can be very frustrating if you do not have a lot of good practices in place for quick and easy setup of your system since integration tests rely so much on the whole system being at a certain "state" to pass successfully. Making these types of tests repeatable and easy to run will make future integration and even full system wide tests easier to implement.