Is it necessary to verify databases for functional automation testing?

Question

Currently, after every action that adds or changes data, we verify that that data was successfully added/changed in the database by querying the tables directly. I feel this is redundant because that data is already being pulled by the application and is displayed correctly (or incorrectly) in the application. Wouldn't any data inconsistencies expose themselves in the application itself?

This is the jist of the issue, but I will provide a simplified test case to expand on what I'm talking about:

1. Go to user "John Smith" profile and update his phone number from "(999) 999-9999" to "(666) 666-6666" and click the save button in the application
2. Verify application displays "Successfully changed user profile"
3. Go to preview profile and verify John Smith's profile reflects the changes
4. Verify table USER_PROFILE in DBusers database updated with changes

Is it necessary to check the table in the database if we've already verified the application is displaying the data correctly? The application is ALREADY pulling the data from the database table, so obviously if the application is displaying the data correctly then the data in the database is correct. Am I missing something?

Thanks for your input.

More info: To me, it seems that the only practical purpose for verifying a database directly (for our circumstances) would be to verify that any data replication is successful. We have web and console based apps that use separate DBMSs. The two we use are SQL Server and DB2. After specific data is added to SQL Server, a job runs that replicates it to DB2. They have different structures, so the tables / fields are all different between the two. I can understand verifying this replication has performed successfully.

Answer 1

Your application uses an API to interact with the database. It is possible to write your API in such a way that it presents correct results to the application and yet still uses the database in the wrong way.

For example, imagine a database with an EMPLOYEE table and a MANAGER table. The tables are alike -- e.g. each contains a first name, last name, company email address, and salary -- but the EMPLOYEE table is only for non-managers and the MANAGER table is only for managers.

Now imagine a database API with two classes, one per table. The programmer writes the employee code first. The manager code is almost the same, so the programmer makes a copy of the employee code and then edits it. He changes the name of the class, and he changes the method names. And then right before he's about to change the name of the table from EMPLOYEE to MANAGER, he's distracted: he decides to grab another cup of coffee, or he remembers he hasn't read the latest XKCD, or he notices a new insightful SQA comment from Sam Woods.

By the time the programmer gets back to work, he's forgotten about changing that table name, so he compiles the code and then writes some unit tests. The tests for both employee and manager do the same kinds of things. One test creates the object, writes it, and then reads it back. Another test tries to read an object that isn't in the database. Maybe there's an update test and a remove test or a test to write several objects and read them back. Those tests all pass, so the programmer declares victory. Of course, there's a bug in the code: manager records are written to the EMPLOYEE table.

There are different kinds of tests you could have used to find this particular problem. One way would have been to call the method that retrieves the entire contents of each table. But if the application doesn't need that method, it may not occur to you to add it just for test purposes. Another way would have been to query the database using something more trustworthy than your database API, i.e. raw SQL.

My example is contrived but I think it gets the point across. You can find a similar question (and some answers) at Is it OK to use the classes under test to initialize the database for the tests?.

Answer 2

Wouldn't any data inconsistencies expose themselves in the application itself?

Maybe, maybe not.

I've seen cases where applications lost some data after you've logged off. So while the UI looked fine, the database was actually incorrect after logoff.

In addition, are you sure that every single element in the database is being displayed in the UI somewhere? That would be very rare in my experience. Fields like "last updated date" are often logged within the database, but not exposed to users.

Additionally, how do you ensure that extra rows aren't incorrectly being created - rows that aren't exposed in the UI.

so obviously if the application is displaying the data correctly then the data in the database is correct.

If the UI element that captures and stored the data, and the UI element that displays the data, are both making the same mistake - then it is indeed possible for the database to be incorrect, but the UI to hide that fact. I've seen that happen occasionally. One I can recall was a data field that was stored incorrect, but the UI that transformed it made the same assumptions as the UI that accepted the data from the user and stored it. The result was that a report which was added later (and didn't make the same mistake as the UI) ended up not working correctly.

I've have seldom found UI-only verification to be sufficient. On the other hand, I do think that querying the database after every single action might be overkill. Perhaps you can chunk some actions together and only query the database then?

Answer 3

I don't want to repeat great answers other have provided, but I would like to share with one more lesson I learned about using database in tests.

Combining feedback you have from both database assertions and UI assertions, often in one test, can be very useful for test case design, test execution performance and defect root cause isolation:

• If you spot an error in database, checking whether it occurs also in UI can help you define a severity. Maybe if corrupted data are not displayed in main screens, then the problem is less severe?
• Exploring database data and finding something smelly or suspicious can help invent new end-to-end test scenario, that would confirm your guess
• Complex algorithms may log intermediary results in database, while in the UI you see only the final output. Having intermediary results in your test report will help you when investigating the root cause.
• In long, multi-step user scenarios the time between saving the data into database and displaying it back on UI might take time, e.g. in e-shopping scenario. Why wait until the final step, if you can shorten test execution time and fail it fast?

Answer 4

In addition to user246's excellent example, some other cases where you'd want to validate the database storage in your scenario would include:

• You have a bulk update/insert function where it's impractical to validate the results via the front end, such as importing new user records from a CSV file. While you can go in through the front end and check that each record you imported is present and correct in the GUI, it's a LOT faster to do a bulk database read and compare against the expected results.
• Your database storage uses dependent records such as storing all addresses in a separate table. Here you would need to check that for a delete, the dependent records were also removed, something that is not going to show up in a front end check.
• As Joe said, not all fields in the database are exposed to the front end. Calculated fields, timestamps and the like all need to be checked.
• Where the application uses the database to store transitional records, you may need to check the relevant tables after each step to ensure that the correct data is stored and cleared. The example I know best is purchasing items with a limited quantity. If the quantity available is 30, and someone adds 5 to their cart, that quantity 5 must be held and quantity available decreased by 5 until either purchased (in which case the 5 held is cleared and the quantity sold increases by 5) or the purchase is cancelled (in which case the 5 held is cleared, and the quantity available is increased by 5). Without those checks, problems with clearing available quantities may not become evident until a later purchase - particularly if the transitional records include dependent records.

Answer 5

I believe it is a very good idea read back from the database. The reason is that things sometimes (or often) are not quite as simple as they may look.

In the example above: what happens if the phone number is all zeroes? What happen if a user actually has a phone number like this +46-728-123456 (a mobile number in Sweden, not mine, hope no-one gets a phone call now). Or if you actually have numbers with characters, say (abc)def-ghijkl

Or if you enter a correct name like this: Håkon Hésteier (a faked name). How does the underlaying layers and the data base handling international characters - there are a lot of settings that may effect that. It is no way certain that these characters will be stored exactly as entered.

In my experience you need to define limits on all database fields. This is not really up to the testing phase to do, but testing can feedback that the design (requirements or what you call them) are insufficiently detailed. As a the tester should try to find these cases close to and outside the "limits" and include them in test harness.

One classical example is SQL injections where if you enter partial SQL statements to, say, a name field the database gets corrupted by the database not simply storing modified data, but actually interpreting the data as SQL statements.

One small example: I lately downloaded an app the supposedly should advise me on how to get better loans. You where supposed to enter current interest rate. The app did allow me to enter one loan with a rate of 2.12% rate, but could not accept another loan with a rate of 3.01. They had forgot to test the case whith the x.0x as loan rate.