Skip to content

Asserting with SQL Database Content

Botium can query SQL databases when testing conversation flow and assert the results and content of the database.

Currently supported SQL database connectors are:

  • MySQL

  • PostgresQL

  • Microsoft SQL Server

  • Oracle (experimental)

Setup and Configuration in Botium Box

The mentioned database connectors are part of Botium Box distribution. Navigate to menu Settings, section Registered Components to see the list of available asserters.

Clicking on one the asserters, for example the MySQL asserter, will show the configuration view.

Database Connection Configuration

The database connection configuration is in the Component Configuration field as JSON structure. This is different depending on the used database technology.

MySQL

The component configuration is handed over to the mysql driver. See Connection Options in the documentation page.

PostgresQL

The component configuration is handed over to the pg-promise driver. See Configuration Object in the documentation page.

Microsoft SQL Server

The component configuration is handed over to the node-mssql driver. See Configuration in the documentation page.

Using multiple Database Connections

In order to use multiple database connections you have to register multiple components. Copy the values of one of the existing SQL components into a new one.

Note

If you have multiple database connections for multiple environments (dev, test, prod):

  • you should have three registered components

  • The ref code should be the same, as this is the code used in the convo files for doing the assertions

  • For the different test projects, you have to choose the asserter appropriate for the environment to test is running on (see below)

Using Database Asserters

For using the database asserters in a test session, you first have to tell Botium Box what components to use.

Adding Asserters to a Test Project

In the Test Project settings, head over to Test Execution and add the SQL asserter to the Registered Components field.

Using Asserters in a Convo File

The asserters can by used by using the Ref Code as “magic word” in convo files, followed by an SQL statement.

hello

#begin
MYSQL create table if not exists example.test(firstname TEXT, lastname TEXT)

#me
Hi, my name is Kurt Wagner

#bot
You said: Hi, my name is $firstname $lastname
MYSQL INSERT INTO example.test (firstname, lastname) VALUES ("$firstname", "$lastname")

#me
Give me some links

#bot
MYSQL select * from example.test where firstname="Kurt"
MYSQL select * from example.test | Kurt | firstname
MYSQL select * from example.test | [ "Kurt" ] | $[0].firstname

#end
MYSQL DELETE from example.test

1st Parameter: SQL Statement

The first parameter is always an SQL statement to execute. Usually this will be a SELECT statement, but you can use all supported SQL statements.

You can construct a dynamic SQL statement by using Scripting Memory variables:

#bot
MYSQL select * from orders where ordernum="$ordernum"

Note

Scripting variables are not the same as SQL variables in a prepared statement!

If no more parameter is given, the asserter will only verify that for a SELECT statement at least one row is returned from the statement - otherwise the test case is failed.

2nd and 3rd Parameter (optional): Asserter Value and Asserter Selector

The second parameter is the value which is expected to be returned from the statement, the third parameter is used for selecting a portion of the returned result set.

  • If 3rd parameter is not given, the 2nd parameter should be a JSON structure to directly compare with the returned result set.

    #bot
    MYSQL select ordernum, status from orders where ordernum="$ordernum" | [ { "ordernum": "$ordernum", "status": "PENDING" } ]
    
  • If 3rd parameter is a JSONPath expression (starting with $), then the assertion is done on the output of this JSONPath expression.

    #bot
    MYSQL select ordernum, status from orders where ordernum="$ordernum" | [ "PENDING" } ] | $[0].status
    
  • Otherwise the 3rd parameter is considered as column name - the value of this column of the first row of the result set is used for assertion.

    #bot
    MYSQL select ordernum, status from orders where ordernum="$ordernum" | PENDING | status