Automate Test Data creation

A month back I started Automating performance suits execution on a project. Like most of the cases we need to create test data prior to script execution, similar was the case with me. Each and every time when there was a need for executing existing Performance suit on a fresh build, tiring manual work of executing a bunch of SQL query would worry me.

A thought of automating Test Data creation struck me. And there begins the journey of Automating Test data creation process. I choose Jenkins as the path to start with. Let's talk more with a real-life example.

 Let's consider we want to Test Login endpoint, but in order to get started with it, we need to have UserName and Password present an application or database. This comes as a part of the Test data creation process. In case the Test data creation is manual then each and every time we want to execute Login script we need to manually create an entry in DB by firing SQL query or by registering user from the application.

 Thanks to Microsoft, they have made above tedious task easy for us. Now with the help of Sqlcmd utility, we can easily fire DB query. We just need to install the utility on DB Server which can be found here and we are good to get started.

 Steps to Automate Test Data creation process can be found below

 Step 1: You can simply create a Freestyle Project from Jenkins by clicking on New Item from the Jenkins home page. 

Step 2: You can enter a general description as shown below

Step 3: You need to specify the environment where we want SQL script to execute( Ideally it would be DB server IP address).

Step 4: Step 4: You can configure how spontaneous you want the Test data job to be triggered by providing Cron expression from the Build Triggers section. 

Step 5: Click on Add Build Step dropdown and click on "Execute Windows Batch command" same can be referred from below image.

Step 6: Redirect to the directory where SQLCmd is installed on Database server with the help of CD command. For eg

cd "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn"

Then replace below command with an actual value according to your server details

sqlcmd -U <UserName> -P <Password> -S <ServerName>.<DomainName>\<SQLVersion> -d <DataBaseName> -i <Location of the SQL File containing scripts for Test Data creation>

Username          = Valid SQL Username

Password           = Valid SQL Password

SQL Version      = Version of SQL server which is installed on the server( i.e 2012, 2014 etc)

DataBaseName = Name of Database where we want to fire SQL query.

Where UserName and Password are login credential of DB server. In the end, we need to specify the .sql file's location present on the same server. Please note the SQL file would contain actual SQL query which would create test data for us.

A sample example would be

sqlcmd -U username -P Password -S TestServer.Global.com\SQL2012 -d Employee -i D:\PerformanceTestDataScript\CreateUserNamePassword_Query.sql

Same can be found in the below snippet

Step 7: We can finally Save the changes we have made in the Job.

Step 8: From Jenkins, Home page click on the newly created job and click on the Build Now button from the left side.

For verification purpose, you can go back to the Database and can check corresponding entry. I am sure you are able to see the same.

Bringing it up altogether

With the help of SqlCMD, we can easily automate test data creation workflow and thus reduce the manual effort. It can be easily integrated with Jenkins as well.

Please share your thoughts in the comment section, we can connect on LinkedIn and talk more. If you enjoyed this post, I’d be very grateful if you’d help it spread it to your connections on Linkedin or other channels.

Thank you!