How to Create Test Data in a Database With JMeter

When performance testing APIs, you sometimes have to use different test data than what is present in the database. Occasionally, the test data in the database is not enough and the testers must create more themselves. Apache JMeter™ is very suitable for these purposes because it allows you to create, modify, and delete data from a database. This post will explain how.

In order to add data to the database, you need to do the following.

Configure the connection to the database as described here. In  JDBC Request, set the following:

Example code:

INSERT INTO `sakila`.`city`(`city_id`,`city`,`country_id`,`last_update`)
VALUES(603,'New York','103',null)

This is a SQL query that adds the specified data to the city table:

• `sakila`.`city`: sakila is the name of the database.
• city is the name of the table that exists in the sakila database.
• `city_id`,`city`,`country_id`,`last_update` are the names of the fields that contain the city table.
• 603,'New York','103',null are the values that we add to the table. If we need to add other values, then we only need to replace these values with the others.

Next, go to Thread Group > Add > Listener > View Results Tree.

View Results Tree displays the sent requests and the received responses. In our case, this element allows us to see the query being sent to the database and the response received from the database.

After starting our request to add data to the database, in View Results Tree, we will see the request itself, as shown in the image below.

The result of the successful addition of data will be as shown in the image below. The successful result of adding data to the table is when the response contains a row of 1 updates. 1 updates means that one row is added to the table.

In the database itself, our added data will look like this:

When specific test data already exists in the database but is partially not suitable for the test case, it makes no sense to add new test data. You can simply change the existing.

For example, above, we added new data to the database. If this data is suitable for the test but only with a different value of city_id, then you can do the following:

In JDBC Request, set the following:

Example code:

update `sakila`.`city` 
set city_id = 7000 
where city_id = 603

This is a SQL query that changes the value of column city_id = 603 to city_id = 7000 in the city table.

After starting our request to updating data to the database, in View Results Tree, we will see the request itself, as shown in the image below.

The result of the successful data change will look like this:

In the database itself, our modified data will appear:

If we need to delete the test data from the database after the test, you can do the following:

In the JDBC Request, set the following:

Example code:

delete 
from `sakila`.`city`  
where city_id = 7000

This is a SQL query that deletes from the table city the row that has the value of the column city_id = 7000.

After starting our request to delete data in the database, in View Results Tree, we will see the request itself, as shown in the image below.

The result of successful data deletion will be shown like this:

When you have to regularly create test data in specific tables by using different SQL queries, then in the JDBC Request you can use a variable to replace the SQL query itself.

In order to use a variable in a JDBC Request, you need to set up the database, and then do the following.

Do Thread Group > Add > Config Element > User Defined Variables.

In User Defined Variables, add the following:

• insert_city is the name of the variable that will be used in the JDBC Request.

• INSERT INTO `sakila` .`city` (` city_id`, `city`,` country_id`, `last_update`) VALUES (603, 'New York', '103', null) is the value of the variable insert_city.

In the JDBC Request, set the following:

Example code:

${insert_city

This is a syntax that allows you to use a variable created in User Defined Variables.

After starting our request that adds data to the database, in the View Results Tree we will see the request itself, as shown in the image below.

The result of the successful addition of data will be as shown in the image below:

In the database itself, our added data will look like this:

The SQL query that resides in the JDBC Request can also contain variables.

To do this, you need to do the following.

Thread Group > Add > Config Element > User Defined Variables. In User Defined Variables, add the following

The variables added in the image above will be used in the query to add data to the database.

In JDBC Request, set the following:

Example code:

INSERT INTO `sakila`.`city`(`city_id`,`city`,`country_id`,`last_update`)
VALUES(${city_id},'${city}','${country_id}',${last_update})

This is a SQL query that adds data to the city table.

$ {city_id}, $ {city} ',' $ {country_id} ', $ {last_update} is a syntax that allows you to use variables created in User Defined Variables.

After starting our request to add data to the database, in View Results Tree, we will see the request itself, as shown in the image below.

The result of the successful addition of data will be as shown in the image below.

In the database itself, our added data will look as shown in the image below.

That's it! You now know how to create test data in a database by using JMeter. To learn more JMeter, check out our free JMeter academy.