My SoapUI Thoughts on Effective Testing in SoapUI

Database Setup in SoapUI

The H2 database used in these examples can be downloaded here. For Linux installations, the database runs from a single JAR file in the installation’s bin directory. The file can be invoked from this directory with the following command in a terminal window (substituting the correct version number for the JAR file in your installation):

java -jar h2-1.4.194.jar -tcp &

This will start the database and enable it to accept connections.

SoapUI Setup

Every test step in SoapUI that accesses a database via a JDBC connection requires two components: a driver string and a connection string. This applies whether you’re using a JDBC test step or a groovy script to access the database programmatically. It’s wise to store these strings as properties so that if they change you won’t need to update all your test steps.

The driver string is specific to each database type (H2, Oracle, SQL*Server etc) and can be stored as a global property. For H2 databases, the driver string is org.h2.Driver , configured as follows.

Preferences01 The connection string format is specific to a database type and refers to an individual database instance. For many database types, the connection string contains the login details and any flags required to access the database. For our tests, the connection string required to access the H2 database running on the local machine using the default user SA will look like this:

jdbc:h2:tcp://localhost/~/test:sa;ALIAS_COLUMN_NAME=TRUE

A database instance is often associated with a project, so for the purposes of these tests we can store this connection string as a dbConnection property on the SoapUI project as follows. ProjProps01 Using these properties for the driver string and connection string, you should be able to create a JDBC test step in any test case and get a successful connection to the database.

dbConn01

Populating the database with JSONPlaceholder data

The tests in these articles illustrate how the responses returned by the JSONPlaceholder APIs can be validated via SQL queries. This implies that our database will need to be populated with the data that the APIs return. (In a real-world scenario the database would already contain this data.) In order to do this, I’ve put together a script to retrieve the data for each resource type and insert it into the database. Note that these instructions are presented only as a way of equipping you to run the tests described in other sections.

To do this, create a test case with a groovy test step called LoadDatabase containing the following script.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
import groovy.json.JsonSlurper
import groovy.sql.*
 
final def driver = context.expand( '${h2Driver}' ),
		dbConn = context.expand( '${#Project#dbConnection}' ),
		tc     = testRunner.testCase,
		apiStep= tc.getTestStepByName( 'GetData' ),
		sqlStep= tc.getTestStepByName( 'Check SQL' ),
		resources = [ 
		   'todos' : [
			dbCreate : "todos( id integer, userId integer, title varchar( 200 ), completed bit )",
			dbInsert : "todos values( :id, :userId, :title, :completed )"
		], 'posts' : [
			dbCreate : "posts( id integer, userId integer, title varchar( 200 ), body varchar( 2000 ) )",
			dbInsert : "posts values( :id, :userId, :title, :body )"
		], 'albums' : [
			dbCreate : "albums( id integer, userId integer, title varchar( 200 ) )",
			dbInsert : "albums values( :id, :userId, :title )"
		], 'photos' : [
			dbCreate : "photos( id integer, albumId integer, title varchar( 200 ), url varchar( 200 ), thumbnailUrl varchar( 200 ) )",
			dbInsert : "photos values( :id, :albumId, :title, :url, :thumbnailUrl )"
		], 'comments' : [
			dbCreate : "comments( id integer, postId integer, name varchar( 200 ), email varchar( 200 ), body varchar( 2000 ) )",
			dbInsert : "comments values( :id, :postId, :name, :email, :body )"
		], 'users' : [
			dbCreate : "users( id integer, name varchar( 200 ), username varchar( 50 ), email varchar( 50 ) )",
			dbInsert : "users values( :id, :name, :username, :email )"
		] ]

def db = null
try{
	db = Sql.newInstance( dbConn, driver )
	resources.each {
		final def res = it.value
		db.execute "drop table if exists " + it.key
		db.execute "create table " + res.dbCreate

		tc.setPropertyValue( 'resourceName', it.key )
		final def stepResult = testRunner.runTestStep( apiStep ),
				exchange 	= stepResult?.getMessageExchanges()[ 0 ],
				apiData	= exchange?.getResponseContent(),
				JSONRows	= new JsonSlurper().parseText( apiData )

		int[] updateCount = db.withBatch( "insert into " + res.dbInsert ) { batch ->
			JSONRows?.each{ row ->
				def rowMap = [:]
				row.each{ rowMap."${it.key}" = it.value.toString() }
				batch.addBatch( rowMap )
			}
		}
		context.JSONRowCount = JSONRows.size()
		testRunner.runTestStep( sqlStep )
	}
}
catch( Exception e ) { log.error e.toString() }
db?.close()

Create a REST test step called GetData configured as follows. getData01 You can also add a HTTP status code assertion to this step to check that the HTTP code is 200.

Create a JDBC test step called Check SQL configured as follows. checkSQL01 Add a script assertion to this test step with the following code.

1
2
assert context.JSONRowCount == 
	new XmlParser().parseText( messageExchange?.getResponseContent() )?.ResultSet.Row?.size()

Run the test case. The test case should run to completion and populate the database with the responses from the SQL query.

Your SoapUI installation should now allow you to follow the articles relating to API testing with SQL validation.