How to test with 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. 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

This article explains how to test with database queries.

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 or variables before clicking Save.

Pass a variable into a query

To pass a variable into a database query, don’t add it directly to the query. Instead, add it to the Parameters section of the mabl query editor:

• Use mabl variable syntax to add the variable as a parameter value. For example, the variable test_var would become {{@test_var}}.
• In the query itself, use the parameter syntax supported by your database type. For example, many database types use the format :paramName to reference a parameter. If you are unsure what the parameter syntax should be, refer to the official documentation for your database type.

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 reference article for more details on working with query metadata.

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

Chain queries across test steps

If a later query in your test needs a value from a previous query, use the following workflow:

1. Add a database query step. Write the query that returns the value you need.
2. In the Variables tab, store the result as a variable. For example, order_id.
3. When you add the subsequent database query step, create a parameter and set its default value to {{@order_id}}.
4. In the query body, reference the parameter using your database’s parameter syntax.

Set up and tear down test data

If your test depends on a specific database state, you can use query steps to set up and tear down test data:

1. Add a database query step at the beginning of your test to insert or update the data your test needs. If the query generates a value you’ll need later, like an auto-generated ID, store it as a variable.
2. Record your UI steps as usual.
3. Add a database query step at the end of your test to delete or revert the data created in step 1. Use parameters to reference any stored variables.

Generate a query with AI

In addition to writing your own query, you can also create queries based on your natural language prompts. Like other generative AI tools, the query generator works best when it has access to the right context. Keep in mind that the following factors can influence outcomes:

• Table metadata: make sure that the credentials for your database connection include permissions to access metadata, including table names, column names, and column types.
• Database naming conventions: the query generator doesn’t know the business context behind your data. If the naming conventions for table and column names aren’t easy to interpret with natural language, you’ll need to include more clarification in your prompts.

For best results, your prompt should be specific about the data you want to include with the dataset, what type of data should be grouped together, and whether anything should be filtered out. Prompts may include explicit SQL syntax, but the model generally does well with natural language prompts as long as they include the right details. For example:

“I am looking for animal visit details at a vet clinic and I want to see a unique list of all pet names with their owner’s name, address and phone number as well as the last time they have visited and the number of times they have visited in the last year. Order the listing by the most visits followed by the most recent visit date.”