Multiple DB queries

[svzhl]

At the moment only one dbQuery statement is supported per test case. It would be nice to query multiple independent statements or run dbQuery alone in a separate test case with no HTTP request emitted.

The current workaround is to run a single big query with multiple joins but it has some drawbacks.

  dbQuery:
    SELECT i.id, i.name, p.value
    FROM items i
    LEFT JOIN properties p ON p.item_id = i.id;

  dbResponse:
    - '{"id":"123","name":"test_name","value":"property1"}'
    - '{"id":"123","name":"test_name","value":"property2"}'

You can see that id and name values should be repeated in the results.

The situation becomes worse if a test case requires to check the data in two or more completely independent tables. Using join for this is misleading for a reader of test as the tables are orthogonal.

[leorush]

Why don't you use subqueries?

[svzhl]

SELECT
(
  SELECT id, name
  FROM items
),
(
  SELECT value
  FROM properties
)

would result in pq: subquery must return only one column as the first subquery selects two columns.

[leorush]

Depends on data you trying to check. Usually i use COUNT funсtion in subqueries, to check that there are given number of records in some table. For ex:

(SELECT COUNT(*) AS value_count FROM properties WHERE value = 'my_value' AND item_id = id )

If you want to transpose rows to columns, check this out https://stackoverflow.com/questions/23060256/postgres-transpose-rows-to-columns

[svzhl]

Checking only count is quite a limiting functionality.

Thanks for pointing out crosstab function, it is indeed useful in some cases. But it adds a little bit of complication to both writing and reading tests. Besides it doesn't solve an issue of querying 2 and more independent tables. Using joins and crosstab() function may mislead readers about intentions of the author.

[fetinin]

I also think that this functionality will be useful and improve tests readability.

Now we use the following workaround with cases:

  dbQuery: >
    {{ .query }}
  cases:
    - dbQueryArgs:
        query: SELECT 1
      dbResponse: 1
    - dbQueryArgs:
        query: SELECT 2
      dbResponse: 2

But that is verbose, non-intuitive and executes test again for each query.

[keyclaim]

I think we need change rule:
Test must contains request + response
to
Test must contains request + response OR dbQuery + dbResponse

[github-actions]

🚀 Issue was released in v1.18.0 🚀