No matter your software architecture, the Database is one of the inevitable parts of a Software Application. Data is everything in the information age and even though many people are trying to find ways to alter the way and shape we use data, it will rain a vital part of software development for many years to come and likely only get even more important as we grow and deepen our usage of big data and machine learning.
Whether it is a web, desktop or mobile, client-server, peer to peer, enterprise or individual system the Database is required everywhere at the backend and the same applies across every industry imaginable. And the bigger the complexity of application increases, the more the need for secure and reliable storage of your data increases. So, no matter your organisation or architecture you need to have a database of some sorts and because data is the core information by which we make decisions, its vitally important to ensure that we test our databases effectively
And when it comes to testing, I guess the first place the conversation often turns to is around the tools that we use for testing. There are several different types of database tools on the market like MS-Access, MS SQL Server, SQL Server, Oracle, Oracle Financial, MySQL, PostgreSQL, DB2, Toad, Admirer, etc. Each may differ in cost, robustness, features, and security, but the fundamentals of testing them remains the same. Most of them also come equipped with everything you will need to be able to test your database and the accuracy and consistency of your data, correctly.
However, with systems so reliant on data storage and databases in generally, I will spend more than one article going through the topic. First looking at why we should test them, then looking at where we should test them, followed by a deeper look into how we should test them.
Why Test the Database?
The reason why I decided to do a why section here is because so many companies I speak to spend an inordinate amount of simply planning their testing strategies for their APIs or Frontends and put less thought into their database testing, despite this being a place where so much goes wrong and arguably, has the biggest impact on customers if things go wrong.
#1) Data Mapping
In software systems, data often travels back and forth from the UI (user interface) to the backend DB and vice versa. So, these are some aspects to watch for:
Checking whether the fields in the UI/frontend forms are mapped consistently with the corresponding fields in the DB table. Typically, this mapping information is defined in the requirements documents.
Whenever a certain action is performed at the front end of an application, a corresponding CRUD (Create, Retrieve, Update and Delete) action gets invoked at the back end. A tester will have to check if the right action is invoked and whether the invoked action in itself is successful or not.
#2) ACID Properties Validation
Atomicity, Consistency, Isolation, and Durability. Every transaction a DB performs has to adhere to these four properties. These should be checked in the testing process to ensure they are followed correctly.
Atomicity means that a transaction either fails or passes. This means that even if a single part of the transaction fails- it means that the entire transaction has failed. Usually, this is called the “all-or-nothing” rule.
Consistency: A transaction will always result in a valid state of the DB
Isolation: If there are multiple transactions and they are executed all at once, the result/state of the DB should be the same as if they were executed one after the other.
Durability: Once a transaction is done and committed, no external factors like power loss or crash should be able to change it
#3) Data Integrity
For any of the CRUD Operations, the updated and most recent values/status of shared data should appear on all the forms and screens. The value should not be updated on one screen and display an older value on another one.
When the application is under execution, the end-user mainly utilizes the ‘CRUD' operations facilitated by the DB Tool.
C: Create – When user ‘Save' any new transaction, ‘Create' operation is performed.
R: Retrieve – When user ‘Search’ or ‘View’ any saved transaction, ‘Retrieve' operation is performed.
U: Update – When user ‘Edit’ or ‘Modify’ an existing record, the ‘Update' operation of DB is performed.
D: Delete – When a user ‘Remove’ any record from the system, ‘Delete’ operation of DB is performed.
Any database operation performed by the end-user is always one of the above four.
So, DB test cases are needed for checking the data in all the places it appears to see if it is consistently the same.
#4) Business Rule Conformity
More complexity in Databases means more complicated components like relational constraints, triggers, stored procedures, etc. This complexity simply places more importance on testers getting involved and ensuring all of these areas are working correctly.
What To Test
So, we know why different database areas need to be tested, but there is perhaps more specifics required to understand some of the actual functional areas that require testing and what you should specifically look at in general with regards to testing specific database operations. In this section, I will get into more specifics of what functional tests are typically expected to be un on your database layer.
#1) Transactions
Transactions are essentially the execution of a SQL statement or command to your database which will alter your data in some form or another, where it be inserting, deleting or modifying the data or structure of the database. What is important though is that each of these executions support the aforementioned ACID properties.
These are the statements commonly used:
BEGIN TRANSACTION TRANSACTION#
END TRANSACTION TRANSACTION#
The Rollback statement ensures that the database remains in a consistent state.
ROLLBACK TRANSACTION#
The rollback statement is something that we often don’t place enough emphasis on, but is just as important as any other transaction statement, as the ability to reverse any changes is equally s important as executing them.
After these statements are executed, use a Select to make sure the changes have been reflected.
SELECT <items involved in transaction> FROM TABLENAME <tables which involve the transactions>
This is needed to verify that he changes in the transaction have indeed taken place. What is important here is to not do too much of a blanket select statement like ‘Select *’ as this can take too long to execute and take longer to verify. Your select statement should preferably be just as optimised as your transactional statement.
#2) Database Schemas
A Database Schema is nothing more than a formal definition of how the data is going to be organized inside a DB. You need to ensure this looks and is shaped correctly, even without data. To test it, use the following two steps:
Identify the Requirements based on which the Database operates.
These are specifics within the schema that you can easily test around and ensure consistency through the different tables in a database.
· Primary keys need to be created before any other fields are created.
· Foreign keys should be completely indexed for easy retrieval and search.
· Field names starting or ending with certain characters.
· Fields with a constraint that certain values can or cannot be inserted.
Use one of the following methods according to the relevance:
· SQL Query DESC<table name> to validate the schema.
· Regular expressions for validating the names of the individual fields and their values
· Tools like SchemaCrawler to better visualise the schema of the database.
The good news is that for this there are tools that can help, making the testing approach a lot easier. This means that as long as you have an idea on what to test for, the tools will be able to quickly provide you with the needed information and often even advise you when things are not up to standard.
#3) Triggers
When a certain event takes place on a certain table, a piece of code (a trigger) can be auto-instructed to be executed.
For Example, a new student joined a school. The student is taking 2 classes: math and science. The student is added to the “student table”. A Trigger could add the student to the corresponding subject tables once he is added to the student table.
The common method to test is to execute the SQL query embedded in the Trigger independently first and record the result. Follow this up with executing the Trigger as a whole and then compare the results.
These are tested in both the Black-box and White-box testing phases.
White box testing:
Stubs and Drivers are used to insert or update or delete data that would result in the trigger being invoked. The basic idea is to just test the DB alone even before the integration with the front end (UI) is made.
Black box testing:
a) Since the UI and DB, integration is now available; we can Insert/Delete/Update data from the front end in a way that the Trigger gets invoked. Following that, Select statements can be used to retrieve the DB data to see if the Trigger was successful in performing the intended operation.
b) The second way to test this is to directly load the data that would invoke the Trigger and see if it works as intended.
#4) Stored Procedures
Stored Procedures are similar to user-defined functions. They are essentially prepared SQL code that can be saved and reused repeatedly by other functions that call it. You can also pass parameters and other variables into these statements and they can behave differently based on the inputted data/variable. Think of them like a modularised SQL statement. You can These can be invoked by Call Procedure/Execute Procedure statements and the output is usually in the form of result sets.
#5) Field Constraints
The Default value, Unique value, and Foreign key:
Much like a lot of the elements that wen need to test, different fields in a database have specific constraints that determine what they are. And while we shouldn’t need to tirelessly test these if we know the structure of the database, we should ensure our system behave appropriately when inputs that fall outside of these constraints is inputted. If you don’t have access to these specific structures and want to test these, then I would recommend the following:
Validate the results with a SQL Query.
Checking the default value for a certain field is quite simple. It is part of business rule validation. You can do it manually or you can use tools For the Foreign Key constraint validation use data loads that directly input data that violate the constraint and see if the application restricts them or not. Along with the back-end data load, perform the front-end UI operations too in a way that will violate the constraints and see if the relevant error is displayed.
Hopefully, it's clear that you need to take your database testing seriously and now have an idea of what to look out for. In my next article, I will go into further details on what areas specifically testers should focus on and how they can test these areas correctly. Data is the lifeblood of many a company and so hopefully by placing greater emphasis on ensuring it is tested correctly, it's one area of your software architecture that you can keep working as expected.