1. Introduction
The purpose of this document is to explain the importance of having a database testing strategy. Database testing is a means to validate the data stored in the database, objects controlling data, and the functionality wrapped around it. The databases use objects to manage data like tables for storage, views for representation, and functions/triggers for manipulation.
2. Areas to Cover
Testing a database requires checking the following areas are working as expected:
· Database objects which include tables, views and stored procedures.
· Validation of data being entered and getting stored in the database.
· Making sure the system is honouring the constraints and data changes (insert/delete/update) are reflecting correctly.
· Ensure the system can execute end to end database transactions and support concurrency.
· Testing of database migration to ensure compatibility across different versions.
· Verify performance of database indices, triggers, and procedures.
· Security compliance testing to rule out any unauthorized access or any threats to data.
3. Database Validations
Testing a database involves 4 validations:
· Data Mapping
· ACID (Atomicity, Consistency, Isolation, Durability) properties validation
· Data Integrity
· Business Rule Conformance
4. Data Mapping in Database testing
Data mapping is a part of the database testing scope. It focuses on validating the data traversing back and forth from the application to the backend database. A software tester can look at it with the following two aspects.
He can check whether the application’s user interface or the input screen has a one-to-one mapping with the target table in the database. This type of information is usually available in software design documents.
Whenever the user submits a form on the application UI, it triggers a CRUD (Create/Retrieve/Update/Delete) event at the backend. So here the tester should ascertain if the right event gets fired and finished successfully or not.
5. ACID Properties Validation in Database Testing
ACID is an acronym for Atomicity, Consistency, Isolation, and Durability. It refers to confirming the four properties (as mentioned) against each database transactions.
· Atomicity – It means that all Database Transactions are atomic. They can end in one of the two i.e. Success or Failure.
· Consistency – It indicates that the database state will remain valid after the transaction gets completed.
· Isolation – Multiple transactions shall run without impacting each other and won’t hinder the database state.
· Durability – Committing a transaction shall preserve the change and will not lose it due to any power loss or crash later.
6. Steps Involved in Database Testing
For the successful testing of a database, we need to adopt a structured approach. And a tester should have at least a basic level of understanding of database concepts like SQL commands including DDL (Create/Drop), DML (Insert/Delete/Update), and DCL (Grant/Revoke).
· DDL => Data Definition Language.
· DML => Data Manipulation Language.
· DCL => Data Control Language.
Now, we are laying down the steps to carry out the testing of a database.
7. Testing Pre-Requisites
First of all, the tester has to create the setup for testing the database. He can do this by cloning a production database or creating a stand-alone database using demo data. He should keep in mind the following points.
The system running the database (Physical or virtual) should have the same hardware characteristics as does the production system.
The database software (Oracle/SQL Server/MySQL or any) should have the same version as running on the production system database.
8. Execute the tests
A software tester can run the tests from the application UI. Or he can write SQL scripts to invoke the CRUD actions. He should monitor the operations, track database changes and verify whether they are ending with success or not.
Better come with both positive and negative tests covering views, triggers, and stored procedures. Make sure all the functions/blocks are getting tested.
Verify Test Status
After running the tests, the tester should analyze all failures or tests that get ignored from execution. It’s wise to come up with a summary of all types of cases and then focus on failures and skipped scenarios.
Validate Results
In this step, we’ll concentrate if the tests approached the right action after executing the SQL queries or not. Also, he has to ensure the data changes affected the right tables and columns.
Consolidate And Publish Report
The final step is to gather all the results and capture all of them into a report. You need to share this data with all stakeholders. Please make sure that all failures or skipped tests have given proper reasons.
9. Writing Test Cases For Database Testing
A software tester should prefer writing a separate set of test scenarios and test cases for database testing. That’s how you can make sure your test cases are independent and won’t mix with the UI related use cases.
Database testing is a type of grey-box testing. And if you don’t’ know, then note that it’s a mixture of both black box & white box testing methods. So, the tester would need to know about the internal functioning of the application and also about the database structure used.
At times, an application could be using multiple databases, so you should be aware of how they relate to each other. Also, make a data mapping sheet which should tell you the UI actions and the tables (and columns) getting affected when the action takes place. For example, sometimes it would intend adding a row in one table and update another row in a different table. While writing the test cases, you need to keep an eye on all of these actions to successfully test a database.
And as we’ve said earlier in this post that you need good SQL skills for testing databases. It will help you write efficient SQL queries that extract data from a table without returning thousands of rows from the database table.
10. Database Testing Guidelines
You can use the below guidelines to prepare good test cases for database testing.
· Get clarity on the functional requirements.
· Make a list of all the tables used and find out-
o Joins used between tables
o Cursors used, triggers used
o Stored procedures used
o Input/Output parameters used.
· Create test cases with multiple input data and try to cover all the paths.
11. Database Testing Checklist
After writing the test cases, refer to the following checklist and see if any information is missing.
· The tests are taking care of all the backend tables used for each requirement.
· If the application/database is using status flags, then tests should verify each of them.
· Tests cover the triggers/stored procedures with combinations of input and expected output parameters.
· Tables might have columns with default values, tests should check them too.
12. Possible Test Scenarios for Database Testing
General Test Scenarios
General tests should capture the following scenarios.
· Name of the database.
· Name of the log file.
· Disk space allocation for databases.
· Names of all tables, columns, and their types.
· Null value checks.
· Verify keys (primary/foreign), indexes, and data types of columns used.
Functional Test Scenarios
· Identify events causing the triggers
· Functions inside stored procedures and possible combinations.
· End to end data flow starting from the front end to the backend.
Non-Functional Test Scenarios
· Create test scripts for major features and use them for regression testing at regular intervals.
· Write tests that track errors (OOM/deadlocks/exceptions) in log files.
· Change data in backend tables and watch the effect on the frontend.
· Insert invalid values from the backend and observe the effect.
Automated Unit Tests
· Check whether dates and times are within the acceptable range.
· Data Map varchar fields that have dropdown menus in the front end.
· Table’s Primary/Foreign key (might help us to find which tables are related and which are heaps).
· Check for Null values.
· Indexes (Clustered/Non-clustered).
· Reorganise indexes
o Less than 10% fragmentation
§ No defragmentation is required
o 10% - 30 %
§ The index should be reorganised
o More than 30%
§ The index should be rebuilt
· Row Counts of Tables
· For ETL Testing we could use our Monitoring Reports to compare historic data
13. Tools That we can use for Automated Testing
There are number of reasons to choose tSQLt as mentioned at tsqlt.org for database unit-testing and some of them are as follows:
You can write tests in T-SQL, as tSQLt itself is written in SQL so no more context switching from SQL to any other tool for testing since you can create and run tests staying within SQL. What a time saver!
Tests can be grouped and different groups of tests can run independently. No need to spend time in an advanced search to find out your lonely unit test lost in piles of tests, just locate the group where it is present and you are done.
Most importantly it provides true isolation for objects under test which is mandatory for a solid database unit-testing. For example if we are unit testing a stored procedure called updateBookStock() which calls another stored procedure addLog() within the main procedure (to log the information about the user who is updating the stock), then unit-test for updateBookStock() written in tSQLt is strictly going to test the main procedure without bothering to test addLog() since the purpose of the test is to go for a single unit.
The tests run in Transactions there by saving the time to write extra clean up scripts as the objects will automatically be cleaned up.
The tSQLt testing framework can integrate test cases with continuous integration servers there by becoming part of CI/CD work flow.
This tSQLt testing framework facilitates advanced database unit testing framework such as cross-database objects testing and offer much more than traditional testing frameworks.
How is tSQLt Testing Framework Cost-Effective?
Apart from third party tools that use tSQLt as an underlying unit-testing framework, tSQLt itself is an open-source project which also means it is free to use commercially.
So, even budget constraints cannot be in your way if you wish to start using tSQLt for your database unit testing.
Obviously, you may not get the fancy Graphical User Interface and ease of use with many other features that are offered by third party tools which use tSQLt as their base, but technically there is nothing that stops you using tSQLt directly just like the way you use SQL.
It also depends on your level of expertise, and again most importantly budget, so if you do not have enough budget to buy third party tools for database unit testing you can still have luxury of using tSQLt without compromising the quality and standards, but some level of expertise is expected.
_edited_edited.jpg)
Comentarios