I've used DBD::Mock, but not DBI::Mock. Using a mock database allows much better control over database failure handling than you can generally achieve with a real database. So they are the tools to turn to when you want to see that your code handles an insertion failure or a lost connection, or other database server side issues that your code need to manage.

Using a real database is a better tool for testing your business logic. If your code is fairly database agnostic you can even use things like DBD::CSV to wrap text files containing the database data. Simple diffs against reference versions of the file let you check that the database is updated as expected. Dealing with text files also has the advantage that diffs between version in your revision control system make sense.

Test::Mock::Class works well to wrap a shim around other modules so the tested code doesn't need to be switched to "Test mode".

Writing the tests can be a lot of work. Test driven development makes it easier to incorporate test development into the general development process and makes it harder to miss important areas of testing.