Applying TDD while dealing with database related actions

There are numerous thoughts about how to go about applying TDD while dealing with database access related code. Some of the common thoughts include

1. Replacing DB with Mock Objects to simulate Database
Most preferred one. Advantages include

• No need to worry about DB info, driver info,
  
• tests run faster as compared to other techniques
• No need to change any test code because you changed the database
• Easier to maintain
  

2. Using a local copy of DB
Some programmers suggest to make use of a local copy of database for testing purposes. It has its own pros and cons:

• One needs to be aware of local Database related APIs
• Keeping the local copy of DB in synch with central test DB is bit tricky, especially with a large project team.
  
• Easier to use
  

3. Use central test DB
In this case, test with the cental DB, and apply transaction/roll backs to clean up DB after the use brining the DB back to its original state

• You can be confident that your code works well with the DB
• goes against some of the unit testing principles created by thought leaders like Michael Feathers
• Easier to maintain and use
• Be aware of Database APIs, drivers, etc .

4. Use an in-memory DB (like HSQL) Or any other faster databases

Not recommended much

• Again this goes against unit testing rules
  
• easier to use
• You are not sure whether your SQL queries tried against in-memory DB works with real DB