Vincent Massol Think Tank - Using rollbacks for unit testing database code?

Using rollbacks for unit testing database code?

[ vmassol ] 08:32, Saturday, 27 September 2003

Let's imagine you wish to perform unit test for code that calls the database. Let's also imagine that you want to test in integration, i.e. verify that the SQL query does actually goes to the database and returns database data.

The traditional approach is to:

Preload static data before the test suite runs
Load specific test related data in the database before each test. A variation is to restore the modified data at the end of the test

There are several disadvantages to point 2:

Finding out the specific data that needs to be set up for the test is time-consuming and you often need to know the full database schema (and not only the domain you're working on). This is especially true for big projects
There are referential integrity concerns that forces you to set up again data for a lot of other tables that the ones you are concerned with as they are linked through keys

One solution that our project team discussed yesterday is about using transaction rollbacks. It would work as follows:

Before the test suite starts, load the database with full data (this is done only once and usually takes a good 5 minutes for complex projets)
Before the test starts, start a transaction (in JUnit's setUp() method for example)
run the test
rollback the transaction to restore the database data in a pristine state

I was suprised to see no mention of this on the articles mentioning database unit testing (http://www.dallaway.com/acad/dbunit.html, http://www.dbunit.org/bestpractices.html). The reason may be that there are some glitches with this technique that make it impossible to use in practice... I'd like to know what you think? Have you done this before?

Notes:

The code under tests should not create a new transaction as nested transactions are usually not supported

While I have not done it, I do remember discussions about it on either the comp.lang.object or extreme programming newsgroups ( it may have even been the xp yahoo group ). It sounds like a good way to test database interaction and manage your data. I don't think you'll have a problem using it and seem to remember others using it also.

Even though we don't rollback in our tests, I still would want the majority of my programmer tests not hitting the db, just for speed. Acceptance/Customer tests would be where it may be more prevalent. Keep us posted how it turns out for your team.

Thanks,

--Jeff Brekke, September 28, 2003 03:23 AM

Hi Brett,

In all code I've written for the past 4 years, I've never had to write transaction code (but that's because I've always been using session beans). In any way, I agree that if there are transactions in your code, then it might not be a good strategy. In practice, I don't think it is a problem for the vast majority of projects. Also this is unit testing (albeit integration), not functional testing, which means you can choose to start your unit test just after the transaction code in the architecture layers.

WRT to setting up the database before the test, we tried it for 6 months and it didn't work at all. I think the reason is that you either need to spend an awful amount of time to understand and set whole bunches of tables or the whole database data every time (takes about 5-10 minutes per test then). I'm sure this works when the model is relatively simple and small (or if your team knows it well), which is unfortunately not our case (250 people, 5 years projets, 70 developers).

Do you have any experience with using the mentioned rollback strategy? We haven't done it yet and I'm curious to know if there are problems with it...

Thanks! :-)

--Vincent Massol, September 29, 2003 06:18 AM

Sorry, no experience with that. The thought never crossed my mind. We don't use EJB's, and have used a persistence abstraction that interfaces to JDBC classes, castor, or hibernate depending on when it was implemented. A transaction is a fundamental unit of that - not long transactions, but short consistency transactions where you are modifying multiple fields and want them to roll back.

I'd encourage taking a look at DBunit for doing the database construction as it will export existing contents and reimport it clean, and can be isolated to the bits you are testing each time.

dIon gave a good little presentation about it at the Sydney JUG a month or so back, which was where I heard about it.

--Brett Porter, September 29, 2003 06:33 AM

I forgot to mention that we have been using DBUnit for the past 2 years.

However, your answer has triggered some thoughts in my little head... One issue with this strategy: it'll only work if you have a data abstraction layer (which we have - we call it a DataBroker) that you can configure to turn auto-commit off for database calls... Otherwise, if you do direct JDBC calls, autocommit is on by default and that will ruin the strategy... So for it to work you definitely need a wrapper class in which you can the equivalent of a setAutoCommit(boolean) for all DB calls.

Thanks!

--Vincent Massol, September 29, 2003 06:44 AM

Hi Vincent !

From my experience, I think this should work fine atleast for the basic CRUD methods. Infact, when I was doing Oracle Development earlier, while testing on SQL prompt or D2K, I have used this strategy many times in order to avoid data resetting.

The benifits, in our scenario would be -

1. Time saving for writing mock objects
2. Time saving in data resetting
3. Database Schema changes would immediately make tests fail. (In case of mock objects, I do not know how schema changes are applied since I have not used them before.)
4. Some environmental dependencies will also get tested, for example getting database connection etc.

Even for transactions, if the test is run one at a time, say during build, and complete rollback is done, it should rollback all the statements executed in the transaction.

Regards,
- Mandakini

--Mandakini, September 29, 2003 10:29 AM

Two remarks:

1) A third possibility is to preload the tests with data returned from a simple JDBC query to the database. Of course, you need to keep the JDBC query in synch with your business logic, but you have this constraint anyway.

2) Your test doesn't seem to make sure the database is *really* hit. How do you tell if what's returned is not coming from a cache?

--
Ced

--Cedric, September 30, 2003 11:15 PM

Good strategy only problems when the transaction has atribute requiresnew . but if you have different test case then it can be done.

I prefer to write diff. test case for each ejb for unit testing. By the way if you are thinking on unit testing then you should not bother about transaction but in practice we always add some functionality test also. :-)

So this problem occurs. By the way thanks to all

--Nikhil, October 19, 2006 05:03 PM