Scenarios / examples on database assertions would help

[Pulsar1224]

Hi Team,

Can we have scenarios related to db connection, and db assertion examples on the https://github.com/authorjapps/zerocode page. This will help a majority of users to get idea on how to work on db assertions.

[authorjapps]

@Pulsar1224 , Yes, we should do it soon with an example of executing the tests against PostGress DB and/or a MySql DB as they are free and Open Sourced (Oracle is licensed). Sorry to see that you didn't find an working example on the README file yet. We will put an example soon and thanks for your patience. 🙏 The idea is to either to spin up some RDS instances on AWS and grant you access, as well as a local docker. Whichever helps you or quicker, we will sort that out soon.

In the mean time, please have a look at this code, which might also help you or unblock you-

{
    "scenarioName": "Execute DB SQL and assert rows as simple JSON array",
    "steps": [
        {
            "name": "execute_db_sql",
            "url": "org.jsmart.zerocode.zerocodejavaexec.DbSqlExecutor",
            "operation": "fetchDbCustomers",
            "request": "select id, name from customers",
            "assertions": {
                "results": [
                    {
                        "id": 1,
                        "name": "Elon Musk"
                    },
                    {
                        "id": 2,
                        "name": "Jeff Bezos"
                    }
                ]
            }
        }
    ]
}

Here is the sample for

• The JSON step
• my_web_app.properties
• DbSqlExecutor.java

FYI- Though, we have executed hundreds of TCs against Oracle DB inside our various Banking projects, we never had any need to spin up a local oracle server instance. Now sounds like it's helpful to put an sample on the README Linked issue https://github.com/authorjapps/zerocode/issues/132

[BeTheCodeWithYou]

@Pulsar1224 will take a look and share details once ready. Cheers!

[BeTheCodeWithYou]

@Pulsar1224 , Please refer to sample java code for DB SQL executions and test scenario.

Wiki Page here explaining a solution(we will link to the README file soon)- https://github.com/authorjapps/zerocode/wiki/Sample-DB-SQL-Executor

Clone the repo and run test as described below https://github.com/BeTheCodeWithYou/SpringBoot-ZeroCode-Integration

We have added now a Postgres DB executor(You can mimic this for your MySql or Oracle DB etc).

Let's see below how we can achieve this:

• Sample Test steps
• Config properties (useful for your env switching)
• Executor code (You can even more customize this for your project specific)
• Test Logs(collected from console logs or target logs)

Sample Test steps:

{
    "scenarioName": "Postgres DB SQL Executor - Simple and generic one, you can extend to ",
    "steps": [
        {
            "name": "fetch_all",
            "url": "com.xp.springboot.dbutils.PostGresSqlExecutor",
            "operation": "executeSimpleSql",
            "request": "select * from employees;",
            "assertions": {
                "rows.SIZE": 2,
                "rows": [
                    {
                        "id": 1,
                        "name": "Jack"
                    },
                    {
                        "name": "Pulsar",
                        "id": 2
                    }
                ]
            }
        },
        {
            "name": "fetch_with_where",
            "url": "com.xp.springboot.dbutils.PostGresSqlExecutor",
            "operation": "executeSimpleSql",
            "request": "select id, name from employees where name='${$.fetch_all.response.rows[1].name}';",
            "assertions": {
                "rows.SIZE": 1,
                "rows": [
                    {
                        "id": 2,
                        "name": "${$.fetch_all.response.rows[1].name}"
                    }
                ]
            }
        }
    ]
}

Config properties

This is where you DB host, user, password details goes along with REST/SOAP/Kafka application host.

# Web Server host and port
web.application.endpoint.host=http://localhost
web.application.endpoint.port=8080
web.application.endpoint.context=

## ---------------------------
##  DB Host configs - Postgres
## ---------------------------
db_host_url=jdbc:postgresql://172.16.123.1:31435/postgres
db_username=replicaowner
db_password=password

## ---------------------------
##   DB Host configs - MySql
## ---------------------------
#db_host_url=jdbc:mysql://localhost:3306/empdb
#db_username=admin
#db_password=secret00

## ---------------------------
##   DB Host configs - Oracle
## ---------------------------
#db_host_url=jdbc:oracle://128.3.3.9:2102/empdb
#db_username=root
#db_password=pass00rd

Executor code

Here the config properties are automatically injected, hence env switching does not affect this code.

public class PostGresSqlExecutor {
    private static final Logger LOGGER = LoggerFactory.getLogger(PostGresSqlExecutor.class);
    Map<String, List<Map<String, Object>>> dbRecordsMap = new HashMap<>();
    public static final String RESULTS_KEY = "rows";

    @Inject
    @Named("db_host_url")
    private String dbHostUrl;

    @Inject
    @Named("db_username")
    private String dbUserName;

    @Inject
    @Named("db_password")
    private String dbPassword;

    public Map<String, List<Map<String, Object>>> executeSimpleSql(String simpleSql) {

        LOGGER.info("DB - Executing SQL query: {}", simpleSql);

        List<Map<String, Object>> recordsList = fetchDbRecords(simpleSql);

        // -------------------------------------------------------
        // Put all the fetched rows into nice JSON key and return.
        // -- This make it better to assert SIZE etc in the steps.
        // -- You can choose any key.
        // -------------------------------------------------------
        dbRecordsMap.put(RESULTS_KEY, recordsList);

        return dbRecordsMap;
    }

Test Logs

See the sample request/response after you have executed the test.

2018-11-30 15:50:07,869 [main] INFO com.xp.springboot.dbutils.PostGresSqlExecutor - DB - Executing SQL query: select * from employees;

2018-11-30 15:50:07,830 [main] INFO org.jsmart.zerocode.core.runner.ZeroCodeMultiStepsScenarioRunnerImpl - 
----- BDD: Scenario:Postgres DB SQL Executor - Simple and generic one  -------

--------- TEST-STEP-CORRELATION-ID: 09857cc4-b13b-48aa-aa52-10a21107a8db ---------
*requestTimeStamp:2018-11-30T15:50:07.857
step:fetch_all
url:com.xp.springboot.dbutils.PostGresSqlExecutor
method:executeSimpleSql
request:
"select * from employees;" 
--------- TEST-STEP-CORRELATION-ID: 09857cc4-b13b-48aa-aa52-10a21107a8db ---------
Response:
{
  "rows" : [ {
    "name" : "Jack",
    "id" : 1
  }, {
    "name" : "Pulsar",
    "id" : 2
  } ]
}
*responseTimeStamp:2018-11-30T15:50:07.958 
*Response delay:101.0 milli-secs 
---------> Assertion: <----------
{
  "rows.SIZE" : 2,
  "rows" : [ {
    "id" : 1,
    "name" : "Jack"
  }, {
    "name" : "Pulsar",
    "id" : 2
  } ]
} 
-done-

2018-11-30 15:50:07,992 [main] INFO com.xp.springboot.dbutils.PostGresSqlExecutor - DB - Executing SQL query: select id, name from employees where name='Pulsar';

--------- TEST-STEP-CORRELATION-ID: 174d132d-07a9-46c0-a8a2-4fd3e38de960 ---------
*requestTimeStamp:2018-11-30T15:50:07.992
step:fetch_with_where
url:com.xp.springboot.dbutils.PostGresSqlExecutor
method:executeSimpleSql
request:
"select id, name from employees where name='Pulsar';" 
--------- TEST-STEP-CORRELATION-ID: 174d132d-07a9-46c0-a8a2-4fd3e38de960 ---------
Response:
{
  "rows" : [ {
    "name" : "Pulsar",
    "id" : 2
  } ]
}
*responseTimeStamp:2018-11-30T15:50:08.002 
*Response delay:10.0 milli-secs 
---------> Assertion: <----------
{
  "rows.SIZE" : 1,
  "rows" : [ {
    "id" : 2,
    "name" : "Pulsar"
  } ]
} 
-done-

POM dependencies

This is for PostGres one, you need to add the similar driver dependencies for Oracle or Sybase or MySql etc.

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>${version.postgresql}</version>
        </dependency>

GitHub repo code files:

• src/main/java/com/xp/springboot/dbutils/PostGresSqlExecutor.java
• src/test/resources/sql_db_tests/postgres_db_get_parkrunners_test.json
• src/test/java/integrationtests/TestDbOperations.java
  • integrationtests.TestDbOperations#test_getParkRunnersFrom_postgresDb

@Pulsar1224 , can you please have a look and advice to close this issue ? (Full details are in the Wiki page)

[authorjapps]

@Pulsar1224, Updated how to doc is available at : https://github.com/authorjapps/zerocode/wiki/Sample-DB-SQL-Executor.

This issue can be close now.

Thanks @BeTheCodeWithYou.