We have some GET APIs written in Python which fetches some data from Oracle i.e. there are some SQLs written in Python APIs through which it fetches the data from Oracle. This is not consumed by an external system but the response of this API is passed to the UI which displays the data.

I need to write some end to end test cases for this API which would check whether the data it fetches from the Oracle is correct or not. This would mean I would need to actually check the SQLs that are written in the API. I have figured out 2 ways to do this :

  1. To mock the data in database, save the expected data and compare it against the API response.
  2. To write SQLs and fetch the data from that SQLs and compare it against API response.

I personally prefer the 2nd way, but this would mean parallel development. How do you do it in your organization ?

2 Answers 2


What you actually have to do is prepare your test data. Depending on the project, access rights and so on, you got a couple of options:

  1. Keep some test data in the database, have a script to recreate it whenever necessary,
  2. Recreate all test data via some script before all tests are run, cleanup afterwards,
  3. Have a pre and post condition in each test, which will create the data before execution and cleanup afterwards.

I usually write tests that create and cleanup after them, I would suggest you do the same. So instead of validating SQL query vs API, what you should do is:

  1. Create test data in precondition via SQL,
  2. Have your test consume the test data,
  3. Clean up after the test, remove the test data.
  • So essentially you are asking me to insert some mock data into Oracle and let API consume it and validate that API response with the expected data, right ?
    – brij
    Commented Oct 23, 2019 at 12:09
  • Yeah, then remove it afterwards.
    – Moro
    Commented Oct 23, 2019 at 12:37

I would also tend to your 2nd option. I wouldn't call it "parallel development". It is just an alternative way of how the logic is applied. A tester's way. If your queries will detect deviation from the observed output of a service, then it would be at least a signal to look into the issue. There could be several reasons of such possible difference. It might be a defect in your queries or in queries provided by devs. However it would still be the reason to investigate.

  • but in this case, if an issue arises first we need to investigate which query is correct and debugging time would increase two folds ?
    – brij
    Commented Oct 25, 2019 at 5:40
  • Yes, I do not see any problem with that. All the options have their risks. If you would have prepared data set then it would not guarantee that everything will be working on some other data. If you will have your test query (that is actually a common case in testing) then you will at least get the deep understanding of how the things work.
    – wec
    Commented Oct 25, 2019 at 9:19

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.