The Big Bad Guide on Database Testing

The process of checking data integrity and consistency of the database is called database testing. Ultimately, it aims to create complex queries to check database responsiveness to load/stress putting schema, tables, triggers, stored procedures, etc., under test. 

The Importance of DB Testing

DB testing importance comes from the database being an essential part of a software application. It is working at backend regardless if it is desktop or web, client-server or peer-to-peer, enterprise or individual business. The database is always in action.

DB Testing Tools

For database testing tools to be useful, they need to cover all tiers of database testing, including the business, data access, UI, and the database itself. These tools have to be reliable, user-friendly and provide great performance. 

When choosing the tool to test your database, consider the following features:

• DB type; 
• DB size;
• Testers skills/experience;
• Testing methodology;
• Testing purpose.

DB testing can be a complicated and costly process. Choose the tool that suits your specific interests best. Here are some of them for you to consider.

Navicat 

Navicat provides an extensive set of features, allowing easier and faster database administration. Some of its features include Data Modeling, Data/Structure Synchronization, Code Completion, Report Builder, the Import/Export Wizard, Batch Job Scheduler, Query Builder, HTTP/SSH Tunnel Connection, Backup, and more.

SQL Server Management Studio

SQL Server Management Studio is designed for configuring, managing, and administering all components within Microsoft SQL Server. Apart from other things, the tool can be used to observe/analyze query plans and optimize DB performance. With its help, you can create a new database, apply changes to an existing database schema by adding or modifying tables and indexes and analyze performance. 

Smartbear TestComplete

Smartbear TestComplete offers a set of program objects that you can use to connect with a database, obtain its tables, execute queries, and perform other testing actions. The tool also provides DB table checkpoints to compare stored values in a database with a baseline copy stored in TestComplete’s project. 

RedGate

RedGate SQL Test is a unit test add-in for SQL Server Management Studio. It allows writing and performing unit tests in SQL Server Management Studio instantly, bypassing a time-consuming and complicated setup process. The tool is built to let you detect defects early in the development process and make ongoing integration.

tSQLt

tSQLt is the DB testing unit framework for SQL Server. It allows the implementation of unit tests. You won’t have to switch between different tools to create your code and your unit tests. The tool offers the following benefits:

Independent tests with minimum cleanup work;

Allows organizing the tests and use common setup methods;

Makes the integration with a continuous integration tool much easier;

Allows the isolation of the code that is under test.

CRUD

When the application is under execution, the end user mainly utilizes CRUD operations the DB Tool facilitates.

Create

Retrieve

Update

Delete

Ultimately, any DB operation the user performs is one of the above CRUD points.

Why Test the DB?

Database testing allows minimizing risks when the system is put into commercial operation. You can check in advance the correctness and safety of the database.

In the process of testing the database, the operation of the application database is checked for compliance with functional and non-functional requirements. Applications that include a database in their architecture require a database testing procedure, for example, corporate information systems, mobile and web applications. Besides, the need for a stronger and secure database increases in direct proportion to the complexity of the application. 

What to Check When DB Testing

1) Data mapping.

At this stage, you have to ensure the mapping between different forms or screens of AUT and that the Relations of its DB is correct and matches the design documents. Verify that respective tables and records are updated when the user clicks ‘Save’, ‘Search’, ‘Edit’ or ‘Delete’ for all CRUD operations.

2) ACID properties validation.

ACID properties are the ‘Atomicity’, ‘Consistency’, ‘Isolation’ and ‘Durability’. Their accurate testing occurs during the DB testing activity. It requires in-depth, detailed and keen testing in the process of database distribution.

3) Data Integrity.

Assume that different modules including screens or forms of application use the same data in different ways and perform CRUD operations. Ensure that the latest state of data is reflected everywhere. Data integrity implies that the system must show the most recent and updated values or the status of the shared data on all the screens and forms. 

4) Ensure Accuracy of implemented Business Rules.

Databases are not only for records storage. They are a powerful tool that assists the developers in implementing the business logic at the DB level. The powerful features of DBs include referential integrity, triggers, relational constraints, and stored procedures. All of these and many other features offered by DBs allow developers implementing the business logic on DB level. When testing, they must ensure that the business logic is accurate and works right.

Database Testing Techniques

Here are the most common techniques of Database Testing.

Transactions

In transactions testing ensure that they satisfy the ACID properties.

Here are the statements that are commonly used:

BEGIN TRANSACTION#

COMMIT TRANSACTION#

The Rollback statement is to make sure that the database is still consistent.

ROLLBACK TRANSACTION#

After the execution of those statements, use a ‘Select’ to see that the changes were reflected.

SELECT * FROM TABLENAME <tables that involve transactions>

Schema Testing

It includes testing each object in the Scheme:

• Databases and devices check;
• Database name check;
• Device data, device registration, and device reset check;
• The space for each database check;
• Database settings check.
• Tables check

Check the data below to see the differences between the actual and application settings.

• The name of all tables in the database;
• Column names for each table;
• Types of columns for each table;
• NULL is checked or not;
• Regardless of whether a table column is bound by default;
• Make rules for the correction of table names and access privileges.

Key and Indexes

• Check the key and indexes in each table:
• The primary key for each table;
• Foreign keys for each table;
• Data types between a foreign key column and a column in another table. Clustered or non-clustered, unique or non-unique indexes.

Stored procedures

It includes checking whether the stored procedure is defined and the output results are compared. The following items are checked:

• The name of the stored procedure;
• Parameter names, parameter types, etc;
• Output signal. Zero lines are executed or only a few records are retrieved;
• What is the function of the stored procedure and what should the stored procedure not do;
• Sending selective input requests to verify that the stored procedure stores the correct data;

Parameters of the stored procedure. Generate the stored procedure with the boundary data and valid data. Make each parameter invalid once and perform the procedure.

Check the values ​​returned by the stored procedure. In the event of a failure, a non-zero value must be returned;

Check error messages, making changes so that the stored procedure does not work and generates every error message at least once. Check all exception scripts if a predefined error message is missing.

Triggers

• The tester should perform the following tasks:
• Make sure the trigger name is correct;
• Confirm the trigger if it is created for a specific column in the table;
• Check for trigger updates;
• Update the entry with valid data;
• Update the entry with invalid data and cover every startup error;
• Update the record when it still refers to a row in another table;
• Provide transaction rollbacks when a failure occurs;
• Find out when a trigger should not rollback transactions.

 Field constraints

• Check default value, unique value, and foreign key by:
• Performing a front-end operation that exercises the database object condition;
• Using the SQL Query to validate the results.

Default value check for a particular field is easy and is a part of business rule validation. It can be done manually or with the help of tools like QTP. When choosing to do it manually, you might perform an action that will add the value of the field from the front end and see if it leads to an error.

Stress testing

Stress testing includes getting a list of basic database functions and associated stored procedures. Follow these instructions for stress testing:

Write test scripts to try these functions, and each function must be tested at least once in a full cycle.

Run test scripts over and over for a period of time.

Check log files to check for any deadlocks, memory failure, data corruption, etc.

Performance Testing

If your database does not have any data issues or errors, you can check the performance of the system. Poor system performance can be found in the test by checking the following parameters:

• System performance;
• Functions;
• Timing;
• Access load.
  

DB Testing Step-by-step

No matter what method you choose, the general process of database testing is the following: 1) Have the environment ready.2) Do the test.3) Check the result.4) Validate based on the expected output.5) Report the findings.

DB Testing Tips

1. Writing your own SQL queries

To check the database correctly and accurately, the tester must have excellent knowledge of SQL and DML (Data Manipulation Language). Besides, the tester should have a good understanding of the internal structure of the database to test it. The tester will perform CRUD operations from the user interface of the application and check the results of the execution using SQL queries.

Writing SQL queries is the best and most reliable way of DB testing, especially for apps with a low and medium level of complexity. However, the requirements for the tester mentioned above have to be met. Otherwise, this method would not suit you.

If the application is of high complexity, it will be hard or even impossible for the tester to write all the necessary SQL queries single-handedly. In such a case, the tester may contact the developer for help.

The method of writing SQL queries not only gives confidence that the testing is done qualitatively but also improves the skill of writing SQL queries.

2. Viewing tables data

If the tester does not know SQL, he can verify the result of CRUD operations using the application's GUI, by viewing the tables of the database. This method of database checking requires excellent knowledge of the structure of the tables and can be a bit tedious and bulky, especially when the database and tables contain a large amount of data.

In addition, this method of DB checking can be very complicated for testers if the data to be checked is stored in multiple tables.

3. Consider using DB automation testing tools

There are several tools available for data testing process. You should choose the correct tool as per your needs and make the best use of it.

4. Developer assistance

This is the easiest way. The tester performs CRUD operations with a graphical interface and verifies their results by executing the corresponding SQL queries written by the developer. This method does not require a good knowledge of SQL or a good knowledge of the database structure of the application.

This method seems to be a simple and good choice for DB testing. Its only flaw is chaos. What if the request written by the developer is semantically incorrect or does not fulfill the user's requirements correctly? In this case, testing does not give any guarantee about the quality of the product.

Conclusion

The database is the main and most important part of almost every application. Its testing requires close attention, great skills in writing SQL queries, knowledge of DB structure and appropriate training.

To ensure the effectiveness of testing, it must be entrusted to an employee who has all these qualities. Otherwise, after delivery of the product, most likely, there will be incorrect or unintended behavior of the application or the errors that the customer will find.

Author bio

By Maria Filina

Maria Filina is a Marketing Specialist at QAwerk Software Testing Company with background and broad expertise gained through experience in the IT industry.