Database Testing – Practical Tips and Insight on How to Test Database

The database is one of the inevitable parts of a software application these days. It does not matter at all whether it is the web, desktop or mobile, client-server or peer to peer, enterprise or individual business; the database is working everywhere at the backend. Similarly, whether it is healthcare or finance, leasing or retail, mailing application or controlling a spaceship; a database is always in action behind the scene.

Moreover, as the complexity of application increases, the need of stronger and secure database emerges. In the same way, for the applications with a high frequency of transactions (e.g. banking or finance application), the necessity of fully featured DB Tool is coupled. Nowadays, we have big data which is large and complex that the traditional databases can’t handle them.

Database Testing – Practical Tips and Insight on How to Test Database

Currently, several database tools are available in the market e.g. MS-Access2016, MS SQL Server 2016, SQL server 2008 R2, Oracle 12c, Oracle Financial, MySQL, PostgreSQL, DB2, Toad, Admirer, etc. These tools vary in cost, robustness, features, and security. Each of these DBs possesses its own benefits and drawbacks. One thing is certain; a business application must be built using one of these or other DB Tools.

Before I start digging into the main topic, let me comprehend the foreword. 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 user ‘Remove’ any record from the system, ‘Delete’ operation of DB is performed.

It does not matter at all, which DB is used and how the operation is performed. The end user has no concern if any join or subquery, trigger or stored-procedure, query or function was used to do what he wanted. But, the interesting thing is that any DB operation performed by the user is always one of the above four.

Database Testing

As a database tester one should be focusing on following DB testing activities:

What to test in database testing:

1) Ensure data mapping:

Data mapping is one of the key aspects in the database and it should be tested rigorously by every software tester.

Make sure that the mapping between different forms or screens of AUT and its DB is not only accurate but also per the design documents (SRS/BRS) or code. Basically, you need to validate the mapping between every front-end field with its corresponding backend database field.

For all CRUD operations, verify that respective tables and records are updated when the user clicks ‘Save’, ‘Update’, ‘Search’ or ‘Delete’ from GUI of the application.

In short, what you need to verify here is:

  • Table mapping, column mapping, and data type mapping.
  • Lookup data mapping.
  • Correct CRUD operation is invoked for every user action at UI.
  • CRUD operation is successful.

2) Ensure ACID Properties of Transactions:

ACID properties of DB Transactions refer to the ‘Atomicity’, ‘Consistency’, ‘Isolation’ and ‘Durability’. Proper testing of these four properties must be done during the DB testing activity. You need to verify that every single transaction satisfies the ACID properties of the database.

ACID Properties

Let us take a simple example through below SQL code:

 CREATE TABLE acidtest (A INTEGER, B INTEGER, CHECK (A + B = 100));

The acid test table will have two columns – A & B. There is an integrity constraint that the sum of values in A and B should always be 100. Atomicity test will ensure any transaction performed on this table is all or none i.e. no records are updated if any step of the transaction is failed. Consistency test will ensure that whenever the value in column A or B is updated, the sum always remains 100. It won’t allow insertion/deletion/update in A or B if the total sum is anything other than 100. Isolation test will ensure that if two transactions are happening at the same time and trying to modify the data of the acidtest table, then these tractions are executing in isolation. Durability test will ensure that once a transaction over this table has been committed, it will remain so, even in the event of power loss, crashes, or errors.

This area demands more rigorous, thorough and keen testing if your application is using the distributed database.

3) Ensure Data Integrity:

Consider that different modules (i.e. screens or forms) of application use the same data in different ways and perform all the CRUD operations on the data. In that case, make it sure that the latest state of data is reflected everywhere. The system must show the updated and most recent values or the status of such shared data on all the forms and screens. This is called as Data Integrity.

di

You can mainly perform following tests for validating data integrity in a database:

  • Check if all the triggers are in place to update reference table records.
  • Check if any incorrect/invalid data exists in the major columns of each table.
  • Try to insert wrong data in tables and observe if any failure occurs.
  • Check what happens if you try to insert a child before inserting its parent (try to play with Primary and foreign keys).
  • Test if any failure occurs if you delete a record that is still referenced by data in any other table.
  • Check if replicated servers and databases are in sync.

4) Ensure Accuracy of implemented Business Rules:

Today, databases are not meant only to store the records. In fact, DBs have been evolved into extremely powerful tools that provide ample support to the developers to implement the business logic at the DB level. Some simple examples of powerful features of DBs are ‘Referential Integrity’, relational constraints, triggers and stored procedures. So, using these and many other features offered by DBs, developers implement the business logic at the DB level. The tester must ensure that the implemented business logic is correct and works accurately.

Above points describe the four most important ‘What To’ of database testing. Now, I will put some light on ‘How To’ of DB Testing. But, first, I feel it better to explicitly mention an important point here – DB Testing is a business-critical task, and it should never be assigned to a fresh or inexperienced resource without proper training.

How to Test Database:

1. Create your own Queries

To test the DB properly and accurately, first, a tester should have very good knowledge of SQL and specially DML (Data Manipulation Language) statements. Secondly, the tester should acquire a good understanding of internal DB structure of AUT. If these two pre-requisites are fulfilled, then the tester is ready to test DB with complete confidence. (S)He will perform any CRUD operation from the UI of application and will verify the result using SQL query. If you are using SQL server then you can make use of SQL Query Analyzer for writing queries, executing them and retrieving results.

This is the best and robust way of DB testing especially for applications with a small to medium level of complexity. Yet, the two pre-requisites described are necessary. Otherwise, this way of DB testing cannot be adopted by the tester.

Moreover, if the application is very complex then it may be hard or impossible for the tester to write all the needed SQL queries himself or herself. However, for some complex queries, the tester may get help from the developer too. I always recommend this method for the testers because it does not only give them the confidence on the testing they perform but also enhance their SQL skill.

2. Observe data table by table

If the tester is not good in SQL, then he or she may verify the result of CRUD operation (performed using GUI of the application)  by manually viewing the tables (relations) of DB. Yet, this way may be a bit tedious and cumbersome especially when the DB and tables have a large amount of data.

Similarly, this way of DB testing may be extremely difficult for tester if the data to be verified belongs to multiple tables. This way of DB testing also requires at least good knowledge of table structures of AUT.

3. Get query from developer

This is the simplest way for the tester to test the DB. Perform any CRUD operation from GUI and verify its impacts by executing the respective SQL query obtained from the developer. It neither requires a good knowledge of SQL nor requires a good knowledge of application’s DB structure.

So, this method seems easy and good choice for testing DB. But, its drawback is havoc. What if the query given by the developer is semantically wrong or does not fulfil the user’s requirement correctly? In this situation, the client will report the issue and will demand its fix. Furthermore, in worst cases, the client may refuse to accept the application.

4. Make use of Database testing tools to turn out the process easier.

Nowadays, several tools are available that aids in the process of database testing. You should choose the correct tool as per your application and make the best use of it to do the database testing for the application.

For more info on database testing tools, check out the Useful Reads section at the end of this article.

Conclusion:

The database is the core and critical part of almost every software application. So, DB testing of an application demands keen attention, good SQL skills, proper knowledge of DB structure of AUT and proper training.

To have the confident test report of this activity, this task should be assigned to a resource with all the four qualities stated above. Otherwise, shipment time surprises, bugs identification by the client, improper or unintended application’s behavior or even wrong outputs of business-critical tasks are more likely to be observed. Get this task done by most suitable resources and pay it the well-deserved attention.

Useful Reads:

This is a guest post by Rizwan Jafri, a Software Testing expert. If you want to post your article on this blog, check out the guidelines here.

If you have queries related to database testing, database testing interview questions, database testing tools or concepts please put it below. We will be happy to address all the queries in comments or part II of database testing article.

70 Comments on “Database Testing – Practical Tips and Insight on How to Test Database

  1. That’s perfect man.

    Very informative and clear .
    U can be a good technical author.

    Thanks again :)

  2. I love the post i am becoming professional tester by reading this post fantastic

  3. This is very informative article.
    Could you please explain on how to prepare test cases, scenarios and test report for DB Testing

  4. I want to know about the database automation testing tools either open source or commercial tools.

  5. Thanks Vijay for this valuable article.
    I am a regular reader and also have bought a book.
    Can you provide me any document which you have for the Quality Center not ALM (With Practical example) on my mail id.
    I have already mail to you for the same.
    Please provide me because there is no book available in market.
    Thanks,
    Ashish Kulkarni
    ashish.kulkarni2@wns.com

  6. Hi,

    Nice article.

    xyz

  7. What is Alternate Flow. Can u explain me about it

  8. What you mean AUT?

  9. Whether it is Application Under Test?

    Anybody knows pls reply immediately.

  10. It was very good info.

    Thank you :)

  11. Nice article very useful information

  12. Nice one

  13. can u give more information on this.

  14. This is simple and superb! A great intro to what SQL testing is all about and what the prerequisites of it are. Thank you very much!

  15. very useful

  16. Nice Article, Thanks for Sharing Tips.

  17. How we can test user data and transaction security in payment gateway?

  18. Please suggest best ETL automation tool.
    Thanks in advance.
    Ketan

  19. Thanks for sharing this information

Leave a Comment