What does it mean to test your Database? usually when someone mentions database testing, what is that they want to test. The application code that interacts with the database, or the sql code the resides in the database like stored procedures and triggers etc. I see all these aspects to database testing as important.
Testing the applications persistence mechanism We should test that the application persists what its supposed to save and retirive the data using SQL and see if the database contains the same information that is being saved, this kind of testing makes sense when the application has complex persistence layer. This type of testing can be achived using unit tests, functional tests etc.
Testing the Database Code The database code like Views, Stored procedures etc, contains business logic that you want tested, since a lot of your application code will depend on it since database code is the API that the database provides for the application to use. You can use your application unit tests(or other test frameworks that your are using) to test the database API. You can achive this using unit tests that actually hit the database, these tests will be slow, but having some tests is better than nothing. You can also use unit testing frameworks for the database code like Ounit, PL/SQL Unit etc (more about how to use these in later posts).