Strategy to run tests on a database

Question

I have started working on an existing project with over 1800 functional/integration tests. These have been coded with MSTest.

Many of those connect directly to the SQL Server database. The database is generated by a code generator which amongst many things create the database. Generating the db is slow and cumbersome.

This as the following problems:

• The test clean the db which mean we must maintain a seperate db for tests and another for using the application. The procedure right now is to change the database when changing between running test and running the app.
• Each branch needs to have it's own db as the db model can be different in each db (which means 2 db per branch with step 1)
• It's slow
• An installation of SQL Server and of the db must be present for the test to run

I would like the existing tests not to be dependent on such on installation, run faster if possible and not have to deal with maintaining databases through the code generator, juggling connection strings, etc.

I am trying to acheive this as quickly as possible since a rewrite of the tests is not in the budget. I have already introduced mocking to help new test be less dependent on the database, my problem now is for the existing tests.

My first though was to change our base unit test class to connect to a SQLite db which would be created by the code generator which already generates the main db instead of the SQL Server db. The SQLite could then be deleted and recopied to the test folder between each run. This would have been fast, not require having 2 SQL Server database, in fact if just running the tests no SQL Server installation would have been required.

My problems were that the generated code uses many concepts not included in SQLite; T-SQL, SQL Server specific syntax, schemas, stored procs and embedded clr assemblies.

I then tried SQL Server CE 4, which had many of the same limitations as SQLite.

Is there any other alternatives available other than rewriting the code to be compatible with SQLite (or CE), rewriting the existing tests, or a system in which we maintain 2 seperatedb ?

EDITs: Changed unit test to functional tests, clarified some things. Put some things in bold. I agree that those tests aren't proper unit tests. I agree that mocking would have been nice here. What I am trying to do is try to fix the mess I am faced with.

Answer 1

If you are connecting to a database or any other resource, you are not Unit testing. A proper unit test is isolated to the Unit Under Test, or your class. Anything outside of the unit being tested should be mocked.

I'm not a .NET developer so I can't recommend the best Mocking framework/tool, but perhaps this question will point you in the right direction.

Why Unit Tests shouldn't access resources:

You'll want to look at this problem differently. You don't have any desire to test the database itself, because you must assume that the database product you're using works correctly. Therefore, there's no need to test that "$dao->save()" actually inserts a record. What you're probably interested in is whether or not the save() method is being called when some other action is done, or whether your DAO is generating the correct INSERT statement.

If you MUST be making database calls in your unit test because you CANNOT mock the objects making database calls, you need to refactor.

Soap Box Time:

This is why Test Driven Development is so beneficial. Being force to keep decoupling and isolation in mind from the beginning leads to better overall design, flexibility, and testability.