Introduction to database testing

In software development, processing and storing data in different states reflects the business rules an application is built on. The heart and soul of any software application is the data that is persisted in databases for retrieval and further processing. The database system (SQL or No-SQL) chosen for an application must serve the required data processing and storage needs of the application. What better way to determine that than to set up a system to test the database to ascertain its capabilities?

What is database testing?

We have looked at testing code and also how to properly test APIs, but what about the data layer of our application? What is database testing all about?

Database testing is mainly composed of constructing SQL queries to assert and validate different database operations, structures, and attributes required by the application the database is for.

This process may include validating the schema, testing CRUD operations and transactions to make sure that the database is set up correctly. These tests can either be fully automated, fully manual, or a hybrid approach using a mix of both manual and automated processes. For example, in a fully manual test, you could go into the database management system and run queries to validate assumptions. Or you could test the application UI to see that it returns the correct data after an operation.

Database testers work with application developers to properly test the scenarios in which the database is to operate. A database tester should fully understand the business rules of the application in addition to being familiar with the database structure.

Why is database testing important?

When the integrity of data in an application is compromised, it can lead to huge monetary losses that could cause the collapse of the entire business. The gravity of these consequences ranges from less data-sensitive features like the number of retweets on a tweet to the actual value of stock on a trading application. While database testing cannot prevent all data breaches or integrity compromises, the worst consequences can be mitigated by thoroughly testing the database system used for an application.

Some of the common reasons for testing databases are:

• To prevent attacks such as SQL injection attacks
• Ensuring that network issues or power outage do not affect the integrity of data or lead to data loss or corrupted data
• To remove errors from the database to maintain the quality of data
• To verify that every piece of data is being stored in the right format
• To validate relationships between entities within the database

And so many more.

What should I test?

Once you understand what database testing is all about and why it is important, you can apply what you know to making sure that all test cases are well covered.

Data Manipulation

The first set of test cases are for the Create, Read, Update, and Delete (CRUD) operations that your application performs. This is to ensure that data is being stored and retrieved in ways that assure data integrity. A tester can do this directly from the application interface or use a database management system (DBMS) to run queries using SQL data manipulation language (DML) commands. Malicious SQL commands that can corrupt the data in the database can also be run in a test environment to ensure that the database is protected against SQL injection attacks.

Concurrency and Transaction Handling

Transactions are sensitive database operations because they require different queries to be performed with proper recovery from failures. A standard database transaction is to follow the ACID principles.

• Atomic: It must complete all operations or perform no operation at all
• Consistent: The state of the database must always be valid, and all constraints must be adhered to
• Isolated: Each transaction must be performed in isolation from other transactions running at the same time and ensuring that data integrity is maintained in a multi-user environment. The eventual database state must be as though the transactions ran sequentially
• Durable: No data loss for any reason once the transaction is committed

The database tester crafts SQL queries to check and validate these properties on transaction operations in the application. This kind of test is especially important in testing financial applications.

Schema

The database schema is the blueprint of the database structure, and testers needs to be very familiar with it. Testers can use SQL describe (DESC) commands to reveal the database schema to make sure that it conforms to what the application expects. Testers also use regular expressions to validate table field names and verify that the value conforms to the data type expected.

Triggers

Triggers are like event handlers in code. When an operation occurs on a table (e.g. when a new row is added), a trigger can be set up to execute a piece of code or query in response to the operation that just occured. Triggers create a cascade effect on some operations. For example, deleting a user from a database can trigger the user’s posts to be deleted also. As a tester, you want to make sure that this operation occurs accurately and does not delete another user’s posts instead.

A tester can run an SQL query to initiate the origin operation that triggers the cascade. The tester could also perform the operation from the application’s interface to examine its effect on the database records.

Security Testing

It involves testing the security mechanisms implemented in the database, such as user access controls, authentication mechanisms, and encryption to identify vulnerabilities, potential loophole and ensuring data and resources are protected from potential intruders.

Performance Testing

When it comes to evaluating the database’s performance, there are several factors to consider. One important consideration is how the database performs under different conditions, such as high load, concurrent users, or large data volumes. To assess this, it’s important to measure response times and throughput, as well as to identify any potential performance bottlenecks. By doing so, you can ensure that your database is able to handle the demands of your organization and provide reliable, efficient service to your users.

Others

The operations I described previously are not the only set of operations that can be tested, they are just some of the most important for most data-sensitive applications.

Other operations and attributes that can be tested are:

• Database constraints
• Stored procedures
• Views
• Indexes

How to test databases

Now that we know what to test, how should we perform the tests? Surprisingly, running database tests is not so different from running tests on applications.

As I mentioned earlier, these tests can be performed manually by performing operations from the application UI to make sure that the right data is returned after each operation or by checking the database records for the results of the operation. You can also use a DBMS to run your test queries and validate the results.

If you would rather not test manually, you may be relieved to know that automated database testing with CI/CD is very similar to automating tests on application code. The major difference is that this time, your tests are running queries instead of application code. The following steps found in testing application code also apply:

• Prepare your test environment
• Run your tests using a test runner like Selenium
• Check and validate your results
• Report your assertions

This is why a database tester’s best attribute is being able to craft SQL queries for every required test case.

Database testing tools

There are loads of database testing tools out there. The database testing tool you choose will depend on one or more of the following:

• Testing strategy (manual, automated or hybrid)
• Database type (SQL or No-SQL)
• Database vendor (MySQL, MSSQL, Oracle, or other)

Most database testing systems are comprised of more than just one tool. For example, Selenium can be used with TestNG for database testing in Java applications. Similarly, SeLite uses Selenium for testing SQLite databases while SQL Server comes bundled with tools for unit testing databases.

You can find a good list of database testing tools here. Make your choice based on the factors listed previously.

Conclusion

Database testing is yet another test-driven development process that assures application reliability. Data is at the core of all applications, and ensuring that users can trust your applications begins with getting your data right.

Happy Coding!