The most obvious and most commonly used is the surrogate DBD testing. This is where the author of a module tests the connection to a database, through DBI and a DBD driver, to ensure that they can process queries. I have often asked why?

It is often very hard to make sure a module really works with a mock object as you're suggesting. After all, your SQL can be exactly what you're expecting and still be wrong.

One module I've found which is very helpful is DBD::AnyData, formerly called DBD::RAM. It can create an in-memory database and run simple SQL queries. Unfortunately, it's become somewhat bloated; it also handles CSV, XML, and a lot of other formats, as well as acting as a sort of adapter between different data sources. (Personally, I'd prefer they all be split out into different DBDs with a common backend...) Still, I'm making my module suggest installing it for testing, and then use it if the user doesn't provide a real database in environment variables.

I would love it if DBI shipped with a DBD::Perl which would create a database in a hash (hash of arrays of arrays, probably). You could insert data through SQL and then check it manually, or vice versa. This would allow you to test without disturbing the user's database server and in a way that would avoid you making the same mistake twice.

(Yes, I'm aware of DBD::Sponge. It's bizarre and unsuitable for a test suite where you're trying to test standard code, not special code designed for that DBD.)