Tips and Tricks for Database Tests

Some tips and tricks how to make unit testing in database environments.

• most of the tips of database tests you will find also under http://www.dbunit.org/bestpractices.html
  • Database test a time consuming tests, use reset of a database.
  • each developer should have a own instance or database schema.
    • if you have not enough resources you should think about in memory databases like H2. For first short developer test it should be enough.
• for TestNG and maven surefire plugin test, you can not reset one database by more than on thread. => For this kind of test you should use single threaded test setups.
• JPA Test
  • write one simple test to check your database mapping
  • write for each named query a simple separate test - this test should only test the correctness of the named query
• OR Mapper like Hibernate and Cayenne
• write one simple test to check your database mapping.
• write for each of your named query a simple execution test
• test all queries on each target database - sometimes on script works on one database vendor but not on a other.
• test database migration not only on empty databases
• some of the SQL statements wont be return in a acceptable time if the database is filled * the SQL wont work if one table is filled or empty.
• try to test each table standalone
• also test important business tables together
• Integrate your database test into continuous integration