Documentation Home

API to DB Assertion

Contents

Overview

This feature is designed to test REST APIs that store data into databases. Validation that the API is functioning properly is made by comparing elements of the request body of the API to values retrieved from a database query.

In practice, we are asserting the input into the API (the Request Body or the Response Body) is generating the desired output (stored in the Database). We refer to these comparisons as Database Assertions.

HTTP Verbs Supported

  • GET

  • DELETE

  • PATCH

  • PUT

  • POST

Databases Supported

  • MYSQL

  • PostgreSQL

  • Oracle

  • Snowflake

  • Netezza

  • DB2

  • DynamoDB

  • Lambda Function

  • Redshift

At the moment, support is only available for these SQL databases and Rest APIs mentioned above.

Create an API to DB Test

Setup

Once you have navigated to an API Testing project,

Step-1: Click on the Project Setup button.

Step-2: Select the Database Configuration tab.

Step-3: Click on the New Configuration Button to create a new database configuration.

Check out how to setup Database Configuration

Build an API to DB Test

Add API Information

Step-1: Navigate to Functional Testing.

Step-2: Build a Test Script.

Step-3: After build, Click Assertions tab.

  • Change toggle to Database Assertion.

  • This toggle is disabled if any of the following is true:

    • Test is parameterized

    • API type is not Rest

Step-4 : Select assertion on body type either on Response or Request body.

  • Note :

    • For Get Verb, Response on body only available.

    • For Request on body, it will take value from Body tab.

    • For Response on body, an user need to provide under Response Body filed.

Add Database Assertions

Step-5: Add Database Assertions

  • The database assertions tab contains two sections:

    1. Database Info

    2. Database Column Mappings

Database Info

  • Select a database configuration from the dropdown. Enter a SQL statement in the Database statement section that returns one result set and contains the data to be compared with the API request body.

Database Column Mapping

  • Create the database assertions in this section. This section is divided into 2 portions:

    1. Request/Response body tree

    2. Database mapping table

Request or Response Body Tree

The JSON tree is built using the request body or response body entered in the “Body” tab or response body field under Assertion on Body card. Nodes that have children can be opened and closed to help handle large JSON bodies. Click on any node data to view its JSON path in the “Json Path” text field on the right side.

Database Mapping Table

The “Json Path” field will be populated based on the node that is clicked on the request body tree, but it is also possible to enter the JSON paths manually as well. There are three options available in the dropdown next to the “Json Path” field that determines how many nodes will be added to the mapping table:

  • All Keys in Object

  • Single Key From Array

  • Single Value

All Keys in Object

If this option is selected, it will add all the JSON paths of every child in the node specified by the value in the “Json Path” field. If one of the children is an array, a JSON path with a wildcard will be used to denote all values in that array.

Single Key from Array

This option is used to generate the wildcard JSON path of one node. Ex: clicking on “tags[0].id” node or entering it manually will yield “tags[*].id.

Note: Each array value will be compared with the corresponding row number in the database results (i.e. id[0] compares to id row 1, id[2] compares to id row3). The number of comparisons is dependent upon the number of results returned, not by the number of values in the array (ex: a database query with 3 results compared to a JSON array with 5 values will only return 3 comparison results)

Single Value

Only the value that is present in the “Json Path” field will be added to the mapping table if this option is selected.

Once an option is selected from the dropdown, click on the Add Mapping button to add the JSON path(s) to the table.

Mapping Table

For each row in the table, provide the name of the database column that should be mapped to the JSON path.

Then select one of the following verifier types:

  • Strict match: checks for equality between the value located at the JSON path of the request body (the input) and the result value found in the column of the database(output)

  • Equal to: checks for equality between a provided value in the “Expected Value” column and a result value found in the provided column of the database

  • Contains: checks to see if the result value found in the column of the database (output) contains the value in the “Expected Value” column.

  • Greater than: checks to see if the result value in the column of the database (output) is greater than the value in the “Expected Value” column

  • Less than: checks to see if the result value in the column of the database (output) is less than the value in the “Expected Value” column

  • Regular expression: checks to see if the result value in the column of the database (output) matches a regular expression pattern provided in the “Expected Value” column

The “Expected Value” column is required if the verifier type is not “Strict match”. Add up to six mappings for each JSON path by following the above procedure.

Step-6: Click on the Save button icon to save the script.

Edit Database Assertions

  • Update the value(s) in the mappings table and click the Save button icon to make any changes to the assertions.

Delete Database Assertions

  • To delete one assertion in the mappings table, click on the Trash Can button of the row. To delete multiple assertions, select the rows to be deleted and click on the Trash Can button in the table headers row.

Test Execution

  • Run the test by clicking on the Play icon button as you would with the regular Functional API test.

Reports Page

  • Click on the View Details button of the test that was run.

Notes

  • The API Call tab will be blue if the API call is successful; otherwise, it will be red and none of the other tabs will be accessible

  • The Database Query tab will be blue if query ran successfully; else the tab will be red and the comparison tab will be not accessible.

  • The Comparison tab will be blue if all the comparisons pass. The tab will be red if the any of the comparisons fail.

API Call Tab

  • Click on this tab to view the following details of the API call that was made:

    • URL

    • Request body

    • Request headers

    • Response body

    • Response headers

  • Move the toggle to “Response” to view the response body and headers and move it back to “Request” to see the request body and headers. Click on the Copy button to copy any of the bodies or headers.

Database Query Tab

  • Click on this tab to see the query that was run and the result that was returned by the query.

Comparison Tab

  • Click on this tab to get the result of the comparison between the request body or response body and the database query.

  • A comparison table will be generated for each JSON path and verifier type combination in the mappings table.

  • Click on one of the JSON path/verifier type combinations in the dropdown to view the results of that table.

  • The row numbers in the table correspond to the data retrieved from the database query, mapped to the data provided in the Request body or Response Body. The “Request Body Value” or "Response Body Value" column shows the data provided by the request body or response body, the “Value” column shows the result from the database, corresponding to the row number and the “Expected Value” column shows the value from the “Expected Value” column of the mapping table. The “Comparison Result” column shows whether the assertion in the corresponding row Passed or Failed.

Notes

  • If the data from the Request Body or Response Body is of type array, the objects will be mapped to the results of the database in a 1:1 fashion

    • Ex: id[0] compares to Row 1…id[2] compares to Row 3

  • If there is only one value in the Request Body or Response Body that maps to the JSON path and the database returns multiple rows, that value will be mapped to each row in the database result.

    • Ex: id: 1 and 3 rows in the results

      • $.id value compares to Row 1 value, $.id value compares to Row 2 value, $.id value compares to Row 3 value

  • If there is only one value returned from the database query, only one value (whether array or not) in the request body with the JSON path will be compared

  • Ex: $id[0] value will be compared to Row 1 but $id[1] will not be compared

Related topics

PreviousFunctional-TestingNextAdvanced Features

Last updated