Pros and cons of using database dump in autotesting?

Question

I'm going to write API and e2e auto-tests, and planning this encountered the problem of how to load test data needed to execute test cases. I see 2 approaches:

1. Use api to create necessary resources.The problem with this approach is that it might be very complicated to create some resources, we will duplicate some logic that exists in front-end. And when api changes, testing helpers need to be changed as well. And if we stored some stub json files with data, we need to go though all of them and make changes.

2. Create data though UI and save mysql dump, then load for each test appropriate dump. Benefits of this approach is that in case of api change, we don't need to change anything, just run migrations. But if 2 persons were changing same test in their feature branches, and then merge, it will be hard(impossible?) to merge 2 mysql dump files.

Which one is better, is there other alternatives. Personally I tend to use second one, is there any severe downsides of it?

Answer 1

I definitely understand the want to do it through the UI. Seems so much easier. I think that you're on the right track though to start doing it with the UI. The data will be much closer to what you actually need to test. But, this should only be a start for you. Ideally, these dumps are there for good and never change. Here's a couple of ideas.

• In all reality, they are going to change. And each time that they do change, you will need to get new dumps for each of these tests. Instead, I'd recommend beginning with option 2, learn the functionality, schema, and interactions, and see if you can start generating your own SQL to use.

• Goes along with the previous, but, stop using dump files if at all possible. Just insert the data that you need, or set it to a known state if at all possible. This will also make making new tests much easier.

• Because this is e2e and api, I'm guessing that most of the internals will normally be pretty similar throughout changes. If multiple people are going to be running this on the same feature branch from their local machines, can you do a setup that would customize the test data to them? For example, within my current organization, when we setup tests or even local environments, we begin by running a script that will modify variables in our app and tests to be for each of us so as to not step on toes.

Answer 2

Why not just stub out the database and deal only with Entities? Databases are details which you can:

(1) - Check behavior independently;

(2) - Isolate your use cases from.

If you want to set up a component check for a service, all you need to do is boot up the service, create the necessary Entities and perform a call to its endpoints.

If you want to check the behavior of the database access layer, all you need is to create this object and play around with it. Your use cases do not care about this behavior because the flow of control goes in the opposite direction of the dependencies.

Coupling your use cases checks to the database is unnecessary and would make your checks useless to be used in refactoring of the use cases.

Answer 3

I would go for a mixed 1/2 approach.

The problems:

1. Repeatedly creating all of the required data using APIs is too slow for CI tests
2. Managing changing SQL dumps is very hard (they don't merge well)

What worked best for us:

• Creating a single baseline data for our test environment and manage it using tools like Flyway (Java) or Knex.js.
• This baseline doesn't change too often and will be used for all of our tests.
• Each test will create the additional entities it requires for its operation and will possibly delete them after its successful completion