Database testing: what to look for

We can no longer store information in paper folders on desks or bookshelves as technology advances. It would require too much paper and huge areas of space to store this paper. Instead, data centers with hard drives have replaced this method of storing information and databases have been invented, where masses of information are stored, processed, and structured.

Database is a structure for storing, organizing, and sharing massive amounts of data. Databases are used everywhere in a wide range of industries: from online stores to NASA Mission Control. An entire set of software and tools called a database management system (DBMS) was invented to create, use, and manage databases.

DBMS is a set of programs used to create, display, content, edit, update and delete information. The most popular DBMSs are Oracle, MySQL, Microsoft SQL Server, PostgreSQL, MongoDB, and many others.

Each system that is implemented in a product requires a series of tests to minimize future problems during its use. In software products, data is transferred from the user to the internal database and the other way around. Database testing is about checking the performance related to the database, as well as verifying the reliability and integrity of the data.

Aspects to consider when testing databases

Below, we will look at some aspects that you need to pay attention to during testing:

1. Data display. A tester checks whether the fields in the user interface forms are matched with similar fields in the database table. This information is usually included in the specification.
2. Updating records in the table. Each time a specific action is performed in the external interface of the application, the corresponding action is activated in the internal interface. To check the update of records in the table, the tester can use the CRUD mnemonic.

C (Create) – when a user saves any new transaction, the «INSERT» operation is performed.

R (Read) – when a user searches or views any saved transaction, the «SELECT» operation is performed.

U (Update) – when a user edits an existing record, the «UPDATE» operation is performed.

D (Delete) – when a user deletes any record from the system, the «DELETE» operation is performed.

3. Checking transactions' ACID properties. Each transaction processed by the database must comply with these four properties: atomicity, consistency, isolation, and durability.

A (Atomicity) – when one part of a transaction fails, it means that the entire transaction has failed. This is also known as an all-or-nothing rule.

C (Consistency) – only valid data is saved, which means that a transaction should always lead to the correct database state.

I (Isolation) – in case there are several transactions and they are executed simultaneously, the result and the database state must be the same as if the transactions were executed one after the other.

D (Durability) – after the transaction is completed and recorded, any external factors, such as power loss or failure, will be unable to change it. 

4. Data integrity. All data, including their changes, should be displayed consistently and in an easy-to-understand way. It is important to check that the system shows the latest changes in tables in the same way in all places they appear to make sure they are consistent everywhere.
5. Compliance with business rules. Databases are used not only for storing data, but they have also become very powerful tools for developers. Great complexity in databases requires the use of more complex components, such as relational constraints, triggers, stored procedures, etc.

The main attributes of database testing

Transactions – a sequence of specific actions with the database where all actions are performed successfully or none of them are performed. Each transaction is atomic, so it is executed as a single unit.

Database schema is a structure of data organization, tables, and column descriptions (their types, valid values, relations between tables, etc.)

A trigger is a special type of stored procedure that is executed automatically after some data modification actions. For example, adding and deleting a row, or changing data in a specific column. Triggers are used to provide data integrity and implement complex business logic. 

Procedures are a set of statements or functions for managing transactions in a database.

The main types of database testing

Database testing can be divided into three types depending on the database's function and structure:

1. Structural database testing – includes testing of tables, columns, and database schemas, as well as testing of stored procedures and triggers.
2. Functional database testing checks the database's functionality from a user's point of view. There are two well-known types of functional testing: white box and black box testing.
3. Non-functional database testing – involves database performance testing, load testing, database risk testing, stress testing, and minimum system requirements analysis.

Database schema testing includes checking and comparing application and database objects using special tools. Simple queries to test database schemas can be written using Microsoft SQL Server.

Testing of tables and columns covers the next areas of testing:

• checking the data types in the database by the field values in the software product;
• checking the length of the data field in the database by the length of the data types in the software product;
• checking database unmapped tables and columns that are available in the software product;
• checking for compliance of database table and column names with business requirements;
• checking if the keys and indexes in the database meet the business requirements.

Testing stored procedures consists of checking these procedures and comparing the results of the command execution output. This testing can be performed with the following tools: LINQ, SP Test tool, etc. To test stored procedures, the following steps are required:

• check the procedure name, as well as the names and types of parameters;
• check the output signal – whether null lines or only a few records are displayed;
• check the function of the stored procedure;
• check input queries to verify that the required data is stored;
• check the error output for each incorrect use of the query.

To check triggers of the tester, it is necessary to perform the following actions:

• check the authenticity of the trigger name and its updates;
• run the trigger with the updated data;
• check such functions as updating, inserting, and deleting triggers;
• run a trigger with false data and check the errors;
• roll back transactions if a failure occurs.

Performance testing is an important stage in working with databases, the purpose of which is to assess the performance of a software product under load. If a system doesn't process the largest query during a cup of coffee, no one will like it.

To perform this type of testing, one needs to:

• measure the time it takes to complete operations under different loads (maximum, minimum, and average);
• determine the number of users who can work with the product at the same time;
• determine acceptable performance limits with increasing workloads;
• investigate performance under heavy, maximum, and stressful loads.

These are the most important properties of performance measurement:

• number of transactions for a certain period;
• number of operations for a certain period;
• number of read rows in the table one transaction;
• average number of sorting operations.

Stress testing is used to determine how well a system performs under stress, and also to assess the system's ability to recover, in other words, its ability to return to its normal state after the end of the stress impact. The stress factor can be an increase in the intensity of operation execution up to very high values or an emergency change in server configuration.

To perform stress testing, the next recommendations should be followed:

• Write test scripts to test the entire function at least once;
• Run these scripts within a certain period;
• check the log files for crashes or errors.

Load testing is conducted with the help of special tools, for example: Apache JMeter або HP LoadRunner. They allow creating of lots of queries by using multiple computers.  The tools have mechanisms for authorizing virtual users, supporting user sessions, logging the test results, and various result visualizations as diagrams and tables. The work of a real server is simulated with the help of a proxy server.

After creating the necessary queries in the load testing, the number of virtual users is set to perform these queries within a certain period of time. The monitoring system makes it possible to observe the time and number of virtual users when the server starts to take up additional resources.

Functional testing checks the functionality of the database from the user's perspective. The goal is to check that all transactions and operations performed by the user meet the technical requirements. There are two types of database functionality testing: black box and white box testing.

Black box testing focuses on testing the database integration to check functionality. Test cases are simple and used to test the input and output data of the function. There are various methods to verify the functionality of the database, including the cause-and-effect method, equivalence partitioning, and boundary value analysis.

White box testing involves testing the internal database structure. It involves unit testing of database functions, triggers, views, and SQL queries. At this stage of testing, internal errors in the database can be detected and fixed.

Testing a database through the interface often means checking its usability. This includes checking the correspondence of button names and the operations assigned to them, links, transitions implemented in the interface, and validation of the entered data.

By following these simple steps, one can find errors using database testing through the interface:

• write queries through the interface and execute them;
• find an existing record in a cell, change it, and save the record;
• insert a new item in the interface menu, fill in the information, and save the data;
• delete or move an existing cell with information;
• repeat all these steps with unacceptable/invalid data and see how the database responds.

The database is the main and essential part of almost every program, so it is very important to test it in advance, checking the correctness and security of the databases. Database testing requires great attention, proper preparation, good SQL query writing skills, knowledge of the database structure, and practical skills.

Database testing minimizes the risks associated with data integrity and the system's ability to respond to load or stress before the software product is released.