Database checks

The Check database step (db.assert, in the Validation category) runs one SQL query and checks the rows it returns. Use it to prove that what the screen says was saved really was saved: the work order is APPR, the invoice total is right, exactly one audit row was written.

It only reads. It cannot insert, update, delete or change the schema. See How read-only is enforced.

Supported databases: PostgreSQL, MySQL / MariaDB and SQL Server.

Before you start

1. Ask your DBA for a read-only login for the test database. For example, on PostgreSQL a role with SELECT only; on SQL Server a user in db_datareader only. This is the most important protection. Don’t use the application’s own login.

2. Store the connection string as a secret (Secrets → Add), for example ORDERS_DB:

   Database	Connection string
   PostgreSQL	postgres://tester:PASSWORD@db.corp.local:5432/orders?sslmode=require
   MySQL / MariaDB	mysql://tester:PASSWORD@db.corp.local:3306/orders
   SQL Server (URL)	sqlserver://tester:PASSWORD@sql01.corp.local:1433/orders?trustServerCertificate=true
   SQL Server (ADO)	Server=sql01.corp.local,1433;Database=orders;User Id=tester;Password=PASSWORD;Encrypt=true

   Special characters in a URL password must be percent-encoded (@ → %40).

3. Check that the server can reach the database. The query runs on the AutoTestX server, not on the tester’s computer, even when you watch the run through the agent. A database behind your company firewall must be reachable from the server. An administrator may also have limited which hosts are allowed. See Which hosts a check may reach.

Writing the step

Parameter	Meaning
connection	{{secret.ORDERS_DB}}. A literal string with a password in it gets a warning.
query	One SELECT. Put test values in {{…}}, without quotes: WHERE wonum = {{wonum}}
expectRows	How many rows: 1, 0, >=1, <5, 2..10, any. Empty means at least one row (unless you set assert).
expect	One line per column, column: value, checked on the first row: status: APPR. For several rows, see Checking more than one row
expectAnyRow	One line per column, like expect, but passes when any returned row has all of them: wonum: 1003 and status: WAPPR. See Is a record in the result?
assert	A condition for anything else, using rows, row and rowCount: row.qty > 0 AND rowCount < 3
saveAs	A variable that keeps the result for later steps
maxRows	Most rows read, 100 by default (max 1000)
retryIntervalMs	Re-run the query every N ms until the checks pass, within the step timeout
sensitive	Hide the returned values in reports and logs

Example: the work order was approved

{
  "type": "db.assert",
  "params": {
    "connection": "{{secret.MAXIMO_DB}}",
    "query": "SELECT status, siteid FROM workorder WHERE wonum = {{wonum}}",
    "expectRows": "1",
    "expect": { "status": "APPR", "siteid": "{{site}}" }
  }
}

Example: nothing was written to the error log

{
  "type": "db.assert",
  "params": {
    "connection": "{{secret.ORDERS_DB}}",
    "query": "SELECT id FROM error_log WHERE order_id = {{order.body.id}}",
    "expectRows": "0"
  }
}

Example: a background job finishes later

The screen says Submitted, but a job fills in the invoice a few seconds later. With retryIntervalMs the step re-runs the query until the checks pass, or the step timeout runs out:

{
  "type": "db.assert",
  "timeoutMs": 30000,
  "params": {
    "connection": "{{secret.ORDERS_DB}}",
    "query": "SELECT total FROM invoice WHERE order_id = {{orderId}}",
    "expect": { "total": "{{expectedTotal}}" },
    "retryIntervalMs": 2000
  }
}

Checking more than one row

expect looks at the first row only. When the query returns several rows, use the other parameters:

How many rows. Set expectRows: 3 for exactly three, >=2 for at least two, 2..10 for a range.

A few known rows. Use assert with rows.<index>.<column>; indexes start at 0. Add ORDER BY to the query, or the database may return the rows in any order:

{
  "type": "db.assert",
  "params": {
    "connection": "{{secret.MAXIMO_DB}}",
    "query": "SELECT wonum, status FROM workorder WHERE siteid = {{site}} ORDER BY wonum",
    "expectRows": "3",
    "assert": "rows.0.status == 'APPR' AND rows.1.status == 'WAPPR' AND rows.2.wonum == '1003'"
  }
}

Every row. There is no “for every row” in an expression, so ask the database for the rows that break the rule, and expect none:

{
  "type": "db.assert",
  "params": {
    "connection": "{{secret.MAXIMO_DB}}",
    "query": "SELECT wonum, status FROM workorder WHERE siteid = {{site}} AND status <> 'APPR'",
    "expectRows": "0"
  }
}

This is the most reliable way to check every row, and when it fails, the report shows the work orders that broke the rule. SELECT COUNT(*) AS bad … with expect bad: 0 works too, but only tells you how many.

Several columns of several records. Use one step per record, each query returning one row (WHERE wonum = '1001'), and expect as usual.

Is a record in the result?

You can’t narrow the query to one record, but you want to know that a given record is among the rows it returns, for example “work order 1003 is in the site’s waiting list”.

Recommended: ask the database. Keep the conditions that define the set, add the one for your record, and expect exactly one row:

{
  "type": "db.assert",
  "params": {
    "connection": "{{secret.MAXIMO_DB}}",
    "query": "SELECT wonum, status FROM workorder WHERE siteid = {{site}} AND status = 'WAPPR' AND wonum = {{wonum}}",
    "expectRows": "1",
    "expect": { "status": "WAPPR" }
  }
}

To check a record is not in the set, use the same query with expectRows 0.

If the set comes from a query you’d rather not change (joins, GROUP BY), wrap it as it is:

SELECT * FROM (
  SELECT ... the query, unchanged ...
) AS result_set
WHERE wonum = {{wonum}}

Or let the step search the rows: expectAnyRow. Keep the query as it is, and list the values one row must have:

{
  "type": "db.assert",
  "params": {
    "connection": "{{secret.MAXIMO_DB}}",
    "query": "SELECT wonum, status FROM workorder WHERE siteid = {{site}}",
    "expectAnyRow": { "wonum": "{{wonum}}", "status": "WAPPR" }
  }
}

• All the values must be in the same row. Work order 1001 being APPR and another one being WAPPR does not match wonum: 1001 + status: WAPPR.
• Values compare as in expect (see How values are compared).
• When nothing matches: No row has wonum = “1003” and status = “WAPPR” among the 3 rows returned, followed by the first rows.
• It searches only the rows read: maxRows, 100 by default. If the query returns more, the rows past that limit are never seen, and the message says so: … among the first 100 rows (the query returned more; narrow it in SQL, or raise maxRows). This is why the SQL way above is recommended: the database searches every row, however many.
• With retryIntervalMs, the step waits for the record to appear.

Using the result later

With saveAs inv, later steps can use {{inv.rowCount}}, {{inv.rows.0.total}} or COUNT(inv.rows).

Which hosts a check may reach

Two settings decide, both in the hands of whoever runs the AutoTestX server:

Setting	Where	Applies to
Database hosts of an organization	System Admin → Organizations → the organization	That organization only. When it has a list, only those hosts are allowed, whatever the server setting says.
ATX_DB_ALLOWED_HOSTS	The server’s environment (Configuration)	Every organization without a list of its own. Unset means any host.

Entries are host names, IP addresses, or *.example.com for every host in a domain, one per line. An organization’s list can’t hold a bare * or the server’s own address (localhost, 127.0.0.1): on a server shared by several organizations, either would let one organization’s tests reach whatever the server itself can. Only a system administrator can change the list; every change is in the activity log.

A typical hosted setup: ATX_DB_ALLOWED_HOSTS=none.invalid blocks every host by default, and each client organization gets its own database hosts. Client A’s hosts are then reachable from Client A’s tests only.

The host must also be reachable over the network: the query runs on the AutoTestX server, so the client’s firewall has to let the server’s address in on the database port.

How values are compared

• Placeholders are parameters. {{wonum}} is sent to the database separately from the SQL, never pasted into it. Data such as O'Brien works as-is and can’t change the query. That’s also why it must not be in quotes: '{{wonum}}' is refused.
• Column names in expect match ignoring case. PostgreSQL returns status, Oracle and DB2 style schemas return STATUS.
• Numbers: 100.00 in the database equals 100 in expect. Decimals and big integers are kept as text, so no digits are lost.
• Dates come back as ISO text in UTC, e.g. 2026-10-12T10:00:00.000Z.
• NULL equals an empty expected value.

How read-only is enforced

Three layers, each enough on its own for most mistakes:

1. The query is checked in the editor and again before connecting. One statement only, starting with SELECT or WITH. INSERT, UPDATE, DELETE, MERGE, DROP, ALTER, CREATE, TRUNCATE, GRANT, EXEC, CALL, SELECT … INTO, FOR UPDATE and similar are refused. Words inside quotes or comments don’t count.
2. The database refuses writes. On PostgreSQL and MySQL the query runs in a read-only transaction. A function that tries to write fails with cannot execute DELETE in a read-only transaction. On every database the transaction is rolled back, never committed.
3. The login can only read. See Before you start. Layer 1 can’t see what a function does inside the database, and SQL Server has no read-only transaction, so this layer is what makes the other two safe to rely on.

In the run report

A database check has no screenshot: the browser plays no part in it. In its place, the report shows the query result:

• the query as written, and what each {{…}} was bound to;
• the rows it read, at most 10 (the step reads up to maxRows; the note under the table says how many there were);
• every cell expect compared, green when it matched and red when it did not, with the expected value under it;
• with expectAnyRow, the row that matched, highlighted;
• with retryIntervalMs, the last attempt.

A step marked sensitive shows only the query and the column names: its values are not kept. Long text is cut at 200 characters. Values that match a secret are masked, as everywhere in a report.

When it fails

Message	Meaning
Expected 1 row(s), but the query returned 0	The record isn’t there (yet). Is the value right? Does a background job need retryIntervalMs?
Column status: expected “APPR”, got “WAPPR”	The row is there, with another value
The query has no column “state”. It returned: status, total	A typo in expect, or the query doesn’t select that column
The database refused the query: …	SQL error (unknown table, syntax). It isn’t retried
Could not connect to PostgreSQL: …	Wrong host or password, firewall, or the server can’t reach the database
This server does not allow database checks against “…”	The host isn’t in ATX_DB_ALLOWED_HOSTS, and the organization has no list of its own
Database checks in this organization may not connect to “…”	The host isn’t in the organization’s Database hosts (System Admin → Organizations)
The query took longer than … ms and was stopped	Raise the step timeout, or add an index / narrow the query