# SQL
> Seed, query and assert on relational databases. PostgreSQL is fully supported (including JSONB and trigger fault injection); MySQL/MariaDB, SQL Server and SQLite support seeds, selections, row counts and locks. JDBC URLs (jdbc:postgresql://...) are accepted as-is.
Source: https://axx.nimbusxr.us/references/steps/sql/
Seed, query and assert on relational databases. PostgreSQL is fully supported (including JSONB and trigger fault injection); MySQL/MariaDB, SQL Server and SQLite support seeds, selections, row counts and locks. JDBC URLs (jdbc:postgresql://...) are accepted as-is.
## `sql.service`
```gherkin
Given a(n) {word} database with the following properties:
| ... | ... |
```
Register a database. The first one registered in a scenario is the default.
Properties: `url` (JDBC or native URL), `user`, `password` (all required; `${env:..}`/`${sys:..}` expanded), `schema` (optional).
**Parameters:** `{word}` (one word, no spaces)
**Example:**
```gherkin
Given a parcels-db database with the following properties:
```
## `sql.seed`
```gherkin
Given a {filepath} db seed[[ on {dbService}]]
```
Insert the rows of a dataset file (resolved against `resources`). Formats by extension: `.yaml`/`.yml` (`schema.table:` → list of rows), flat XML (``), `.json`, `.csv` (a directory of `.csv` files with `table-ordering.txt`) and `.xlsx` (one sheet per table). Replacers: `[null]`, `[DAY,NOW]`, `[DAY,PLUS,1]`, `[UNIX_TIMESTAMP]`. Rows are inserted in one transaction and never deleted.
**Variants** (optional parts in `[[...]]` above):
- `a {filepath} db seed`
- `a {filepath} db seed on {dbService}`
**Parameters:** `{filepath}` (A file of the project, without whitespace: a path relative to the `resources` directories or to the directory of axx.yaml, or an absolute path. Editors link it to the file), `{dbService}` (The name of a database registered in the scenario)
**Example:**
```gherkin
Given a seeds/manifest-kestrel.yaml db seed
Given a seeds/dispatching.yaml db seed on parcels-db
```
## `sql.lock`
```gherkin
Given the rows in the {word} table[[ on {dbService}]] are locked where:
| ... | ... |
```
Lock matching rows with SELECT ... FOR UPDATE on a separate connection, held until the locks are released or the scenario ends. Values in the table become `column = 'value'` conditions joined with AND; `null` becomes `IS NULL`. Values are escaped.
**Variants** (optional parts in `[[...]]` above):
- `the rows in the {word} table are locked where:`
- `the rows in the {word} table on {dbService} are locked where:`
**Parameters:** `{word}` (one word, no spaces), `{dbService}` (The name of a database registered in the scenario)
**Example:**
```gherkin
Given the rows in the parcels.parcels table are locked where:
```
## `sql.unlock`
```gherkin
Then the row locks[[ on {dbService}]] are released
```
Release row locks taken with the lock step.
**Variants** (optional parts in `[[...]]` above):
- `the row locks are released`
- `the row locks on {dbService} are released`
**Parameters:** `{dbService}` (The name of a database registered in the scenario)
**Example:**
```gherkin
Then the row locks are released
```
## `sql.select`
```gherkin
Then a[[ {ordinal}]] selection of rows is retrieved from the {word} table[[ on {dbService}]] where:
| ... | ... |
```
Query rows (SELECT * ... WHERE) and keep the result as the next selection for later assertions. Selections are numbered in the order they are retrieved; `the selection` means the first. Values in the table become `column = 'value'` conditions joined with AND; `null` becomes `IS NULL`. Values are escaped.
**Variants** (optional parts in `[[...]]` above):
- `a selection of rows is retrieved from the {word} table where:`
- `a {ordinal} selection of rows is retrieved from the {word} table where:`
- `a selection of rows is retrieved from the {word} table on {dbService} where:`
- `a {ordinal} selection of rows is retrieved from the {word} table on {dbService} where:`
**Parameters:** `{ordinal}` (A 1-based position such as `1st`, `2nd`, `3rd` or `4th`. Omitting an optional ordinal means the first), `{word}` (one word, no spaces), `{dbService}` (The name of a database registered in the scenario)
**Example:**
```gherkin
Then a selection of rows is retrieved from the parcels.parcels table where:
```
## `sql.select.poll`
```gherkin
Then within {duration} a[[ {ordinal}]] selection of at least {int} row(s) is retrieved from the {word} table[[ on {dbService}]] where:
| ... | ... |
```
Poll every 500ms until the query returns at least the given number of rows or the time is up. On timeout the last result (possibly empty) is kept, so assert on it with a row-count step.
**Variants** (optional parts in `[[...]]` above):
- `within {duration} a selection of at least {int} row(s) is retrieved from the {word} table where:`
- `within {duration} a {ordinal} selection of at least {int} row(s) is retrieved from the {word} table where:`
- `within {duration} a selection of at least {int} row(s) is retrieved from the {word} table on {dbService} where:`
- `within {duration} a {ordinal} selection of at least {int} row(s) is retrieved from the {word} table on {dbService} where:`
**Parameters:** `{duration}` (A duration in seconds or minutes, e.g. `5s` or `2m`), `{ordinal}` (A 1-based position such as `1st`, `2nd`, `3rd` or `4th`. Omitting an optional ordinal means the first), `{int}` (a 32-bit integer), `{word}` (one word, no spaces), `{dbService}` (The name of a database registered in the scenario)
**Example:**
```gherkin
Then within 10s a selection of at least 1 row is retrieved from the parcels.manifest_lines table where:
```
## `sql.select.jsonb`
```gherkin
Then a[[ {ordinal}]] selection of rows is retrieved from the {word} table[[ on {dbService}]] where the {word} jsonb column contains:
| ... | ... |
```
PostgreSQL: select rows whose JSONB column contains the given properties (`@>`). Dotted keys (`a.b`) build nested objects; every value is compared as a JSON string (`null` means JSON null).
**Variants** (optional parts in `[[...]]` above):
- `a selection of rows is retrieved from the {word} table where the {word} jsonb column contains:`
- `a {ordinal} selection of rows is retrieved from the {word} table where the {word} jsonb column contains:`
- `a selection of rows is retrieved from the {word} table on {dbService} where the {word} jsonb column contains:`
- `a {ordinal} selection of rows is retrieved from the {word} table on {dbService} where the {word} jsonb column contains:`
**Parameters:** `{ordinal}` (A 1-based position such as `1st`, `2nd`, `3rd` or `4th`. Omitting an optional ordinal means the first), `{word}` (one word, no spaces), `{dbService}` (The name of a database registered in the scenario)
**Example:**
```gherkin
Then a selection of rows is retrieved from the parcels.parcels table where the details jsonb column contains:
```
## `sql.json.are`
```gherkin
Then the {ordinal} row {word} property for the[[ {ordinal}]] selection[[ on {dbService}]] json properties are:
| ... | ... |
```
Assert JSON properties (JSONPath) of a JSON column in the given row of a selection. Every scalar is compared as text; `null` means JSON null and `undefined` means the property is absent.
**Variants** (optional parts in `[[...]]` above):
- `the {ordinal} row {word} property for the selection json properties are:`
- `the {ordinal} row {word} property for the {ordinal} selection json properties are:`
- `the {ordinal} row {word} property for the selection on {dbService} json properties are:`
- `the {ordinal} row {word} property for the {ordinal} selection on {dbService} json properties are:`
**Parameters:** `{ordinal}` (A 1-based position such as `1st`, `2nd`, `3rd` or `4th`. Omitting an optional ordinal means the first), `{word}` (one word, no spaces), `{dbService}` (The name of a database registered in the scenario)
**Example:**
```gherkin
Then the 1st row details property for the 2nd selection json properties are:
```
## `sql.json.match`
```gherkin
Then the {ordinal} row {word} property for the[[ {ordinal}]] selection[[ on {dbService}]] json properties match:
| ... | ... |
```
Like the `are` form, but each value is a regular expression that must match the whole property value (as text).
**Variants** (optional parts in `[[...]]` above):
- `the {ordinal} row {word} property for the selection json properties match:`
- `the {ordinal} row {word} property for the {ordinal} selection json properties match:`
- `the {ordinal} row {word} property for the selection on {dbService} json properties match:`
- `the {ordinal} row {word} property for the {ordinal} selection on {dbService} json properties match:`
**Parameters:** `{ordinal}` (A 1-based position such as `1st`, `2nd`, `3rd` or `4th`. Omitting an optional ordinal means the first), `{word}` (one word, no spaces), `{dbService}` (The name of a database registered in the scenario)
**Example:**
```gherkin
Then the 1st row recipient property for the 3rd selection json properties match:
```
## `sql.rows.eq`
```gherkin
Then the[[ {ordinal}]] selection[[ on {dbService}]] has {int} row(s)
```
Assert that a selection has exactly the given number of rows. `the selection` means the first selection of the scenario.
**Variants** (optional parts in `[[...]]` above):
- `the selection has {int} row(s)`
- `the {ordinal} selection has {int} row(s)`
- `the selection on {dbService} has {int} row(s)`
- `the {ordinal} selection on {dbService} has {int} row(s)`
**Parameters:** `{ordinal}` (A 1-based position such as `1st`, `2nd`, `3rd` or `4th`. Omitting an optional ordinal means the first), `{dbService}` (The name of a database registered in the scenario), `{int}` (a 32-bit integer)
**Example:**
```gherkin
Then the selection has 2 rows
Then the 2nd selection on parcels-db has 1 row
```
## `sql.rows.gt`
```gherkin
Then the[[ {ordinal}]] selection[[ on {dbService}]] has more than {int} row(s)
```
Assert that a selection has more than the given number of rows. `the selection` means the first selection of the scenario.
**Variants** (optional parts in `[[...]]` above):
- `the selection has more than {int} row(s)`
- `the {ordinal} selection has more than {int} row(s)`
- `the selection on {dbService} has more than {int} row(s)`
- `the {ordinal} selection on {dbService} has more than {int} row(s)`
**Parameters:** `{ordinal}` (A 1-based position such as `1st`, `2nd`, `3rd` or `4th`. Omitting an optional ordinal means the first), `{dbService}` (The name of a database registered in the scenario), `{int}` (a 32-bit integer)
**Example:**
```gherkin
Then the selection has more than 2 rows
Then the 2nd selection on parcels-db has more than 1 row
```
## `sql.rows.lt`
```gherkin
Then the[[ {ordinal}]] selection[[ on {dbService}]] has fewer than {int} row(s)
```
Assert that a selection has fewer than the given number of rows. `the selection` means the first selection of the scenario.
**Variants** (optional parts in `[[...]]` above):
- `the selection has fewer than {int} row(s)`
- `the {ordinal} selection has fewer than {int} row(s)`
- `the selection on {dbService} has fewer than {int} row(s)`
- `the {ordinal} selection on {dbService} has fewer than {int} row(s)`
**Parameters:** `{ordinal}` (A 1-based position such as `1st`, `2nd`, `3rd` or `4th`. Omitting an optional ordinal means the first), `{dbService}` (The name of a database registered in the scenario), `{int}` (a 32-bit integer)
**Example:**
```gherkin
Then the selection has fewer than 2 rows
Then the 2nd selection on parcels-db has fewer than 1 row
```
## `sql.trigger.raise`
```gherkin
Given a(n)[[ {ordinal} ordered]] before insert trigger on the {word} table[[ on {dbService}]] will raise a(n) {sqlState} exception where:
| ... | ... |
```
PostgreSQL: create a BEFORE INSERT trigger that raises the SQLSTATE for inserted rows matching the table (`column | value`, `null` for IS NULL), so you can test how your app handles database errors. The trigger is dropped when the scenario ends.
**Variants** (optional parts in `[[...]]` above):
- `a(n) before insert trigger on the {word} table will raise a(n) {sqlState} exception where:`
- `a(n) before insert trigger on the {word} table on {dbService} will raise a(n) {sqlState} exception where:`
- `a(n) {ordinal} ordered before insert trigger on the {word} table will raise a(n) {sqlState} exception where:`
- `a(n) {ordinal} ordered before insert trigger on the {word} table on {dbService} will raise a(n) {sqlState} exception where:`
**Parameters:** `{ordinal}` (A 1-based position such as `1st`, `2nd`, `3rd` or `4th`. Omitting an optional ordinal means the first), `{word}` (one word, no spaces), `{dbService}` (The name of a database registered in the scenario), `{sqlState}` (A five-character SQLSTATE code, e.g. `23505` (unique violation))
**Example:**
```gherkin
Given a before insert trigger on the parcels.parcels table will raise a 40001 exception where:
```
## `sql.trigger.raise.times`
```gherkin
Given a(n)[[ {ordinal} ordered]] before insert trigger on the {word} table[[ on {dbService}]] will raise a(n) {sqlState} exception {int} time(s) where:
| ... | ... |
```
PostgreSQL: create a BEFORE INSERT trigger that raises the SQLSTATE for inserted rows matching the table (`column | value`, `null` for IS NULL), so you can test how your app handles database errors. The trigger is dropped when the scenario ends. Only the first N matching inserts raise.
**Variants** (optional parts in `[[...]]` above):
- `a(n) before insert trigger on the {word} table will raise a(n) {sqlState} exception {int} time(s) where:`
- `a(n) before insert trigger on the {word} table on {dbService} will raise a(n) {sqlState} exception {int} time(s) where:`
- `a(n) {ordinal} ordered before insert trigger on the {word} table will raise a(n) {sqlState} exception {int} time(s) where:`
- `a(n) {ordinal} ordered before insert trigger on the {word} table on {dbService} will raise a(n) {sqlState} exception {int} time(s) where:`
**Parameters:** `{ordinal}` (A 1-based position such as `1st`, `2nd`, `3rd` or `4th`. Omitting an optional ordinal means the first), `{word}` (one word, no spaces), `{dbService}` (The name of a database registered in the scenario), `{sqlState}` (A five-character SQLSTATE code, e.g. `23505` (unique violation)), `{int}` (a 32-bit integer)
**Example:**
```gherkin
Given a before insert trigger on the parcels.parcels table will raise a 40001 exception 1 time where:
```
## `sql.trigger.insertRaise`
```gherkin
Given a(n)[[ {ordinal} ordered]] before insert trigger on the {word} table[[ on {dbService}]] will insert and raise a(n) {sqlState} exception where:
| ... | ... |
```
PostgreSQL: create a BEFORE INSERT trigger that raises the SQLSTATE for inserted rows matching the table (`column | value`, `null` for IS NULL), so you can test how your app handles database errors. The trigger is dropped when the scenario ends. The row is still committed through a second connection (dblink) before the error, simulating a write that succeeded but reported failure.
**Variants** (optional parts in `[[...]]` above):
- `a(n) before insert trigger on the {word} table will insert and raise a(n) {sqlState} exception where:`
- `a(n) before insert trigger on the {word} table on {dbService} will insert and raise a(n) {sqlState} exception where:`
- `a(n) {ordinal} ordered before insert trigger on the {word} table will insert and raise a(n) {sqlState} exception where:`
- `a(n) {ordinal} ordered before insert trigger on the {word} table on {dbService} will insert and raise a(n) {sqlState} exception where:`
**Parameters:** `{ordinal}` (A 1-based position such as `1st`, `2nd`, `3rd` or `4th`. Omitting an optional ordinal means the first), `{word}` (one word, no spaces), `{dbService}` (The name of a database registered in the scenario), `{sqlState}` (A five-character SQLSTATE code, e.g. `23505` (unique violation))
**Example:**
```gherkin
Given a before insert trigger on the parcels.parcels table will insert and raise a 40001 exception where:
```
## `sql.trigger.insertRaise.times`
```gherkin
Given a(n)[[ {ordinal} ordered]] before insert trigger on the {word} table[[ on {dbService}]] will insert and raise a(n) {sqlState} exception {int} time(s) where:
| ... | ... |
```
PostgreSQL: create a BEFORE INSERT trigger that raises the SQLSTATE for inserted rows matching the table (`column | value`, `null` for IS NULL), so you can test how your app handles database errors. The trigger is dropped when the scenario ends. The row is still committed through a second connection (dblink) before the error, simulating a write that succeeded but reported failure. Only the first N matching inserts raise.
**Variants** (optional parts in `[[...]]` above):
- `a(n) before insert trigger on the {word} table will insert and raise a(n) {sqlState} exception {int} time(s) where:`
- `a(n) before insert trigger on the {word} table on {dbService} will insert and raise a(n) {sqlState} exception {int} time(s) where:`
- `a(n) {ordinal} ordered before insert trigger on the {word} table will insert and raise a(n) {sqlState} exception {int} time(s) where:`
- `a(n) {ordinal} ordered before insert trigger on the {word} table on {dbService} will insert and raise a(n) {sqlState} exception {int} time(s) where:`
**Parameters:** `{ordinal}` (A 1-based position such as `1st`, `2nd`, `3rd` or `4th`. Omitting an optional ordinal means the first), `{word}` (one word, no spaces), `{dbService}` (The name of a database registered in the scenario), `{sqlState}` (A five-character SQLSTATE code, e.g. `23505` (unique violation)), `{int}` (a 32-bit integer)
**Example:**
```gherkin
Given a before insert trigger on the parcels.parcels table will insert and raise a 40001 exception 1 time where:
```
## `sql.trigger.raised`
```gherkin
Then the[[ {ordinal} ordered]] before insert trigger on the {word} table[[ on {dbService}]] was raised {int} time(s)
```
Assert how many times a simulated trigger raised its exception. Triggers are numbered in creation order; the table must match the trigger's table.
**Variants** (optional parts in `[[...]]` above):
- `the before insert trigger on the {word} table was raised {int} time(s)`
- `the before insert trigger on the {word} table on {dbService} was raised {int} time(s)`
- `the {ordinal} ordered before insert trigger on the {word} table was raised {int} time(s)`
- `the {ordinal} ordered before insert trigger on the {word} table on {dbService} was raised {int} time(s)`
**Parameters:** `{ordinal}` (A 1-based position such as `1st`, `2nd`, `3rd` or `4th`. Omitting an optional ordinal means the first), `{word}` (one word, no spaces), `{dbService}` (The name of a database registered in the scenario), `{int}` (a 32-bit integer)
**Example:**
```gherkin
Then the before insert trigger on the parcels.parcels table was raised 1 time
```