How to write a unit test for a repository method that returns some data, where data can change over time?

Question

I have some code that reads five numbers from a database:

class Repository extends DatabaseRepository
{
    function getCoefficients(string $model)
    {
        return $this->getDatabaseLink()->query("
            select a, b, c, d, e from coefficients 
            where model = ? and active = 1
        ", $model)->getSingleResult();
    }
}

Calling the above method returns something like this:

array (
  'a' => '0.0001',
  'b' => '0.001',
  'c' => '0.01',
  'd' => '0.1',
  'e' => '1.0'
)

The returned numbers can change over time, based on which particular record is active at any one time.

How do I write a unit test for this? Should I write one? What do I test for? To avoid dealing with the live database, I can mock up the database, but then what will I be really testing?

What I have now

For now I went ahead and wrote a test, which engages a live database and tests for the exact numbers that are returned, but that test will break as soon as relevant data changes in the database or if database implementation changes (since my test has to call and set up the live database).

Specific Questions

• What should I be testing for in a Repository level?
• How do I deal with the fact that data returned by live database can change?
• Should a unit test be written for this code at all?

Answer 1

If the whole purpose of a method is to get some data from a database, it it obviously useless to write an automatic test for the method which mocks the database away, that would thwart the whole purpose of the test.

Moreover, it seldom makes sense to write tests against a production system. Test environments and production environments should always be separated for various reasons. So better set up a test database, with stable data in it, then your problem vanishes.

Some remarks to your comments:

• if you are not using some lightweight DB system, setting up a test database just for a few tests may not be worth the effort. But in bigger DB systems you can typically implement different schemas and data areas side-by-side, just make the schema a parameter of your repository, and implement a stable test area in your development database

• if the whole code is still in flux, testing for the exact results may simply be too early now. However, it may already make sense to test things like the db connection working, if the SQL syntax is correct or if the query delivers a minimum number of records. If that is your situation, then test exactly that.

Answer 2

Should a unit test be written for this code at all?

No, because unit tests need to run in total isolation and you test the logic within the method. Instead, write an automated test.

What should I be testing for in a Repository level?

Your automated test can:

• Programmatically insert some data in your TEST db instance;
• Call your Repository code;
• Assert on the retrieved data.

How do I deal with the fact that data returned by live database can change?

Same answer as above.

Answer 3

Do you have a setCoefficients() method? If so, what I prefer to do is this. (Warning: It's controversial. My points may suffer...)

1. Clear the TEST db.
2. Insert data using your code.
3. Retrieve and compare.

This is not a "unit test". But neither are the other answers. This tests what you (usually) really want to know: that what you write is what you read. And you should add tests that error cases, e.g. a bogus model in your code, are handled. Do you really care if the data is in an SQL db with columns named a,b,c...? Usually not.

Answer 4

In addition to the other answers, it might be a good idea to break the test down in multiple parts. Instead of just testing the very broad behaviour of persisting an object, it would be better to test the individual things like how to object is converted into data and the part of making connection with the database.