views:

770

answers:

4

What is the best practice for testing an API that depends on data from the database? What are the issues I need to watch out for in a "Continuous Integration" environment that runs Unit Tests as part of the build process? I mean would you deploy your database as part of the build scripts (may be run your installer) or should I go for hardcoded data [use MSTest Data Driven Unit Tests with XML]?

I understand I can mock the data layer for Business Logic layer but what if I had issues in my SQL statements in DAL? I do need to hit the database, right?

Well... that's a torrent of questions :)... Thoughts?

+2  A: 

As far as possible you should mock out code to avoid hitting the database altogether, but it seems to me you're right about the need to test your SQL somewhere along the line. If you do write tests that hit the database, one key tip for avoiding headaches is to make sure that your setup gets the data into a known state, rather than relying on there already being suitable data available.

And of course, never test against your live database! But that goes without saying :)

andygeers
So you simple delete all data in the SetUp method and execute hand tailored SQL as first step in the database testing test cases, right?
Kasper
@Kasper - This assumes you have a database already setup [ideally by running SQL scripts from build]... When you have too many test fixtures, the best way to do, I believe, is to setup the database with seed data first.
Vyas Bharghava
A: 

One thing I did was create static methods that returned test data of a known state. I would then use a "fake" DAL to return this data as if I was actually calling the database. As for testing the sql/stored procedure, I tested it using SQL Management Studio. YMMV!

Brian Lee
What if the SQL is dynamically generated - e.g. different WHERE clauses, etc.?
andygeers
+3  A: 

It's a good idea to automatically wipe the test database and then populate it with test harness data that will be assumed to be there for all of the tests that need to connect to the database. The database needs to be reset before each test for proper isolation - a failing test that puts in bad data could cause false failures on tests that follow and it gets messy if you have to run tests in a certain order for consistent results.

You can clear and populate the database with tools (DBUnit, DBUnit.NET, others) or just make your own utility classes to do the same thing.

As you said, other layers should be sufficiently decoupled from classes that actually hit the database, so the need for any kind of database being involved in testing is limited to tests run a small subset of your codebase. Your database accessing components can be mocked/stubbed for everything that depends on them.

LeedsSideStreets
+3  A: 

As mentioned, use mocking to simulate DB calls in unit tests unless you want to fiddle with your tests and data endlessly. Testing sql statements implies more of an integration test. Run that separate from unit tests, they are 2 different beasts.

Andrew Cowenhoven
Andrew, I'm not sure... Are you saying DAL cannot / shouldn't be unit tested but tested during integration?
Vyas Bharghava
If you have rolled your own DAL you should absolutely unit test it. My point is that unit testing will be much cleaner using mocks and not real database calls. This may not be possible in your case but if you can mock by all means do.
Andrew Cowenhoven