I've had this discussion recently and would appreciate some more opinions and experiences of this community.

When setting up data for integration tests (API or UI), is the favored approach via SQL or via API calls?

Pros for doing API calls:

  • if business logic or database changes, data will be treated accordingly and still be correctly inserted
  • no need to maintain separate scripts
  • realistic as this is how data is added in production as well

Pros for using SQL scripts:

  • if the API contains bugs, you won't be blocked during setup already (no dependency on other API endpoints)
  • easier to get data into a specific state
  • full control over which data is inserted

Additional question When using APIs, I'm kind of worried that tests will inevitably become end-to-end (rather than integration) with too many points of failure. Let me clarify with an example, supposing we do setup via API calls.

  • Create customer test: calls CreateCustomer route
  • Create order test: calls CreateCustomer > CreateOrder routes
  • Create invoice test: calls CreateCustomer > CreateOrder > CreateInvoice routes

... And so on. If CreateCustomer fails, then all tests fail while they shouldn't (as merely used as setup step in most).

  • what's the difference from this question ?
    – Rsf
    Commented Sep 17, 2019 at 8:27
  • This is HOW data is setup, the other question is WHERE (at which level or grouping).
    – FDM
    Commented Sep 17, 2019 at 9:28

2 Answers 2


What if the datastore changes and the API stays the same? Tests should help developers to refactor safely. This includes changing data storage types, maybe even to flat files or another API for storage. I prefer to use the least implementation details as possible for this reason.

The API the test-fixtures use do not have to be an web-service, but could also be a create user class used by the web-api.

When using APIs, I'm kind of worried that tests will inevitably become end-to-end (rather than integration) with too many points of failure.

Using the database means it is sorta end-to-end, you need a fully deployed and working system for your test. Else you could mock the database for example.

These points of failure should be fixed asap, for example by a rollback if broken. Isn't it the general idea that tests detect issues in API's as soon as possible? :)

  • Good points, let me clarify: with "end-to-end" I mean complete flows (from create customer to send invoice) rather than separate API routes being tested. But indeed, we're talking about "integration" tests with everything deployed. With "more points of failure" I mean that a test can break on a preliminary API call that isn't really the subject of the test - which obscures the test reporting.
    – FDM
    Commented Sep 17, 2019 at 9:32
  • So, "using the least implementation details as possible" what is your concrete suggestion or usual approach? A class that creates users contains either a script (albeit in a wrapper, not SQL) or logic similar to that of the API, with the same pros/cons as listed I suppose.
    – FDM
    Commented Sep 17, 2019 at 9:34
  • @fdm Sorry for the late reply. I understand the wish for cleaner test reporting, but in a continuous delivery world any failing test should be fixed asap, so personally I do not really care about the reporting, unless its 100% green. Any broken API should be rolledback or fixed asap. Teams should have enough tests to not push broken API's into the "integration" test env. Commented Sep 25, 2019 at 8:04
  • @FDM For the create user example I would prefer to use the actual classes that the API code uses, but as the world shifts to services architectures I guess just using the real API is cleaner, because you do not have to worry about anything it is the responbility of the service. Commented Sep 25, 2019 at 8:07

Am in favor of using the application itself to create any data needed for the (regression) test whether testing the UI or the API. Whenever possible I also like to clean up after the test by removing data created for it. Prefer also to do this through the application if possible but directly in the (test!) database if necessary.

This isn't as scalable as one would like, but it is much more manageable as each test script/case is independent and idempotent.

To me "end to end" implies business process/use case level flow. API endpoints generally are (should be?) focused on working with bounded data and a more granular objective, which is enforced by the http method used (GET, POST, etc.)

UI based testing is usually at a higher level of abstraction and much more like to approach "end to end" from the user perspective.

It is easier/more manageable to keep testing independent for interfaces: i.e., test APIs separately so components using the API can treat it as black box. Same for any well-defined and, of course, accessible to testing interfaces. That tends to help with "using the least implementation details as possible." It also makes locating the problem easier when the tests themselves are bounded.

My working definition of "regression" testing means working within a live (dev, qa) image of the system. Mocking is more applicable to unit testing and allows deeper diving into the internals of the application, i.e., more white-box in flavor.

If the number of cases and the overhead of making them idempotent begins to impact test run time, then doing setup/restore at the database level needs consideration. And which sets of cases to run under which circumstances (sanity test, component focused, full regression, etc.) need to be addressed as well.

Plenty to keep us employed!


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.