Writing database queries

In mabl, you can use database query steps to create, read, update, and delete data in a connected database. Before writing database queries, you or someone on your team should be familiar with the schema of the database you’re querying and know how to safely construct queries. This article explains capabilities and limitations when writing database queries in mabl.

The query editor

SQL queries

When working with SQL databases, ensure your query adheres to the database’s specific SQL syntax. For example, a query to retrieve records created in the last 10 minutes from a test_users table would look different in MySQL and Oracle.

In MySQL, the query uses the NOW() function and a simple INTERVAL syntax:

SELECT * FROM test_users WHERE created_at > NOW() - INTERVAL 10 MINUTE;

In Oracle, the same query uses the SYSDATE function and requires single quotes for the INTERVAL value:

SELECT * FROM test_users WHERE created_at > SYSDATE - INTERVAL '10' MINUTE;

For the more detailed rules on syntax and conventions, please refer to the official documentation for your respective database.

NoSQL queries

Many NoSQL shells require you to type a single, complete command, such as db.todo.find({"completed": false}).sort({"dueDate": 1}). However, in the mabl Query Editor you need to break down the components of the command and input them in separate fields: method, collection, filter, and options.

If you’re more familiar with using the db.collection() notation to write NoSQL queries, the following table shows how to break down the one-line command and input in the mabl Query Editor:

GenAI DB Query Generator

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.”

Query parameters

With parameters, you can make your query more reusable in different contexts. To add an existing variable from your mabl tests 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 uses @paramName format for parameters.

Save options

Click on the down arrow next to the Save button to see the full range of save options. 

Save as one-time query

Select this option if you have no plans to reuse the query. If you change your mind later, however, you can always convert a one-time query into a reusable query by clicking “Save as new reusable query.” One-time queries are versioned like any other test step and are not available from the queries page: Tests > Queries.

Save as new reusable query

Select this option to create a new query and reuse it across tests. This option is only available for queries created in the mabl Trainer. You cannot create a new query from an existing query on the queries page: Tests > Queries.

Save

Update changes for an existing query. For reusable queries, saving changes updates the query across all tests that use it.

After saving a query, you can add assertions and create variables on the database query step in the mabl Trainer. Please note that these assertions and variables are linked to specific test steps. You cannot configure assertions and variables for a reusable query from the directly queries page: Tests > Queries.

Those actions are only possible when you add the query as a test step in the mabl Trainer.

Limitations

The query editor includes the following known limitations. If any of these limitations present challenges for your use case, please share feedback in the mabl Product Portal.

Size limitations

Query results cannot exceed 100 rows or 1MB in size. To avoid returning queries that exceed these size limits, consider writing a more precise query so that you get only the necessary rows and columns. This type of precision querying is generally recommended as a best practice for constructing tests. Alternatively, if all the information is necessary, create multiple database query steps.

Unsupported column types

While most database column types are supported, the query editor does not support some specialty column types, including JSON and Buffer types.

Querying from multiple tables

Queries that return results from multiple tables are not supported, such as:

SELECT * FROM TABLE1; SELECT * FROM TABLE2;

If applicable, write the query using JOIN or UNION instead.