Database queries

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…

• Validating that changes in the UI are appropriately reflected in the database
• Validating that changes in the database are appropriately reflected in the UI
• Populating mabl test data by storing data from your database as mabl variables
• Setting up data to simulate a specific state in your application (also known as data seeding) and tearing it down when the test is complete

This article explains how to add database query steps to your test, including how to…

• Create a database query
• Use an existing database query
• Update a database query

Creating a database query

Before creating a database query, make sure you have configured a connection to the database in the configuration section of the mabl app: Configuration > Database connections.

To create a database query step in the mabl Trainer, take the following actions:

1. Click on the plus sign to add a new step.
2. Select the Database query step.
3. In the Query Editor, select a configured database.
4. Click on the New query button.
5. Write your query.
   • If you want to include dynamically generated values, add parameters.
   • If you're new to writing queries or want help writing a more complex query, try describing your query in the AI Query Generator.
6. Click on the Run query button. 
7. Add an assertion that the query returns the expected results.
8. If you want to use data from the result in subsequent test steps, create one or more variables.
9. Select a save option:
   • Save as one-time query: one-time queries are used once in the test and cannot be reused in other tests.
   • Reusable query: reusable queries can be used more than once in the test and can be reused in other tests.

The Query Editor

Query parameters

With parameters, you can make your query more reusable in different contexts. To add an existing variable from your test as a parameter value, use mabl variable syntax. For example, a variable named test_var would become {{@test_var}} in the parameter section of the Query Editor.

In the query itself, most database types use the format :paramName to reference a parameter. For database types that have a specific format that developers may be familiar with, the query editor supports that specific driver's format. For example, Microsoft SQL Server uses @paramName format for parameters.

AI Query Generator

In addition to writing your own query, you can also create a query using generative AI. In the AI prompt input, describe the objective for your query using natural language and click Generate.

If you use the AI Query Generator, keep in mind the following:

• As with any generative AI capability, the results are not fully predictable, and errors can occur. Be sure to double-check any results before incorporating into your tests.
• Ensure the credentials that are used to set up the database connection include permissions to access the metadata table.

For more on generative AI at mabl, click here.

Assertions

Add assertions to confirm that the query returned the expected results.

Asserting on a result

To assert on a value in the results table, select the cell, open the Assertions tab, and click + Add assertion. Configure assertion details as needed.

Asserting on result metadata

To assert on result metadata, take the following steps:

1. In the Assertions tab, click + Add assertion
2. Update the Source to "Metadata"
3. Select the Target: "Row count", "Column count", "Rows affected", or "Message"
4. Configure the assertion type and value as needed.

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

Variables

Create variables from the results of a database query step. After saving the database query step, you can use the new variable just like any other variable in the mabl Trainer.

Creating variables from a result

To create a variable from a value in the results table, select the cell, open the Variables tab, and click + Add variable. Give your variable a name.

Creating variables from result metadata

To create a variable from result metadata, take the following steps:

1. In the Variables tab, click + Add variable.
2. Give the variable a name.
3. Update the Source to "Metadata"
4. Select the Target: "Row count", "Column count", "Rows affected", or "Message"

Storing a value from the result in a variable

Using an existing database query

To use an existing database query into your test, take the following steps:

1. Click on the plus sign to add a new step.
2. Select the Database query step.
3. In the Query Editor, select a configured database.
4. Select a query.
5. Click Save to add the database query step to the test.

Updating a database query

To update a database query in the mabl Trainer, take the following steps:

1. Hover over the database query step and click on the edit pencil.
2. Click on the Edit query button.
3. Update the database query as needed.
4. Choose a save option:
   • Save
   • Save as new reusable query
   • Save as one-time query

You can also update reusable queries from the mabl app:

1. Go to the queries page: Tests > Queries.
2. Click on the query you'd like to update.
3. Update the query as needed.
4. Save the query.