My situation:

I am creating automated tests for testing a server's API endpoints. It is connected to a database and making requests to its API endpoints can result in it grabbing data from the database and, possibly, processing said data before returning it.

Let's say, for example, that for one of its endpoints which accepts a POST request, the general flow is as follows:

  1. Send a configuration to the endpoint
  2. The configuration is stored in the database
  3. The server processes and returns data based on the passed-in configuration

The POSTed configuration depends on an "instance" to already exist in the database. This dependency can be met by using the server's other API endpoints, or by prepopulating the database with my own queries.

I want to avoid mocked unit level tests, which are the responsibility of the Software Engineers. That being said, how can I go about testing this endpoint at the scope of a SQA tester?

My solution:

There are 2 obvious ways that I see I could go about testing this endpoint:

  1. Directly -- Prepopulate the database using my own queries and avoid using the other API endpoints. Then call the specific API endpoint that I wish to test, and verify that its output is expected given the prepopulated database input and the configuration I created.
  2. Indirectly -- Use the server's other API endpoints to create a "flow test", testing this particular endpoint in a group of endpoints.


I believe both methods above are valid testing strategies that should be covered. The former is a bit involved in the sense that I must understand the database schema and have a general understanding of how it's being used, which results in a gray-box kind of testing. But prepopulating data also allows me to avoid test chaining and allows me to test the endpoints at a more granular level, but still not at a mocked unit level, as desired. It can also be argued that I am doing integration level testing, testing the integration of the server with the database, a kind of sandwich/hybrid integration test. The latter is only slightly less involved and tests at an integration level, which is obviously valuable for me as a SQA tester.


Many responses mentioned that if I rely on prepopulating the database, my tests become prone to breaking due to database schema changes. I agree. However, in order to have complete test coverage of the server and its functionality, I've already created automated tests for the database schema. In fact, in my API endpoint tests, I am using some of the functionality from my schema tests to prepopulate my database. This is Bottom-Up testing. And, in an ideal world, my automated schema tests should be broken long before my automated API tests break.

3 Answers 3


I would personally go for approach 2 wherever possible.

If the API itself is capable of making its own state changes, then it's a safer and more accepted way of doing things. If you make direct changes to the database, any bugs that you find as a result will inevitably be second guessed by the developers.

The first approach is also more prone to breaking due to code changes. What if they rename a field in the database? You'd have to figure that out when your tests start failing.

The first also creates a situation where you may not be sure that what you're testing is the reality of the API. What if the API starts storing data in a different set of database fields during a create event, but during a retrieval event it still accesses the old data location? That would lead to a false negative, where you think the API is working as intended, but it only works based on you directly inserting information into the database.

  • I agree that the second approach may be safer, and more realistic. But I also want to note that, fortunately, I am not constrained to go with only a single option. Both approaches are valuable to some extent and each can test a different aspect of the API, which, in conjunction, increases the test coverage of the server. Although it would be more to maintain, consider the false positive example. If you only use the second approach, and your second API does additional work behind the scenes that inadvertently corrects the mistakes of your first API, then you've got an undetected problem.
    – natn2323
    May 22, 2019 at 4:39


You are correct that mocking or stubbing out parts of the system is more something for the unit test level than for testing the end-to-end flow for API endpoints. You are also correct that by not mocking or stubbing you run the risk of needing to introduce dependencies to your tests.

Possible Solutions

As Anonygoose says, you face risks by using your first potential approach. Your second approach is much safer to work with.

Other Considerations

Some other considerations you may find helpful:

  • Can you restore a copy of a known-good database at the start of your test run? If you can, you can arrange to have any data you need for an endpoint available in the database file, without needing to worry that changes will make your schema invalid. If you use this method, you will want to have a regularly running job to backup and snapshot the parent database, and include an OnStart/OnLoad event that handles restoring the database to your test server. Note that this method is only helpful if you can restore the database quickly.
  • Separate the mechanics of the API call from the test code - If you have to work with a shared database, it helps to maintain the API calls separately from the test code so you're not duplicating code. Your test code becomes simply pulling your test data from wherever, formatting it the way the API requires it, then calling the endpoint method and asserting that whatever result you get is what you expect. That way you can have the test do something like this:

    public void UpdateConfigExpectSuccess()
        Api.InsertConfig(configObject); //Obviously this is defined elsewhere
        Configuration updateConfig = new Configuration(testdata); //pull test data from test context or manually add it - it doesn't matter
        ApiResponse response = Api.UpdateConfig(updateConfig);
        // Whatever you plan to assert

    The way I'm handling a similar situation is to create classes representing the requests and the responses, and using serialization/deserialization. The API itself is represented by a separate class that has a singleton object, and a [BeforeTest] method that ensures the authentication token has been generated. The API class handles sending the correct headers with each call.

  • Build Iteratively Once you decide how to approach the situation, build in small chunks. My method is to get the call working first, then extract POST parameters into their own object. Once I'm done with that, I can look at more complex cases. It's working reasonably well, although the object with over 100 fields is a pain (not that I can do anything with it, that's what the database table is like, and the whole team is keeping the mapping as close to the database as possible).
  • Interesting approach. My current implementation for prepopulating the database isn't quite like yours in (1), but that's something to consider. Yes, I have code separation as in (2). And yes, so far, building tests has not been tremendously difficult following an iterative approach as in (3), but I can tell you that my objects do not contain over 100 fields ;)
    – natn2323
    May 22, 2019 at 4:46

It actually depends on what do you plan on testing, in case your tests are basic functionality tests with low volumes of data (the "write one thing, read it back" kind) then option 2 could be a good option due to it's simplicity, but once you move to testing of bigger amounts of data (non functional tests like performance or timing, but not only) here are a few more considerations-

If you plan on using production data for testing (anonymised I assume) then option 1 is probably the only viable option.

Thinking of setup and provisioning speed, then option 1 could be much faster, in fact you could keep fresh copies of the database ready for immediate use, and clean them up after testing in the background while using other copies. Option 2 seems to be non scalable for a large amount of data in the database, especially if you plan on cleaning up the database after each test and setting it up again.

Reliability is indeed a concern, but it's not an easy call which method will work better though. Like @anonygoose mentioned option 1 adds the another layer of complexity to your test code since it needs to be aware now to the structure and content of the database, but on the other hand I have found that having a large number of API calls tends to be fragile and more prone to environment problems

Debugging, Developers and other trust issues becomes simpler if your tests are simple and preferably test just a few things, in option 2 you are basically exercising the code for each test, this in turn will fill up logs with practically irrelevant messages that will make debugging and communication with developers harder- you planned on testing X but ended up testing X+Y

Note that there's a middle way between mocking and using the actual database, you can setup an in memory database and test against it, you will gain a lot in execution speed while testing against a real databse

  • 1
    I agree with your comment in paragraph 4, that API calls tend to be fragile and more prone to environment problems is a difficult thing to debug and tend to. In many cases, I feel that it is easy to fall into, and get trapped in, the situation you described in paragraph 5 about planning to test X but ending up testing X+Y. In regards to your last paragraph, it is within my plans to spin up Dockerized databases to test with. My current project merely tests functionality, so performance and load testing on a more "real" database is not pertinent.
    – natn2323
    May 22, 2019 at 4:55

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.