Seed and query SQL

View .md Open in Claude

The SQL steps put a database in a known state before a scenario and check what your service wrote afterwards. PostgreSQL is first-class; Oracle, MySQL, SQLite and SQL Server are supported.

Register the database

Background:
  Given a parcels-db database with the following properties:
    | url      | postgres://localhost:5432/parcels |
    | user     | parcels                           |
    | password | parcels                           |

Native URLs (postgres://, mysql://, sqlserver://, oracle://, sqlite:) and JDBC-style URLs (jdbc:postgresql://, jdbc:mysql://, jdbc:mariadb://, jdbc:sqlserver://, jdbc:oracle:thin:@//, jdbc:sqlite:) are accepted. Name a table with its schema, like parcels.parcels, or set currentSchema in a PostgreSQL URL. The drivers are pure Go and come with the sql pack. JSONB containment needs PostgreSQL, triggers PostgreSQL or Oracle, and row locks a database other than SQLite; seeds, selections and row counts work on every database.

Seed

Given a seeds/manifest-kestrel.yaml db seed
Given a seeds/dispatching.yaml db seed on parcels-db

A seed is a dataset keyed by table. YAML is the usual format:

parcels.manifest_lines:
  - id: "ML-KES-0412-1"
    manifest_id: "M-KESTREL-0412"
    reference: "PX-KES-1001"
    sender: "kestrel-books"
    weight_grams: 850
    service_level: "STANDARD"
    recipient: '{"name": "Emmy Noether", "street": "Bunsenstrasse 3", "city": "Goettingen", "postcode": "37073", "country": "DE"}'
  - id: "ML-KES-0412-2"
    manifest_id: "M-KESTREL-0412"
    reference: "PX-KES-1002"
    sender: "kestrel-books"
    weight_grams: 2300
    service_level: "EXPRESS"
    recipient: '{"name": "Lise Meitner", "city": "Hamburg", "postcode": "20095", "country": "DE"}'

Flat XML (.xml), JSON (.json), Excel (.xlsx, one sheet per table) and CSV dataset directories (one <table>.csv per table, in the order table-ordering.txt lists when there is one) work the same way. The [null] and [UNIX_TIMESTAMP] placeholders are supported, and so are times counted from now, such as [DAY,NOW], [DAY,PLUS,1] or [HOUR,MINUS,2] (in DAY, HOUR, MIN or SEC). A time counted from now is counted from the database’s own clock, in its time zone, so it agrees with what the database compares it to, like SYSDATE or now(), wherever axx runs. A column with no time zone holds the database’s wall-clock time, and one that holds a moment, like timestamptz, holds that very moment. SQLite has no clock of its own, so it gets the machine’s. Values are escaped, so text containing ' is safe.

Scenarios share the database and run in parallel, so give every seeded row an id that belongs to one scenario. Isolate test data shows how to enforce that with axx lint.

A seed inserts its rows in file order, in one transaction. It never updates or deletes existing rows, so a row whose key already exists fails the step and nothing from that file is written.

Select rows and assert

A selection is a query with equality conditions. Later steps check its size and contents:

Then a selection of rows is retrieved from the parcels.manifest_lines table where:
  | manifest_id | M-KESTREL-0412 |
And the selection has 2 rows
And the selection has more than 1 row
And the selection has fewer than 5 rows

A value means what it says, and is held to its column’s real type before the query, on every database, since each would otherwise convert one of them its own way:

A column of	Takes
text (`VARCHAR`, `TEXT`, `CITEXT`)	a string: `M-KESTREL-0412`, or in double quotes when it reads as a number: `"37073"`
integers (`INT`, `BIGINT`, unsigned ones)	an integer: `850`
decimals (`NUMERIC`, `DECIMAL`, `FLOAT`, `DOUBLE`, `REAL`)	a decimal, a whole one too: `1.5`, `12.0`
booleans (`BOOLEAN`, SQL Server’s `BIT`)	`true` or `false`; MySQL’s `TINYINT` and `BIT` take these and integers
Oracle’s `NUMBER`	by its scale: `NUMBER(10,2)` is a decimal, `NUMBER(10)` an integer, and a `NUMBER` with no scale is held to what the row stores
anything else (dates, times, UUIDs, JSON, enums, binary, PostgreSQL’s `MONEY`)	a string: `2026-09-24`

null is IS NULL, and {...} and [...] go to the database as they are. A value of another type fails at once, a poll’s too, and says what to write:

postcode is text in parcels.pickups (VARCHAR): write "10115" [AXX-E0207]

An SQLite column declared with no type has none to hold a value to, so what the database selects is held to what each row stores: 850 selects the integer, and not the real 850.0. An empty cell is an error: write "" for the empty string.

A date or time says as much as its column holds, in a table and in a column check, or it fails at once and says what to write. A column with no time zone (DATE, TIMESTAMP, DATETIME, and MySQL’s TIMESTAMP, which a client reads as a wall clock) takes no offset, which each database would drop its own way. A column that holds a moment (timestamptz, Oracle’s TIMESTAMP WITH TIME ZONE, SQL Server’s DATETIMEOFFSET) needs one, as a time without it would mean something else on another machine:

planned_at has no time zone in pickups (TIMESTAMP), so an offset would be dropped: write "2026-09-24 07:40:00" [AXX-E0207]
confirmed_at holds a moment in pickups (TIMESTAMPTZ), which a time with no offset does not say: write its offset, like "2026-09-24T07:40:00Z" [AXX-E0207]

A scenario can hold several selections. They are numbered in the order they are retrieved, and later steps address them by ordinal (the 2nd selection); in a 2nd selection of rows is retrieved the ordinal is only a label. Leaving the ordinal out means the first:

Then a selection of rows is retrieved from the parcels.manifest_lines table where:
  | manifest_id | M-KESTREL-0412 |
And a 2nd selection of rows is retrieved from the parcels.manifest_lines table where:
  | id | ML-KES-0412-1 |
And the 2nd selection has 1 row

JSON columns

Assert on properties inside a JSON or JSONB column of a selected row:

Then a selection of rows is retrieved from the parcels.parcels table where:
  | reference | PX-REG-1001 |
And the 1st row details property for the selection json properties are:
  | source | api  |
  | zone   | DE-1 |
And the 1st row details property for the selection json properties match:
  | zone | ^DE-.*$ |

Values are typed, so a check sees a property’s type change: 850 is the number and "37073" the string, and neither matches the other. Against the manifest line seeded above:

Then a selection of rows is retrieved from the parcels.manifest_lines table where:
  | id           | ML-KES-0412-2 |
  | weight_grams | 2300          |
And the 1st row recipient property for the selection json properties are:
  | city     | Hamburg   |
  | postcode | "20095"   |
  | street   | undefined |

Or select by JSONB containment (PostgreSQL):

Then a selection of rows is retrieved from the parcels.parcels table where the details jsonb column contains:
  | manifestId | M-KESTREL-0412 |
And the selection has 2 rows

Check one value of a selection by its row and column:

Then a selection of rows is retrieved from the parcels.parcels table where:
  | reference | PX-REG-1009 |
And the 1st row status column of the selection is 'REGISTERED'
And the 1st row weight_grams column of the selection is '1200'
And the 1st row label_printed_by column of the selection is 'null'

The value is of the column’s type, as in a where: table, and strict: '1200' is an integer, '1200.0' a decimal and '"1200"' a string. A date or a time compares as a moment, written '2026-09-24', '2026-09-24 07:40:00' or '2026-09-24T07:40:00+02:00', and a CHAR column’s padding is no part of its value.

Selections are numbered in the order a scenario retrieves them, and a step without an ordinal checks the first. After a second retrieval, name the one a step checks: the 2nd selection has 1 row. axx validate warns about a step that checks the first while a later selection goes unchecked.

In json properties are: and json properties match:, paths are JSONPaths such as zone or items[0].sku. json properties are: compares values with their types: true is a boolean, null null, and undefined a property the JSON lacks (the rules); json properties match: matches a regular expression against the value’s text. The containment step takes dotted names (recipient.city) and the same typed values, so | pieces | 3 | finds the integer 3 and | recipient.postcode | "37073" | the string; 3 does not find 3.0, though PostgreSQL’s containment alone would.

Wait for asynchronous writes

When the service writes in the background (importing a manifest, say), poll instead of sleeping:

Then within 10s a selection of at least 2 rows is retrieved from the parcels.manifest_lines table where:
  | manifest_id | M-KESTREL-0412 |
  | status      | IMPORTED       |
And the selection has 2 rows

The step queries again every 500 ms until the selection has at least that many rows, and moves on as soon as the rows appear; it fails when the time runs out with fewer. the selection has 2 rows then checks that no more than those came back.

Oracle

The steps are the same on Oracle, with a few of its own ways:

Nothing to say about session formats. A date or a time reads as 2026-09-24, 2026-09-24 07:40:00 or 2026-09-24T07:40:00+02:00 in a seed or a table, whatever the database’s date format is.
An empty string is null. Oracle stores '' as null, so "" could never select a row: the step fails at once and says to write null.
Bytes are written in hex. A RAW or BLOB column takes and reads as DEADBEEF.
A JSON column compares as JSON. {"count":3,"zone":"DE-1"} selects a row whose keys are in another order, and {"count":"3"} does not select a row holding the number.
Oracle refuses a condition on some types: LONG, LONG RAW, XMLTYPE and VECTOR. Select the row by another column and check the value with the 1st row doc column of the selection is '<parcel/>'.
A GENERATED ALWAYS column takes no value from a row, as Oracle has no way to give it one: leave it out of the seed, or declare it GENERATED BY DEFAULT.
A trigger’s code is an ORA error. will raise an ORA-20001 exception raises it, and will raise an ORA-00060 exception the deadlock’s. Its digits alone, 20001, say the same.

Inject faults with triggers

To test how your service handles database errors, install a trigger that raises an SQL state for matching inserts:

@isolated
Scenario: A brief database failure does not fail the registration
  Given a before insert trigger on the parcels.parcels table will raise a 40001 exception 1 time where:
    | reference | PX-DBF-3001 |
  And a POST request to /api/parcels
  And a request payload using an application/json content example
  And the request payload property reference is 'PX-DBF-3001'
  When the request is executed
  Then the response status code is 201
  And the before insert trigger on the parcels.parcels table was raised 1 time

The trigger raises a serialization failure (40001) once; the service retries and the second insert succeeds. Without 1 time the trigger raises on every matching insert. Another variant inserts the row and then raises (... will insert and raise a 23505 exception where:).

Triggers change a shared table, so to run those scenarios alone after the parallel phase, tag them with a tag in run.exclusive (for example @isolated) or list sql.trigger in run.exclusiveUses. See Run in parallel.

An update or a delete fails the same way, with a before update or before delete trigger. An update’s conditions are on the row as the update would leave it, and a delete’s on the row it would take away, which stays:

Given a before update trigger on the parcels.parcels table will raise a 40001 exception where:
  | reference    | PX-DBF-3102 |
  | weight_grams | 2500        |
And a before delete trigger on the parcels.parcels table will raise a 40001 exception 1 time where:
  | reference | PX-DBF-3104 |

Triggers need PostgreSQL or Oracle. Each kind is numbered on its own, so the before update trigger on the parcels.parcels table was raised 1 time means the first before update trigger, whatever else the scenario created.

Row locks

Scenario: A parcel cannot be changed while the depot dispatches it
  Given a seeds/dispatching.yaml db seed
  And the rows in the parcels.parcels table are locked where:
    | reference | PX-DSP-2001 |
  And a PATCH request to /api/parcels/PX-DSP-2001
  And a request payload using an application/json content example named 'Heavier'
  When the request is executed
  Then the response status code is 409

Scenario: A parcel can be changed again once the depot lets go of it
  Given a seeds/dispatched.yaml db seed
  And the rows in the parcels.parcels table are locked where:
    | reference | PX-DSP-2002 |
  And a PATCH request to /api/parcels/PX-DSP-2002
  And a request payload using an application/json content example named 'Heavier'
  When the row locks are released
  And the request is executed
  Then the response status code is 200

Lock rows to test timeouts and contention in your service. The lock holds until the row locks are released or the end of the scenario.

Named databases

Every step has an on <service> form for scenarios with more than one database, for example a selection of rows is retrieved from the parcels.parcels table on parcels-db where: and the selection on parcels-db has 1 row.