Fog Creek Software
Discussion Board




Unit testing a database app

Hi all,

I wonder how I can apply the concepts of 'Test' and 'TestFixture' to a database application.

Should I set up and tear down a new database for every test run? Run tests in an existing database with specially scoped identifiers for my 'current' objects? Abstract from the database and run tests against a 'test proxy db layer'?

I'd like to hear your opinion (especially if you use nunit together with a RDBMS)...

Alex
Monday, September 22, 2003

I believe the general practice is to rebuild the entire database before every unit test. That is the best way to be sure that you have no interaction.

I would look closely at any test data if you wind up having such large quantities that that would impart a speed hit.

Mike Swieton
Monday, September 22, 2003

I think it's cleanest to unit test your business logic apart from the database. The tests run much faster, and you can easily simulate DB data (generally much faster than actually putting it in the DB, then running the test).

The two basic ways to do this are:

1. Create a facade layer which doesn't use database APIs.

2. Use a Mock Object strategy which provides objects which are in-memory but use the database APIs

Of course, you'll want to test the database code as well; but if it's a thin layer that'll be much easier to do.

There are some database unit testing frameworks out there (eg, dbunit for Java), you may want to investigate these, and use them or just steal ideas.

Portabella
Monday, September 22, 2003

Here are a couple sites that might help:

http://www.dallaway.com/acad/dbunit.html#resources

http://martinfowler.com/articles/evodb.html

Herbert Sitz
Monday, September 22, 2003

I'm sure I'm preaching to the choir...but you have to make sure you understand what you are testing.

A unit test should ideally test just the bit of code you are interested in, and not anything it depends on. What that means in practice is that you should test the "consumers" of database services independently of those services. 

For instance, if you have a class representing customers, it makes sense to have a test which creates a customer object, finds that customer through the customer class, and compares saved v. found. It doesn't make sense to do something like "customer.save()" and then test that "select * from customers where this = that" returns the same customer.

Likewise, it makes sense to test your database code in isolation. If you use stored procedures, create unit tests specifically to test their actions. If you use a dedicated persistence layer underneath your business logic layer, you have a very clean, testable interface to create unit tests against. 

If your business objects contain embedded SQL statements (which is pretty common), you have to seriously consider  the level of unit testing you require for the database code, and how much additional work you're willing to do to get it.

A neat solution is to put all the actual SQL in an external resource file and create unit tests based on known parameters and results. For instance, in java I have used a sqlStatements.properties file with lines like "insert_customer=insert into customer values (?, ?, ?)". You can then create a unit test to execute that statement and check that the data was written to the database. 

Nev (www.kuyt.com)
Tuesday, September 23, 2003

> A neat solution is to put all the actual SQL in an external resource file and create unit tests based on known parameters and results.  For instance, in java I have used a sqlStatements.properties file with lines like "insert_customer=insert into customer values (?, ?, ?)".

I think that this is only a partial solution because there's now a dependency between the properties file, which contains the number of parameters, and the code, which sets the parameters.  If you have to change the number of parameters, you now have two places to do it.

Don't get me wrong: with this approach you're already "smarter than the average bear", but I think it could be better still.

Code generation is one approach for making sure that what you specify in metadata really matches the code. Another approach is to specify the mapping between the business object and the database, and create the insert statement dynamically.

Portabella
Tuesday, September 23, 2003

Whenever I read unit testing I encounter term like objects or business objects. This rises question if unit testing applicable only on object oriented development?

How to [unit?] test complex reports?  For example SQL queries flowing over several screens (this is not hypotetical case - we have system containing reports where SQL is built dynamically and procedure for single report is few hundres lines). So I am not talking on simple "insert object into table" case.

I think only way in this case is to build database from scratch with well-know data and test report againts known results.

But there are several technical problems (partial data validity, data loading procedures, date ranges) and I would like to see good well implemented testing environment for cases like this.

msc
Tuesday, September 23, 2003

> This rises question if unit testing applicable only on object oriented development?

I don't think so, but it is definitely easier if the pieces to be tested are small.

You certainly can write a unit test to determine if a stored procedure is working correctly, even if the procedure is not written in an OO language.

There are certainly points in your scenario where you could unit test, but I think common advice would be to rewrite it more modularly.

You might want to check out the work on Agile Databases to get some ideas.

Portabella
Tuesday, September 23, 2003

*  Recent Topics

*  Fog Creek Home