API tests against production DB with no endpoints to set up test data

Question

I'm creating regression tests for one of our APIs. I'd like to discuss how to go about it in my particular situation.

I have no problem on the dev environment, but when it gets deployed to a staging env for final tests before going to production, it gets tricky because:

• our staging DB equals our production database, so whatever I change in the database I change it in production as well
• some of the endpoints don't have an equivalent for deletion/change of a resource, so if I create a resource via a certain endpoint, I can't delete/change it using a different endpoint of the same API
• in my particular example, there is not an endpoint for creation of a resource that I need to use/test later

The problem I'm trying to solve is as follows:

There's endpoint /Cards/{ean} that creates a card. The card has to be created for a particular user, if not, I get 404:

if (email == null) return NotFound(string.Format("The email is required when issuing a new card."));

The card (its ean) also has to exist, or I get 404, and has to be in a particular state, or I get 409 Conflict:

if (newCard == null) return NotFound(string.Format("Card with EAN {0} does not exist.", ean));
if (newCard.CardStatusId != Model.CardStatus.New) return Conflict(string.Format("Card with EAN {0} has already beeen emitted.", ean));

There're about a thousand cards that fit these conditions in the production DB. However, I can't just use some of them for testing purposes, because there's a business process related to them, they are actually printed out (they're these small plastic cards you might get in a store), so obviously if I change their state, someone in a store might get disappointed.

All in all, the problem seems to me like there's no straightforward way to create test data.

There're a few possible solutions I can see at the moment:

• I can create a new card directly in the database (insert into Card...) before testing the API, so then I could use the card number I created prior to my API tests. Obviously there's a huge benefit that I don't play around with any production data. On the other hand, I'd probably need to go for a different tool than Postman (that I thought would be sufficiently easy to use with little focus on other things that the tests itself), because as far as I know, I wouldn't be able to easily access the DB (not MS SQL https://documenter.getpostman.com/view/5922408/RznJmGfn?version=latest, https://stackoverflow.com/questions/51295594/integrate-postman-with-sql-server-management-studio), but that's probably bearable for my.
• I use some of the production data (cards), and right after the tests, I change their state back to the original "unused" one. There's still a small change I'll cause a trouble in production, plus I'd need to research more if this is fully possible (so far it seems it is)
• Devs create a new endpoint that'd be able to change card states (return them to the state before the tests), or create new cards. Obviously, I have no idea if devs would be very cheerful about this.
• We start using a different database where I can do whatever I want. I think this is quite nice, but requires some resources that might not be available fur such a purpose.
• I forget about the staging environment and test it only on dev. Well, it will get tested on dev anyway. But it seems reasonable to run some tests even on staging as well, so I don't like this very much.

What should I do? What good solutions I didn't even mention?

Thank you

Answer 1

Reading your question it seems you have a messed up DevOps pipeline. The fix should not be for the testing strategy but the DevOps implementation itself. Few things that needs to be restructured are:

1. Why are you automatically syncing staging data to Production?

The purpose of staging is to mimic the production environment and make sure things work in production once the product is deployed.

The main purpose of stagging is to make changes and validate its effect when it is actually deployed in the production environment. SO if you are making changes and syncing it immediately to production without any validation, then it is a risky implementation.

You should be able to make changes and without fear restore it to the production environment when needed.

2. How to achieve it through postman

I can create a new card directly in the database

CI/CD has many powerful tools, tools like Jenkins, Octopus, etc supports Powershell and batch commands, etc. You don't have to interact with DB through the postman itself. You can add a build step for creating a table for the card in the database and then after postman scripts step finishes add a final step to remove or restore the DB to production.

3. Risk with your approach

I use some of the production data (cards),

That would be the worse thing you could do. Scripts won't always work as we expect, a small change in output might change the program flow and could result in unintended changes in production cards.

If you are going to replace the entire table with production DB then it would be safer. Do not try to individually manipulate or restore values that could affect production settings.

Devs create a new endpoint that'd be able to change card states

This is a good solution not only for now but also for the future. Such API endpoints avoid unintended DB changes by inexperienced persons. It can make sure no other table or fields are affected by providing proper access control.

We start using a different database where I can do whatever I want.

Staging database should be able to manipulate and rebuild as when needed. It should mimic actual production one but should not automatically sync two-way.

I forget about the staging environment and test it only on dev.

This would be highly risky as you wouldn't be able to predict how the product behaves in actual world.

Summary:

1. Ask for more API endpoints to manipulate the database and thereby avoiding unnecessary DB changes
2. Make changes to the table in Staging DB, but don't sync production with staging. Restore changes and sync staging with production at the end of tests.
3. If you want to sync staging to production add separate build step, don't automatically do it. Trigger it only when needed