Database testing consists of black box testing, white box testing, and ACID. If you’re interested in learning about these things, keep reading. I’ll give you definitions, how-to, and examples.
Jump to sections or read straight through:
- What Is Database Testing?
- Why is Database Testing Important?
- Database Testing Principles
- Types of Database Testing
- Database Automation Testing
What Is Database Testing?
Database testing is also known as backend testing or data testing. It 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 schema, tables, 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 the Database Management Systems (DBMS) such as Oracle and SQL Server.
Related Read: THE POSITIVE OUTCOMES OF NEGATIVE TESTING
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. Database testing is important because if a database 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
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.
- 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 makes sure 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 very 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 that you have to make the transfer all over 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.
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.
Types of Database Testing
Now that we’ve covered two of the core principles of database testing, we can move on to some different types of testing. It’s likely you’ve 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 need to be addressed.
Database Server Validations
Server validations make sure the server is configured according to requirements. It also makes sure 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 referred to as 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.
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 short list of the best database testing tools available in 2019:
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!