Getty Images


Follow these database testing basics for better data

Data is typically a company's most valuable asset. The data should be treated as such, with vigorous, almost constant testing, regardless of its location.

Applications come and go, but no one throws away a database. Databases are generally migrated, massaged and secured, but IT organizations rarely completely abandon them.

Accordingly, testing a database should be an important task. An organization doesn't need to hire specialized database testers, but teams should employ specific techniques to verify the accuracy and consistency of a database along with any application integrations.

For example, database testing can help ensure that an application gets a timely response from the collection. Data must be secure, accurate and always available. And that's where database testing comes into play.

Functional data testing

Functional database testing verifies that the database works from the end user's perspective. In other words, such tests check if an application saves data as expected and doesn't fail when values are changed and saved. Testers shouldn't find it difficult to incorporate database testing into the tests their team already runs; it requires minimal editing to add database verification points.

Another example of functional testing of data involves how the application and database respond when a user enters invalid data. A team can set up tests that include overloading character fields to ensure that the data won't be amended or corrupted. Tests that, for example, see what happens under the following circumstances:

  • when a data field for a last name that accepts up to 50 characters is filled in with more than 50 or with incorrect punctuation; or
  • when a user enters some numeral values instead of alpha characters.

A test can help solve these problems before a user ever encounters them.

QA professionals execute tests from the application side, but they can also work with or test the database itself. With only basic SQL skills and view-only access to the database, testers can validate that data columns and values display appropriately both in the database itself and the application.

Negative testing

Negative testing is another important component of database testing. These tests not only assess the application's error messaging capabilities, but also ensure that bad or invalid data won't make it to your database. For example, when a user enters invalid data characters, the application should denote the input as an error and prevent this data from being saved to the database.

Testers can develop relatively simple SQL statements to query data in an ad hoc fashion to check for irregularities. Testers also can execute triggers and verify views and indexes.

QA professionals should spend time testing applications' save, cancel and delete type features. They should also try to modify data and then save or cancel the input to see the application's response. Most of this testing can be done via the application. It's important to verify default values where possible.

When data includes names, addresses and other personally identifiable data, include accented letters and other special characters. Teams need to verify that the application won't result in an error on display, calculation or save because the database can't understand the character. Something as simple as a last name with an apostrophe can cause a database to lock up and crash an application.

Data consistency and integrity testing

As a team's database testing capabilities and understanding grows, the next logical inclusion is data consistency and integration testing. This type of testing allows QA professionals to learn more about how data is organized and secured in the database.

Databases often save junk data, sometimes from initial development phases where invalid data was saved or from negative testing where invalid data should have resulted in an application error rather than a save.

Regardless of how it got there, problematic data deserves attention. Data integrity testing can identify that data, and the practice helps with pesky performance problems, application inconsistencies and potential functionality trouble.

One important database integrity testing option is to verify that data can be successfully rolled back. Data rollback occurs when a disconnect between the application and database exists -- an uncoupling that causes the database to save bad or invalid data. When teams notice this problem as a result of testing, it ensures the database can return to a clean state without the invalid data.

Next Steps

The main types of database testing explained

Dig Deeper on Software testing tools and techniques

Cloud Computing
App Architecture