Unit Tests - Working with SQL

[KingKaeru]

I'm exploring Test Driven Development and Unit testing in VS2008 and was wondering if anyone had any real world examples on how i would write unit tests on objects that modify a sql database.

For non sql routines, tests are easy to write. If i wanted to test an add routine in a calculate object the test would just be as follows.

   1:  [TestMethod]

   2:  public void AddNumbersTest()

   3:  {

   4:      int result = Calculate.Add(2, 2);

   5:      Assert.AreEqual(result, 2 + 2);

   6:  }]

Now comes the tricky part. Lets say I have an object that will increment a sql record's integer attribute when called. How would I write a test for this?

public void Increment(int recordID) {
  using(SqlCommand cmdIncrement = new SqlCommand("update myTable set myAttribute = myAttribute+1 where recordID = @recordID"))
   {
      ....
   }
}

 

[mikeblas]

It depends on what it is you want to test.

Are you checking that the addition worked? That locking happens correctly? That the database connection works? That the SQL statement is correct? That overflow is handled correctly? That you have adequate response time?

I think once you have answers to those questions, you'll know what your test method needs to do.

 

[KingKaeru]

Are you checking that the addition worked? That locking happens correctly? That the database connection works? That the SQL statement is correct? That overflow is handled correctly? That you have adequate response time?

The method is called Increment() so the intent of the method is to increment a value in the db. Therefor, I would guess that the test would verify that calling the method would result in proper incrementing of the record attribute.

Knowing the above, how would you setup your unit test? The part I'm missing is the database...if the application would be deployed using sql server, would I have to have a db stood up that for the unit tests?

Is there something built into Visual Studio that allows me to easily stand up a db for unit testing purposes? Would the db be configured and run before the unit tests are run and then be destroyed. Or would a "test db" have to be persistent outside of the development environment... ready for any unit tests to connect and be run against?

Surely both models would work but what's the "real world best practice" for unit testing sections of code that would connect to a db?

edit: i guess I should clarify further that the question pertains mainly with the setup of the testing environment more than the actual unit test itself. The unit test would look as follows but what db would the unit test connect to?

[TestMethod]

public void IncrementTest()
{
    int before, after;
    using(SqlCommand cmdQueryRecord = new SqlCommand("select myAttribute from myTable where recordID = @recordID"))
    {
              ...
              before = dr.getInt32(0);    
    }

    MyClass.Increment(1);
    

    using(SqlCommand cmdQueryRecord = new SqlCommand("select myAttribute from myTable where recordID = @recordID"))
    {
              ...
              after = dr.getInt32(0);    
    }
    
    Assert.AreEqual(after, before + 1);

   }]

 

[fluxion]

id imagine you'd want a "pristine" DB setup for each run....otherwise if a previous unit test clobbered something you might leave it in an unpredictable state on subsequent runs causing additional failures even after the fix. in general, ideally, testing/builds should be done in a pristine environment for consistent builds/tests

 

[AlienKing]

If there is other code in your function that you want to test inside the same method without actually using the datbase, you can use a mock object instead of a real database connection.

Rhino mocks is my favorite mocking framework. http://ayende.com/projects/rhino-mocks.aspx

 

[mikeblas]

The method is called Increment() so the intent of the method is to increment a value in the db. Therefor, I would guess that the test would verify that calling the method would result in proper incrementing of the record attribute.

Sure. But what's your bar for "proper"? Is it just doing the increment? Do you care about all the other code paths that are involved? If you're writing a shallow test, you don't; if you're writing a thorough and robust test, you do care about at least some of them.

Knowing the above, how would you setup your unit test? The part I'm missing is the database...if the application would be deployed using sql server, would I have to have a db stood up that for the unit tests?

How can you test a database call without a database?

Since you're not forthcoming with your goals, let's try a straw man. I'd code a test that:

1. Read the existing value from the row.
2. Incremented it in SQL.
3. Read the new value from the row.
4. Compared the result from #3 and #1 to see if the result had been incremented by one.

Software tests are intended to see if the code under test is performing as designed. Without knowing how the code is intended to perform, you'll have a hard time testing it.

For example: how should the increment a row code react, in production, if the value it is trying to increment doesn't exist? That is, what's supposed to happen if the WHERE clause doesn't match any rows at all? Is it OK to realize you never found the existing row in Step #4, and therefore haven't incremented anything?

What's supposed to happen if someone else tries to increment the value at exactly the same time? If the database is to be robust across such a collision, the test must be, too. And in that case, either the test is wrapped in a transaction, or it's prepared to see an arbitrarily higher number in Step #4 rather than simply an increment by one.

In production, you should also be testing error cases. If the code can't connect to the database to perform the increment in the first place, what is it to do? Does it do that correctly?

Is there something built into Visual Studio that allows me to easily stand up a db for unit testing purposes? Would the db be configured and run before the unit tests are run and then be destroyed. Or would a "test db" have to be persistent outside of the development environment... ready for any unit tests to connect and be run against?

I don't know what "stand up a db" means. I'd expect that you might want the database to be created automatically for testing if it doesn't exist, but that you'd want to leave it around after testing is done. If it is destroyed after testing, investigating test failures becomes difficult because all of the evidence is gone.

Surely both models would work but what's the "real world best practice" for unit testing sections of code that would connect to a db?

I think this is one of those cases where you determine what you need for yourself, rather than follow any recipe.

edit: i guess I should clarify further that the question pertains mainly with the setup of the testing environment more than the actual unit test itself. The unit test would look as follows but what db would the unit test connect to?

This is entirely up to you. Maybe you have a test database that runs on the machine where the tests are being run. Perhaps that database is created empty by the tests. This test expects to find a row that was produced by another, previous test. Or, maybe you have a local database that's pre-populated by set, testing data. The row that's to be incremented comes from that stock set of test data. Or, maybe you have a database that's shared across your team; maybe it's pre-poulated, maybe it's not.

I suppose it's even conceivable that you use special records for testing on your production server. This is pretty aggressive, since a bad test could have effect on the customer-facing system. On the other hand, it guarantees code and situational coverage that's not otherwise possible.

Point is, though, your own requirements guide you.

 

[KingKaeru]

Thanks mike, your posts are always appreciated.

I think you're overcomplicating the question here. Yes, your points on requirements specifications is important but I think you fail to see the real question being asked.

Please refer to AlienKing and fluxion's posts to gain insight on where the topic of discussion actually is.

Sure. But what's your bar for "proper"?

Please refer to the sample IncrementTest() unit test. Though...the question isn't so much about the test itself but what environment the test would run against (database specifically).

Is it just doing the increment?

Refer to above.

Do you care about all the other code paths that are involved?

Yes but not in this particular test. See above.

If you're writing a shallow test, you don't; if you're writing a thorough and robust test, you do care about at least some of them.

Refer to above

How can you test a database call without a database?

This is exactly why I'm asking the question. How would you stand up your database to allow the unit test to pass.

Since you're not forthcoming with your goals, let's try a straw man. I'd code a test that:

1. Read the existing value from the row.
2. Incremented it in SQL.
3. Read the new value from the row.
4. Compared the result from #3 and #1 to see if the result had been incremented by one.

Please re-read the previous post. This is exactly what i posted as an example for the unit test.

Software tests are intended to see if the code under test is performing as designed.

Correct. Software tests definately are intended to see if the code under test is performing as designed.

Without knowing how the code is intended to perform, you'll have a hard time testing it.

Please refer to the code and the sample test posted above.

For example: how should the increment a row code react, in production, if the value it is trying to increment doesn't exist?

This is beyond the scope of the question. Please refer back to the thread.

That is, what's supposed to happen if the WHERE clause doesn't match any rows at all?

If this is a concern, we'd generate another unit test to test this or include it in our IncrementTest(). This is beyond the scope of the original post topic.

Is it OK to realize you never found the existing row in Step #4, and therefore haven't incremented anything?

Refer to the above.

What's supposed to happen if someone else tries to increment the value at exactly the same time?

Refer to the above.

If the database is to be robust across such a collision, the test must be, too. And in that case, either the test is wrapped in a transaction, or it's prepared to see an arbitrarily higher number in Step #4 rather than simply an increment by one.

Refer to above.

In production, you should also be testing error cases.

Yes you are correct. But this is beyond the scope of the question.

If the code can't connect to the database to perform the increment in the first place, what is it to do?

This is beyond the scope of the question.

Does it do that correctly?

Refer to above.

I don't know what "stand up a db" means.

"Create a database for a unit test run tested code against."

I'd expect that you might want the database to be created automatically for testing if it doesn't exist

Yes! That would be the ideal case. Now how do you go about doing this? Is there an existing framework that can be used or would one need to be implemented?

, but that you'd want to leave it around after testing is done.

Sure, we could keep it around after testing is done. Lets figure out the db can be "created automatically for testing".

If it is destroyed after testing, investigating test failures becomes difficult because all of the evidence is gone.

Correct. Lets figure out the db can be "created automatically for testing".

I think this is one of those cases where you determine what you need for yourself, rather than follow any recipe.

If we can't find a recipe for db's to be "created automatically for testing." We'll home brew something. I prefer not to reinvent the wheel

This is entirely up to you.

Refer to above.

Maybe you have a test database that runs on the machine where the tests are being run.

Yes this is what we want. How do we do it automatically?

Perhaps that database is created empty by the tests. This test expects to find a row that was produced by another, previous test. Or, maybe you have a local database that's pre-populated by set, testing data. The row that's to be incremented comes from that stock set of test data. Or, maybe you have a database that's shared across your team; maybe it's pre-poulated, maybe it's not.

maybe...

 

[mikeblas]

This is exactly why I'm asking the question. How would you stand up your database to allow the unit test to pass.

Write code to create it before the tests start. In SQL Server, you can just use the "CREATE DATABASE" command. Once created, you can write a script that creates the necessary database objects. You could even copy data from a reference source, if that's what you decide you want to do.

If this is a concern, we'd generate another unit test to test this or include it in our IncrementTest(). This is beyond the scope of the original post topic.

What I'm responding to in the original post is your own question:

Lets say I have an object that will increment a sql record's integer attribute when called. How would I write a test for this?

... reinforced by your repetition of the same question, later:

Therefor, I would guess that the test would verify that calling the method would result in proper incrementing of the record attribute.

If that's not your intended question, that's fine--but it's probably just better to admit that.

 

[KingKaeru]

Thank you mikeblas .

Anyone else have any input on this?

 

[Stupendous]

Here is how we do things for database tests:

-Manually create the test database since this only has to be done once per environment (once for local development, once for the unit test database used by our continuous integration builds)
-Before the test run create the schema if the unit test is database dependent and any test data if necessary (either in the specific test method or in the TestFixtureSetup or SetUp. I'm using NUnit vocabulary here.)

We always create the schema at the beginning of the unit test that uses the database because this ensures we have a clean slate. Creating the schema is easy for us because we use NHibernate. We make a call to a method in the NHibernate library and it creates all the tables, etc using what we setup our on our object model.

As far as test data goes there are 4 approaches we've found for setting it up

1. Master suite of data used by all database dependent tests
2. Set up your specific test data in TestFixtureSetup.
3. Set up your specific test data in Setup.
4. Set up your specific test data in the actual test.

We used to have a mix of all of these and now only do 2 - 4. The master suite of data became very hard to follow because of the number of tests we have.

 

[KingKaeru]

Here is how we do things for database tests:

-Manually create the test database since this only has to be done once per environment (once for local development, once for the unit test database used by our continuous integration builds)
-Before the test run create the schema if the unit test is database dependent and any test data if necessary (either in the specific test method or in the TestFixtureSetup or SetUp. I'm using NUnit vocabulary here.)

We always create the schema at the beginning of the unit test that uses the database because this ensures we have a clean slate. Creating the schema is easy for us because we use NHibernate. We make a call to a method in the NHibernate library and it creates all the tables, etc using what we setup our on our object model.

As far as test data goes there are 4 approaches we've found for setting it up

1. Master suite of data used by all database dependent tests
2. Set up your specific test data in TestFixtureSetup.
3. Set up your specific test data in Setup.
4. Set up your specific test data in the actual test.

We used to have a mix of all of these and now only do 2 - 4. The master suite of data became very hard to follow because of the number of tests we have.

Stupendous, your post was EXACTLY what I was looking for. This will help greatly.

I will look into NHibernate.

Thanks! You made a very concise and helpful post.

 

[Stupendous]

Stupendous, your post was EXACTLY what I was looking for. This will help greatly.

I will look into NHibernate.

Thanks! You made a very concise and helpful post.

NHibernate is an ORM and that is a completely different topic. To use it would require a big change on how you persist things and the rest of your project as well. You would not use it for just creating your database schema. I've never worked on a project that has not used an ORM so I'm not aware of any other best known methods for creating schema for unit test use.

 

[KingKaeru]

Just looked into NHibernate and as you said, we probably won't find much use for it in our lab.

Your comment about setting up 1 database per environment helps a lot and makes sense. I'm experimenting with your test data approaches. Producing procedures for schema generation will be something we definately need to look into further.