My SoapUI Thoughts on Effective Testing in SoapUI

Multiple Records

In Scriptless Loops, I demonstrate an extensible method for running tests that return single records. But what approaches are available when the API returns multiple records? Let’s take a look at how we can validate the content of data in an efficent manner regardless of the number of rows in the response, or the number of fields in the records.



  • A test case which uses a script assertion to validate each field in each record of a REST response against a SQL query.

You’ll recall that some of the GET methods in the JsonPlaceholder API are designed to return more than one row:

These methods both return all the posts created by the user whose ID is 1. We can begin this test case by implementing a way to randomise the user ID value such that it differs each time the test is run. Let’s start by creating a userId member on the context variable in the test case’s Startup script, together with a rowCount variable to control the iteration over the steps in the test case as shown in the Scriptless Loop article. The Setup Script and the test case properties should appear as follows. MultiRecordTC01 We can now add REST test steps based on the two methods shown above, as follows. GetPostsByUserId01 This request implements the GET by UserId method defined in the service definition, and sets its userId parameter according to the context.userId variable. I’ll show you shortly why I’ve used a context variable for the userId rather than a simple test case property. The second REST step looks like this. GetPostsByChildResource01 This implements the Nested GET child resource method. At this point you should be able to run the test case and have both steps return the posts associated with user of ID 1. In each step the response should contain ten records, looking like this.

      "userId": 1,
      "id": 1,
      "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
      "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
      "userId": 1,
      "id": 2,
      "title": "qui est esse",
      "body": "est rerum tempore vitae\nsequi sint nihil reprehenderit dolor beatae ea dolores neque\nfugiat blanditiis voluptate porro vel nihil molestiae ut reiciendis\nqui aperiam non debitis possimus qui neque nisi nulla"

Configuring the JDBC test step.

A SQL query designed to retrieve the information in these two REST test steps might look like this:

from  users u
join  posts p on = p.userId 
where = 1

(In fact, the query could be simpler: all of the fields in these REST responses come from the POSTS table and could be retrieved via select * from posts where userId = 1. I’ve chosen to demonstrate the use of a join clause for the benefit people who are just starting out with SQL because it’s very common for an API to return records derived from multiple tables. The join clause allows you to build SQL queries that cater for this, and it’s a technique worth learning.)

We can create a JDBC request step as the first test step in the test case based on this query such that it looks like this when run. SQL GetPostsByUser01 The query returns the rows from the table with the column names in upper case letters, qualified by the table name. As we develop this test case, you’ll see that it will be to our advantage to make the column names in the query results match exactly with the corresponding field names returned by the API. Knowing that the API returns the field names in lower case, we can apply column aliases to the SQL query to achieve this, as shown here. SQL GetPostsByUser02 For these column aliases to take effect, you’ll need the following:

  • SoapUI 5.3.0 or later (or Ready!API 1.9 or later)
  • The “Converts the names of fields in request results…” option must be cleared
  • If you’re using an H2 database to follow these examples, the connection string must set the ALIAS_COLUMN_NAME parameter e.g.

In all versions of SoapUI earlier than 5.3.0 the database column names always came back in upper case, even if you attempted to override them via column aliases. In response to a specific request from me, the good folk at SmartBear fixed this in SoapUI 5.3.0 so that the case of the column aliases is preserved in the SQL results.

Let’s add a couple of assertions to the JDBC request. I want to keep track of the number of rows returned by the query for use in a subsequent test step. To do this, create a script assertion with the following text:

context.numRows = context.expand( '${ResponseAsXml#count( //Row )}' ).toInteger()

This will store the number of rows in the response in the context. We know with the JSONPlaceholder database that this number will always be 10, but in the interests of providing a maintainable solution it’s safer not to make assumptions about the contents of the response. You can also add a JDBC Status assertion if you wish.

Finally, I want to add a parameter to the test step that refers to the userId member we created in the context map. This is done in two steps.

  1. Add a property to the JDBC test step called userId and with the value ${=context.userId}
  2. Reference this property in the SQL statement by replacing the 1 in the where clause with :userId

Note that the syntax for parameters in the SQL query involves preceding the parameter name with a colon. The test step should now resemble the following screenshot.
SQL GetPostsByUser03 You should now be able to run the test case and have the SQL and REST steps return the posts for user ID 1.

When you use the context variable as a parameter in the JDBC test step, you’ll need to execute the test step via running the test case as a whole. The context variable won’t be defined until the test case runs, and you’ll get a ‘Data conversion error’ if you attempt to run the JDBC step directly.

Adding assertions

The point of this test case is to show how to assert on multiple rows with multiple fields. You could in theory create assertions for each field in each row in both REST test steps, but if both your requests returned 100 rows with 20 fields, you’d have a pretty worn out mouse by the time you created 4000 assertions. You could also avoid the risk of wrist-droop by creating assertions for a small sample of fields.

Fortunately there’s a relatively short script that can do the work for you, giving complete coverage of every field in the response content. Open the REST GetPostsByUserId test step and create a script assertion with the following text.

import groovy.json.JsonSlurper

final def	sqlStepName	= context.getTestCase().getTestStepList()[ 0 ].name,
		sqlContext	= '${' + sqlStepName + '#ResponseAsXml}', 
		dbData		= context.expand( sqlContext ),
		apiData	= messageExchange?.getResponseContent(),
		JSONRows	= new JsonSlurper().parseText( apiData ),
		SQLRows	= new XmlParser()  .parseText( dbData ).ResultSet.Row

JSONRows?.eachWithIndex { row, rowNo ->
	row.each{ apiField ->
		final def apiVal = apiField.value.toString(),
			    sqlVal = SQLRows[ rowNo ]."${apiField.key}".text()
		assert apiVal == sqlVal, "API returned different values from database"

Let’s work through how this script does its processing. The declarations are designed to obtain the SQL response and the API response via instances of the XMLParser and JsonSlurper classes. With the data from the responses parsed in these formats, it’s easy to traverse the JSON data in the API response row-by-row, field-by-field, comparing against the corresponding field in the SQL response. Perhaps the only challenging syntax is in the line used to extract the values from the SQL fields:

sqlVal = SQLRows[ rowNo ]."${apiField.key}".text()

The apiField.key variable contains the name of each field in the row as the script iterates over the row{} closure. By enclosing this variable in “${}”, its value is dynamically substituted at runtime, so the expression resolves to e.g.

sqlVal = SQLRows[ rowNo ].userId.text()


sqlVal = SQLRows[ rowNo ].title.text()

This is sufficient to locate each specific field in the SQL response, and the comparison with the corresponding field in the API response then becomes trivial.

You could now run the test case and see that the assertion on the REST GetPostsByUserId test step passes.

The two assertions we need for the REST GetPostsByNestedChildResource test step are simpler. The first assertion relies on the fact that the response should be identical to that in the REST GetPostsByUserId test step. There are a couple of ways to go about this, but the easiest is completely straightfoward. Create a JSONPath Match assertion as follows. GetPostsByUserIdAssertion01 This is all that’s required to assert that the every element of the two responses, from the root down, are identical. This is the approach I recommend for this test case.

(As an alternative, it’s also possible to re-use in the REST GetPostsByNestedChildResource test step the assertion we created in the REST GetPostsByUserId test step. Via a script assertion, you can extract and execute the assertion from a given test step as follows:

import com.eviware.soapui.impl.wsdl.teststeps.*
final def tc 		= context.getTestCase(),
	testStep	= tc?.getTestStepsOfType( RestTestRequestStep.class )[ 0 ],
	assertion	= testStep?.getAssertionByName( 'Check All Fields' )
assertion?.assertResponse( messageExchange, context )

The motivation for such an approach might be if you have a reasonably complex script assertion that you don’t want to duplicate but haven’t implemented as a class that you can invoke via the GroovyScriptEngine. For now I’m quite happy to go with the JSONPath Match assertion we created above.)

I alluded to the test step requiring two assertions. Recall that we created a script assertion in the JDBC test step to retain the number of rows returned by the SQL query. This value comes into play when selecting a userId for the next iteration through the test steps. (Recall that this is an iterative test case.) Create a script assertion with the following text:

context.userId = ( Math.random() * context.numRows ).toInteger() + 1

This script updates the userId with a random value within the range of the number of records returned by the SQL query. This value will be used in the next iteration over the test case.

We’re almost done. The last task is to create a Conditional Goto test step called Get Next Record to iterate over the test case, as follows. GetNextRecord This will return the test case execution to the SQL GetPostsForUser test step for as long as the rowCount variable on the context is less than the iterations property on the test case. You can run the test case now and should see the two log messages appear in the Get Next Record test step. If you have either of the REST test steps open while executing the test case, you should see the requests referencing different userIds and the responses containing the corresponding records.

With our database techniques in place, we can look at how to use them as a way of testing virtualised services.