Skip to content
Saffronv1.4.0
Get started
Writing tests

Database steps (beta)

Beta. Database steps are new in 1.4.0: they work and are covered by tests, and are still being shaped by use.

A step can check what the database holds, or seed the data a scenario starts from, without a query written by hand:

Scenario: A new customer is saved
  When I sign up as "ann-{unique:c}@example.test"
  Then the customer "ann-{unique:c}@example.test" should be saved in the database

While recording, the agent looks at the schema (its tables, their columns and keys), writes one parameterized query, runs it, and records it with the step's values as binds. Every later run replays that query at zero tokens. It works in browser scenarios and in @api scenarios alike. It runs on PostgreSQL, MySQL and SQL Server (see Engines); MongoDB is planned.

Connect it

{
  "db": { "url": "{env:DATABASE_URL}", "writes": false, "allow": ["localhost", "shop_test"], "timeout": 3000, "cleanup": "all" }
}
  • url is read from the environment, never written in the file: {env:DATABASE_URL} with DATABASE_URL=postgres://user:pass@localhost:5432/shop_test. Its scheme names the engine: postgres:// (or postgresql://), mysql://, or mssql:// (or sqlserver://, with ?encrypt=false, ?trustServerCertificate=true or ?instanceName=... as the server needs). Its password never reaches a recording, a proposal, a report, a trace or the agent.
  • Install the engine's driver and the SQL parser beside Saffron: npm install pg node-sql-parser for PostgreSQL, npm install mysql2 node-sql-parser for MySQL, npm install mssql node-sql-parser for SQL Server. Both are loaded only when db is set. The parser is not installed with saffron-ai, so a project without database steps does not carry its 88 MB.
  • The host and the database are written in the URL itself. A connection string that names them as query parameters (?host=...), or names no host, is refused for writes, so allow always checks where the driver connects.
  • When the database does not answer, a run says so and starts no agent session, as it does for an app that does not answer.
  • timeout (milliseconds, default 3000) is how long a check reads again for its rows (see Checks).
  • cleanup (default "all") is what is undone when a scenario ends (see Cleanup).
  • Give Saffron a database user of its own on a test database. The rules below hold whatever the user may do, and a user that may only read and insert holds them twice.

Checks

A Then about the database records an expectRows action: one SELECT, its values as $1, $2 binds, and checks on the rows it returns, read as a JSON list (count, /0/column equals, an item wherever it stands).

{ "action": "expectRows", "engine": "postgres",
  "sql": "SELECT tier FROM customers WHERE email = $1",
  "body": ["ann-{unique:c}@example.test"],
  "json": [{ "path": "", "count": "1" }, { "path": "/0/tier", "equals": "gold" }] }
  • A check must be bound to a value the scenario wrote: an Examples column, a {unique:...} value, a value an earlier step kept, or a value the steps spell out. SELECT count(*) FROM customers with a check that it is above zero would pass on rows any earlier run left behind, so the agent's tool refuses it. Each side of an OR, and every value of an IN, needs such a value (email = $1 OR role = 'admin' is refused); one side of an AND is enough. A LIKE value made only of % or _ matches every row, so it is refused unless another condition ties the rows, and so are LIMIT 0, TOP 0 and FETCH ... 0. A value the steps spell out (ann@example.test) is the same on every run, so a row the app saved on an earlier run still matches it: bind a value that is new on each run ({unique:...}, an id an earlier step kept) where a check must prove this run saved the row.
  • An aggregate with no GROUP BY (or with ROLLUP) is always one row, so only a value checked on it is taken: SELECT count(*) AS n ... with { "path": "/0/n", "equals": "1" }, or a count or item check on a list it holds (/0/items from json_agg), never a count, an exists or an item check on the row itself. A count above 0, or a check that there is no second row, under LIMIT, TOP, FETCH, DISTINCT or a GROUP BY with no aggregate is refused, as it could not see a second row the app wrote by mistake, also when they sit in a query read FROM or WITH; so is any check under OFFSET, which skips rows. A field that must not be in a row holds only when that row is there.
  • A null bind, and an empty one in a "not in the database" check, is refused: = NULL matches no row, so the check would hold whatever the database holds. Write IS NULL in the SQL instead.
  • A count(*) AS n checked as 0 is a "not in the database" check: an empty bind is refused, and the 0 must hold for all of db.timeout.
  • A bind of one or two characters (1, ok) does not count as the scenario's value: it is in too many places to say which data it means.
  • No value is written into the SQL itself: a literal is refused (an E'...' or $$...$$ string and a DATE '...' too), and so is a comment. Each bind is compared with a column (email = $1, id IN ($1, $2), lower(email) = lower($1)): a query that returns a bind (SELECT $1 AS email), compares values with no column in them ($1 = $1), has a WHERE that reads no column, uses a comparison as a value ((email = $1) IS NOT NULL), or has an OR side with no bind (OR true, OR email IS NOT NULL) is refused, as it would pass on any data.
  • The WHERE must tie its rows to a bind: a column compared with it by =, IN, LIKE (or ILIKE on PostgreSQL) (email = $1, id IN (SELECT id FROM customers WHERE email = $1)), on one side of an AND and on each side of an OR. email <> $1, NOT (...), a comparison inside COALESCE or CASE, a range alone (created_at > $1), a bind in LIMIT or OFFSET, and UNION, INTERSECT or EXCEPT are refused. A subquery on the tying side must tie its own rows the same way. A JSON field can tie a check: data->>'orderRef' = $1, data @> CAST($1 AS jsonb). Cast a bind with CAST($1 AS type); $1::type is not read.
  • The binds carry the values, and the recording writes them as their tokens, so each Examples row checks its own value and never passes on another row's. What a check expects is kept as the step says it; a value an earlier step kept is written there as {captured:name}.
  • A function whose effects reach past the step's transaction is refused in any statement: ending sessions (pg_terminate_backend), other connections (dblink), large objects, settings (set_config), sequences, advisory locks, files.
  • A date, a timestamp, a bigint or a numeric is read as text, in UTC and ISO style (2026-11-07, 2026-11-07 09:00:00+00), so a check reads the same on every machine, whatever its time zone (see Engines for each engine's form).
  • The query runs in a transaction that is rolled back (read-only on PostgreSQL and MySQL; see Engines), and is read again within db.timeout (default 3000 ms). A check that rows are there passes as soon as they are: a worker that writes a moment after the app answered is still seen. A check that rows are not there (a count of "0", or a field that must not exist) waits too, for a row a worker deletes a moment late, and once it holds it must go on holding to the end of the window, so a row a worker writes late fails it. A row still there when the window ends fails the check. A check that holds both (the order is paid, and there is no second order) waits for the first part, then holds the second to the end of the window. Each absence check takes the whole db.timeout on every run: lower it when nothing writes late.
  • The run report shows, under the step, the rows each check found: its SQL, the row count and the first five rows, masked like any other value, a credential the scenario captured included.
  • When the query runs and finds other data, the step is plain red, and no agent is started, in a browser scenario or an @api one, under any assertion policy.

A check whose query no longer fits the schema

A Then is never healed, with one narrow exception. When a database check's stored query fails because the schema changed (a table or column it names is gone, or a type no longer fits what it compares), the agent looks at the schema and repairs the query, as it repairs a step whose button was renamed:

  • It may change only where the query looks: its table, columns and joins. The binds (the scenario's values) and the row checks (what is expected) must be exactly the ones recorded. Saffron compares them, and a repair that drops or changes a bind, or changes an expected value, is refused: the step is red and nothing is filed.
  • It must find as many rows as the recording did: each check keeps rowCount, the number of rows its query returned when it was recorded. A join that fans out to several rows for one customer, or reads an old history row beside the current one, is refused, even when its first row holds the expected value. A repair adds no LIMIT, DISTINCT, GROUP BY or aggregate (max(), count()) the recorded query did not have: each would find one row and hide the fan-out. A check recorded without rowCount, or one that expects no rows, a count(*) of 0, a count taken from an Outline column (a row may make it 0), or a field that must not be there (a repaired query that stops selecting it proves nothing), is not repaired: record it again.
  • The repaired check must hold on the live data, then on a proof replay of the whole scenario. The rest of the scenario runs, the step is reported healed (yellow), and a proposal is filed for review. Its notes name the old and the new SQL, and its database check repaired after a schema change: note keeps it out of accept --all: accept it by file once you have read the new query.
  • A query that runs and returns the wrong rows is not a schema change: it is plain red, and no agent is started.

Seed data

With "writes": true, a Given or When may seed or change data the app gives no way to set, with dbWrite: one INSERT, or one UPDATE with a WHERE, its values as binds. extract keeps a field of the row written, such as a generated id, as {captured:name} for later steps.

Given a customer "ann-{unique:c}@example.test" on the "basic" tier
  • Writes are off unless writes is true, and go only to a host or a database name listed in allow. Anything else is refused before it runs.
  • An UPDATE's WHERE binds a value the scenario wrote, as a check does: one bound to nothing of the scenario would change rows another run or another worker made.
  • A Fixture: block does not write to the database: its execution is cleaned up when it ends, before its scenarios run. Seed in each scenario's Given.
  • Seed with {unique:...} values where a column must be unique: two workers, or a pending proposal's proof replayed beside the failed run, would otherwise write the same value at once.
  • Only INSERT and UPDATE ... WHERE. DELETE, TRUNCATE, DROP, ALTER, GRANT, several statements in one, a write inside a WITH, an UPDATE without a WHERE (or with one that reads no column, such as WHERE true), an INSERT ... ON CONFLICT (MySQL's ON DUPLICATE KEY UPDATE) and MySQL's INSERT IGNORE are refused. The statement is read by a SQL parser, never by its text, and must be exactly one statement, so nothing the parser reads as a string can carry a second one.
  • Each write runs in its own transaction. A write that changes no row fails its step.
  • A Then never writes.
  • A seed step that fails heals like any other step. A heal that adds a database write to a step, or writes something else than the step was recorded to write, carries a database write added by the heal: note, which keeps the proposal out of accept --all.

Cleanup

When a scenario ends, green or red, Saffron undoes its writes, the latest first, by primary key and nothing else. What it undoes is db.cleanup:

cleanup Inserts Updates
"all" (default) deleted put back: the rows the UPDATE's WHERE named are read by primary key, in the same transaction, before it runs, and their changed columns get those values back
"inserts" deleted left as they are
"none" left left, for debugging a scenario's data
  • What a failed replay wrote is undone before an agent records again too, what a recording wrote is undone before its proof replays it, and what the proof wrote is undone before the stability and row proofs run.
  • An UPDATE that changes a row's primary key is refused under "all", since nothing could find the row to put it back. Only the columns whose values changed are put back; a generated column follows them.
  • A write that must be undone needs a table with a primary key, or its rows could not be told apart: such a write is refused unless cleanup leaves it alone. An UPDATE ... FROM is refused: name the rows with a WHERE on the table itself.
  • A row cleanup cannot undo (a row the app made since points at it, or a row that is gone) is named on the console, and the verdict stands.
  • Stopping a run (Ctrl-C or SIGTERM) undoes what the scenarios still running wrote.
  • A run that is killed (a CI job cut off with SIGKILL, out of memory) undoes nothing: sweep what it left in teardown.
  • Rows a database trigger writes are left as they are: cleanup undoes only the rows the step's own statement wrote.
  • saffron run --plan and the MCP run_plan tool list a scenario whose recorded write cleanup keeps (an UPDATE under "inserts", any write under "none") as one with side effects.
  • A heal that makes a step write to the database where it was recorded through the app is named in the proposal's notes, for the reviewer.

Engines

Every engine takes the same steps, the same rules and the same $1, $2 binds; the agent writes each engine's own SQL (backticks on MySQL, TOP and brackets on SQL Server). A recording keeps the engine it was made on, and replay refuses it on another one: record the scenario again after a change of engine.

  • PostgreSQL: a write returns its rows itself (RETURNING).
  • MySQL and SQL Server have no RETURNING, so Saffron reads the rows a write made back by their primary key, in the write's own transaction:
    • An INSERT gives the key's columns as binds, any number of rows at a time, or leaves an auto-numbered key (AUTO_INCREMENT, IDENTITY) to the database, one row per INSERT: several rows with a generated key are refused, and nothing is written.
    • An UPDATE reads the rows it will change first, locked, and may not set the primary key. It writes one table: UPDATE a JOIN b and UPDATE a, b are refused, and so are ORDER BY and LIMIT, which would change fewer rows than its WHERE names.
    • A write goes only to the database db.url names, so db.allow means what it says: otherdb.customers on MySQL and otherdb.dbo.customers on SQL Server are refused. A write through a view (MySQL, SQL Server) or a synonym (SQL Server) is refused too, as it may reach a table in another database: write the table itself.
    • A write whose rows its keys do not find again (a key that does not read back the same) is rolled back while cleanup is on, so nothing is left that cleanup could not undo.
    • With "cleanup": "none", a table with no key takes writes too; its rows are not read back, so extract finds nothing in them.
  • MySQL: a read runs in a READ ONLY transaction, rolled back. Dates and times read as 2026-11-07 and 2026-11-07 09:00:00 (UTC), DECIMAL and BIGINT as text, BOOLEAN as true or false, JSON as itself (data->>'$.orderRef' = $1 ties a check). SLEEP, BENCHMARK, locks (GET_LOCK, and a read that locks rows: FOR UPDATE, FOR SHARE, SKIP LOCKED, LOCK IN SHARE MODE), LOAD_FILE, SELECT ... INTO and /*! ... */ comments are refused.
  • SQL Server: it has no read-only transaction, so a read is one SELECT the SQL check lets through, rolled back after. Dates and times read as ISO 8601 in UTC (2026-11-07, 2026-11-07T09:00:00), BIGINT as text, BIT as true or false, DECIMAL and MONEY as numbers (exact to 15 digits); an UPDATE that changes one wider than that is refused while cleanup would put it back, as it could not be put back exactly; so is one that changes a datetime2 or time finer than milliseconds, or a datetimeoffset. Table hints (WITH (NOLOCK), READPAST) are refused: a check reads only what the database committed. Writes lock the rows they change first (UPDLOCK, HOLDLOCK). JSON_VALUE(data, '$.orderRef') = $1 ties a check. OPENQUERY, OPENROWSET, SELECT ... INTO, and a backslash before a quote in a string (which SQL Server and the parser read differently) are refused.

In status

saffron status --json, and the MCP project_status tool, say whether database steps are configured, never the connection string; engine is there once the URL's variable is set, named by its scheme alone: "database": { "configured": true, "engine": "postgres", "writes": false, "allowlisted": false, "cleanup": "all", "timeoutMs": 3000 }. allowlisted is whether db.allow lists any host or database; a write still runs only on a database whose host or name is in it.

Not yet

  • MongoDB.
  • saffron eject writes a scenario with database steps as test.fixme.
  • The trace shows a database step as its SQL and binds, with no rows.
  • The VS Code and JetBrains plugins show database steps only as the runner reports them: VS Code and the language server (JetBrains) say a database step replays at zero tokens only in its own scenario; neither has database tools of its own yet.
  • A Fixture: block cannot seed the database.
  • A write into a partitioned table cannot be undone: set cleanup to "none" for it.