Seed and query SQL

The SQL steps put a database in a known state before a scenario and check what your service wrote afterwards. PostgreSQL is first-class; 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://, sqlite:) and JDBC-style URLs (jdbc:postgresql://, jdbc:mysql://, jdbc:mariadb://, jdbc:sqlserver://, 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 and triggers need PostgreSQL, 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:

seeds/manifest-kestrel.yaml

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 the machine’s local time. PostgreSQL also gets its offset, so a timestamptz column holds that very moment whatever time zone the machine is in, and a timestamp column holds the local wall-clock time. 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 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-.*$ |

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

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, and values are compared as text (the rules). The containment step takes dotted names (recipient.city) and compares each value as a JSON string, so it never matches a number or a boolean.

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.

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 tag those scenarios with a tag in run.exclusive (for example @isolated) to run them alone after the parallel phase. See Run in parallel.

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.