Strategy to run tests on a database

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.

You could create a database snapshot before each test and restore the database afterwards. Creating and restoring snapshots is lighter operation than backup/restore or rebuilding the database entirely

 CREATE DATABASE myDb_snapshot ON
    ( NAME = myDb_snapshot, FILENAME = 'C:\MSSQL\Data\myDb_snapshot.ss' )
 AS SNAPSHOT OF myDb;

To restore after your test execute:

 RESTORE DATABASE myDb FROM DATABASE_SNAPSHOT = myDb_snapshot

So after each test your can restore the snapshot to have your db ready for the next test.

As an extra note: Running Unit tests on a Mocked data tier is not a substitute for running your tests on a "real database". Many "knock-on" effects are not visible from outside your database. (triggers/defaults/permissions/resource availability). For faster test cases and to produce more pluggable code, please do mock your data tier. But eventually you will have to run your test cases on a "real" database.

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.

Instead of deleting and re-creating the database between each test run, how about just running each test in a transaction so that the changes don't persist?

Wrap each test in a System.Transactions.TransactionScope, which rolls back automatically when you're done. (If you want it not to roll back, you'd assign TransactionScope to a variable and then call Complete() on it.)

public void MyTestMethod()
{
    using (new TransactionScope())
    {
        // do your database tests here
        // rolls back when you're done
    }
}

While I haven't tested it, I've read that you can do the same thing with System.EnterpriseServices.TransactionAttribute:

[Transaction(TransactionOption.RequiresNew)]
public void MyTestMethod()
{
    // do your database tests here
    // rolls back when you're done (or so I hear)
}

One of the core tenets of Unit testing is that you shouldn't rely on external things(file systems, databases, etc.) One common strategy for dealing with these problems is using mock objects(I use EasyMock for java). If you architect your code well (i.e. construct a solid Data Access tier) you can mock up the objects responsible for connecting to the database and test the code that relies on data access independent of the database.

Now I understand I am talking in terms of java/objects here, but most modern programming languages offer some type of support for mocking.

EDIT - Sometimes all you can do is start with your own code, code with TDD in mind, and retroactively add tests to all the code you touch. Eventually you will start to make a dent in those 1800 tests. Also depending on what testing framework you are using you might consider running two packages of tests(NEW and OLD) so that you can run your sleek state of the art tests without needing to run the old ones

Generating the DB shouldn't be so slow and cumbersome. Can you change your config to run against a localhost DB? Installing SQL Server on each developer's machine isn't very troublesome, and test will be quicker, since you'll reduce network delays.

The accepted answer seems to have small syntax issues. It seems the logical name of the database needs to match the database name, and since my database name had dashes in it, I also needed to wrap the database names in square braces:

CREATE DATABASE [myDb_snapshot] ON
    ( NAME = [myDb], FILENAME = 'C:\MSSQL\Data\myDb_snapshot.ss' )
AS SNAPSHOT OF [myDb];