Is it necessary to verify databases for functional automation testing?
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:
- 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
- Verify application displays "Successfully changed user profile"
- Go to preview profile and verify John Smith's profile reflects the changes
- 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.
carriann last edited by user
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?.