0

Assumptions: We use .NET coding against SQL Server 2005

I was just wondering how most people incorporate state into the Unit Tests that affect their database? I know when and where to use mocking, but when you want to move past that and actually do some database tests... what strategies do you use to setup and teardown your database? Do you do this per test? or setup a certain scenario in the database and run several tests against that "state of the world". Any advice would help. Thanks.

BuddyJoe
  • 69,735
  • 114
  • 291
  • 466

6 Answers6

4

I run most of the testcases against an empty database, meaning that only the database schema exists, but no data. The actual data is created and deleted by the test cases or the setup and teardown as needed. This takes longer than having an a prepared database, but is more resistent against database schema changes.

Sebastian Dietz
  • 5,587
  • 1
  • 31
  • 39
  • I like this idea. It'll also catch bugs in code that don't handle the case where a table is empty, as seems to crop up in legacy systems that've been around for a while. – Dana Jan 29 '09 at 18:55
  • +1 - Got to test those empty tables. This is basically the model I'm using now. It works ok for me. – BuddyJoe Jan 29 '09 at 19:11
2

Here's what I do in NUnit...Start a database Transaction in a method marked with the [Setup] Attribute. Setup the database state as you would like. NUnit then runs the test against that state. Roll back the database transaction in a method marked with the [TearDown] attribute. You never change the state of the database that way.

Restore the Data Dumps
  • 38,967
  • 12
  • 96
  • 122
  • It works, but scales terribly. If the methods your testing already open transactions or open their own connections to the database, your test deadlocks. The worst case scenario is when you have to kill your test process because a deadlock and it leaves an unclosed transaction on the database. Ouch. – Juliet Jan 29 '09 at 18:53
  • I understand about the scaling, but I had not thought about using transactions in this way before. +1 – BuddyJoe Jan 29 '09 at 19:09
  • @Princess: The multiple open connections I could see...But the MSDN article on Nesting Transactions says they should work. I agree that the worst case scenario is bad...but it's a test database...I definitely wouldn't do this on a production server. – Restore the Data Dumps Jan 29 '09 at 19:13
2

First of all, all my Database DDL 'scripts' are written in C# classes. I use Migrator.NET to do this: I have several classes in where every class contains some logic to upgrade or downgrade my DB.

I have a database that is called 'projectname_test' that I use to run my unittests against that need to have DB access. This DB is being upgraded by my Migrator.NET classes. This DB is upgraded by a CI process (CC.NET).

The unittests that access that DB will remove everything that is in that DB after they ran. And, when I want to play it drastically, I can simply drop my test DB; it will be rebuilt by the CC.NET process. :)

Frederik Gheysels
  • 56,135
  • 11
  • 101
  • 154
  • Actually, I've read that it has been updated or some important new features are planned, but I haven't looked at it after I've downloaded. I've written some extensions to it myself to fit my needs. – Frederik Gheysels Jan 29 '09 at 19:04
  • +1 - I didn't know about Migrator.NET. What do you know about the project status? I see it has been updated since Aug 05, 2008 – BuddyJoe Jan 29 '09 at 19:05
  • It looks like it would be easy to extend. I wish this project well, as I don't have much faith in M / Oslo model yet. – BuddyJoe Jan 29 '09 at 19:08
1

Take a look at this question and answers How do I test database-related code with NUnit?

Community
  • 1
  • 1
Mike Two
  • 44,935
  • 9
  • 80
  • 96
  • MbUnit has something like the NUnit RollBack attribute. Personally I like the finer grained control of managing the transaction yourself. – Mike Two Mar 01 '09 at 23:05
1

If you use NHibernate as ORM it is easy to swap the real SQL Server database (a few lines in app.config) with an in-memory SQLite database that is easily recreated on every test. This way your integration/build server which will execute the unit tests no longer needs to access an SQL Server.

Darin Dimitrov
  • 1,023,142
  • 271
  • 3,287
  • 2,928
0

In most products whether its a Large Hosted WebSite or Shrink Wrapped product with a DB, you usually have 2 scenarios you want to Unit Test (I understand this to some may cross over to much more than simply Unit Testing)

  1. The Fresh Install Path - When you create the DB from scratch
    • Automating the DB Creation using MsBuild and SQLCMD, passing parameters in
    • Use PowerShell for any Server Level Administrative stuff
  2. The Upgrade Path - When you are upgrading the DB of an existing customer
    • Restore a backup of a cleansed Real World DB as your Baseline
    • If necessarily apply upgrades after upgrades
    • Again use PowerShell, MsBuild, SQLCMD to orchestrate the harness

For me, even when Unit Testing I like to test with "Real World" Data especially real world Volume of data, to catch issues as close to the Implementation Phase as early as possible. So in general I don't even do Mock Testing and go for a better Unit Testing Harness that can be rebuilt and run ondemand. Basically the Continuous Integration approach, or partial CI (oxymoron) approach.

Notice that if you take the Continuous Integration approach, what you are keeping in Mind even during Implementation and Development is the Automation of Deployment of the Deliverables/Release into Production. Basically keeping the End in mind from the very Beginning, because your new feature isn't worth a thing if its not easily deployable. This is especially the case when Big Data horizontal scalability today usually means you are separating Customers in different DB's and have to repeatably run the Deployment Scripts on several of those DBs, in a predictable fashion.

How many times has a Developer declared they are "done", but then you have no clue how to even start doing functional testing on it, because it can't be deployed to anywhere other than the Developer's Environment.

So the Unit Testing harness you are creating, really test not just your Code but also the Deployment Scripts. In general to achieve this I use Jenkins, MsBuild, PowerShell. And if browsers are involved I use VirtualBox for the VM to launch different browsers.

CodeCowboyOrg
  • 2,983
  • 1
  • 15
  • 12