Introduction To The API Under Test
Suppose you have an Article API that exposes an HTTP endpoint. When a client invokes it with details via HTTP PUT, it updates an article record in a SQL Server database table using a SQL statement like
update dbo.article set title = :title, content = :content where id = :id.
Check section Sample Test Case if you are eager to see what the test case looks like.
The example uses SQL Server database, but in the real world it can be any brand of database. The way of doing test isolation, database test data preparation and test case creation is similar, with some differences in detailed techniques.
Use Stub Database Endpoint
To integration unit test the Article API, design the API to point to different dependencies in different environments. This is normally achieved by setting different dependency endpoint addresses in different property files. Each property file contains all properties for the API for a specific environment like Dev/Test/QA/Prod. When deploying and running the API, dynamically load the property file for that specific environment.
In an integration unit testing environment like the Dev environment (which is typically a developer’s local machine), a property file like
article-api-dev.properties is used for the Article API. The property file contains something like below
database.host=localhost database.port=1433 database.name=Article database.username=<encrypted username> database.password=<encrypted password>
Here the database properties are of a local SQL Server Express (stub) database that is used for integration unit testing purpose. We use a local dedicated stub database, instead of any fully implemented and shared SQL Server database like one in the SIT environment, as the dependency during integration unit testing, like shown below.
Create Stub Table
The Article API will write into the dbo.Article table, so we need to create the table in the stub database, as part of test isolation work. Effectively, we need a SQL script that creates the table. Based on different conditions, the SQL script can be obtained or drafted in ways like
- If the script can be provided by the database administrator, then tailor it to suit your testing purpose.
- Else if the table is already created in a shared database, like the one in SIT environment, use a query like below to get the table structure. Then manually create the SQL script based on the structure.
SELECT c.name as column_name, t.name as column_type, c.max_length, c.precision, c.scale, c.is_nullable FROM sys.columns c left outer join sys.types t on c.user_type_id = t.user_type_id WHERE object_id = object_id('dbo.Article');
- Else if the table is only defined in a design document, manually create the SQL script based on the document content.
Below is a sample SQL script
IF DB_ID('Article') IS NULL CREATE DATABASE Article; USE [Article]; IF OBJECT_ID('dbo.Article', 'U') IS NOT NULL DROP TABLE dbo.Article; CREATE TABLE [dbo].[Article] ( ID INT, Title VARCHAR(50), Content VARCHAR(5000) );
Once the SQL script is obtained or drafted, run it in the stub database, to create the stub table.
Capture the SQL script somewhere, like in the description of the ATB folder that contains the integration unit test cases for the API, in case you need to reuse it later.
Database Test Data Preparation
We have created the stub table, but it is empty. Each test case will need to populate the table with corresponding test data. Effectively, we need some SQL
INSERT statements for this purpose. Based on different conditions, the
INSERT statements can be obtained or drafted in ways like
- If the statements can be provided by the database administrator, then tailor them to suit your testing purpose.
Else if there are already some rows in a shared database, like the one in SIT environment, use Squirrel SQL Client to export the rows as
INSERTstatements. To achieve this, just run a
SELECTquery in Squirrel SQL Client to show some rows, manually select the rows from the result set table, right click the rows and choose
Select entire row(s), right click the rows again and choose
Copy as SQL INSERT-VALUES statement, and then paste the
INSERTstatements to a text editor.
- Else if sample data is only available in a design document, manually create the
INSERTstatements based on the document content.
Once a couple of
INSERT statements are prepared, they can be tailored and used for different test cases of the API.
Test Case Creation
Refer to Creating Automated Test Case. On that page, the database is an H2 database, but the same steps for test case creation and run can be similarly applied here. Just remember to use your own local Article API endpoint (instead of the ATB bundled one) and your SQL Server stub database endpoint (instead of the H2 one) in the test case.
Sample Test Case
The test case created above is available for download at sample test case. After download, right click any folder on ATB UI and import it.