I'm somewhat new to Automated API Testing and have been experimenting with doing some API (functional?/regression?) tests in SoapUI and Katalon against multiple microservices (no-UI, just API), along with using assertions to check for a valid response. However, I'm not sure how to handle the state of the database before and after a test is run.
For example, if I upload something through a POST to an endpoint and it's supposed to create 5 entries, then I can assert that those 5 were created by calling another endpoint with a GET and checking whether 5 objects are returned in the array. However, if I run that same test case again then I'll receive 10 entries (since the old 5 entries still exist) but my old assertion will fail since it's expecting 5.
I guess I can try to use DELETE calls to manually clear the state before a run but then I have to first track down the ids of what I want to delete. Or I can have some sql that runs to make sure those records are deleted but I run into the same issue, and both options feel janky. The only other option seems to be to restore the databases from dump files but that limits one to only running such tests on a test environment, which I guess might be a good thing? However, I haven't seen such support in SoapUI or Katalon for restoring database so far.
What is the best way to handle the state of databases behind microservices while running API tests whether it's on a local machine or in the cloud?