Integration vs Unit test for the database layer

A commonly posted question on Stack Overflow is how to unit testing the database layer. Any question about this generally ends up with two diverse answers. The first is that all database activities should be mocked and tested during integration testing, the second is that a real database should be used.

I feel that testing against a database is important and should be done frequently. As it needs to be done frequently it should be quick to set up and execute. Now setting up a database may sound like work, but if all developers have access to a dedicated database that sits either on the local machine or on a decent sized box in the background and the dataset are restricted to use what is reliant to the test then this take little time to connect to and set up.

As I'm a lazy git I don't really want to be using different technologies for different sets of tests. Any test that wants to run frequently and is fast to run just becomes a JUnit test.

So finally when should I write it? Well I know before starting a second of code what the data should look like before and after the test runs. So I write the test first. Now I could set up a mocked Statement, ResultSet and all that fun stuff but really that is a lot more work than creating the XML files for dbUnit and I'm going to have to set up all this data for integration tests. So why repeat myself.

I think the name used is not as important as the doing. Still if I had to pick a side, it would be "unit test".