Skip to content

PostgreSQL plugin

Use PostgreSQL to check the business data behind a user journey. Queries retain column names and row values, so a reviewer can inspect the database state alongside screenshots and command output.

Select your application collection during onboarding. Use its ID in the target command below.

Install the bundled plugin with reflow init --collection <collection-id>. Your test database must be running and reachable from the machine that runs Reflow.

Save a private bindings.json with your test database connection string:

{
"db": {
"provider": "PostgreSQL",
"configuration": {
"connectionString": "postgresql://test_user:replace-me@127.0.0.1:5432/test_db"
}
}
}

connectionString is required. Keep this file out of Git; credentials belong in the private connection, not the flow’s plugin requirements. allowWrites is an optional boolean and defaults to false.

The plugin uses pgx v5 to parse URL or keyword/value connection strings. PostgreSQL session parameters such as search_path are supported in the DSN, for example postgresql://test_user:replace-me@127.0.0.1:5432/test_db?search_path=public. Percent-encode parameter values when needed and keep the complete DSN private. The server must recognize each runtime parameter; application-specific URL options are not automatically PostgreSQL settings: use search_path=public, not an application-only option such as schema=public. Reflow sets application_name to reflow-plugin-postgresql and uses a five-second connection timeout.

Terminal window
chmod 600 /absolute/path/to/bindings.json
reflow target --group database-tests --collection <collection-id> --bindings /absolute/path/to/bindings.json

Copy this into /tmp/database-check.md, outside your checkout. The example returns one row without requiring an application table:

---
name: Capture database rows
providers:
db: PostgreSQL
---
### Capture database rows
```rfl
db.query (
sql="SELECT 1 AS id, 'ready' AS status"
key="id"
expect=json(
{"rows": 1, "json": [{"pointer": "/0/status", "equals": "ready"}]}
)
checkpoint="database-ready"
)
```

Create the test in the team’s chosen location, then run it:

Terminal window
reflow flows create --name database-check --file /tmp/database-check.md
reflow plan database-check
reflow apply database-check
reflow dashboard

The alias db selects the private connection with the same name. For an application with an orders table, select the business columns you want to compare:

db.query sql="SELECT id, status, refunded FROM orders ORDER BY id" key="id" checkpoint="orders"
ArgumentTypeMeaning
sqlstring, requiredOne SQL statement, executed in a read-only transaction.
keystring, optionalA unique, non-null returned column used to identify and sort rows.
expectJSON text, optionalWhat the rows must hold; see Check business data.

checkpoint is a host RFL modifier, not a PostgreSQL command argument. RFL removes it before validating and dispatching the provider arguments, so it does not appear in the query descriptor returned by list_plugins. Use a literal label of 1-128 letters, digits, underscores or hyphens. Other unknown arguments are still rejected. A query already captures its result; there is no additional snapshot command.

Use a key or an explicit ORDER BY to make ordering repeatable. A missing, null or duplicate key fails the step. The step keeps its rows, and its error names the key values. Returned column names must also be unique. A capture is limited to 10,000 rows and 7 MiB of serialized snapshot state; narrow the query instead of relying on a partial result.

A statement the server refuses fails the step with PostgreSQL error: <message> (SQLSTATE <code>), for example PostgreSQL error: invalid input syntax for type integer: "abc" (SQLSTATE 22P02). The step keeps that error, with no rows, as evidence. A value Reflow knows as private, such as a binding value or an input substituted into the SQL, appears there as its ${…} name. The connection’s password, and its user, database and host where they appear as whole words, appear as [withheld]. The server’s detail, hint and position are not kept. Other failures, such as a lost connection, report PostgreSQL statement failed (connection and statement details withheld).

Typed inputs can supply numeric limits and booleans; see the database flow example. Do not concatenate untrusted text into SQL. Plugin arguments do not provide SQL bind parameters.

Give expect to state in the flow what the rows must hold. Write it as a json( block in a grouped call:

db.query (
sql="SELECT id, status, refunded FROM orders WHERE id = 1042"
expect=json(
{"rows": 1, "json": [{"pointer": "/0/status", "equals": "refunded"}, {"pointer": "/0/refunded", "equals": true}]}
)
checkpoint="order-refunded"
)
db.query (
sql="SELECT id FROM api_keys WHERE name = 'zz-e2e-revoked' AND deleted_at IS NULL"
expect=json(
{"rows": 0}
)
)
KeyMeaning
rowsThe exact number of returned rows.
jsonAssertions over the returned rows as a JSON array of row objects. Each has an RFC 6901 pointer and one operator.

/0/status is the first row’s status column, /1/status the second row’s, and "" the whole array. With key, rows are sorted by the key first. The operators are the same as in the shell plugins’ expect: exists, equals, type, count, includes, lessThan, lessThanOrEqual, greaterThan and greaterThanOrEqual. Values are the ones the snapshot keeps: SQL NULL is null, and numeric columns and integers beyond ±2^53-1 are strings, so compare those as strings ({"pointer": "/0/amount", "equals": "12.50"}).

Every check must hold. Otherwise the step fails, keeps its rows, and the error says what was expected and what was received:

expect.rows: expected 1 row, received 0 rows (count_differs)
expect.json[0] "/0/status": expected equals "refunded", received "paid" (value_differs)

An invalid expectation, such as an unknown key or operator, is refused before any SQL is sent. A refused statement or a key violation fails with its own error and is not judged. Do not make the statement raise an error on purpose: PostgreSQL may evaluate a constant expression while it plans the statement, so a deliberate error can fail every run.

A query with expect can wait for its rows to hold with within=. It runs again, every second unless every= says otherwise, while the rows do not satisfy expect, and ends at once on a refused statement or a lost connection:

db.query (
sql="SELECT count(*) AS n FROM audit_logs WHERE subject = 'zz-e2e-export'"
expect=json(
{"json": [{"pointer": "/0/n", "equals": 2}]}
)
within=30s
every=2s
)

within is the last time an attempt may start. When it has passed, the step fails with the last attempt’s rows and its expect message, after did not hold within 30s: 15 attempts in 30.2 s (28.0 s between attempts). The same query can decide an if or unless: rows that satisfy expect take the block, rows that do not take else, and a statement that fails stops the step:

if db.query sql="SELECT id FROM agents WHERE name = 'zz-e2e-agent'" expect="{\"rows\":1}"
db.execute sql="DELETE FROM agents WHERE name = 'zz-e2e-agent'"

execute and value cannot wait or decide a branch: execute writes, and value has no expect.

db.value runs one read-only statement like query and returns one cell of the first row for later steps: the column named by column, or the first column.

db.value sql="SELECT id FROM agents WHERE name = 'zz-e2e-agent' ORDER BY created_at DESC, id LIMIT 1" -> agent_id
db.query (
sql="SELECT count(*) AS keys FROM agent_keys WHERE agent_id = '${agent_id}'"
expect=json(
{"json": [{"pointer": "/0/keys", "equals": 2}]}
)
)
ArgumentTypeMeaning
sqlstring, requiredOne SQL statement, executed in a read-only transaction.
columnstring, optionalThe column to return; the first column when omitted.
raw"true", optionalReturn any printable text, not only a safe token.

The value is returned as text: numbers in their JSON spelling and booleans as true or false. Look up a unique row; when several rows can match, add ORDER BY with a unique tie-break column and LIMIT 1, as above, or the first row is arbitrary. No rows, a missing column, NULL, a JSON object or array, or a value over 4 KiB fail the step, for example with value: column "id" is NULL in the first row.

The value must be a safe token of letters, digits and . _ : / @ + - unless raw="true". A safe token has no quotes or backslashes, so it can be single-quoted into a later statement, as the example does. That is still not a bind parameter: never interpolate raw text into SQL, neither a raw="true" value nor a free-text input such as a name (O'Brien breaks the statement). Pass a raw value only to an argument that treats it as data. The value and the rows it was taken from are kept as evidence, so never select a secret.

db.execute accepts a required sql string. It runs only when the private connection explicitly sets "allowWrites": true; it retains the command tag and number of affected rows. Enabling writes does not make query writable.

When the server refuses a statement in db.execute, the step ends with its completion unknown, not failed: sql can hold several statements, and the ones before the refused statement may have committed. Reflow does not run that step again by itself. Check the test database, then taint the step to run it again.

Use a separate, deliberate setup step against your test database. Make repeated setup safe for your application: rerunning a flow does not reset database contents, and reflow reset rotates named test values and leaves application data in place. Undo a setup write in a cleanup step below it, which runs after the flow’s steps even when one of them failed:

```rfl cleanup step="Delete the agent"
db.execute sql="DELETE FROM agents WHERE name = 'zz-e2e-agent'"
```

The snapshot keeps the returned columns and exact represented row values. Compare those retained results across runs and relate differences to the application’s rules; see database evidence. A query without expect records its rows and judges nothing; with expect, the snapshot also records each check and whether it held.

Flows using this plugin can be saved in Reflow or in your repository. Read test storage for the team preference, private bindings and the shared plan/apply workflow.