SQL

View .md Open in Claude

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`

Given a(n) {word} database with the following properties:
  | ... | ... |

Properties: url (JDBC or native URL), user, password (all required; ${env:..}/${sys:..} expanded), schema (optional).

Parameters: {word} (one word, no spaces)

Example:

Given a parcels-db database with the following properties:

`sql.seed`

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 (<dataset><schema.table col="v"/></dataset>), .json, .csv (a directory of <table>.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:

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

`sql.lock`

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:

Given the rows in the parcels.parcels table are locked where:

`sql.unlock`

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:

Then the row locks are released

`sql.select`

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:

Example:

Then a selection of rows is retrieved from the parcels.parcels table where:

`sql.select.poll`

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:

Then within 10s a selection of at least 1 row is retrieved from the parcels.manifest_lines table where:

`sql.select.jsonb`

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:

Example:

Then a selection of rows is retrieved from the parcels.parcels table where the details jsonb column contains:

`sql.json.are`

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:

Example:

Then the 1st row details property for the 2nd selection json properties are:

`sql.json.match`

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:

Example:

Then the 1st row recipient property for the 3rd selection json properties match:

`sql.rows.eq`

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:

Then the selection has 2 rows
Then the 2nd selection on parcels-db has 1 row

`sql.rows.gt`

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)

Example:

Then the selection has more than 2 rows
Then the 2nd selection on parcels-db has more than 1 row

`sql.rows.lt`

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)

Example:

Then the selection has fewer than 2 rows
Then the 2nd selection on parcels-db has fewer than 1 row

`sql.trigger.raise`

Given a(n)[[ {ordinal} ordered]] before insert trigger on the {word} table[[ on {dbService}]] will raise a(n) {sqlState} exception where:
  | ... | ... |

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:

Given a before insert trigger on the parcels.parcels table will raise a 40001 exception where:

`sql.trigger.raise.times`

Given a(n)[[ {ordinal} ordered]] before insert trigger on the {word} table[[ on {dbService}]] will raise a(n) {sqlState} exception {int} time(s) where:
  | ... | ... |

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:

Example:

Given a before insert trigger on the parcels.parcels table will raise a 40001 exception 1 time where:

`sql.trigger.insertRaise`

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:

Example:

Given a before insert trigger on the parcels.parcels table will insert and raise a 40001 exception where:

`sql.trigger.insertRaise.times`

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:
  | ... | ... |

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:

Example:

Given a before insert trigger on the parcels.parcels table will insert and raise a 40001 exception 1 time where:

`sql.trigger.raised`

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)

Example:

Then the before insert trigger on the parcels.parcels table was raised 1 time