How to TDD a database application

After the publishing of Failed attempt to apply Tdd response, some readers have asked me how to test a database application, as an example of code which is not suitable for xUnit frameworks.

Let's start with saying that database is not a special case for testing, as it's only a port of your application. Whenever the application interact with an external program I would say it presents a port, and this pattern is called Hexagonal Architecture.
Your effort should be in testing thoroughly the core of the application, building a solid object-oriented Domain Model piece after piece, by writing a test at the time an making it pass. The Domain Model should not have dependency on any infrastructure like database, http requests, twitter adapters, and so on: this refinement of the Domain Model pattern is called Domain-Driven Design and when applied produces easy testable code. The Domain Model would be tested by injecting fake adapters as there should be no logic in the database: this is object-oriented programming and in database object do not exist nor we can test it easily and automatically with Junit.
Though, this approach is very complicated and it is considered when the domain has a rich set of rules and behavior, while many applications have only CRUD capabilities.

Think of your application as existing only in Ram memory and strip out all the unnecessary code. The classes which remains are the core of the Domain Model. For instance if I had to manage the list of the users of a forum, I would write initially only a User class and a generic collection of User objects. User in my point of view is a POJO or POPO, which does not extend anything:
class User { ...

Insulating the database from view will hide this generic collection behind an abstraction, since it is not present at all in memory except for caching. Subsets of it can be reconstituted as needed. This abstraction is called a Repository or, at a low level, a DataMapper. Hibernate for Java and Doctrine 2 for php are example of a DataMapper pattern: they let you work on your objects and then take them and synchronize the database with the new data you have inserted: a change in a User's password, or new Users to be added. To polish the DataMapper Api, which is very generic, a UserRepository class can be created.
Even if you do not have a generic DataMapper, and work with mysql_query, PDO or JDBC queries, you can write a UserRepository which will act as the port for the database (or maybe a text file; since the repository decouples it, the storage mechanism can be anything from a memcache server to a group of monkeys which writes down on paper serializations of objects).
Depending on your architecture, your controllers or other domain classes will now have the UserRepository as a collaborator, and will talk to it and call its methods instead of accessing the database directly; this is a form of Dependency Injection. Obviously if there are other entities which are persisted, like Groups, Posts, etc. they should have their own repository class.

Of course the point of this discussion is how to test this code, since to write it we have to prepare a test before (it's called red green refactor and not code and then try to test). If we manage to write these tests first, the code will be automatically testable, and very decoupled and reusable as this is a characteristic of testable code.
What we need to write are not tests, but unit tests. If you want to check the entire path of data in the application, you can write integration tests which will exercise even the user interface, but Test-Driven Development prescribes to write unit tests: your test methods should have a dependency only on the system under test, and to the interface it uses.
Continuing our example, we need:

• unit tests for User, Group, Post classes (Domain Model entities);
• unit tests for controllers or other classes from the Domain Model which uses the adapters.
  
• unit tests for UserRepository and similar classes (adapters);
• unit tests for DataMapper or (infrastructure);
  

The point of unit testing is you can test singular components separately, and not the entire application as you would do with functional testing. Moreover, you must test each component separately or otherwise it would reveal too difficult to write classes to satisfy an integration test and the TDD benefits of adding a bit of design with every test method would be lost.
With this knowledge, we can say:

• unit tests for Domain Model entities must be written by the developer of this application. If the project is mostly a CRUD application and is data-intensive, these test cases will be very short.
  
• unit tests for controllers and everything that uses the adapters also must be written by the developer, mocking the adapters out. If you use the real adapters in testing, it will be integration testing and it will be heavy and brittle; you won't know if it's the controller or the adapter which does not work after a red test.
• unit tests for the adapters concrete classes: this is the only interaction with the database. Fortunately, we are unit testing for this classes so we can even use a new database for every test method as every class will present a few methods; compare this approach with testing every single feature of the application by using a real database.
• unit tests for infrastructure: these are included in the framework so we shouldn't worry.
  

Note that if you use a DataMapper which abstracts the particular database vendor as infrastructure, you can run the unit tests for the adapters by using a in-memory database like sqlite instead of the real database which will likely be very heavy to manage and recreate every time; this way, you de facto exclude database from your unit tests. Of course some integration tests will be necessary, but they are not part of TDD and of the design process.

I hope to have given you an idea of what unit tests means and how to deal with an application which uses a database for persistence, which is a very common scenario. Feel free to raise questions in the comments if something is not clear.

Do you want more? There's a book for .NET developers which explains DDD and database-independent testing.
The image at the top is a photograph of integrated circuits, which are real world components designed for testability and which are tested indipendently from the card where they will work.