A QA’s Guide To Database Testing in 2023
Database testing consists of black box testing, white box testing, and ACID (atomicity, consistency, isolation, and durability). In this guide, I’ll give you definitions, how-to, and examples.
What Is Database Testing?
Database testing or backend testing or data testing, differs from its User-interface counterpart by not being at all concerned about the visual interface of the system software. It’s primary concern is checking that all the internal processes run properly and can quickly, accurately, and securely retrieve data.
Database testing tests the database schema, tables, and triggers of the database. It puts the database under stress and can include running complex queries to thoroughly test its capabilities and responsiveness. It also tests Database Management Systems (DBMS) such as Oracle and SQL Server.
Related Read: THE POSITIVE OUTCOMES OF NEGATIVE TESTING
What is Database Schema?
A database schema is a logical structure that defines the organization and relationship of data within a database. It outlines the blueprint of how data is stored, accessed, and manipulated in a DBMS. The schema describes the data types, constraints, and relationships between database entities, such as tables, views, indexes, and triggers.
The database schema is critical for ensuring the integrity and consistency of data within a database. It provides developers and database administrators with a framework to organize and structure data meaningfully. It also helps to ensure that the data stored in the database meets specific requirements, such as data validation, data security, and data accessibility. Organizations can use a database schema to ensure organization and optimization in their databases for efficient data management, improving business operations, and decision-making.
Why is Database Testing Important?
Database testing is important because
- Some bugs can only be found through database testing
- Certain usage conditions can only be database tested
- Database testing improves stability and security
- Database testing ensures consistency
Database Function Example
Imagine opening an online banking application on your phone (or actually open the online banking app on your phone). Now, move a small sum of money from your checking into your savings. When you’re done, think about all the things that had to happen behind the scenes in those few seconds.
- The app sent the transaction information to the database.
- None of your information (i.e. MONEY) got lost on the way.
- The app didn’t crash or fail to complete the transfer.
- The transaction happened securely.
- Your money is now sitting comfortably in your savings account (congratulations!)
All those functions happen within the database. The database testing process is important because if a database system critically fails, the system grinds to a halt. No information can be sent or retrieved or transferred or secured. The application may appear to work on the surface (you can navigate windows and prepare to make transactions) but nothing impactful will happen.
Everything looks fine on the outside, but internally it’s a disaster.
Database testing ensures no disaster. No fire, no halt and no missing money. It’s in everyone’s best interest that database testing is performed. So I’ve written a comprehensive guide to help you feel confident that when you’re done, everything has been thoroughly tested.
Database Testing Principles
The word ‘principles’ might sound more like why than how. Trust me, these are two important, practical concepts.
- ACID Properties
- Atomicity
- Consistency
- Isolation
- Durability
- Data Integrity
What are ACID Properties?
This is the safest version of acid you’ll ever use. No gloves required, the ACID properties stand for Atomicity, Consistency, Isolation, and Durability. Every single transaction in database testing must meet these principles.
A transaction is a group of tasks. Even a simple real-world transaction, like the bank transfer I mentioned previously, will involve several lower-order tasks. The ACID properties are there to confirm that each transaction is completed accurately, fully, and with integrity.
ACID Propeties
- Atomicity states that each transaction must be treated as an atomic unit. In non-molecular terms, it means every transaction must be completed in full or else it fails. Treating each transaction in such a way avoids the messy situation of a money transfer half completing. That just sounds stressful, doesn’t it? With atomicity, if the transaction is halfway completed when something goes wrong, the whole transaction fails. Money back. No stress.
- Consistency requires that the database remain consistent after a transaction. No transaction may negatively impact on any other data in the database. You depositing money in your banking account won’t withdraw any from someone else's account.
- Isolation prevents the crossing of streams. It ensures that, in the case of multiple transactions happening at once, each transaction is treated as if it's the only one happening in the database. Nobody gets their information mixed up with other people’s information.
- Durability requires the database to be durable enough to hold onto all the latest transactions, even if there is a system failure. Again, there are practical reasons for requiring such durability from a database. If you make a money transfer and the database goes down ten minutes later, how would you know you must make the transfer again? Having such strict requirements prevents confusion on the end users’ side.
Data Integrity
Data integrity is the practice of verifying that all the latest data is updated everywhere. There are four individual parts that combine to confirm that the data has integrity. For a high level of data integrity, a QA tester must confirm that:
- The data is verifiable
- The data is retrievable
- The data is accurate
- The data is complete
If the data doesn’t meet those four requirements, then it likely does not meet the standard of data integrity. Data management tools can help with this but ultimately it's up to you to ensure the highest data quality.
As Huw Price pointed out in The QA Lead Podcast episode titled Your Data Quality Sucks, ensuring that your systems are running on high-quality, secure data is rising in importance as more and more companies build up their own data banks.
Database Manipulation Language
A Database Manipulation Language (DML) is a programming language used to manipulate data within a database management system. DML is primarily used to perform tasks such as inserting, updating, deleting, and retrieving data from a database. In essence, DML provides a set of commands and functions that enable users to interact with data in a database, and to modify or retrieve that data according to specific criteria. DML is an essential component of any database system, allowing users to perform operations on data without understanding the database's underlying structure or technology.
DML commands are typically used in conjunction with a Database Definition Language (DDL), which is used to create and modify database objects such as tables, views, and indexes. DDL and DML form the core components of a database system, allowing users to create, modify, and manipulate data in a secure, efficient, and reliable way. The use of DML in database management enables organizations to store, manage, and analyze large amounts of data, making it a critical tool for businesses in today's data-driven world.
Types of Database Testing
Now that we’ve covered two of the core principles of database testing, we can move on to different testing types. You’ve likely heard of some of these approaches before. Remember, I did say that if you understood QA testing than you’d get the hang of database testing, no problem. Here I’ll explain how each of these concepts apply to database testing.
There are three types of database testing:
- Structural
- Functional
- Non-functional
Within those three types of database testing are subtypes. Don’t worry, I’ll explain those too.
Structural Testing
Structural testing validates elements within the data repository that are used for data storage. These elements are hidden from end users and happen entirely behind the scenes. Database testers will perform these tests by writing SQL queries.
Data Mapping Testing
Mapping data structures is the process of establishing connections between two different data models. Also referred to as schema testing, a data mapping test will validate the front end and the back end. They make sure the two are talking to each other properly.
For example, if you’re filling out a sign-up form on a website, proper data mapping will take the web form and transfer it to the database. Your name, email, and password will be stored where they should be. Data mapping testing confirms that process is working.
There are several data mapping tools that a QA tester will work with. A brief list of data mapping tools includes:
Database Table Testing
Table testing performs several checks on the data mapping structure. It checks that the fields in the front end and back end are compatible with each other. It validates the length of the database fields. And it confirms whether there are any unmapped database tables or columns that you need to address.
Database Server Validations
Server validations make sure the server configuration meets requirements. It also ensures anyone trying to access certain areas of the database server have proper authorization. This is an important step in keeping the database safe and secure for its users. Finally, the server validation makes sure the server can handle the maximum number of transactions at once.
For example, a server validation for a banking app would check to make sure the server could handle the financial transaction of a set number of users and that all users could only access their own information.
Functional Testing
We’ll put the fun back in functional testing. Really, we will. Functional testing is all about checking that the database meets the clients specifications, and that the end user actions are consistent with those requirements. This means there’s a lot of pretending to be the end user. Doesn’t that sound fun? I think it sounds fun.
Black Box Testing
Black box testing is a software testing strategy where the design of the software system being tested is unknown to the tester. Without intimate knowledge of the software’s design, the tester will approach the software with similar expectations as the end user.
Black box testing is performed during database testing because it finds bugs in common pathways that the end-user is likely to come across. It’s the ultimate test run as an end-user.
We covered black box testing extensively in our 9 Types of Software Testing Every QA Analyst Should Know guide.
White Box Testing
White box testing is the polar opposite of black box testing. In white box testing the QA member fully understands the internal structure and design of the software being tested.
They approach the test as an inspector. White box testing is sometimes called clear box testing because the tester observes the interactions between units as they test the software. Unlike black box testing, a white box tester is not nearly as concerned with the user experience.
Unit Testing
Unit testing is a software testing technique that involves testing individual units or components of an application in isolation from the rest of the system. This test type to test individual database objects such as tables, views, stored procedures, and functions. By testing these objects in isolation, developers can ensure that each object functions as intended.
Unit tests for databases typically involve writing automated tests that execute specific SQL commands against a database object or module and verify that the expected results are returned. For example, a unit test for a stored procedure might involve passing a set of input parameters to the procedure and verifying that the output is correct. By using unit tests in this way, developers can ensure that each database object performs as expected and that any changes to the database schema or application code do not introduce unintended consequences or errors.
Non-functional Testing
Non-functional testing doesn’t have a care in the world about the end user. Not that the end user isn’t important. They simply have other things to worry about. Non-functional tests check the database’s performance under load, stress, and looks for places to optimize performance.
Load Testing
Database load testing is a type of database performance testing which checks that user load won’t have a dramatically negative impact on database performance. The QA tester will run a bunch of load queries and run them over and over and over to simulate an active user base.
Stress Testing
Stress tests are load tests but stronger. They don’t want to see if the database slows down, they want to see where it can be broken. This is far more destructive. So, that’s fun.
Have you ever anxiously anticipated the release of a new video game or phone? If you have, you might be familiar with loading the website or opening the app to make your purchase and being met with unusually long loading screens, unexpected error codes, and failures to process your information. That right there is a database under stress.
It’s different from load testing because it puts the database through a sudden, unexpectedly high volume of traffic.
Database Automation Testing
Database automation testing has become more popular in recent years. Requiring the database to be manual testing would take too long, require too many people, and simply cost too much money.
There are many ways to perform automation testing. Here’s a list of some database management tools that offer testing features.
Testing a Database with API
An API (Application Programming Interface) is a set of protocols and standards that enable different software applications to communicate with each other. With database testing, an API can connect to a database and perform various operations such as inserting, updating, deleting, and retrieving data.
The relationship between an API and database testing is that APIs can be used to automate and streamline the testing process. By using an API to connect to a database, testers can automate tasks such as data insertion, updates, and deletions and perform queries to retrieve data and validate results. This can significantly reduce the time and effort required for manual testing and improve the accuracy and consistency of testing results.
Additional Resources
After reading this article, you should understand how to execute database testing. Of course, it’s impossible to cover absolutely everything in a single article. Here are two additional resources that might help you overcome specific problems.
- How to Regression Test a Relational Database
- How to Perform a Performance Test Against a SQL Server Instance
What Do You Think?
Being charged with testing a database can feel like a lot of responsibility. I’m confident that you can handle it. Do you feel more confident in your approach to database testing? Let me know in the comments!