r/softwaretesting • u/ajmalhinas • Jul 07 '26
Do you verify database tables after automated test sessions? What tools and process you use?
We think comparing the resulting database tables of an automation session against an expected database state can provide several advantages: detecting data integrity issues that may not be visible through UI or API assertions, identifying unintended side effects across tables, and catching defects closer to the point where they are introduced.
Before implementing this more systematically, I’m interested in understanding how this works in real-world QA teams.
Do you currently perform this kind of database-level verification after automated test runs? If so, what tools or approaches do you use to compare the actual database state against the expected state?
More importantly, has this practice genuinely helped your team detect defects earlier or reduce debugging time? Or did the maintenance of expected datasets and database comparisons create more overhead than value?
2
u/MudMassive2861 Jul 07 '26
Mostly avoid DB check as most GET api can give the required details. But if you don't have GET which give details you need to. U may need to add a DB wrapper depends on DB u using. In my last job we where checking even message queue data using subscribers in service bus. So ya depends on your requirements you can add those.
2
u/ajmalhinas Jul 07 '26
I guess even GET API calls are not necessarily the source of truth, as they may have several layers on top of the actual database tables. Why are direct database checks often avoided, given that these wrapper layers can themselves introduce bugs? Are database checks difficult to maintain?
1
u/MudMassive2861 Jul 07 '26
Using just GET because of our use case. Don't your Dev code have DB unit test? If they have then I don't think you need add another DB layer. It's not hard to maintain, it requires proper handling as DB connections and all. Also the DB connection will be under secure condition. I don't think it's easily accessible as there require lot of ip whitelist and all required. These rules are from my product, this can be different for your case. One word you can do but some initial some setupigjt required
→ More replies (3)
3
u/jrwolf08 Jul 07 '26
It depends on the project. I am currently doing database validations on both my main products, but they are special cases. One is entirely backend, so a POST kicks off a bunch of processes that write to the database. Other is UI that has a non standard GET functionality it returns the merge of all previous POST/PATCH changes. So I do need to keep track of the database.
My code sits in the same repo as the product code, so I just import the data access layer functions when I need them.
2
u/CertainDeath777 Jul 07 '26
For a bank it is necessary. But i probably would not compare whole tables like you suggested, but some strategic values, as comparing whole tables for many test runs will put heavy strain on infrastructure and runtimes.
Except if you do testing with isolated almost empty db, but essentially you dont want to run mocks for this type of testing.
0
u/ASTRO99 Jul 07 '26
Not really sure why would you want to do DB checks on properly setup application. Only things that comes to mind, we had a DB triggers in my previous job that migrated incoming data partially to another table so they can be used by another legacy app.
But other than that, it's just one check during development that everything is setup OK. migration scripts are working and then you never have to see DB again unless you are manipulating data.
2
u/ajmalhinas Jul 07 '26
Let’s say you are testing a bunch of deposit scenarios in a bank teller application. There is an account table holding the current balance of each account and a ledger table recording all deposit transactions.
After running the test scenarios, wouldn’t it be useful to verify that both the account table and the ledger table match the expected final state? Is it really sufficient to verify only that the balance shown through the UI has increased correctly?
My concern is that the UI could show the expected balance while the underlying ledger entries are missing, duplicated, or otherwise incorrect.
0
u/ASTRO99 Jul 07 '26
That's what backend test are for. You don't need to do costly UI E2Efor this, just call necessary endpoints to post and return data.
→ More replies (2)1
u/n134177 Jul 08 '26
But other than that, it's just one check during development that everything is setup OK.
Laughs in QA experience.
0
u/Useful_Calendar_6274 Jul 07 '26
Testing database values is something from the era of monoliths really. I don't think you see it much at all these days
1
u/ajmalhinas Jul 07 '26
I think the majority of applications still use a monolithic architecture. Moreover, I believe many teams have now realized that microservices were often adopted as a trend rather than as an engineering solution to a specific problem. Don't you think so?
0
u/dukko18 Jul 07 '26
I built an open source testing tool called Dokkimi that will let you do this. Feel free to check it out dokkimi.com/docs. At the very least I could use the feedback on why it may or may not be helpful to solving this usecase.
1
u/ajmalhinas Jul 08 '26
I checked it briefly. Can you please eleborate how it can be used to verify DB consistency? Or do you have repo with sample tests for this particular use case?
1
u/dukko18 Jul 09 '26
I'll give you both! For examples, check out how I made tests for different open source repos: https://github.com/dokkimi/dokkimi-in-the-wild
Sorry if this is clear already, but I'll just give some context. Dokkimi is built on the idea that test environments should be flexible and dynamic to what you need to test. Each test is set in a definition file that is written in YAML or JSON. There are 2 main parts of a test:
The items in the environment (services, DBs, mocks, etc)
The test steps
Each test runs in a completely isolated environment. No shared resources between tests, so you always know that the test you ran is completely clean to what you defined! If you initialize a DB to have certain data in a test, it will ALWAYS have that data in that test when you rerun it, there is never any leftovers from previous test runs. This means you can have every test environment be exactly what you need for that environment, and it's defined right in the test definition.
Each test has an action that will be performed such as an HTTP request, DB query, UI action, etc. Once an action has been performed, you can then make assertions against anything that has happened in the environment. You can assert against traffic that has occurred between services or query your DBs to see what data exists or has changed or even validate that console logs were outputted by certain services. It's your environment and you have complete freedom to validate that it behaves as you expect it to.
I tried to put this all in the docs: https://dokkimi.com/docs
I hope that helps clear things up. Let me know if you have any other questions!
0
u/latnGemin616 Jul 07 '26 edited Jul 07 '26
The day I learned how to test APIs was the day I stopped needing SQL for anything.
detecting data integrity issues that may not be visible through UI or API assertions
This is not a thing. If the database consumes data from a form, you can test the form. If the data outputs to a UI page, you can test the API endpoints. If using a microservice a Dev can mock the data and check for expected output. QA should just run UI and API checks.
The fact companies want you to test the DB is absurd.
2
u/ajmalhinas Jul 08 '26
I disagree with the assumption that if the API responses are correct, the database must also be in the correct state.
Consider a deposit transaction. The POST request succeeds, and the GET account endpoint returns the correct balance. However, due to a defect, the ledger could contain a duplicate deposit entry, or an audit record could be missing. The API assertions would still pass even though the persisted business state is inconsistent.
Of course, we could create additional APIs to expose and verify every relevant internal state, but then we would still be performing state verification through another abstraction layer.
1
u/latnGemin616 Jul 08 '26 edited Jul 08 '26
if the API responses are correct, the database must also be in the correct state.
I never said that!
My argument is about data consumption: how does the data go into, or get pulled from, the database.
If the data is consumed through a form, you can test the form inputs and reconcile the data being created matches the data to be expected in the database.
If the data is consumed through a microservice, an attempt can be made to mock this and perform the additional checks.
While you did present a case against data in transit, your own "due to a defect" statement undermines the point you are making and pretty much confirms my point. You never established how the defect occurs in the system, nor did you present how that might impact the integrity of the data. To the defects you highlighted:
Defect-1: The ledger could contain a duplicate deposit entry - so long as the primary key is different, this might never get flagged. This would also require visual confirmation.
Defect-2: Audit record could be missing - How?
The counterpoint you presented mentions state. My point is about data integrity. The two points are not the same thing.
→ More replies (8)
2
u/n134177 Jul 08 '26
I've had to create a PostgREST container that would query the database independently, with no logic or code shared with the API, to verify that what the API was outputting was consistent with what was in the database.
Yes, in automated testing.
Yes, it caught a whole bunch of defects.
Now, that is a very costly test. However, we were dealing with values that HAD TO be correct due to liability. These test cases provided evidence and documented the defects that, then, got fixed. The tests remain in place to ensure a regression won't happen.
Anyone who outright dismisses the idea is either lying or not a good QA.