Databases and Unit/Integration Testing

Question

I have had a discussion with someone about unit/integration testing with web applications and I have a disagreement about 1 core idea. The issues is that the person I am talking to think that the database the unit test work off of should have pre-populated data in it and I think it should be completely empty before and after the tests are executed.

My concern with pre-populated data in the database is that there is no way to make sure that data is maintained in a good state. The tests themselves are going to be creating, deleting, and modifying data in the database so I really don't see how having data in the database before you start the tests is a good thing.

Is seems the the best way of testing database functionality would be having the following setups:

1. In a "setup" phase before the test actually run, you first truncate all the tables in the database
2. Then you insert all the data needed for the test cases you are about to run
3. Then you run and validate the test cases
4. Then in a "teardown" phase you once again truncates all the tables in the database

I don't see any other better way to ensuring that the data you are testing against in is a good testable test.

Am I missing something here? Is this not the best way to test database related functionality? Is there some benefit to have pre-populated database that always exists in the database (even before you start the tests or after the tests are done)? Any help in ideas to explain my process differently to better get my point across would also be great (that is if my point has merits).

Answer 1

For me unit tests should not deal with the database, integration tests deal with the database.

Integration tests that deal with the database should in practice have a empty database with a tear up and tear down approach, using a transaction based approach is quite a good way to go (i.e. create a transaction on setup and rollback on tear down).

What your friend sounds like they want to do is test from a 'regression' point of view, i.e. have real data there and see how the system reacts, after all no system is perfect and there can usually be bad data lying around somewhere that provide some quirks to your domain model.

Your best practices are the way to go, and what I tend to do, is if I find a scenario for bad data, write an integration test with a setup up and tear down with that exact scenario.

Answer 2

If your tests depend on the database, then I think it's more important that the data you care about is in a known state for your testing, rather than the database being empty. One of the measures of good tests is that each test should fail for one reason and no other test should fail for that same reason.

So, if your testing cares about the state of the data then get the data into that known state and return the data to that state after your tests have ran, so that your tests are reproducible.

If you can decouple your tests from the state of the data by mocking then that would also be a good thing. You mention you are doing unit / integration testing, but of course those two things should be considered separately. Your unit tests should be de-coupled from the database if at all possible and your integration tests should be testing with the database in a known state.

Answer 3

Well, I see one benefit in having a prepopulated database: you don't have to write the code that will insert the data you need, since it is there. Otherwise there are only drawbacks. Maybe someone modified the test data on the database ? Maybe someone attempted to refresh the data ? But the worse thing is having one test case badly messing up the database... You end up recreating the whole database manually several times.

You are right in how tests should be written, except that I would not truncate anything:

• setup phase: get a connection to the database and insert the data
• run phase
• tear down phase: remove the inserted data (truncate)

Now, that scenario is great for unit tests. When one needs data for both unit and integration testing, I found that one big setup phase common to all test cases (we regrouped all "inserts" into one static method) may also work very well. It's like a middle ground between your idea and your friend's idea. The only drawback is that you have to be very careful when adding some new data in order to not break existing test cases (but if you add like two-three rows per table like we did, it should not be a problem)

Answer 4

I think you need to narrow down an example with your collegue and find out what they mean exactly. You may both be on the same page.

Example: Checking Account Transaction Table

1. Wouldn't you want to test viewing this table for a user/account with no transactions?
2. Test adding the first record and see if you can create a balance.
3. Create records when there are already existing records and check the running balance and any other business rules.
4. View table with existing records and all the other CRUD.

Whether you achieve this by executing steps 1 & 2 or starting with a database already in this state (restore a backup?) I'm not sure it matters. Your idea of scripting it to me makes it easier to manage any changes you need (Like if you forgot to create an admin account and need it for a new user.). Script files are easier to put into source control than some backup file. This also gets affected by whether or not you distribute this app.

Answer 5

To draw aspects of a few answers together and add my 2p ...

Note: my comments relate to database testing particularly, and not UI testing (though obviously similar applies).

Databases are in just as much need of testing as front end applications but tend to get tested on the basis of 'does it work with the front end?' or 'do the reports produce the correct result?', which in my opinion is testing very late in the process of database development and not very robust.

We have a number of clients that utilise unit/integration/system testing for their data warehouse database in addition to the usual UAT/performance/et al. tests. They find that with a continuous integration and automated testing they pick up many problems before getting to traditional UAT, thus saving time in UAT and increasing the chance of UAT success.

I'm sure most would agree that a similar rigour should be applied to database testing as to front end or report testing.

The key thing with testing is to test small simple entities, ensuring their correctness, before proceeding onto complex combinations of entities, ensuring their correctness before expanding to the wider system.

So giving some context to my answer ...

Unit Testing

• has a testing focus to prove that unit works, e.g. a table, view, function, stored procedure
• should 'stub' the interfaces to remove external dependencies
• will provide its own data. You need a known starting state of data, so if there is a chance of data existing pre-test, then truncations/deletions should occur before population
• will run ideally in its own execution context
• will clear up after itself and remove the data it used; this is only important when stubs aren't used.

The advantages of doing this are that you are removing all external dependencies on the test and performing the smallest amount of testing to prove correctness. Obviously, these tests cannot be run on the production database. It may be that there are a number of types of tests you will do, depending on the type of unit, including:

• schema check, some might call this a 'data contract' test
• column values passing through
• the exercising of logic paths with different values of data for functions, procedures, views, calculated columns
• edge case testing - NULL, bad data, negative numbers, values that are too large

(Unit) Integration Testing

I found this SE post helpful in talking about various types of testing.

• has the testing focus to prove that units integrate together
• performed on a number of units together
• should 'stub' the interfaces to remove external dependencies
• will provide its own data, to remove the effects of outside data influences
• will run ideally in its own execution context
• will clear up after itself and remove the data created; this is only important when stubs aren't used.

In moving from unit tests to these integration tests, often there will be slightly more data, in order to test a wider variety of test cases. Obviously, these tests cannot be run on the production database.

This then proceeds onto System Testing, System Integration Testing (aka end-2-end testing), with increasing data volumes and increasing scope. All these tests should become part of a regression testing framework. Some of these tests might be chosen by the users to be performed as part of the UAT, but UAT is the tests defined by the users, not as defined by IT - a common problem!

So now that I have given some context, to answer your actual questions

• prepopulating data for unit and integration testing can cause spurious test errors and should be avoided.
• The only way to ensure consistent tests is to make no assumptions about the source data and control it rigorously.
• a separate test execution context is important, to ensure that one tester is not conflicting with another tester performing the same tests on a different branch of source controlled database code.

Answer 6

Frankly I think if you do unit testing without a database roughly the same size as the existing production database, you are going to have many things that pass the tests and fail in production for performance. Of course I'm against perople developing aon a tiny local database for this reason as well.

And if the code is data specific, how can you effectively test it without data? You will miss seeing if the queries returned the correct results. Why would you even want to consider testing against an empty database, all that tells you is if the syntax is correct not if the query is correct. That seems short-sighted to me. I've seen too much stuff that runs and passes tests that is categorically wrong. Don't you want to find that in unit testing? I do.