Writing a testable "import data from database" class

Question

I am tasked with pulling all the rows from a 3rd party vendor's SQLite data table, creating business objects from those records, and sending the new business objects off to another class.

Pseudo-code:

var databasePath = "%user profile%\application data\some3rdPartyVendor\vendor.sqlite"
var connection = OpenSqliteConnection(databasePath);
var allGizmoRecords = connection.Query(...);
var businessObjects = TransformIntoBizObjs(allGizmoRecords);
someOtherClass.HandleNewBizObjs(businessObjects);

I've got all that working.

My question is: How can I write this class so it's unit testable?

Should I:

• use the repository pattern to mock out the data access
• actually provide a dummy SQLite database in the unit test

Or any better ideas? I'm using C#, but this question seems rather language-agnostic.

Answer 1

Well, the only thing that would really need to be tested here is TransformIntoBizObjs, I would think, since the connection code should have been written/tested elsewhere. Simply passing things that might show up to Transform and seeing if the right thing pops out would be what you need to do.

Remember to test all usecases of Transform, even potentially weird items that probably shouldn't end up in the function call, but might. Never know what people have been shoving in their databases.

Answer 2

Databases are complicates, you need to test your query code and you need to test it against a real sqlite instance - otherwise you can't be sure you didn't hit some rare sqlite quirk or bug.

And since the only way to test your query is to run it on a real sqlite file, and it's really easy to include such a file with your test there's no point to adding another layer just to make it "more" testable or to have "pure" unit tests.

Just make sure to add all the strange edge cases you can think of to your sample file.

Answer 3

Inversion of Control (IoC) and Dependency Injection (DI) will go a long way towards making your code more testable. There are a lot of frameworks out there that can assist you with this, but for your purposes you don't necessarily need to go to all that effort.

Start with extracting an interface that might look something like this:

Interface ISqlLiteConnection
{
     public IList<GizmoRecord> Query(...);

}

Once you've done that, you should refactor the OpenSqlLiteConnection() method to return an instance of ISqlLiteConnection, rather than the concrete implementation. To test, just create a class that implements your interface, which mocks up the actual DB queries and connections with determinate results.

Answer 4

You could inject a test-only Sqlite database quite easily, refactoring the code to look like below. But how are you asserting the results? The business objects are passed to someOtherClass. If you inject an ISomeOtherClass, that class's actions need to be visible too. It seems like a bit of pain.

public class KillerApp
{
    private String databasePath;
    private ISomeOtherClass someOtherClass;

    public KillerApp(String databasePath, ISomeOtherClass someOtherClass)
    {
        this.databasePath = databasePath;
        this.someOtherClass = someOtherClass;
    }

    public void DoThatThing()
    {
        var connection = OpenSqliteConnection(databasePath);
        var allGizmoRecords = connection.Query(...);
        var businessObjects = TransformIntoBizObjs(allGizmoRecords);
        someOtherClass.HandleNewBizObjs(businessObjects);
    }
}

[TestClass]
public class When_Doing_That_Thing
{
    private const String DatabasePath = /* test path */;
    private ISomeOtherClass someOtherClass = new SomeOtherClass();
    private KillerApp app;

    [TestInitialize]
    public void TestInitialize()
    {
        app = new KillerApp(DatabasePath, someOtherClass);
    }

    [TestMethod]
    public void Should_convert_all_gizmo_records_to_busn_objects()
    {
        app.DoThatThing();
        Assert.AreEqual(someOtherClass.Results, /* however you're confirming */);
    }
}

Using an IRepository would remove some of the code from this class, allowing you to mock the IRepository implementation, or fake one just for test.

public class KillerApp
{
    private IRepository<BusinessObject> repository;
    private ISomeOtherClass someOtherClass;

    public KillerApp(IRepository<BusinessObject> repository, ISomeOtherClass someOtherClass)
    {
        this.repository = repository;
        this.someOtherClass = someOtherClass;
    }

    public void DoThatThing()
    {
        BusinessObject[] entities = repository.FindAll();
        someOtherClass.HandleNewBizObjs(entities);
    }
}

[TestClass]
public class When_Doing_That_Thing
{
    private const String DatabasePath = /* test path */;
    private IRepository<BusinessObject> repository;
    private ISomeOtherClass someOtherClass = new SomeOtherClass();
    private KillerApp app;

    [TestInitialize]
    public void TestInitialize()
    {
        repository = new BusinessObjectRepository(DatabasePath);
        app = new KillerApp(repository, someOtherClass);
    }

    [TestMethod]
    public void Should_convert_all_gizmo_records_to_busn_objects()
    {
        app.DoThatThing();
        Assert.AreEqual(someOtherClass.Results, /* however you're confirming */);
    }
}

But this still feels quite cumbersome. There are two reasons, 1) the Repository pattern has been getting some bad press lately from Ayende, who knows a thing or two about Repository. And 2) what are you doing writing your own data access!? Use NHibernate and ActiveRecord!

[ActiveRecord] /* You define your database schema on the object using attributes */
public BusinessObject
{
    [PrimaryKey]
    public Int32 Id { get; set; }

    [Property]
    public String Data { get; set; }

    /* more properties */
}

public class KillerApp
{
    private ISomeOtherClass someOtherClass;

    public KillerApp(ISomeOtherClass someOtherClass)
    {
        this.someOtherClass = someOtherClass;
    }

    public void DoThatThing()
    {
        BusinessObject[] entities = BusinessObject.FindAll() /* built-in ActiveRecord call! */
        someOtherClass.HandleNewBizObjs(entities);
    }
}

[TestClass]
public class When_Doing_That_Thing : ActiveRecordTest /* setup active record for testing */
{
    private ISomeOtherClass someOtherClass = new SomeOtherClass();
    private KillerApp app;

    [TestInitialize]
    public void TestInitialize()
    {
        app = new KillerApp(someOtherClass);
    }

    [TestMethod]
    public void Should_convert_all_gizmo_records_to_busn_objects()
    {
        app.DoThatThing();
        Assert.AreEqual(someOtherClass.Results, /* however you're confirming */);
    }
}

The result is a much smaller class and a business object and data-layer that you can change more easily. And you don't even have to mock the database calls, you can configure and initialize ActiveRecord to use a test database (in-memory, even).