1. Docs
  2. Use cases
  3. Use cases
  4. How do I check the database inside a UI test?

How do I check the database inside a UI test?

In SmartestQA you add a Database step to the test case in Automate with More, then Database: it runs an SQL query on a connection from Data Source, Connections, and saves the columns of the first row as variables. Assertion steps then compare those variables with what the UI did, in WEB, MOBILE and DESKTOP projects, on PostgreSQL, MySQL, Oracle and SQL Server.

10 min read Updated CustomersAdmins

In short

On this page you can

  • Run an SQL query between the UI steps of a test case.
  • Check that the record the UI created is really in the database.
  • Use values from the UI in the query, and values from the query in the UI.
  • Prepare or clean test data with INSERT, UPDATE or DELETE.
  • Keep the database password in the connection, not in the test.

How to use it

  1. In Data Source › Connections click New DB, fill it, Test Connection, Save.
  2. In Automate click More › Database and pick the Configuration.
  3. Type the SQL Query with named columns, for example with {{tc:orderId}}.
  4. List one name per column in Output Values and click Add.
  5. Add an Assertion step on {{tc:status}} and run the test case.

The short answer#

Add a Database step between the UI steps. In Automate click More › Database, pick a connection, type the SQL Query and list the names of the columns in Output Values. When the step runs, the values of the first row become variables such as {{tc:status}}, and an Assertion step compares them with the expected value.

The connection, with its password, is created once per project in Data Source. The query runs from the machine that runs the test, the server or your agent, so a database in your office network is reachable from your agent.

The problem this solves#

A UI test sees what the screen shows. It does not see what was stored. A form can show “Saved” while the record is missing, has the wrong status or carries the wrong amount. Many teams close that gap by hand: after the test, a tester opens an SQL client, copies the number from the screen into a query and looks at the row.

That check depends on a person having the time, the access and the right query. It is not part of the test, so it is not repeated on every run and leaves nothing in the report. Teams with their own test scripts can add database code, but then drivers, connection settings and passwords have to be managed in that code.

An example#

This is an illustration, not a customer story. An insurance team tests a web form for claims, and the QA lead wants every run to prove that a submitted claim is stored with the right status.

  1. The QA lead opens Data Source › Connections, clicks New DB and fills in the connection Claims DB (TEST). Test Connection answers “Connection test successful.”, and Save “DB configuration saved successfully.”
  2. In the test case that submits a claim, a tester adds a Get step that stores the claim number from the confirmation page as claimNo.
  3. The tester clicks More › Database, chooses Claims DB (TEST) under Configuration, types the SQL Query SELECT status, amount FROM claims WHERE claim_no = '{{tc:claimNo}}', lists status, amount in Output Values and clicks Add. The step shows the chip DB.
  4. An Assertion step follows: actual {{tc:status}}, Equals, expected SUBMITTED.
  5. The test case runs. The log shows Query executed successfully (…). Rows: 1 with the query, the connection and the extracted values.
  6. Some days later the form still shows its confirmation, but no row is stored. The query returns no row, no variable is set, the Assertion sees an empty value and fails.

The look into the database is now a step of the test, with the same query on every run.

By hand, with your own scripts, and with SmartestQA#

QuestionBy hand in an SQL clientYour own test scriptsSmartestQA
When is the database checked?After the test, when someone has timeInside the script, if it has database codeIn a Database step between the UI steps
How does the query know which record?The tester copies the number from the screenA variable in the codeA variable saved by an earlier step, for example {{tc:orderId}}
Where is the password?In each tester’s SQL clientIn a configuration file or secret store you manageIn the connection in Data Source, stored encrypted and never sent back to the screen
What fails the test?Nothing; a person judges the rowAn assertion in the codeAn Assertion step on the output. An error in the query ends the step as ERROR and stops the test case
Can it prepare or clean test data?Yes, by handYesYes: INSERT, UPDATE, DELETE and stored procedures run too. Changes are committed
Can it reach a database inside our network?Yes, from the tester’s machineYes, if the runner is inside the networkYes, from your agent: the query runs from the machine that runs the test

Steps#

  1. Create the connection

    Go to Data SourceConnections and click New DB. Fill Name, for example Orders DB (TEST), Database Url (it must start with jdbc:), DB Driver Class, Username and Password. Click Test Connection, then Save. See Add a database connection.

    “Connection test successful.”, then “DB configuration saved successfully.” The connection appears under Databases.

  2. Save the value the query needs

    In the test case, let a UI step save what identifies the record, for example a Get step that stores the order number as orderId. Later steps read it as {{tc:orderId}}. See Steps that produce variables.

  3. Open the Database dialog

    Click More › Database. The dialog DB Requests opens. Fill Test Step Name, for example Order status in DB, and choose the connection under Configuration. If the project has no connection, the list is empty.

  4. Write the query

    Type the SQL Query and name the columns you want: SELECT status, total FROM orders WHERE id = {{tc:orderId}}. Put text values in quotes: WHERE email = '{{tc:email}}'. The dialog checks the query as you type. See SQL rules.

  5. Name the outputs

    In Output Values list one name per column, separated by commas, in column order: status, total. Click Add.

    The step shows the chip DB. When you select it, the right panel shows a DB Requests tab with the query and outputs.

  6. Assert on the values

    Add an Assertion after the step with the actual value {{tc:status}} and the value you expect. You can also use the values in a later UI step, for example as the value of a Type step.

  7. Run it

    Run the test case. On success the log shows Query executed successfully (…). Rows: 1, followed by the query, the connection and the extracted values. Any error ends the step as ERROR and stops the test case. See At run time.

Rules and limits of a Database step#

ItemRule
DatabasesPostgreSQL, MySQL, Oracle, SQL Server
Project typesWEB, MOBILE, DESKTOP
Query typesSELECT, INSERT, UPDATE, DELETE, CREATE / ALTER / DROP and stored procedures (CALL / EXEC)
SELECTNamed columns only. SELECT * is refused: “Asterisk (*) is not allowed. Please specify explicit column names.” Aliases are fine: SELECT COUNT(id) AS n FROM orders.
Output mappingBy position, not by name: the first name gets the first column. Values come from the first row.
Output ValuesRequired: “Output values cannot be empty.” Extra names beyond the query’s columns stay empty.
No row returnedNo variable is set; an Assertion on it sees an empty value.
Query timeout30 seconds
Rows fetchedat most 100
ChangesPermanent: modifying queries are committed. The dialog warns, without blocking.
On errorThe step ends as ERROR and the test case stops. The log adds a hint.
Connects fromThe machine that runs the test: the server or your agent

Connection URL and driver examples#

DatabaseDatabase UrlDB Driver Class
PostgreSQLjdbc:postgresql://db.test.example.com:5432/shoporg.postgresql.Driver
MySQLjdbc:mysql://db.test.example.com:3306/shopcom.mysql.cj.jdbc.Driver
Oracle (SID)jdbc:oracle:thin:@db.test.example.com:1521:ORCLoracle.jdbc.OracleDriver
Oracle (service)jdbc:oracle:thin:@db.test.example.com:1521/shoporacle.jdbc.OracleDriver

SSL options go into the URL the way your driver expects them, for example ?sslmode=require for PostgreSQL. Do not put the password in the URL: it would be visible to everyone in the list. See URL and driver examples.

What you get#

  • UI and database in one test. The step sits between UI steps and reads variables saved before it; its outputs are available to every step after it.
  • Four databases. PostgreSQL, MySQL, Oracle and SQL Server, in WEB, MOBILE and DESKTOP projects.
  • More than SELECT. INSERT, UPDATE, DELETE and stored procedures run too, so a step can prepare or remove test data. Changes are committed.
  • Passwords out of the test. The database password lives in the connection, is stored encrypted and is never sent back to the screen. Steps pick the connection by name.
  • Readable failures. The log adds a hint, for example “The table "…" was not found.” or “Database authentication failed. Check the connection username/password.”
  • Fixed limits. 30 seconds per query, at most 100 rows fetched, values from the first row.

Questions and answers#

Which databases can a test query?Customers

A Database step in SmartestQA works with PostgreSQL, MySQL, Oracle and SQL Server. The connection is a JDBC URL plus a driver class, created in Data Source on the Connections tab. See Database step.

Does the database check work in mobile and desktop tests too?Customers

Yes. The Database step is offered in WEB, MOBILE and DESKTOP projects, under More › Database. See Add a Database step.

Why is SELECT * refused?Customers

SELECT needs named columns, and output names are mapped to the columns by position. SELECT * shows “Asterisk (*) is not allowed. Please specify explicit column names.” Write SELECT status, total FROM orders … and list status, total in Output Values. See Output Values.

Can a Database step change data, for example insert or delete test records?Customers

Yes. Any query type works: INSERT, UPDATE, DELETE, CREATE / ALTER / DROP and stored procedures. Changes are permanent because modifying queries are committed; the dialog warns, for example “This query makes permanent changes in the database (committed). Please make sure before continuing.”, without blocking. See SQL rules.

Our database is only reachable inside our network. Can the test reach it?Customers

Yes, when the test runs on your agent inside the network. The query runs from the machine that runs the test: a database in your office network is reachable from your agent, not from the server. See Run on Your Agent.

Where is the database password stored, and who can see it?Customers

In the DB connection in Data Source. It is required when you create the connection, stored encrypted and never sent back to the screen; the field stays empty when you open the connection. The runner receives the password with the run that needs it and masks it like any other secret. See Connection passwords.

What happens if the query returns no row?Customers

No variable is set, and an Assertion on it sees an empty value. An error, such as a wrong column name or a failed login, is different: it ends the step as ERROR and stops the test case. See Output Values.

How do I use a different database for TEST and DEV?Customers

A Database step selects one connection, whatever the run’s environment. Create one connection per environment and name them clearly, for example Orders DB (TEST). Environment-specific keys apply to keys and datasets, not to connections. See Change a connection.

Test Connection works. Why can the run not connect?Customers

Test Connection loads the DB Driver Class on the SmartestQA server. A test run reads the database type from the start of the Database Url and connects from the machine that runs the test, with its own client. Keep URL and driver consistent, and run on an agent inside the network when the database is not reachable from outside. See Test Connection works, the run cannot connect.

What happens to my tests if someone deletes the connection?Customers

The dialog Delete DB Configuration first lists the test cases and data sources that use it. With Delete Anyway, every DB step on the connection is removed from its test case, and there is no undo. Switch the DB steps to another connection first. See Delete a database connection.

Can I check a Kafka message in the same test as well?Customers

Yes. SmartestQA has Kafka steps under More › Kafka: Produce sends a message to a topic, Consume waits for a matching message. They use a Kafka connection from the same Connections tab. See Kafka steps.

Read more#

Didn’t find your answer?Write to support with your project name and what you see on screen.

info@smartestqa.ai