Database Testing: Five Key Elements

Programs that interact with databases have common elements, and testing each requires a different approach. These elements include
1. Mapping application layer interactions
2. Mapping the data layer interactions
3. Functional interactions between the application and database
4. Embedded code
5. Database Migration

Mapping application layer interactions are often more easily handled through the normal segregation unit testing techniques; mapped objects can often be replaced by stubs. When testing larger subsystems, databases stubs may be useful, although the existence of integrated databases and in memory databases reduces the need for them.

Mapping the data layer interactions often benefit from using a real database of any kind. Differences in behavior between different types of target databases can be identified or verified prior to integration. Run of cases, this needs to be available to each developer. These days, a multi-processor desktop has more than enough power to run multiple virtual machines hosting "real" databases such as Oracle, Microsoft SQL Server or Sybase. For the consistency check of the base mapping layer, which is usually sufficient to use something like SQLite.
No development work should never be done against the production database. This is a recipe for disaster. Mistakes can easily destroy data production successfully withdraw amendments to the database from a known state, which has the potential of automation to the ruine. If a problem can not be replicated in the development or quality assurance, which suggests that something is wrong with the development and quality assurance resources.

Functional interactions between the application and the database are processed using real databases. As mentioned earlier, VM is useful for this. Indeed, I know that several organizations in which a reduced version of the entire production network runs on every PC developer. In the coming years, we can expect this kind of comprehensive simulation environment to become more frequent.

Embedded code refers to code that runs in the database. Triggers and stored procedures are most commonly used types of embedded code. The only way to test this code with a database directly.

Database migration must be tested against the target databases. Migration should be tested against both the patterns and realistic data sets.