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.
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
- In Data Source › Connections click New DB, fill it, Test Connection, Save.
- In Automate click More › Database and pick the Configuration.
- Type the SQL Query with named columns, for example with
{{tc:orderId}}. - List one name per column in Output Values and click Add.
- 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.
- 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.” - 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. - The tester clicks More › Database, chooses
Claims DB (TEST)under Configuration, types the SQL QuerySELECT status, amount FROM claims WHERE claim_no = '{{tc:claimNo}}', listsstatus, amountin Output Values and clicks Add. The step shows the chip DB. - An Assertion step follows: actual
{{tc:status}}, Equals, expectedSUBMITTED. - The test case runs. The log shows
Query executed successfully (…). Rows: 1with the query, the connection and the extracted values. - 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#
| Question | By hand in an SQL client | Your own test scripts | SmartestQA |
|---|---|---|---|
| When is the database checked? | After the test, when someone has time | Inside the script, if it has database code | In a Database step between the UI steps |
| How does the query know which record? | The tester copies the number from the screen | A variable in the code | A variable saved by an earlier step, for example {{tc:orderId}} |
| Where is the password? | In each tester’s SQL client | In a configuration file or secret store you manage | In the connection in Data Source, stored encrypted and never sent back to the screen |
| What fails the test? | Nothing; a person judges the row | An assertion in the code | An 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 hand | Yes | Yes: INSERT, UPDATE, DELETE and stored procedures run too. Changes are committed |
| Can it reach a database inside our network? | Yes, from the tester’s machine | Yes, if the runner is inside the network | Yes, from your agent: the query runs from the machine that runs the test |
Steps#
Create the connection
Go to Data SourceConnections and click New DB. Fill Name, for example
Orders DB (TEST), Database Url (it must start withjdbc:), 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.
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.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.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.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.
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.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#
| Item | Rule |
|---|---|
| Databases | PostgreSQL, MySQL, Oracle, SQL Server |
| Project types | WEB, MOBILE, DESKTOP |
| Query types | SELECT, INSERT, UPDATE, DELETE, CREATE / ALTER / DROP and stored procedures (CALL / EXEC) |
| SELECT | Named 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 mapping | By position, not by name: the first name gets the first column. Values come from the first row. |
| Output Values | Required: “Output values cannot be empty.” Extra names beyond the query’s columns stay empty. |
| No row returned | No variable is set; an Assertion on it sees an empty value. |
| Query timeout | 30 seconds |
| Rows fetched | at most 100 |
| Changes | Permanent: modifying queries are committed. The dialog warns, without blocking. |
| On error | The step ends as ERROR and the test case stops. The log adds a hint. |
| Connects from | The machine that runs the test: the server or your agent |
Connection URL and driver examples#
| Database | Database Url | DB Driver Class |
|---|---|---|
| PostgreSQL | jdbc:postgresql://db.test.example.com:5432/shop | org.postgresql.Driver |
| MySQL | jdbc:mysql://db.test.example.com:3306/shop | com.mysql.cj.jdbc.Driver |
| Oracle (SID) | jdbc:oracle:thin:@db.test.example.com:1521:ORCL | oracle.jdbc.OracleDriver |
| Oracle (service) | jdbc:oracle:thin:@db.test.example.com:1521/shop | oracle.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#
- Database step: the dialog, SQL rules, outputs and log hints.
- Connections: create, test, change and delete database and Kafka connections.
- Secrets and credentials: where passwords belong and who can see them.
- Data and variables: reference syntax and the steps that produce values.
- Checks and assertions: compare the values the query returned.
- Troubleshooting › Integrations: fixes for each database error.
- How do I test a REST API without writing code?: the same idea for API calls.
Didn’t find your answer?Write to support with your project name and what you see on screen.
info@smartestqa.ai