When unit testing, do you have to use a database to test CRUD operations?

Question

When unit testing, is it a must to use a database when testing CRUD operations? Can sql lite help with this? Do you have to cre-create the db somehow in memory?

I am using mbunit.

Hardiman

Answer 1

No. Integrating an actual DB would be integration testing. Not unit testing.

Yes you could use any in-memory DB like SQLite or MS SQL Compact for this if you can't abstract (mock) your DAL/DAO in any other way.

With this in mind I have to point out, that unit testing is possible all the way to DAL, but not DAL itself. DAL will have to be tested with some sort of an actual DB in integration testing.

Conal

Conceding the point that it is actually an integration test, is there some value in faking a database to perform the unit tests? – Heteromorphic

Yes it is. It would help you unit test your data repositories, that use DAL (in this case mocked ones). – Conal

-1. I think that Mark's answer is more accurate: It depends because if you are writing code which persists data on behalf of other code (n-tier, framework library, etc.), then a unit test is exactly that, testing that a particular unit of code is working as expected. For example, say you use IDENTITY columns in your DB, and return the created ID as a SELECT query after an INSERT, this code should be unit tested i.e. asserting return value is expected ID. Agreed that creating mock repositories is good, but there are some things you just can't mock away. – Salmons

...you might argue that a unit test against the SQL query rather than the execution of the query is sufficient, but sometimes it's not enough. Swapping out one RDBMS for another (Sqlite instead of SQL Server) is not an option when you want to test that your SQL Server 2005 specifc code is working correctly against an SQL Server 2005 instance. – Salmons

Answer 2

As with all complicated question, the answer is: It depends :)

In general you should hide your data access layer behind an interface so that you can test the rest of the application without using a database, but what if you would like to test the data access implementation itself?

In some cases, some people consider this redundant since they mostly use declarative data access technologies such as ORMs.

In other cases, the data access component itself may contain some logic that you may want to test. That can be an entirely relevant thing to do, but you will need the database to do that.

Some people consider this to be Integration Tests instead of Unit Tests, but in my book, it doesn't matter too much what you call it - the most important thing is that you get value out of your fully automated tests, and you can definitely use a unit testing framework to drive those tests.

A while back I wrote about how to do this on SQL Server. The most important thing to keep in mind is to avoid the temptation to create a General Fixture with some 'representative data' and attempt to reuse this across all tests. Instead, you should fill in data as part of each test and clean it up after.

Photomultiplier

Answer 3

When unit testing, is it a must to use a database when testing CRUD operations?

Assuming for a moment that you have extracted interfaces round said CRUD operations and have tested everything that uses said interface via mocks or stubs. You are now left with a chunk of code that is a save method containing a bit of code to bind objects and some SQL.

If so then I would declare that a "Unit" and say you do need a database, and ideally one that is at least a good representation of your database, lest you be caught out with vender specific SQL.

I'd also make light use of mocks in order to force error conditions, but I would not test the save method itself with just mocks. So while technically this may be an integration test I'd still do it as part of my unit tests.

Edit: Missed 2/3s of your question. Sorry.

Can sql lite help with this?

I have in the past used in memory databases and have been bitten as either the database I used and the live system did something different or they took quite some time to start up. I would recommend that every developer have a developer local database anyway.

Do you have to cre-create the db somehow in memory?

In the database yes. I use DbUnit to splatter data and manually keep the schema up to date with SQL scripts but you could use just SQL scripts. Having a developer local database does add some additional maintenance as you have both the schema and the datasets to keep up to data but personally I find is worth while as you can be sure that database layer is working as expected.

Electrocorticogram

Answer 4

As others already pointed out, what you are trying to achieve isn't unit testing but integration testing.

Having that said, and even if I prefer unit testing in isolation with mocks, there is nothing really wrong with integration testing. So if you think it makes sense in your context, just include integration testing in your testing strategy.

Now, regarding your question, I'd check out DbUnit.NET. I don't know the .NET version of this tool but I can tell you that the Java version is great for tests interacting with a database. In a few words, DbUnit allows you to put the database in a known state before a test is run and to perform assert on the content of tables. Really handy. BTW, I'd recommend reading the Best Practices page, even if you decide to not use this tool.

Cate

Answer 5

Really, if you are writing a test that connects to a database, you are doing integration testing, not unit testing.

For unit testing such operations, consider using some typed of mock-database object. For instance, if you have a class that encapsulates your database interaction, extract an interface from it and then create an inheriting class that uses simple in-memory objects instead of actually connecting to the database.

Jewell

but how can you test an insert or a delete then? i just don't get it! – Hardiman

@homestead: unit testing should test only a certain UNIT of code not a complete business process. – Conal

@homestead: You write an integration test for it. – Jewell

-1 See my comments in Roberts answer. Yes most of the time you can mock the DB out, but sometimes you cannot. – Salmons

Answer 6

As mentioned above, the key here is to have your test database in a known state before the tests are run. In one real-world example, I have a couple of SQL scripts that are run prior to the tests that recreate a known set of test data. From this, I can test CRUD operations and verify that the new row(s) are inserted/updated/deleted.

Cupboard

Answer 7

I wrote a utility called DBSnapshot to help integration test sqlserver databases.

If your database schema is changing frequently it will be helpful to actually test your code against a real db instance. People use SqlLite for speedy tests (because the database runs in memory), but this isn't helpful when you want to verify that your code works against an actual build of your database.

When testing your database you want to follow a pattern similar to: backup the database, setup the database for a test, exercise the code, verify results, restore database to the starting state.

The above will ensure that you can run each test in isolation. My DBSnapshot utility will simplify your code if your writing it .net. I think its easier to use than DbUnit.NET.

Jessee