top of page
  • Writer's pictureCraig Risi

Diving deeper into database testing – How To Test

In the last article, we looked at the importance of testing specific areas in a database and what you need to test in them. In part 2, I want to provide a few more specifics on the “how-to” part to help make testing these areas a whole lot more practical for you. Ultimately, there is a lot that is unique to every data source and its structure, so will try and keep things as lightweight as possible, but hopefully, the following guidelines will help to make testing your databases a whole lot easier.

Database Tester Should Focus on the following Testing Activities:

#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 your application and its DB is not only accurate but also per the design documents or code (wherever you get your schematic designs from). Basically, the point of testing here is to validate the mapping between every front-end fields with its corresponding backend database fields.

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.

What you need to verify:

To effectively ensure you have achieved the above, you will want to test the following or your different fields.

  • 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'. These four properties should be tested for in each database transaction you test.

Below a simple example of how this could be done. It’s a very basic example of a simple table. No doubt your actual tables will be far more complex, but this guideline should provide a foundation in which you can expand to for your different tables:


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 ACID test 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 and thorough testing if your application is using the distributed database., as this will only escalate the need for data consistency and conformity across a diverse set of data.

#3) Ensure Data Integrity

Data should look and read the same whether it is displayed in the UI or stored or shared across different databases and systems. Considering that different modules in any system will use the same data in different ways and perform all the CRUD operations on the data, it's important to ensure that not only is data stored correctly throughout, but that the latest state of the data is reflected everywhere.

This is the purpose of data integrity testing and why you need to test it across your entire system in the following areas:

  • 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 the Accuracy of the implemented Business Rules:

Databases have evolved into extremely powerful tools that don’t just store data but provide a platform for developers to implement business logic at the DB level. This is useful as it helps ensure referential integrity across the entire system while also reducing impact elsewhere in the system, making applications more scalable and performant if the database features are designed correctly.

Examples of certain features built into dates that you would want to test include ‘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.

Know your SQL

To effectively test a database you do need to be comfortable with SQL. While you can get by executing things via the application UI and simply just checking databases on simple data structures this is not a very efficient solution. Not only does it not aid your automation efforts and allow you to test your databases separately to the other aspects of your system, but it just doesn’t work for many decently complex databases. So, learning SQL and becoming familiar with the different commands is crucial.

Usually, SQL queries are used to develop the tests. The most commonly used command is typically your “Select” statement:

Select * from <tablename> where <condition>

However, outside of your SELECT statement, there are many other commands you will want to master for your testing efforts, with the most commonly used group in the following way:

Data Definition language Uses CREATE, ALTER, RENAME, DROP and TRUNCATE to handle tables (and indexes).

Data Manipulation language Includes statements to add, update and delete records.

Data control language: Deals with giving authorization to users for manipulation and access to the data. Grant and Revoke are the two statements used.

Grant syntax:

Grant select/update

On <table name>

To <user id1, user id2…useridn>;

Revoke syntax:


on <table name>

from<user id1, user id2…useridn>;

If the application is very complex, then it may be hard or impossible for the tester to write all the required SQL queries. For complex queries, you can seek help from developers, though you would ideally want to be able to write these yourself.

Database testing is a major part of ensuring effective quality in any application and hopefully, it’s something you as a tester will try and focus sufficient attention on. Don’t relegate your SQL skills to something that is simply supplementary, but make them a powerful weapon in your arsenal, so that you can work with all the different aspects of your database and ensure its quality.  



Thanks for subscribing!

bottom of page