Testing APIs using Postman and Excel
Hello everyone, I’m the head of the testing department, and recently a task for API testing has appeared. To solve it, I mastered the new Postman and JavaScript tool for me.
Initially, for each API, I wrote my collections and prepared test data in JSON format. This is quite convenient, but with a large number of tests and collections it becomes unprofitable to maintain. And it is not convenient to validate data in JSON.
To solve these problems, I wrote a macro for Excel and a collection in Postman. Now in Postman I have one collection for all APIs and a standard set of functions for processing incoming data and validating returned results. I managed to transfer test data management and query execution sequence to Excel.
1. JSON with data
Previously, the test kit was stored in this form
2. 2. The sequence of query execution with JS handlers was stored in Postman collections.
1. Test suite moved to Excel
All data is entered into Excel (control key characters: R, H, I, etc. I will write below) and then, using a macro, they are redirected to json format:
2. In Postman
The experiment was conducted on a standard set of CRUD operations, which can be further expanded.
Since in Postman all operations are performed only within the framework of the request, I had to enter an empty get request at the end of which the next request from the sequence is determined. Run JS code before the request and determine the first request without an empty failed.
In all requests, the Pre-request script and Test sections are empty, all code is unified for requests and is stored in the general sections of the Pre-request script and Test of the API Collection folder.
In all requests, it is important to pay attention to the url and the Body section in POST and PUT requests, their values are determined by variables, the values of which are entered from the JSON with the data.
How to read excel . The first non-empty line is the number of the test case, that is, the test case is stored vertically and on this page there are 9 test cases. In the current set, in each test case, a POST request will be executed first, then Delete.
How to start Json generation from Excel . In Excel, press F11 and go to "Ebook", there run the macro.
R - request, means the beginning of a new request, in the second cell of the line the type of request is stored, in the third is the address to which you must contact. Please note that in url you can specify Postman variables
The value from the variable will be pulled
H - Data for verification in the header, so far only the response code has been entered, Postman only checks for it in the postman. It is important that in Excel the name is the same "response code", or corrected in Postman
I, I2 ... - The input data section supports the storage of data models of any nesting, the figure to the right of I is responsible for the nesting level. The next data set is wrapped in JSON like this. If the variable that stores the data is empty, then it will not be added. That is, if there is no value in the inn variable, then it will not be added, and the chief variable will be added, since it stores the model. Moreover, if the entire model is empty, then it will not be added either.
The data in this section will be submitted in the request body.
O, O2 ... - Section of the output parameters, they will be compared with those returned by response. Like the Input section, it supports model storage.
When comparing, the data is converted to string, I also additionally limited the level of recursion, this can be removed
PO - Postman Output, the values from this section will be taken from the response body of the request by the name of the variable and written to the Postman variable.
In Excel, it’s enough to put any character, the value from response is written to the variable, not excel
This section is needed to store data between scripts, for example, to delete an object with id that was created in the previous request
PC - Postman command, introduced only one “terminate” command, it is used to force termination after executing the current request. Useful for a negative test, so as not to cause a step to delete the created object.
Entering this command allowed storing both positive and negative tests on one sheet
PI - Postman Input, values from this section will be written to Postman variables before url definition
It may be useful if you need to override the variables that are specified in the request url.
By the way, you can use data from Postman variables in the supplied data, for this you need to use a special design
In case 1, we entered the obtained value into a variable, in case 2 we used it. It can be used not only in the next case, but also in the current one at the next request. For example, it may be necessary if the definition of the object for the change does not follow the url, but according to the value of the variable in the request.
And now, run, run the Postman runner, select the desired collection in it and upload the file with test data:
We will serve the following set:
15 tests are described here, with steps 1-11, 13, 15 being positive with a result of 200 with a POST request and steps 12, 14 are negative with a result of 400. No information will be entered into the database and therefore the Output section is empty, and the command is indicated terminate. This command will abort the execution of the sequence and the request for deleting “Delete” will not be sent.
After cases 1-11, 13, 15, we remember the id that was assigned to the new object, then to delete it.
All 15 tests were successful, the picture shows test 14, in which Delete is not called after POST
In tests 1-11,13,15 after POST, the deletion of the created object is called:
Initially, for each API, I wrote my collections and prepared test data in JSON format. This is quite convenient, but with a large number of tests and collections it becomes unprofitable to maintain. And it is not convenient to validate data in JSON.
To solve these problems, I wrote a macro for Excel and a collection in Postman. Now in Postman I have one collection for all APIs and a standard set of functions for processing incoming data and validating returned results. I managed to transfer test data management and query execution sequence to Excel.
What happened
1. JSON with data
Previously, the test kit was stored in this form
2. 2. The sequence of query execution with JS handlers was stored in Postman collections.
What became
1. Test suite moved to Excel
All data is entered into Excel (control key characters: R, H, I, etc. I will write below) and then, using a macro, they are redirected to json format:
2. In Postman
The experiment was conducted on a standard set of CRUD operations, which can be further expanded.
Since in Postman all operations are performed only within the framework of the request, I had to enter an empty get request at the end of which the next request from the sequence is determined. Run JS code before the request and determine the first request without an empty failed.
In all requests, the Pre-request script and Test sections are empty, all code is unified for requests and is stored in the general sections of the Pre-request script and Test of the API Collection folder.
In all requests, it is important to pay attention to the url and the Body section in POST and PUT requests, their values are determined by variables, the values of which are entered from the JSON with the data.
Now about the tests themselves
How to read excel . The first non-empty line is the number of the test case, that is, the test case is stored vertically and on this page there are 9 test cases. In the current set, in each test case, a POST request will be executed first, then Delete.
How to start Json generation from Excel . In Excel, press F11 and go to "Ebook", there run the macro.
Keywords
R - request, means the beginning of a new request, in the second cell of the line the type of request is stored, in the third is the address to which you must contact. Please note that in url you can specify Postman variables
The value from the variable will be pulled
H - Data for verification in the header, so far only the response code has been entered, Postman only checks for it in the postman. It is important that in Excel the name is the same "response code", or corrected in Postman
I, I2 ... - The input data section supports the storage of data models of any nesting, the figure to the right of I is responsible for the nesting level. The next data set is wrapped in JSON like this. If the variable that stores the data is empty, then it will not be added. That is, if there is no value in the inn variable, then it will not be added, and the chief variable will be added, since it stores the model. Moreover, if the entire model is empty, then it will not be added either.
The data in this section will be submitted in the request body.
O, O2 ... - Section of the output parameters, they will be compared with those returned by response. Like the Input section, it supports model storage.
When comparing, the data is converted to string, I also additionally limited the level of recursion, this can be removed
PO - Postman Output, the values from this section will be taken from the response body of the request by the name of the variable and written to the Postman variable.
In Excel, it’s enough to put any character, the value from response is written to the variable, not excel
This section is needed to store data between scripts, for example, to delete an object with id that was created in the previous request
PC - Postman command, introduced only one “terminate” command, it is used to force termination after executing the current request. Useful for a negative test, so as not to cause a step to delete the created object.
Entering this command allowed storing both positive and negative tests on one sheet
PI - Postman Input, values from this section will be written to Postman variables before url definition
It may be useful if you need to override the variables that are specified in the request url.
By the way, you can use data from Postman variables in the supplied data, for this you need to use a special design
In case 1, we entered the obtained value into a variable, in case 2 we used it. It can be used not only in the next case, but also in the current one at the next request. For example, it may be necessary if the definition of the object for the change does not follow the url, but according to the value of the variable in the request.
Preparation for the run
And now, run, run the Postman runner, select the desired collection in it and upload the file with test data:
We will serve the following set:
15 tests are described here, with steps 1-11, 13, 15 being positive with a result of 200 with a POST request and steps 12, 14 are negative with a result of 400. No information will be entered into the database and therefore the Output section is empty, and the command is indicated terminate. This command will abort the execution of the sequence and the request for deleting “Delete” will not be sent.
After cases 1-11, 13, 15, we remember the id that was assigned to the new object, then to delete it.
We launch
All 15 tests were successful, the picture shows test 14, in which Delete is not called after POST
In tests 1-11,13,15 after POST, the deletion of the created object is called:
Summary
- The sequence of requests for testing the API is made in Excel and is processed in Postman.
- All test data is also rendered in Excel, it is convenient to manage and validate them. At least more convenient than in JSON format.
- The Postman collection is standardized and does not need to be improved when testing similar APIs.
References
All Articles