My SoapUI Thoughts on Effective Testing in SoapUI

Database Techniques

A very common use for APIs is to provide controlled access to the contents of a database. In such cases the source of truth for the response returned by an API is the corresponding database record, and SoapUI recognises this relationship by providing built-in support for SQL queries via JDBC for a range of databases. In this article I want to explore some of the techniques you can use to validate the behaviour of an API against the underlying database.



  • A test case which validates single posts resources against the contents of the database via SQL calls.

In my daily work I interact with MS SQL Server and Oracle databases. By contrast, as a means of illustrating the examples in these articles, I downloaded and installed an H2 Database which proved to be lightweight and easy to install.

Testing Single Records

One common scenario in testing APIs against a database is where the API takes a unique value and returns a single record in response. You might have an API that returns a user record in response to the username, for example. The JSONPlaceholder API has several such calls, e.g. /posts/1, returning the post resource with ID 1.

When testing an API like this, you have several options. You could maintain a list of potential known post IDs and iterate over them. This could cause problems though if you suddenly need to run your tests against a different database and the IDs no longer match your list. A better approach is to consult the database you are running against, retrieve a post record at random, and run the API against the corresponding ID. Let’s take a look at how to configure such a test.

The JDBC test step

The JDBC test step allows you to submit SQL statements to the database. You can also refer to the response from the query in other test steps in your test case. A request to return all the fields from a randomly selected row from the posts table might look like this. JDBCStep01 Note that I’ve used properties in the Driver and Connection String fields, shown in the article in setting up the database. It’s wise to avoid using literal driver strings and connection strings in the individual test steps because you may need to update your database in the future.

You might also notice an option called Convert Column Names to Uppercase. To see this option, you’ll need to run at least SoapUI 5.3.0 or Ready!API 1.9. Older versions don’t have this option and some articles on this website make use of it.

At this point you should be able to execute the test step and see a record returned from the POSTS table. There are a couple of assertions I generally create on SQL statements of this type. One is a JDBC Status assertion, which simply tests that the connection was valid. The second assertion is an XPath match, and confirms that the SQL query returned exactly one row. This is a useful way to detect if no rows were returned (which would happen if the posts table is unexpectedly empty) and can be used to fail the test case pending further investigation. The assertion to check for the existence of a single row looks like this. JDBCAssertion01 After renaming my Xpath assertion with a specific name 1 result found, my completed JDBC window looks like this.
JDBCStep02 If you execute the step again, you should see a different record from the POSTS table returned each time.

Selecting random values via SQL

Note that the order by rand() clause in the SQL query is specific to the H2 database dialect of SQL. You’ll need to investigate the SQL required to return a single random row from your database type, but there are plenty of on-line examples for the major database types.

You should also be aware that selecting a random value from tables with many rows may incur a performance penalty. This consideration is beyond the scope of this article but there are examples for each database type on how to deal with this.

The REST test step

This is all that’s required for the JDBC test step. Call the step SQL GetPosts, and move on to configuring the REST test step.

This test is an example of the GET method, already configured in your service definition. Create a REST test step in the test case and configure its parameters as shown below. GETPosts02 You should create a property called resourceName on the test case with the value posts.

When configured as in the screenshot above, the id parameter takes its value from the POSTS.ID field in the response from the SQL test step. This is a key technique in this test case as it allows the REST request to call a resource based on whatever ID value the database randomly returned. Each time the test case runs, the value will be different, ensuring representative coverage of the test data. It also ensures that the values will refer to valid records if the database contents change over time.

You should now be able to run the REST step and see the response matching the record returned by the SQL call to the database. The next task is to configure assertions on each field that ensure that this is the case.

Configuring assertions

For the id field, create a JsonPath Match assertion that looks like this. JDBCAssertion02 This assertion looks at the value of the id field in the REST response and compares it against the POSTS.ID field in the SQL response. This is a simple way to validate the data in a REST request against the response from an earlier step in the test case. You can copy this approach in other assertions for the remaining fields in the records i.e.

$.userId → ${SQL GetPosts#ResponseAsXml#//POSTS.USERID}
$.title → ${SQL GetPosts#ResponseAsXml#//POSTS.TITLE}
$.body → ${SQL GetPosts#ResponseAsXml#//POSTS.BODY} 

After you rename your assertions with specific field names, the REST test step should look like this. GETPosts03 From now you can run the test case any number of times. Each time it runs the REST request will call a randomly selected post from the database and will validate its contents.

Let’s now look at how to set up a loop in the test case so that it runs against multiple post records.