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.
Connect
Section titled “Connect”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.
chmod 600 /absolute/path/to/bindings.jsonreflow target --group database-tests --collection <collection-id> --bindings /absolute/path/to/bindings.jsonWrite a flow
Section titled “Write a flow”Copy this into /tmp/database-check.md, outside your checkout.
The example returns one row without requiring an application table:
---name: Capture database rowsproviders: db: PostgreSQL---
### Capture database rows
```rfldb.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:
reflow flows create --name database-check --file /tmp/database-check.mdreflow plan database-checkreflow apply database-checkreflow dashboardThe 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"Query arguments and stable rows
Section titled “Query arguments and stable rows”| Argument | Type | Meaning |
|---|---|---|
sql | string, required | One SQL statement, executed in a read-only transaction. |
key | string, optional | A unique, non-null returned column used to identify and sort rows. |
expect | JSON text, optional | What 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.
Check business data
Section titled “Check business data”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} ))| Key | Meaning |
|---|---|
rows | The exact number of returned rows. |
json | Assertions 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.
Wait for rows, or branch on them
Section titled “Wait for rows, or branch on them”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.
Return a value with db.value
Section titled “Return a value with db.value”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_iddb.query ( sql="SELECT count(*) AS keys FROM agent_keys WHERE agent_id = '${agent_id}'" expect=json( {"json": [{"pointer": "/0/keys", "equals": 2}]} ))| Argument | Type | Meaning |
|---|---|---|
sql | string, required | One SQL statement, executed in a read-only transaction. |
column | string, optional | The column to return; the first column when omitted. |
raw | "true", optional | Return 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.
Setup writes
Section titled “Setup writes”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'"```Review the result
Section titled “Review the result”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.