I've got a database-backed application which, sadly, has very couple of tests of their database. I am looking to get up to date on effectively testing the database.

To date, I have learned:

  • test ale the applying to placeOrget data towards the database
  • use mock DB's to check object/relation-mapping code

I am still battling to implement the very first point. It appears that the only method to test that's to place data in to the real tables. However the consumer will not need to see my test data. This is what I am presently doing in pseudo-code:

begin a transaction
insert a few rows
check that no error has occurred
'select' the rows back out
check that no error has occurred, check that data out matches data in
tell the test framework that it's successful
abort the transaction

Is a best practice, or some type of horrible anti-pattern? Much better suggestions?

Your user certainly should not call at your test data, because testing being produced is actually bad practice! Gradually alter produce a test atmosphere and do your testing there.

One solution I have found to operate is to produce a separate database instance, populate it with a few known data, and employ that for the integration testing. Make sure to cleanup your database after each test situation, as test cases ought to be independent.

Using transactions within the test database isn't this type of bad practice, since confirm things after which rollback the transaction, however i could be concerned that you are likely to call code which will invest in the database, therefore altering the parameters for following tests. Obviously, in case your DB supports nested transactions, this is not a large deal, but ISTM that does not all DBs do this.