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?
  • When unit testing database repository in C#, I mock the database either in memory, or using an SQLite provider. Don't know if the equivalent exists for php: docs.microsoft.com/en-us/ef/core/miscellaneous/testing
    – Eternal21
    Commented Dec 22, 2017 at 17:04
  • 4
    What's important? Is it the values themselves, the fact you have values, or that connecting to the database isn't broken? The answers to that dictate what kind of tests you may need to write. Next, is if you have any post-processing after you get your results, you'll want to ensure the post-processing is correct. Commented Dec 22, 2017 at 17:09
  • probably that I have values and that there are 5 of them and they are number-like
    – Dennis
    Commented Dec 22, 2017 at 18:53

4 Answers 4


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.

  • 3
    +1... Might be notable that some would call this "integration testing." ... And that's ok. Nothing wrong with integration tests ...
    – svidgen
    Commented Dec 22, 2017 at 17:35
  • +1; but since I wanted to provide more details I wrote a new answer. Commented Dec 22, 2017 at 19:51
  • Thanks. I do not quite see how or where the test database fits in. I have a development database that has a "stale" non-live data that does not update automatically. I suppose I could use that. But then it will change at some point, so it is not a viable database. I do not quite see a point in having a test-only database with stable data, unless it will be used only to power this test. By the way my SQL have already changed (I am actively developing this), to include more where clauses. Now I have two more column parameters, so any test of this method would have to be changed as well.
    – Dennis
    Commented Dec 22, 2017 at 20:16
  • 1
    @Dennis; see my edit
    – Doc Brown
    Commented Dec 23, 2017 at 8:34
  • Doc - for testing SQL syntax, are you suggesting that they talk HTTP with sqlFiddle? Or are you just saying "try it on the DB driver and see if it blows up"? I'd be interested in learning if anybody makes "unit tests" to SQLfiddle, and library code to help.
    – user949300
    Commented Dec 23, 2017 at 16:57

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.

  • 1
    Semi serious question: in this type of test, how do you test that the TEST db is properly set-up? Especially for a complex query with JOINs, you can easily get into a chicken and egg problem.
    – user949300
    Commented Dec 23, 2017 at 6:52
  • 1
    Usually we use an old backup from a production db to init the test db with some real data.
    – Simon
    Commented Dec 23, 2017 at 13:58

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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.