MySQL Database Unit Testing and Test Driven Development

Xenonstack

Published in

Digital Transformation and Platform Engineering Insights

10 min readDec 27, 2017

Reading Time: 10 Minutes

by Navdeep Singh Gill | December 27, 2017

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 fulfil that test and refactoring.

Test Driven Development With Database

Database Testing

Database 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 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.

Behaviour Driven Development — 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

Testing Different Structural Components in SQL Database

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.

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.

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 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.

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.

Unit Testing 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 database testing 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 database testing?
How to test database as a manual tester?
How do we test database with respect to the specific database(For Example — MYSQL)?

Functional Testing for Database

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.

Database Testing Tools

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

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 Test Driven Development with Database

Installation Of SQL on LINUX

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

sudo systemctl start mssql-server

Unit Testing for Database 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;

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 );

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 database testing.

It also requires the basic knowledge of SQL statements and specially DML(Data Manipulation Language) statements. Database testing 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 Database Testing.

How Can XenonStack Help You?

XenonStack follows the Test Driven Development Approach in the development of Enterprise level Applications following Agile Scrum Methodology.

Originally published at www.xenonstack.com on December 27, 2017.