Is testing an API by payload comparison with a database query advisable at the end to end level?

Question

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?

Answer 1

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.