The sql step

A sql step runs one read-only query against a database the plan declares in connections, and puts the rows in front of the assertions. It never writes, and that is enforced twice: the query is inspected at load and refused if it could write, and the connection is opened read-only. Every query also runs in a transaction that is rolled back. Read-only plus rollback is redundant on purpose, because the role is the layer that gets forgotten. The point of the step is the check an API cannot make for you: the 201 came back, and the row is really there, for the right tenant. Plan format has the keys every step shares and the connections block; this page has what a sql step adds.

A plan

sqlite.yaml creates an order through the API and then reads the row the fixture server persisted, through a SQLite file beside the plan:

connections:
  orders: { driver: sqlite, dsn: orders.db }
resources:
  - name: db/orders
tasks:
  - name: order-is-persisted
    needs: [create-order]
    holds: [db/orders]
    steps:
      - sql:
          connection: orders
          query: |
            select status, total_cents, tenant_id
            from orders where id = $1
          args: ["{{ tasks.create-order.results.orderId }}"]
        assert:
          - rows count 1
          - rows[0].status == "pending"
          - rows[0].total_cents == 2000
          - rows[0].tenant_id == env.tenantId

postgres.yaml is the same plan with orders: { driver: postgres, dsn: "${VERO_TEST_PG_DSN}" }. Against a Postgres on this repository's k3s cluster, with the fixture server writing through one role and the plan reading through another:

run 01M3ZS5T539FAC71T4RCMASKXX  postgres.yaml  --jobs 4  lifecycle unique

3 passed  0 failed  0 errored  0 timed out  0 skipped  0 blocked  0 not run   (3 tasks, 2 requests, 0.0s)

A sql step counts no request. Running the examples has the fixture flags and the two Postgres roles.

Connections

A connection names a driver, sqlite or postgres, and a dsn. The DSN may use ${VAR}, ${VAR:-default}, {{ env.x }} and {{ run.id }}, or be { secretRef: NAME }, and never a task result: a connection belongs to the whole plan. It is rendered and opened the first time a step uses it, and closed when the run ends. A connection no step uses is a load error, since an unused connection is a typo's other half. A connection declared writes: true is for fixture steps, and a sql step may not read through it: declare the same database a second time without writes for the reads.

SQLite is opened with mode=ro whatever the DSN said. A relative path is relative to the plan file's directory, :memory: is left alone, and a file: prefix is accepted. Unless the DSN sets its own busy_timeout pragma, vero adds a 5 s one, so a step waits for a writer's lock instead of failing: the application under test may be writing the file while the step reads it, and SQLITE_BUSY is not an answer about the data.

Postgres runs every step in BEGIN ISOLATION LEVEL READ COMMITTED READ ONLY, and asks the server SHOW transaction_read_only inside that transaction before the query. A pooler or a proxy that dropped READ ONLY is refused: the server says transaction_read_only = off inside the step's READ ONLY transaction; refusing to run the query.

The role is checked too. vero plan and vero run both open every Postgres connection that is not writes: true and read what its role could write from the catalogs, so vero plan is not entirely offline when a plan has one. A role that could write draws a warning on stderr; the step still runs, since the transaction is read-only either way:

warning: connections.orders: role vero_fixture can write (CREATE on schema public, DELETE on public.countries, DELETE on public.orders, DELETE on public.pg_nums, DELETE on public.pg_probe, INSERT on public.countries); a sql step's connection should use a read-only role (chapter 6.3). vero still runs every query READ ONLY and rolls it back

The check names a superuser, CREATE on the database or on schema public, and up to five INSERT, UPDATE, DELETE or TRUNCATE table grants. A connection vero could not reach says connections.orders: could not check its role: ... instead. Neither is a lint, so --strict does not turn them into errors.

What the query may do

The query is inspected at load. Its first word is SELECT, WITH, EXPLAIN, SHOW or VALUES, it is one statement, and none of these appear anywhere outside a string literal, a quoted identifier or a comment: INSERT, UPDATE, DELETE, MERGE, UPSERT, REPLACE, DROP, CREATE, ALTER, TRUNCATE, RENAME, COMMENT, GRANT, REVOKE, COPY, VACUUM, REINDEX, ATTACH, DETACH, INTO, LOCK, CALL, DO, PRAGMA, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, RELEASE, CLUSTER, REFRESH, NOTIFY, LISTEN, DISCARD, RESET, SECURITY, IMPORT, LOAD. That refuses a data-modifying CTE, SELECT ... INTO, and an EXPLAIN ANALYZE of a write, which would run it. ANALYZE on its own is allowed. Dollar-quoted strings, -- and /* */ comments and doubled quotes are read the way the database reads them.

The query is a literal. Values go in args, which are templated and bound as parameters $1, $2, and so on; a {{ in the query is a load error, because SQL built from strings could not be inspected here. The step's deadline is its timeout, or sql.timeout, 30 s when neither is set; setting both is a load error. repeat does not apply.

Rows

rows is a list of maps, one per row, keyed by column name, in the order the query returned them. It is empty when the query ran and matched nothing, and nil when the query did not run: the two are different facts, and the failure output says which.

Values compare exactly. An integer column is an exact number, so a 64-bit id past 2^53 stays itself: rows[0].id == 9007199254740993 holds and rows[0].id == 9007199254740992 does not. A float is an exact decimal, bytes are a string, and a time is an RFC 3339 string in UTC with nanoseconds. On Postgres, NUMERIC and DECIMAL are exact numbers (NaN and Infinity stay strings), JSON and JSONB are decoded with the strictness a response body gets, duplicate keys refused, and a DATE is YYYY-MM-DD. A column vero cannot convert fails the step with column <name>: <reason>.

Names in scope

rows count 1 is the assertion that makes a sql step honest. Without it, rows[0].status == "pending" on an empty result is an error about an index, which fails the task too, but says less. Reading a result of another task in args or in an assertion needs a graph path from that task; the template in args above is the edge, and the example adds needs: [create-order] beside it. duration on its own is refused: name duration.total. Assertions has the syntax.

What a failure shows

The failure block prints the query as written, line by line, every bound argument, how many rows came back and that the transaction was rolled back, then the first five rows, then each assertion that did not hold with the values it read. The order this run looked for does not exist:

FAIL  order-is-persisted › step 1 › assert #1                     sqlite.yaml:43

  sql  select status, total_cents, tenant_id
  sql  from orders where id = $1
    $1 = "no-such-order"
    0 rows, 0.6ms, read-only and rolled back

  assert  rows count 1    failed
          rows = [] (0 rows)
          sqlite.yaml:43
  assert  rows[0].status == "pending"    error
          column 5: index out of range: 0 (array length is 0)
          rows[0].status: index out of range: 0 (array length is 0)
          sqlite.yaml:44
  assert  rows[0].total_cents == 2000    error
          column 5: index out of range: 0 (array length is 0)
          rows[0].total_cents: index out of range: 0 (array length is 0)
          sqlite.yaml:45
  assert  rows[0].tenant_id == env.tenantId    error
          column 5: index out of range: 0 (array length is 0)
          rows[0].tenant_id: index out of range: 0 (array length is 0)
          env.tenantId = "tenant-42"
          sqlite.yaml:46

2 passed  1 failed  0 errored  0 timed out  0 skipped  0 blocked  0 not run   (3 tasks, 2 requests, 0.0s)

A query the database refused ends the step errored with the database's message, and the block reads no rows: the query did not run. A query the deadline cut off is timed_out with the query's deadline fired: .... Rows wider than 200 characters are cut in the block; the event log carries the whole query, its bound arguments, the row count and the column names as a query event (vero.events/v1):

{"schema":"vero.events/v1","ts":"2026-10-03T02:23:33.088Z","runId":"01M3ZS5T6HZQ60C7XQGBVHN1S1","type":"query","task":"order-is-persisted","step":1,"connection":"orders","query":"select status, total_cents, tenant_id\nfrom orders where id = $1\n","args":["01M3ZS5T6KBW7SMRGXEAAPDHTD"],"rows":1,"columns":["status","total_cents","tenant_id"],"durationMs":8.285}

Load errors

The messages cite chapter 6.3 of the design dossier that vero was built from; the rule is the one this page describes.