I've worked with a big project where the developers are writing wrong queries to bring data from the DB. Eg a DB query which brings all data from DB and then filter it. It is impacting the performance of the application.

When they run performance test, tests run with dummy data in the database which is not in very high volume.

So instead of writing tests like this below example;

Scenario: Application returns list of inactive participants
    Given I access /participants?status=inactive
    When service returns 'OK' response

I want to write tests like

Scenario: service returns empty list when no inactive participants
    # setup DB/mocks
    Given there is no participant in DB
    # hit endpoint
    And I access "/participants?status=inactive"
    # assert query
    Then Application query DB with inactive filter
    And Application returns 'OK' response with an empty list

Scenario: service respond with 400 for invalid status
    # hit endpoint
    Given I access "/participants?status=invalid"
    # assert query
    Then Application doesn't query DB
    And Application returns 'Invalid Data' response

Scenario: service returns list of inactive participants
    # setup DB/mocks
    Given there are participants with all status in DB
    # hit endpoint
    And I access "/participants?status=inactive"
    # assert query
    Then Application query DB with inactive filter
    And Application returns 'OK' with participants having following properties each participant
    | id |
    | full_name |
    | status |

The first challenge here is that I'm hitting same API with same inputs and expecting different response. But that I can probably solve with some mocking library.

I need your suggestion to write correct tests and approach if possible.

  • Application query DB with inactive filter ? how do you validate that ?
    – PDHide
    Oct 8 '21 at 8:51
  • Why can't you increase the volume of data in the database? You may need a dedicated environment that is much closer to the production infrastructure to get valid performance tests, so you may not be able to run the performance tests on every commit, but there's no reason why you can't run them at least once a day and highlight areas of the application that are poorly performing. Oct 8 '21 at 11:28
  • @PDHide, That solution depends on what approach I take to connect to the DB. If it is spring JPA then logs watcher, if it is node js based then probably some signaling. Oct 8 '21 at 11:51
  • @ThomasOwens we'll definitely not get the dedicated server for that :) Hence, I was asserting my approach. However, I'm trying for that in parallel Oct 8 '21 at 11:54
  • How does your improved appraoch validates performance? its just validating more data right
    – PDHide
    Oct 9 '21 at 8:05

If your test framework is completely black-box and only has access to the API like a consumer would, could you repeatedly hit the endpoint that adds a participant to build up the DB state like you expect, before hitting/testing the other endpoints that retrieve data (and then cleaning those up with different endpoints)?

I assume this would be rather slow and inefficient, and so if your test framework does have direct DB access doing the setup/cleanup directly there would more than likely be preferable. But if not, that may be the only option.

Also I'm assuming there's already some automated way of monitoring/reporting the performance of these requests. If not, that's probably the first step, just getting some way to do/automate the measurements, whether in the production app and/or in the tests. That way it can hopefully evolve into a feedback tool for the developers too as they write new queries.


You can use a test double of type Spy:

enter image description here

In a summary, you would replace the DB with your test-controlled Spy, which registers what are the outputs of SUT (in your case, the queries). You can then look what outputs were generated and make assertion over it (e.g. the inactive filter).

For the scenarios that you need a response, you can use a Mock or a Fake Database.

  • Yes Fake Database and one more mock library Stubmatic that I created myself is in my mind for the solution. But I'm confused if I should assert how another service is being called. Or there can be some better approach. Oct 8 '21 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.