Database testing for improved test coverage and execution speed

Database testing can mean different things for different people. This article refers to the practice of verifying the correctness of the data stored in relational databases as a result of executing an automated UI or API test.


Most non-trivial applications make use of a database to store and retrieve data. In fact, as many of us find out the hard way, the database layer of an application often makes the difference between a working system and a broken one. This is why your automated tests will end up altering data in some database, either directly or indirectly. It stands to reason that the usefulness and effectiveness of automated tests can be significantly improved by inserting database validation steps at strategically-placed points in the flow of a test. Similarly, test execution speed can be improved by short-circuiting some of the time-consuming steps in a test and replacing them with simple database update statements.

OpenTest 1.0.8 introduces two keywords that can be leveraged to add database testing steps: JdbcQuery and JdbcUpdate.

To be successful at database testing, you will need to:

  • Have basic familiarity with the SQL language and with relational database concepts, in general.

  • If you are part of a QA group that is external to the dev team, you need a good working relationship and communication channels with the developers, since they are going to be the ones to help you decipher the "internals" of the application logic and how it interacts with the database.

Tips, tricks and pitfalls:

  • Keep in mind that, once you touched the database (either to update it or validate it), you just left the black-box testing space and stepped into the potentially perilous realm of white-box testing. In order to be successful at this you’ll need to be fairly familiar with the inner workings of the application under test, so you can properly identify the tables and fields that are being used to store the data you are trying to get at.

  • Try to avoid depending on database structures that are likely to change often.

  • Try to replace database tests with API tests, if such APIs are available.

A practical example