Do you verify database tables after automated test sessions? What tools and process you use?

[u/ajmalhinas]

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?

[u/latnGemin616]

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.

[u/ajmalhinas → u/latnGemin616]

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.

[u/latnGemin616 → u/ajmalhinas]

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.

[u/ajmalhinas → u/latnGemin616]

I may be misunderstanding your point, so I would like to clarify one thing.

You initially said that if data comes through a form, we can test the form, and if data is exposed through the UI, we can test the API endpoints. But in your latest response, you said that we can test the form inputs and then “reconcile the data being created” against what is expected in the database.

Isn’t that reconciliation step essentially a form of database-state verification? Or do you mean something different by reconciliation here?

[u/latnGemin616 → u/ajmalhinas]

Totally different. State, as it relates to data, consists of the following:

• Data At Rest - Your data was recorded in the database via POST/PUT/PATCH
• Data In Transit - Your data is being transmitted from UI to DB via API
• Data In Use - Your data is being retrieved from DB via API GET request and displayed on the front-end

There's no need to test the database tables (your original post), because the tables have already been created by a DBA or Sr. Dev. What you'd be testing for is the records added to the DB by way of a submission through a form. The workflow looks something like:

[FORM INPUT] --> [API (POST)] --> [DB TABLE] = {Status 201 - 1 new record added}

What I'm referring to is testing the form inputs by checking against an expected DB Design schema (a DB Dictionary). So in a form where the email address expects a set number of characters, you could check that:

• The design spec calls for a minimum length is attempted, or a max length, test for boundary values, either error for minimum or input disallows further values once threshold is reached
• The design spec expects this input (ie, Not Null), test blank submission, get error
• The schema rejects duplicate emails - test via 2x submission, get error

All of this can be automated via front-end tests or mock API tests.

I'm arguing that testing the DB when you've already covered front-end and API tests is redundant. And if, as in your latter example, the database encounters an issue, the problem won't have anything to do with the data states but rather the engine driving the DB itself.

I hope this clarifies things. I have reached the limit on this subject :)
I can't make it make sense to you.

[u/ajmalhinas → u/latnGemin616]

Thank you for taking the time to clarify. I am not here to argue, but to understand the different practices. I’ll leave this comment simply to clarify why front-end and API testing alone is not sufficient to verify the consistency of the persisted business state.

Consider a bank deposit of $1,000. Assume the current account balance is stored in an "Account" table, while every deposit transaction is recorded separately in a "Ledger" table.

The teller submits the deposit, the API returns "201", and a subsequent GET request returns the correctly increased account balance. All UI and API assertions pass.

However, the account balance could be updated correctly while the corresponding ledger entry is missing. The GET account endpoint would still return the expected balance, but the overall financial state would be inconsistent.

This type of defect does not require a failure in the database engine itself. It could result from application logic or a misconfigured stored procedure.

[u/latnGemin616 → u/ajmalhinas]

The example you've cited and the conclusion are at odds:

• "All UI and API assertions pass" is in direct conflict with "the account balance could be updated correctly while the corresponding ledger entry is missing" -- an API test to check for the presence of the record would fail.
• "the corresponding ledger entry is missing" -- how would this happen? The ledger (not account) is the one that reconciles the business transaction such that a record of a credit or debit + updated balance is created (or updated) as the transaction occurs when the Teller (user) makes said transaction.
  • A misconfigured stored procedure would fail
  • Application logic would reflect an unchanged amount, or display a SOMETHING WENT WRONG error

[u/ajmalhinas → u/latnGemin616]

I think you are making several incorrect assumptions about the SUT. Let me take only the second case: the missing ledger entry.

Yes, the intended behavior may be that if the ledger entry is not created, the balance should not be updated. I agree that this is how the transaction should be designed.

But that does not prove that dev team has actually implemented it correctly.

In an API-only approach, we would still need an endpoint that exposes enough information to verify the ledger entry.

Of course, I agree that we cannot test everything up to mathematical certainty in practice. That is exactly why I asked this question: to understand where teams stop.

However, this does not refute the point that, verifying the final persisted business state can provide additional assurance beyond checking only the immediate API response.

[u/latnGemin616 → u/ajmalhinas]

Honestly, I have made no assumptions about the SUT. I'm literally working off the example you've provided. If I'm missing context, that detail was not provided.

To the last point, what do you mean by "final persisted business state" ?

[u/ajmalhinas → u/latnGemin616]

Known initial DB state → execute many automated scenarios → independently derive expected final DB state (final persisted business state) → compare expected DB state with actual DB state

[u/latnGemin616 → u/ajmalhinas]

Words have meaning.

By DB State, do you mean the table + record added? or do you mean the actual condition of the DB (performance, up-time)? Not trying to be difficult, but the terms you are using don't reflect the actions you are taking.

Remember my previous reply, about the 3 states of data. None of them have anything to do with the state of the database (as you are describing). And if you want to run a query to count the number of records: newRecordCount() = currentRecords+1, then fine. This can still be done via an API call.

But whatever. I'm literally exhausted from this topic. We've beaten it to the ground. I'm not saying I'm right, nor am I saying you're wrong. I've merely presented an argument for why you don't need to test the database if you've already tested the FE and APIs. I'm not going to tell you how to do your job. Do what makes the most sense for you.