DataFresh - Database rollback library for unit tests

A while ago I wrote an article about database unit testing, and the various ways you could "rollback" the database state to the way it was before the test ran.

Today I got an interesting comment from Mike Brockey:

 

"I have yet another solution to this very common problem. We have been able to isolate the steps for preparing a database snapshot, tracking the database modifications and then refreshing the modified tables back to their original state all using built in SQL statements.  We call it dataFresh and it is available now from our website as part of our TDD Developer's Toolkit, all free of charge.

http://blogs.ent0.com/blogs/datafresh/default.aspx"

 

the downloads section on the site is over here. I haven't tried it yet, but it looks interesting. Thanks Mike!

Published Saturday, October 14, 2006 2:42 AM by RoyOsherove
Filed under: , , , ,

Comments

Friday, October 13, 2006 10:33 PM by matt

# re: DataFresh - Database rollback library for unit tests

looks interesting, but seems like a lot of overkill for what could be as simple as attach / detach of database files stored in source control. here's our formula: MDF / LDF files stored in Subversion, along with schema scripts, stored proc / function files, etc... revert svn working copy (in case of failure below) attach database build database - run schema/procs/etc... run tests detach database revert svn working copy I have setup-database and teardown-database targets in NAnt that perform the required functionality. (plus a wrapper reset-database target for devs to run locally when the codeline changes) Attach / detach are extremely fast operations, coupled with the svn revert - been very happy with the results.
Saturday, October 14, 2006 2:31 AM by Arnon Rotem-Gal-Oz

# re: DataFresh - Database rollback library for unit tests

If your database is SQL Server 2005 you can do the same with two lines of TSQL -- Create the Snapshot CREATE DATABASE ON ( NAME = , FILENAME = 'PathAndFileName ') AS SNAPSHOT OF -- and later restore the DB RESTORE DATABASE from DATABASE_SNAPSHOT = 'SnapshotName; The nice thing about SQL 2005 snapshots is that the snapshot creation is instantaneous (SQL uses copy on write to fill the snapshot with data, so it just includes the diff. from the original database as you commit changes) Arnon
Saturday, October 14, 2006 11:19 AM by Mike Brockey

# re: DataFresh - Database rollback library for unit tests

There are certainly other ways to accomplish this. What makes dataFresh stand out is speed and the minimal amount of disk IO required for the refresh task. We use standard features of sql to track what tables are being modified and our refresh command only refreshes those tables. dataFresh was built out of necessity -- we have over 3,000 unit tests. We used to use a strategy that dropped the entire database and reran the setup data script when ever a change was detected. This could take anywhere from 2 - 4 seconds. This time adds up quickly! On a typical database write test, dataFresh runs in less than a second. By integrating dataFresh alone we reduced our build time from two hours to just under one.
Saturday, October 14, 2006 11:46 AM by Ian Cooper

# re: DataFresh - Database rollback library for unit tests

Beware though that talking to the DB is really a smell for unit testing (as opposed to integration or acceptance testing). http://iancooper.spaces.live.com/blog/cns!844BD2811F9ABE9C!258.entry
Saturday, October 14, 2006 12:02 PM by Mike_Brockey[0].Blog()

# Excellent Publicity for dataFresh! -- Thanks Roy!

First of all, I would like to thank Roy Osherove for his recent blog about dataFresh . Right away we

Saturday, October 14, 2006 12:13 PM by RoyOsherove

# re: DataFresh - Database rollback library for unit tests

Ian:

True - It's not realy a "unit test" if you do DB work in there. It's an Integration test if anything. The problem is you are using the unit test framework to accomplish it so the lines feels a bit blurry.

I don't consider it a "smell" really. I think DB unoit testing (integration testing really) is essential for testing you data layer along with teh database because you need to make sure it works well with the database. Mocking the DB isn't realistic IMHO as it still leaves you with bugs you don't know about on the DB which you only find out in integration (unless you use something like SQL unit which is pretty hard to use right now).

You can read the article I'm pointing to find out more about my view on this, or you can download the first chapter of my upcoming book "The art of unit testing" fomr here:

http://the.artofunittesting.com which talks about what is the difference between unit and integration tests, among other things.

Roy.

Saturday, October 14, 2006 5:07 PM by Ian Cooper

# re: DataFresh - Database rollback library for unit tests

Roy: Great I think we are on the same page on integration tests. I think its just important to try and point out this difference as a lot of advice .NET developers in this area talks about how to talk to the DB, less how not to.