how do i implement / build / create an 'in memory database' for my unit test

Question

i've started unit testing a while ago and as turned out i did more regression testing than unit testing because i also included my database layer thus going to the database verytime.

So, implemented Unity to inject a fake database layer, but i of course want to store some data, and the main opinion was: "create an in-memory database"

But what is that / how do i implement that?

Main question is: i think i have to fake the database layer, but doesn't that make me create a 'simple database' myself or: how can i keep it simple and not rebuilding Sql Server just for my unit tests :)

At the end of this question i'll give an explanation of the situation i got in on the project i just started on, and i was wondering if this was the way to go.

Michel

Current situation i've seen at this client is that testdata is contained in XML files, and there is a 'fake' database layer that connects all the xml files together. For the real database we're using the entity framework, and this works very simple. And now, in the 'fake' layer, i have top create all kind of classes to load, save, persist etc. the data. It sounds weird that there is so much work in the fake layer, and so little in the real layer.

I hope this all makes sense :)

EDIT: so i know i have to create a separate database layer for my unit test, but how do i implement it?

Answer 1

Define an interface for your data access layer and have (at least) two implementations of it:

• The real database provider, which will in turn run queries on an SQL database, etc.
• An in-memory test provider, which can be prepopulated with test data as part of each unit test.

The advantage of this is that the modules making use of the data provider do not need to whether the database is the real one or the test one, and hence more of the real code will be tested. The test database can be simple (like simple collections of objects) or complex (custom structures with indexes). It can also be a mocked implementation that will assert that it's being called appropriately as part of the test.

Additionally, if you ever need to support another data storage method (or different SQL database), you just need to write another implementation that conforms to the interface, and can be confident that none of the calling code will need to be reworked.

This approach is easiest if you plan for it from (or near) the start, so I'm not sure how easy it will be to apply to your situation.

What it might look like

If you're just loading and saving objects by id, then you can have an interface and implementations like (in Java-esque pseudo-code; I don't know much about asp.net):

interface WidgetDatabase {
    Widget loadWidget(int id);
    saveWidget(Widget w);
    deleteWidget(int id);
}

class SqlWidgetDatabase extends WidgetDatabase {
    Connection conn;

    // connect to database server of choice
    SqlWidgetDatabase(String connectionString) { conn = new Connection(connectionString); }

    Widget loadWidget(int id) {
        conn.executeQuery("SELECT * FROM widgets WHERE id = " + id);
        Widget w = conn.fetchOne();
        return w;
    }

    // more methods that run simple sql queries...
}

class MemeoryWidgetDatabase extends WidgetDatabase {
    Set widgets;

    MemoryWidgetDatabase() { widgets = new Set(); }

    Widget loadWidget(int id) {
        for (Widget w: widgets)
            if (w.getId() == id)
                return w;
        return null;
    }

    // more methods that find/add/delete a widget in the "widgets" set...
}

If you need to run more other queries (such as batch selects based on more complex criteria), you can add methods to do this to the interface.

Likewise for complex updates. Transaction support is possible for the real database implementation. I'm not sure how easy it is to build an in-memory db that is capable of providing proper transaction support. To test it you'd need "open" several "connections" to the same data set, and to only apply updates to that shared dataset when a transaction is committed.

Answer 2

i used Sqlite for unit test as fake DB