I’ve recently joined a company who are rolling out an end to end API testing strategy based around explicitly verifying the payload returned against the database at the end to end level. The approach seems a little flawed to me so I wanted to get some other opinions.

The current setup is that data is retrieved from the database with a handwritten SQL query against the various tables that hold the data. This query is then formatted manually to a JSON structure in the test suite (containing curly braces and so on {}) so that it can provide a character by character equivalent to what the endpoint should return. During the test both the SQL query and the endpoint are called. An assertion is then run to ensure both match, this is against the raw JSON (neither the SQL or the data returned from the endpoint is deserialised, but could be).

The drawbacks I see are:

  • Duplication of logic - the test suit is redefining the SQL structure to be pulled from the database, this logic must already exist in the source code
  • Time consuming to debug failures - essentially diffing two large JSON files (could be improved by objectifying the data though)
  • Flaky - getting the structure perfectly aligned between database call and API call is flaky due to changes in the database structure, or the data in the database turns up unexpected scenarios in the query (the data is an obfuscated copy of live)
  • Because of the complex nature of the tests the coverage at the end to end level isn’t great (as they are so complex to write and maintain)

I was always led to believe that end to end tests should be fairly light in number, the old ratio of 70% unit, 20% integration and 10% e2e. And that integration tests should be used to mock the return of SQL data etc from other units to ensure this logic is correct. End to end tests being there to ensure something is returned by the endpoint when running in the real environment and that data is sensible (based around asserting the data types and content to some degree, i.e. a list of items is returned that deserialises to an object correctly)

Is having such a reliance on testing at the end to end level in this way a good idea? Or this type of approach OK and used more widely?

2 Answers 2


This looks more like a contract testing use case than end-to-end. You may read more here - https://techbeacon.com/app-dev-testing/end-end-vs-contract-based-testing-how-choose

A few noteworthy points from this article -

  1. You test contract agreements between a consumer endpoint and an API provider endpoint.
  2. Contract-based test suites are combined to cover each interaction scenario that takes place between the consumer and provider endpoints.
  3. End-to-end testing involves testing a user workflow from beginning to end, including all back-end processing engines, be they API services or other messaging or data transfer services.

While testing an API by payload comparison with a database query can be a valid approach, it is not necessarily the best approach for end-to-end testing.

One of the main drawbacks of this approach is the duplication of logic, as you mentioned. It can be time-consuming to maintain and update the SQL query and the JSON structure in the test suite, especially if there are frequent changes to the database structure.

In addition, this approach can be flaky, as you also noted. There is a risk that the structure of the database query and the API response may not always align perfectly, leading to false positives or false negatives in the test results.

Another concern is that this approach may not provide adequate test coverage at the end-to-end level, as the tests are complex and difficult to maintain. It may be more efficient to use integration tests to mock the return of SQL data and other units to ensure that the logic is correct.

In general, a good approach to end-to-end testing is to focus on testing the functionality of the API in the context of the overall system, rather than relying solely on database queries. This can be achieved by testing the API endpoints in a realistic environment, with realistic data, and ensuring that the API returns the expected results in response to different inputs and scenarios.

While it may be useful to include some database-related tests in the end-to-end suite, it should not be the only method of testing the API. Instead, a variety of testing approaches should be used to ensure comprehensive test coverage at all levels of the system.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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