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
The mentioned database connectors are part of Botium distribution. Navigate to menu Global Settings, section 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 what components to use.
Adding Asserters to a Test Project
In the Test Project configuration, head over to Execution and add the SQL asserter to the Involved 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.test1st 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"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
Comments
0 comments
Please sign in to leave a comment.