How to use database query steps in mabl tests

With database query steps, you can make calls to your database within the context of a mabl test. Interacting directly with your database is useful in a variety of scenarios, including setting up/tearing data to simulate a specific state in your application.

This article explains how to use database query steps to a browser or mobile test, including:

• Creating a database query step
• Asserting on query results
• Storing query results in variables
• Understanding query metadata

Common use cases

Here are some ways you might use database query steps in a test:

• Set up test data: run a query to insert a new user or customer record into your database to get your application in a specific state for testing
• Validate data changes: after recording an interaction in the UI, run a query to ensure these changes are reflected on the backend, and vice versa
• Clean up after a test: use a query to delete data created during a test to ensure your database is ready for the next run

Create a database query step

Before you can send database queries from your mabl tests, you’ll need to set up a database connection in mabl.

In the mabl Trainer, get your application in the correct state and add the query step: + (Add step) > Database query. In the step editor, select a configured database:

The database step editor

You can either select an existing query or click on + New query to write a new query. When you create or update a query, the Trainer opens the Query Editor. For more information about writing database queries in mabl, check out this doc.

The query editor

Click on the Run button to test out the query.

After creating or selecting your query, add any necessary assertions and/or variables before clicking Save.

Assert on query results

After adding a database query step in the mabl Trainer, you can use assertions to confirm that the query returned the expected results.

To assert on a specific value in the results table, select the cell you want to validate and click on + Add assertion. Then configure the assertion as needed.

Asserting on a result

To assert on the values from an entire row, click on + Add assertion and enter the row number in the Target field. You can also assert on the entire JSON result by leaving the Target field empty.

Asserting on an entire row

To assert on query metadata, including column count, row count, rows affected, and response message, click on + Add assertion, set the source to Metadata, and configure the assertion as needed. See the section on understanding query metadata for more details.

Failure options

Database query steps fail if either the query fails or an assertion within the step fails. By default, database query steps are marked “Fail immediately”, which means that if the step fails, the test stops running and is marked “failed”.

If you want the test to continue running even if the step fails, update the behavior on failure in the Options tab of the Query Editor:

• “Fail at end of test” - if the step fails, the test continues running and is marked “failed” at the end.
• “Continue on failure & mark warning” - if the step fails, the step still passes and is marked with a warning.

Failure options for a database query step

Store query results in variables

After saving the database query step, you can store the results in variables and use them just like any other variable in the mabl Trainer.

To create a variable from a specific value in the results table, select the cell, click on + Add variable in the Variables tab, and give the variable a name.

Storing a result in a variable

To store an entire row as a variable, click on + Add variable and enter the row number in the Target field. You can also store the entire query result as a variable by leaving the Target field empty.

Storing the entire response in a variable

To store response metadata in a variable, including column count, row count, rows affected, and response message, click on + Add variable, set the source to Metadata, and configure the variable as needed. See the following section for more information on understanding query metadata.

Storing metadata in a variable

Understanding query metadata

In query steps, you can create assertions and variables based on query metadata, including row count, column count, rows affected, and the response message. For example, to confirm that an UPDATE query was successful, you might assert that rows_affected equals 1.

The meaning of this metadata depends on the type of database you're querying.

• Relational databases - metadata attributes are more straightforward in relational, SQL databases because they are tied to the rigid, tabular structure of the data. For example, column count refers to the fixed number of columns in a table, and row count refers to the number of rows in the result set.
• NoSQL databases - since NoSQL databases can accommodate less structured, schema-less data, metadata concepts are interpreted differently:
  • row count - in NoSQL queries, this refers to the number of documents returned, not rows in a table.
  • rows affected - in a NoSQL context, this refers to the number of documents affected by the query.
  • column count - this attribute is absent from NoSQL query metadata because documents don't have a fixed number of fields or a predefined column structure.