Database Unit Testing and Test-Driven Database Development

Overview of Test-Driven Development

Test-Driven Development (TDD) is a software development process which includes test-first development. It means that the developer first writes a fully automated test case before writing the production code to fulfill that test and refactoring.This article will give an overview of Test Driven Database Development and Database Unit Testing.

Software testing plays an important role in the life cycle of software development.” Click to explore about, Test Driven Development for Java using JUnit, Mockito

What are the approaches for Test-Driven Database Development?

The below highlighted are the best approach for the Test-Driven Database Development:

Database Unit Testing

Database Unit testing requires that the tester should have knowledge in checking tables, writing queries and writing procedures, so to make tests effectively. Testing can be performed in a web application or desktop because the database can be used in the application. Some points are given below to test the database -

First of all, the tester should be sure that he understands all the application totally and which database is used in the backend of application.

Check all the tables which are available for the application and the tester should try to write the database queries in such a way that it covers most of the tables which are available in the database. This is the best process for the testers to perform for the DB testing. It can be done for any application. It does not matter application is small or big because every database requires testing.

Database Unit Testing Checklist

To perform testing with Databases SQL queries are used to develop the tests. The most commonly used command is “Select” in the case when we are using SQL database. Select * from <tablename> where <condition> Apart from Select, SQL has 3 important types of commands -

DDL - Data definition language
DML - Data manipulation language
DCL - Data control language

We can develop our queries on the basis of above SQL commands.

Operates on a low level and is tightly coupled with source code.” Click to explore about, Golang Unit Testing and Testing Best Practices

What are the components of Structural Testing with Database?

Structural testing includes testing the following components -

Schema Testing
Database column, column Testing
Stored Procedure Testing
Trigger Testing
Database server validations

Various Structural Components in SQL Database for Testing are below:

Transactions

When we working with databases to test transactions it is important to make sure that they satisfy the ACID properties. These are the statements commonly used -

BEGIN TRANSACTION TRANSACTION#
END TRANSACTION TRANSACTION#
ROLLBACK TRANSACTION#: The Rollback statement ensures that the database remains in a consistent state.
SELECT * FROM TABLENAME <tables which involve the transactions>
After these statements are executed, use a Select to make sure the changes have been reflected.

Database Schema

A database schema defines that how the data is going to be organized inside a Database. To test it, we identify the requirements based on which the database operates. Sample requirements -

Primary keys to be created before any other fields are created.
Foreign keys should be completely indexed for easy retrieval and search.
Fields with a constraint that certain values can or cannot be inserted.
Following methods according to the relevance:
SQL Query DESC<table name> to validate the schema.
Regular expressions for validating the names of the individual fields and their values.
Tools like SchemaCrawler - It helps us to view the complete structure which is explained further in this blog.

Database Triggers

We specify in our code when a certain action occurs on a table, a piece of code (a trigger) can be auto-instructed to be executed. For example, a new student joined a school. The student is taking 2 classes - math and science. The student is added to the “student table”. The trigger which is adds the student to the corresponding subject tables once he is added to the student table. The common method to test is to execute the SQL query embedded in the trigger independently first and record the result.
These are tested in both the black box and white box testing phases.

- White Box Testing - Stubs and drivers are used to insert or update or delete data that would result in the trigger being invoked. The basic idea is to test the database alone before integrating it with the database.
- Black Box Testing - In Black Box Testing, UI (User Interface) integrated with DB(Database) are now available, so it is possible to insert/delete/update data from the front end in a way that the trigger gets invoked. Following that, Select statements can be performed to retrieve the data from a database to see if the trigger was successful in performing the intended operation.

In another way, we can directly load the data that would invoke the trigger and see if it works as intended.

Stored Procedures

Stored procedures are more or less similar to user-defined functions. These can be invoked by Call Procedure/Execute Procedure statements and the output is usually in the form of result sets.These are also tested during -

White Box Testing - Stubs are used to invoke the stored procedures and then the results are validated against the expected values.
Black Box Testing - It is performed from the front end (UI) of the application and checks for the execution of the stored procedure and its results. Basically, in this it is checked database is properly integrated with UI and stored procedures give the proper results.

Field Constraints

In this, we check for the default value, unique value and foreign key. In the particular field constraint, we check that column should contain a unique value if it is set to the primary key and check that value in a column should not be other than the default value.

Checking the default value for a certain field is quite simple. We can add a value other than the default value of the field from the front end and see if it results in an error.
For the foreign key constraint validation use data loads that directly input data which violate the constraint and see if the application restricts them or not. Along with the back end data load, perform the front end UI operations too in a way that will violate the constraints and see if the relevant error is displayed.
Validate the results with an SQL Query.

A great approach for software development. TDD is nothing but the development of tests before adding a feature in code.” Click to explore about, Test and Behavior Driven Development and Unit Testing in Python

What is Database Unit Testing with NoSql Database?

We can test the database through various ways, if we are using SQL server then we can open the SQL server query analyzer and write queries to retrieve the data.

Then after that, we check that the expected result is correct or not.
If the data is not inserted into the database.
For the database testing, we can play with the queries, we can insert delete and update the data from the backend.
And then we can check it using SQL queries or test it from the front end of the application.
The process of it is similar to performing testing in other languages. The following are the steps of database testing -
- Prepare the environment
- Run the test
- Check the result
- Validate according to the expected results

Usually, SQL queries are used to develop the tests. The most used command is the “select”.

Unit Testing Using SQL Queries

For the Database Testing, you would want to test a scenario and see if they are populated in relevant tables with right values. For this to be done, we should SQL skills, so that we can insert, update, create, select from the database.
Perform the test (for example--->we can perform the sign up).
Then in the database, we can check that signup parameter have been stored in the relevant table at which we want to store the values for the sign-up, as per the input of the user.
Now, if there are deviations from the expected result, you might have to clean the database and then perform the test again. This will be good if we check that, all the test values are as per the guidelines and that no bad data are there in the database during the test.

Unit Testing Using UI (User Interface)

We can perform direct database querying but it is not a good to perform database testing because in this way we only check that things happen the way as we expected.
We can write such a test case in which we refer to the database. For example, we are testing the user creation portion-verifying the standard CRUD operations happen in both the UI and the DB for the username, password, profile data etc. would be valuable.

We can test this, that is going to relevant table at which we want to make insertions. For example -

Go to particular user profile i.e “ABC” and update his phone number from "9900000078" to "9999999999" and click the save button in the application.
Verify that the application displays "Successfully changed user profile".
Go to user ABC’s profile to verify that the changes are reflected in the user profile.
Verify the table USER_PROFILE(contains data for registered users) in DB users database updated with changes.

Things required to be covered under Database Testing -

What are some tools that are under user for it?
How to test database as a manual tester?
How do we test database with respect to the specific database(For Example - MYSQL)?

What is functional Testing for Database Test Driven Development?

In this, we firstly test each component to check that output is produced according to the code and then double checked to ensure that the rest of the system is not impacted by the output of this particular component. It is particularly imperative that all cases of possible impact are carefully covered, including the edge case or boundary case scenarios. It mainly focuses on the accessibility, usability, and main function testing.

Example For Functional Testing

In an application, performing an action like register new user adds data or changes data when we make updates or delete the user from our application. We can verify it the database by querying the tables directly. But these changes can also be pulled by our application because data is already being pulled by the application and is displayed correctly (or incorrectly) in the application. As an example, we refer to above example which is shown under Testing with the help of integration of database with UI portion, for an example we can refer to this portion.

Empowering Development of Cloud Native Applications with DevOps Best Practices and effective cluster management.” Click to explore about, Continuous Delivery Solutions with GitOps

What are the best tools for Database Testing?

Another way is available to test the database by using various testing tools which are available for Database Testing, for example -

Selenium WebDriver [to test the functionality of database]
Apache JMeter [for load testing on database]

If we want to do accurate testing then you should get first knowledge of database tables, structure. Once we are familiar with the database which we are going to test, then we can test the database more accurately and in more detail. Testing Database as a Manual Tester For automation database testing we will need to write a script. Testing Database with respect to Specific Database (For Example - MYSQL) Testing corresponding to SQL database is performed at the bottom of the database under the Demo portion. So for this, we can refer to Demo section, which is written especially for SQL Database.

Selenium WebDriver

Apache Jmeter

Apache JMeter may be used to test performance both on static and dynamic resources, Web dynamic applications. It can be used to simulate a heavy load on a server, group of servers, network or object to test its strength or to analyze overall performance under different load types.

Implementing Database Unit Testing and Test-Driven Development

Implementation of Database Unit Testing and Test-Driven Database Development is defined below:

Installation Of SQL on LINUX

sudo apt-get install mssql-server=<version_number> sudo systemctl start mssql-server

Database Unit Testing using the SQL Database

Firstly, a table is created named as month_value with fields eid of integer type, m (month) of integer type and y (year ) of integer type and v(date) of integer type. The syntax used to create this table as follows -

create table month_value( eid int not null, m int, y int, v int );

Data is inserted into the month_value table as follows -

insert into month_value(eid,m,y,v)values(1,12,2017,20);

The view is created on the basis of month_value, syntax to create view shown as follows -

create view cm_abs_month as select *, y * 12 + m as am from month_value; Unit Testing For SQL Database

After that, we write the test case on the basis of the above-created table named as month_value and above-created view which is named as cm_abs_month.If the condition written under the test case is satisfied then the passed is printed, otherwise, if the test condition failed then the failed message will be displayed.


select concat(
 'For every (y,m) there is value for (am): ',
 case when(select count(distinct y, m) from month_value) = (select count(distinct am) from cm_abs_month) then 'passed'
 else 'failed'
 end
);

Conclusion

TDD refers to test the product while it is developed and we highly give the importance to feedback which we get after failing test rather than the passing test. This feedback is used in both the cases to refactor the code and to minimize the changes that we have to make for the next time, but there is also a bottleneck for database testing that a lot of additional expenditure is added for the it. It also requires the basic knowledge of SQL statements and specially DML(Data Manipulation Language) statements. It plays an important role when integrated with the application. The database acts as a client-server system. If any of the malfunctions appear it may cause system deadlock, data corruption, data loss and bad performance. So we cannot ignore the importance of it.

How Can XenonStack Help You?

XenonStack adopted Agile-scrum Methodology in Product engineering and Cloud Native application development for Enterprise with Devops for Database, Database Unit Testing and Test-Driven database Development.