Relational Database Plugin

The plugin provides capabilities to work with relational databases via JDBC and contains a set of the steps for comparison of data tables.

Java Database Connectivity (JDBC) is an application programming interface for Java, which defines how a client may access a database. Every JDBC requires using specific driver. JDBC drivers are client-side adapters (installed on the client machine, not on the server) that convert requests from Java programs to a protocol that the database management system (DBMS) can understand.

Installation

  1. Copy the below line code to dependencies section of the project build.gradle file

    Example 1. build.gradle
    implementation(group: 'org.vividus', name: 'vividus-plugin-db', version: '0.5.2')
  2. Define your JDBC driver and add appropriate dependency. Let’s take PostgreSQL as an example:

    • Open JDBC page on maven repository;

    • Find driver for your database and open details (PostgreSQL JDBC Driver);

    • Choose and open version (42.4.0);

    • Go to the Gradle tab and copy implementation string;

    • Paste chosen dependency into your build.gradle file:

      Example 2. build.gradle
      implementation(group: 'org.postgresql', name: 'postgresql', version: '42.4.0')
      In case Maven repository does not contain driver you need, please refer to the documentation of your database to define suitable JDBC driver.
  3. If the project was imported to the IDE before adding new plugin, re-generate the configuration files for the used IDE and then refresh the project in the used IDE.

Configuration

Database connection

For database interaction, you will need to set related properties. They have db.connection. prefix followed by the database connection name (vividus in the example). Come up with your own database name to use within the tests. Put properties into the environment.properties file:

Example 3. environment.properties
db.connection.vividus.url=jdbc:dbms://databaseUrl:port/databaseName?parameter1=true&parameter2=value (1)
db.connection.vividus.username= (2)
db.connection.vividus.password= (2)
db.connection.vividus.driver-class-name=(3)
1 Database address. Consists of:
  • jdbc: - reference to JDBC;

  • dbms: - Database Management System, e.g. postgresql;

  • //databaseUrl - address to your database;

  • :port - port number, e.g. 5432;

  • /databaseName - name of your database;

  • parameters - any additional driver parameters you need to pass;

    URL structure slightly changes depending on the database management system. You can find templates for different DBMS in setup examples.
2 Credentials for given database connection;
3 Driver class name (Optional), e.g. org.postgresql.Driver. You might need it to specify the driver name for the exact database.

Setup examples

Here are some examples of setting different databases connection for most popular DBMS-s.

MySQL
Example 4. build.gradle
implementation(group: 'com.mysql', name: 'mysql-connector-j', version: '8.0.31')
Example 5. environment.properties
db.connection.vividus.url=jdbc:mysql://mysql.db.server:3306/my_database?useSSL=false&serverTimezone=UTC
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 6. URL template
jdbc:mysql://host:port/database?parameters
Microsoft SQL Server
Example 7. build.gradle
implementation(group: 'com.microsoft.sqlserver', name: 'mssql-jdbc', version: '12.1.0.jre11-preview')
Example 8. environment.properties
db.connection.vividus.url=jdbc:sqlserver://mssql.db.server:1433\mssql_instance;databaseName=my_database
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 9. URL template
jdbc:sqlserver://serverName\instanceName:portNumber;parameter1=value1;parameter2=value2
Azure SQL
Example 10. build.gradle
implementation(group: 'com.microsoft.sqlserver', name: 'mssql-jdbc', version: '11.2.1.jre11')
Example 11. environment.properties
db.connection.vividus.url=jdbc:sqlserver://vividusdb.database.windows.net:1433;database=demo;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 12. URL template
jdbc:sqlserver://serverName:portNumber;parameter1=value1;parameter2=value2
PostgreSQL
Example 13. build.gradle
implementation(group: 'org.postgresql', name: 'postgresql', version: '42.5.1')
Example 14. environment.properties
db.connection.vividus.url=jdbc:postgresql://postgresql.db.server:5432/my_database?ssl=true&loglevel=2
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 15. URL template
jdbc:postgresql://host:port/database?parameters
Oracle Database
Example 16. build.gradle
implementation(group: 'com.oracle.database.jdbc', name: 'ojdbc10', version: '19.17.0.0')
Example 17. environment.properties
db.connection.vividus.url=jdbc:oracle:thin:@//myoracle.db.server:1521/my_servicename
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 18. URL template based on the services approach
jdbc:oracle:thin:user/password@//host:port/service
DB2
Example 19. build.gradle
implementation(group: 'com.ibm.db2.jcc', name: 'db2jcc', version: 'db2jcc4')
Example 20. environment.properties
db.connection.vividus.url=jdbc:db2://localhost:50000/my_db
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 21. URL template
jdbc:db2://<host>:<port>/<database_name>
CSV

It is possible to perform SQL queries against CSV resources. Please see the step-by-step guide for more details.

Example 22. build.gradle
implementation(group: 'net.sourceforge.csvjdbc', name: 'csvjdbc', version: '1.0.40')
Example 23. environment.properties
db.connection.csv-data.url=jdbc:relique:csv:classpath:path/to/csv/data
Example 24. URL template
jdbc:relique:csv:classpath:path/to/resources?parameters
H2

VIVIDUS allows using in-memory databases during test execution. Such databases are created at the beginning of the tests and are dropped after the run is finished. Please see the detailed guide.

Example 25. build.gradle
implementation(group: 'com.h2database', name: 'h2', version: '2.1.214')
Example 26. environment.properties
db.connection.csv-data.url=jdbc:relique:csv:classpath:path/to/csv/data
Example 27. URL template for in-memory named database
jdbc:h2:mem:databaseName
Snowflake
Example 28. build.gradle
implementation(group: 'net.snowflake', name: 'snowflake-jdbc', version: '3.13.25')
Example 29. environment.properties
db.connection.vividus.url=jdbc:snowflake://myinstance.west-europe.azure.snowflakecomputing.com/?role=DEVELOPER&warehouse=WAREHOUSE&db=MY_DB
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 30. URL template
jdbc:snowflake://account_identifier.snowflakecomputing.com/?connection_parameters

Data Comparison

Property name Acceptable values Default Description

db.duplicate-keys-strategy

NOOP

DISTINCT

NOOP

The strategy defining how records with duplicate keys should be handled:

  • NOOP - the records with duplicate keys are compared in the order they are returned by the query or presented in the expected data

  • DISTINCT - only 1 top record from left and right sides are compared, other records with duplicate keys are ignored

db.diff-limit

integer

100

The max number of records in the comparison result output

db.query-timeout

ISO-8601 duration

PT30M

The maximum time to wait for DB query completion

Table Transformers

The table transformers properties marked with bold are mandatory.

FROM_DB

FROM_DB transformer generates table based on the data retirved from the specified database using SQL query.

Parameter Description

dbKey

Database key

sqlQuery

SQL SELECT query to retrieve the data from DB

nullReplacement

The value to put in the generating table instead of NULL-s contained in the DB data set

Example 31. Generate ExampleTable using data from H2 database
{transformer=FROM_DB, dbKey=testh2db, sqlQuery=SELECT * FROM students, nullReplacement= }

Steps

Execute SQL query and save its result

Executes SQL query against the specified database and saves the query result to a variable.

When I execute SQL query `$sqlQuery` against `$dbKey` and save result to $scopes variable `$variableName`
Example 32. Given launch_rockets table available at 'launches' db connection
|id|name        |payload_cost|country|
|1 |Falcon 9    |$2,720      |USA    |
|2 |Zhongxing-19|$4,320      |CHINA  |
Example 33. Query USA rocket launches and validate result set
When I execute SQL query `
SELECT *
FROM launch_rockets
WHERE country='USA'
` against `launches` and save result to scenario variable `usa-launches`
Then `${usa-launches}` is equal to table:
|name    |payload_cost|country|
|Falcon 9|$2,720      |USA    |

Individual rows are available by index starting from zero as per the following example:

Example 34. Query USA rocket launches and check name in first row
When I execute SQL query `
SELECT *
FROM launch_rockets
WHERE country='USA'
` against `launches` and save result to scenario variable `usa-launches`
Then `usa-launches[0].name` is equal to `Falcon 9`

Execute update query

Executes SQL query that changes the target table e.g.

In case of SELECT SQL query an exception will be thrown.
When I execute SQL query `$sqlQuery` against `$dbKey`
Example 35. Given launch_rockets table available at 'launches' db connection
|id|name        |payload_cost|country|
|1 |Falcon 9    |$2,720      |USA    |
|2 |Zhongxing-19|$4,320      |CHINA  |
Example 36. Update payload_cost for 'Falcon 9' launch
When I execute SQL query `
UPDATE launch_rockets
SET payload_cost='unknown'
WHERE name='Falcon 9'
` against `launches`

Compare two result sets

Compares the result set queried from the baseline database with the result set queried from the checkpoint database matching by user-defined keys. Please see Data Comparison for more fine-grained comparison configuration. The order of columns is ignored.

Then data from `$leftSqlQuery` executed against `$leftDbKey` $comparisonRule data from `$rightSqlQuery` executed against `$rightDbKey` matching rows using keys:$keys
Example 37. Given launch_rockets table available at 'launches' and 'launches-replica' db connections
|id|name        |payload_cost|country|
|1 |Falcon 9    |$2,720      |USA    |
|2 |Zhongxing-19|$4,320      |CHINA  |
Example 38. Compare name and country columns in launch_rockets table contained in two databases
Then data from `SELECT * FROM launch_rockets` executed against `launches` is equal to data from `SELECT * FROM launch_rockets` executed against `launches-replica` matching rows using keys:name,country

Compare result set with table

Compares the result set queried from the baseline database with the user-defined data set matching by the specified keys. Please see Data Comparison for more fine-grained comparison configuration. The order of columns is ignored.

Then `$leftData` matching rows using `$keys` from `$leftDbKey` $comparisonRule data from:$rightTable
  • $leftData - The baseline result set.

  • $keys - The database columns to compare.

  • $comparisonRule - The comparison rule.

  • $rightTable - The checkpoint data set.

Example 39. Given launch_rockets table available at 'launches' db connection
|id|name        |payload_cost|country|
|1 |Falcon 9    |$2,720      |USA    |
|2 |Zhongxing-19|$4,320      |CHINA  |
Example 40. Compare name and country columns in result set against defined data set
When I execute SQL query `SELECT * FROM launch_rockets` against `rockets` and save result to scenario variable `all-launches`
Then `${all-launches}` matching rows using `name,country` from `launches` is equal to data from:
|name        |country|
|Falcon 9    |USA    |
|Zhongxing-19|CHINA  |