Database query syntax and reference

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 describes the supported syntax and format for writing queries in 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:

MongoDB data types in filters

When filtering by ObjectId, omit the single quotes that you would normally use in the MongoDB shell. The filter field in the mabl query editor parses values as JSON, so single quotes inside an ObjectId('...') function can cause unexpected results.

For example, to filter by a specific ObjectId in the MongoDB shell, you would write:

{protocolId: ObjectId('507f1f77bcf86cd799439011')}

In the mabl query editor filter field, remove the single quotes around the ID value:

{"protocolId": "ObjectId(507f1f77bcf86cd799439011)"}

Saving queries

The query editor offers the following 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 the specific test step, not the query. For example, if you add assertions to a query step, and reuse the same query for another test step, those assertions don’t transfer to the new test step.

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.

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.