One thing I have really gotten into in the past few months is using SQLite for unit tests. This time last year, I was convinced that unit tests should have absolutely 0 to do with databases, but if your application just has a lot of data access code, then using SQLite gives you a much lighter load than using SQL Server or any other out of process database.

Why Do this?

• You’re doing TDD or just using xUnit style unit tests.
• You have a lot of code doing data access.
• Breaking the dependency between your code and the data access code is hard and you are a lazy programmer.
• You want to be able to run tests very fast.
• You don’t want to remember how to set up the database for your tests.
• You want a plastic mat under your tests at the dinner table – you want to clean up easily.
• You don’t have stored procedures or views that would prevent you from using a database without these features.

Before Starting

• You need to have NUnit or another .NET unit testing framework.

• You need to get a .NET version/wrapper for SQLite. I am going to use this one because NHibernate wanted that one, but here’s another one I would have liked to have used for various reasons.
• The rest of this recipe assumes you already have a library project with some business entities. I am using Castle ActiveRecord for my examples. I embed my tests in the class under test, but the recipe will work fine even if you organize your code differently.

Steps

1)      Create a base class for your unit test fixtures:

 

    [TestFixture]

    public class EntityTestBase

    {

 

…

    [ActiveRecord("Person")]

    public class Person : ActiveRecordBase

    {

 

       [TestFixture]

       public class Tests : EntityTestBase

       {

 

2)      Create a test fixture setup method in the base test class, which can initialize your DAL.

 

        [TestFixtureSetUp]

        public virtual void TextFixtureSetup()

        {

            InPlaceConfigurationSource source = new InPlaceConfigurationSource();

            Hashtable properties = new Hashtable();

            properties.Add("hibernate.connection.driver_class", "NHibernate.Driver.SQLiteDriver");

            properties.Add("hibernate.dialect", "NHibernate.Dialect.SQLiteDialect");

            properties.Add("hibernate.connection.provider", "NHibernate.Connection.DriverConnectionProvider");

            properties.Add("hibernate.connection.connection_string", TEST_CONNECTION_STRING);

            source.Add(typeof(ActiveRecordBase), properties);

            ActiveRecordStarter.Initialize(System.Reflection.Assembly.GetExecutingAssembly(),

                source);

 

            _session = new SessionScope();

        }

 

 

3)      Create a virtual method for creating schema objects for your entity classes. Since I am using ActiveRecord, this is very straightforward.

 

public virtual void CreateSchema()

{

   ActiveRecordStarter.CreateSchema();

}

 

4)      Create another virtual method to create test data.

 

public virtual void LoadTestData()

{

   // derived classes use this chance to put test data in the database

}

 

5)      Call 3) and 4) from test fixture setup.

 

…

ActiveRecordStarter.Initialize(System.Reflection.Assembly.GetExecutingAssembly(),

                source);

 

CreateSchema();

LoadTestData();

 

_session = new SessionScope();

…

 

6)      In each entity class, override LoadTestData to load any test reference data you want for all your tests.

public override void LoadTestData()

{

   base.LoadTestData();

 

   new Person("FName1", "LName", new DateTime(1941, 9, 7)).Create();

   new Person("FName2", "LName", new DateTime(1951, 9, 7)).Create();

   new Person("FName3", "LName", new DateTime(1961, 9, 7)).Create();

}

 

7)      Add a PrepareForTest method to the base test fixture class, which creates the schema and loads any test data for an entity. Other entity test fixtures can use this to express a dependency:

 

public void PrepareForTest()

{

   CreateSchema();

   LoadTestData();

}

 

8)      If you have an entity you want to test that depends on another entity, call its PrepareForTest method in your own LoadTestData override.

 

public override void LoadTestData()

{

   base.LoadTestData();

 

   new Car.Tests().PrepareForTest();

 

   new Person("FName1", "LName", new DateTime(1941, 9, 7)).Create();

   new Person("FName2", "LName", new DateTime(1951, 9, 7)).Create();

   new Person("FName3", "LName", new DateTime(1961, 9, 7)).Create();

}

 

9)      Back in the base test fixture class, clear up your database when done. When things are going bad, and you need to look at the mess, comment this part out:

 

[TestFixtureTearDown]

public virtual void TestFixtureTearDown()

{

   if (_session != null)

      _session.Dispose();

 

   if (File.Exists(@"..\..\SampleData\TestDatabase.db"))

      File.Delete(@"..\..\SampleData\TestDatabase.db");

 }

10) Now you have a stateful world in which to do tests.

 

[Test]

public void Person_ShouldFastenSeatBeltWhenGettingInCar()

{

   Car theCar = Car.FindFirstByMakeModel("Ford", "Pinto");

   Person thePerson = Person.FindByName("FName1", "LName");

   thePerson.GetIn(theCar);

   Seat thePersonsSeat = theCar.GetSeatOf(thePerson);

   Assert.IsTrue(thePersonsSeat.Belt.IsFastened);

}

Going Further

Most of this article has been about how I setup my unit tests so that they work nicely with SQLite. Microsoft recently announced SQL Server 2005 Anywhere Edition. This edition, similar in spirit to SQLite, can be xcopy deployed, runs in process, yet is likely much more closely related to the database you are ultimately going to be using in a production environment. In the future, I am going to investigate using SQL Server AE for this type of testing scenario. I may update this article after that.
 

Conclusion

Using in-process database engines for unit testing is a fast painless way to improve your coverage and test things that would otherwise be a lot more work.