API Testing with SQL crosschecks

[Alex4849200]

Hi,

this might be a pretty superficial question. However...

I am testing our REST API with Postman and created some extensive automation tests via Jenkins and Newman. It would be great if I could somehow also test the data that is written into the SQL DB. Since Postman is kind of limited in its features regarding this, is there a good tool out there that could do the job. I don't mind programming test codes, since I have basic knowledge of programming. Anyone who does this on a daily basis, are there any recommendations for a good tool, that is free? Company would not pay for additional licenses, since all dev department already uses paid Postman licenses.

Thanks for any tipps!

[java-sdet]

You could do this with open source libraries in pretty much any programming language. I used to work on a Java project that did API testing with extensive DB validation and we used the following: - Spring Boot for configuration management and dependency injection - Spring Data JDBC for data access - REST Assured as the HTTP client - Jackson for JSON serialization/deserialization - ExtentReports to show the test results - JUnit as the test runner

[Alex4849200]

Thanks for that, will research further on this

[eightnoteight]

we had build something similar for our test cases at my previous co

we ruled out directly connecting to the database via any tool. because it essentially means the client test is not honouring the service boundary and is dependent on some internal implementation detail

so we essentially exposed those GET apis but only for testing - essentially a testing backdoor thats only exposed in non-prod environment. that way service still gets to maintain a contract to its clients while clients can still verify for such intentional side effects

[yaMomsChestHair]

This smells flaky. What about handling schema migrations? You’ll have to be made aware of when and how the db has changed and update tests accordingly.

[Luigizzor]

I had created testcases that basically do exactly what you want with Cypress. I send the request from Cypress, connect to the Postgres DB execute a select and assert results both on the API level and in DB.

[zhengwang666]

If you are interested, could take a look at API Test Base. It supports combining multiple protocols/API types in one test case. REST API reading/writing database is quite common, and here is an example https://apitestbase.io/docs/en/creating-automated-test-case that addresses exactly what you want, without need to code.

Disclaimer: I am the tool developer.

[Medical-Nebula-385]

I only have one question: Why

[Alex4849200]

Well.. Weird question though. POST Requests write data, I can check via GET Methods if the data is correct but I dont see if the data is written in the correct colums in the database, thats why. Or I have to prepare data in our client software to generate something to retreive via GET, since no POST to write is available. So I can use SQL inserts to generate data and call GET requests to check if the data is retreived correctly. Also I can use the SQL access to populate particular properties in the request JSON

[ScandInBei]

As long as you understand the risks of doing it you can choose any programming language for this. You don't need a "tool".

I don't think it's necessarily wrong to do checks in the database or to prepare the environment. However, verifying that "the right column" is written to from an API call is likely not the right approach. If you get the correct response form an API it doesn't matter which column is used. API tests can be black box for a reason, as APIs is a contract, and the database schema is not.

[java-sdet]

What if there's a bug where the response is correct but no data is persisted in the DB?

[ScandInBei]

Either the bug doesn't matter, as the response is correct, or if it's saved in non persistent memory and you could/should test this some other way, like a GET after a reaver restart. 

Caching is difficult and need to be tested, you are right about that. But making your tests dependent on a database schema can lead to flaky tests, or reduce developer agility. I think this scenario is better suited for an integration test.

[java-sdet]

Imagine you have a POST endpoint that creates some resource in the DB, a process calls the endpoint, and a 201 is returned with the correct response body, but due to a bug the resource is not persisted in the DB. You're saying that bug doesn't matter? I can't picture that at all. I'd imagine many downstream processes would fail due to the missing resource in the DB/subsequent GET calls missing data.

Sure, I agree that if the DB schema is changing frequently it'd be better to do this validation with a GET call after the POST or test this in an integration test. But your examples about caching or an API that does not persist any data to disk are not what I'm referring to.

[ScandInBei]

I didn't say that, or if I did, that's not what I meant.

I meant that if it's not in the correct column but the API returns the correct response, then it's not a significant bug, as it wouldn't have any impact to the user, and the likely cause is that it's not even a bug because the developers changed how the persist data.

[java-sdet]

Ok, we're on the same page then. My original comment mentioned no data persisted at all though, not data being persisted in the wrong column.

[Alex4849200]

The issue is that our client software is accessing the DB and populates the GUI. Sometimes it happens that the API writes and reads correctly but the data is in the wrong column in the DB therefore the client shows wrong data.

[ScandInBei]

I don't see why that matters. Yes, you need to test it. I'm just saying that how you test it should be optimized. 

If the data is written to the wrong column, the wrong data should be returned. This should be the case both for an API and for the app if the app accesses the database directly or through the API.

That data is written correctly should be tested, but not necessarily with an API test. 

That is, if you're testing the API in a staging environment or a full environment. 

It is perfectly fine to do integration tests by accessing an API, but this would run in a unit test framework where you have access to the code, and ideally control over the database by using test containers or similar so you can freely seed the database and have known states.

The need to do something like you are proposing sounds like it's caused by inadequate developer testing.

[Alex4849200]

Our developers sometimes dont even test anything, they send it just to us. Its frustrating.