Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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).

share|improve this question
see also: Software Testing Techniques – gnat May 16 '13 at 16:00
up vote 19 down vote accepted

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.

share|improve this answer
I just I am note sure what the difference is between unit testing and integration testing besides I hear unit should use mocked data and integration should use a database (started another thread… to figure out the difference). Other than that, everything you are saying seems to fall in line with what I am thinking. – ryanzec Aug 15 '11 at 21:10
No problem, I have added more information to your other answer – Nicholas Mayne Aug 15 '11 at 21:39
why can't you unit test the DB? If you put your SQL in stored procedures, you can unit-test them with test-defined data, and suddenly everything is easy. This is definitely a best practice more people should follow, see what MS says – gbjbaanb May 8 '13 at 12:31
integration tests - what do you mean? As I mentioned modules that are using database can and should be tested with unit tests. Database can me mocked manually or replaced with in-memory implementation – hellboy Feb 25 '15 at 7:53

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.

share|improve this answer

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)

share|improve this answer
Wouldn't I rather be creating the the parts of the database needed each for for the test than have someone accidentally modify the data in a way that causes a failure? Having to make sure the data is correct when a test fails seems like something that can be prevented. – ryanzec Aug 15 '11 at 21:13
The big setup phase that inserts data useful to different test cases may only be useful for integration tests where you need to check different parts of the application working together. It may be worth having this big common set of "inserts" because you will most likely need some of them for other integration tests. Otherwise, if we're talking only pure unit testing, I'm absolutely for having one set of data to insert for each test case. – Jalayn Aug 15 '11 at 21:44

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.

share|improve this answer

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.

share|improve this answer
I am not suggestioning running against an empty database, if you see step 2 I have "Then you insert all the data needed for the test cases you are about to run". About the performance issue, I don't think that is what unit testing is for, that is more load testing. Unit testing to me it to test to make sure the logical in your code work. if the logical works, it is going to work for 1 record or 100,000,000,000 records of the same basic data (thought it will be a lot slower). – ryanzec Aug 15 '11 at 20:42
Database queries are not just about the logical and the sooner you find out it won't work in prod the better. What works for 1 record often will timeout on prod and the unit test shoudl show that as soon as possible. – HLGEM Aug 15 '11 at 20:45
Unit and integration tests are for functionality and not performance so you can test with small amounts of data – Mark Aug 15 '11 at 21:13
Unit testing should never use a database - integration tests use databases. – Nicholas Mayne Aug 15 '11 at 21:45
What you are actually talking about is load testing. If you had a set of Acceptence tests and hooked them in to a load testing tool you would then be able to achieve the desired effect. – Nicholas Mayne Aug 15 '11 at 22:02

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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