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.8')

  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.5.4);

    • 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.6.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.32')
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.2.0.jre11')
Example 8. environment.properties to connect using port number
db.connection.vividus.url=jdbc:sqlserver://mssql.db.server:1433;databaseName=my_database
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 9. environment.properties to connect using instance name
db.connection.vividus.url=jdbc:sqlserver://mssql.db.server\\mssql_instance;databaseName=my_database
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Pay attenttion to \\ before instance name: it’s required because \ is a special char in properties-files and thus meaningful backward slash should be additionally escaped: \\ will result in \.
Example 10. URL template
jdbc:sqlserver://serverName\instanceName:portNumber;parameter1=value1;parameter2=value2
Azure SQL
Example 11. build.gradle
implementation(group: 'com.microsoft.sqlserver', name: 'mssql-jdbc', version: '11.2.1.jre11')
Example 12. 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 13. URL template
jdbc:sqlserver://serverName:portNumber;parameter1=value1;parameter2=value2
PostgreSQL
Example 14. build.gradle
implementation(group: 'org.postgresql', name: 'postgresql', version: '42.6.0')
Example 15. 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 16. URL template
jdbc:postgresql://host:port/database?parameters
Oracle Database
Example 17. build.gradle
implementation(group: 'com.oracle.database.jdbc', name: 'ojdbc10', version: '19.18.0.0')
Example 18. 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 19. URL template based on the services approach
jdbc:oracle:thin:user/password@//host:port/service
DB2
Example 20. build.gradle
implementation(group: 'com.ibm.db2.jcc', name: 'db2jcc', version: 'db2jcc4')
Example 21. environment.properties
db.connection.vividus.url=jdbc:db2://localhost:50000/my_db
db.connection.vividus.username=vividus
db.connection.vividus.password=pa$$w0rd
Example 22. 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 23. build.gradle
implementation(group: 'net.sourceforge.csvjdbc', name: 'csvjdbc', version: '1.0.40')
Example 24. environment.properties
db.connection.csv-data.url=jdbc:relique:csv:classpath:path/to/csv/data
Example 25. 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 26. build.gradle
implementation(group: 'com.h2database', name: 'h2', version: '2.1.214')
Example 27. environment.properties
db.connection.csv-data.url=jdbc:relique:csv:classpath:path/to/csv/data
Example 28. URL template for in-memory named database
jdbc:h2:mem:databaseName
Snowflake
Example 29. build.gradle
implementation(group: 'net.snowflake', name: 'snowflake-jdbc', version: '3.13.29')
Example 30. 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 31. 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 Durations format

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 32. 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 33. 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 34. 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 35. 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 36. 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 37. 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 data 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:$columnsForMapping

  • $leftSqlQuery - The SQL query to execute to retrieve baseline data set.

  • $leftDbKey - The database connection key to execute baseline query against.

  • $comparisonRule - The data set comparison rule: either is equal to or contains.

  • $rightSqlQuery - The SQL query to execute to retrieve checkpoint data set.

  • $rightDbKey - The database connection key to execute checkpoint query against.

  • $columnsForMapping - The comma-separated list of column names to map rows in the retrieved data sets. If empty then all column names from the retrieved data sets are used to map rows.

Example 38. 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 39. 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 40. 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 41. 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  |