Should I use custom queries or re-use developer queries in your automation framework?

Question

Modifying the question based on Kate Paulk suggestion.

I had a very interesting discussion on Data Validation using Automation

I have summarized Pros and Cons of Reusing Developer Database Queries in Automation

Application Type - OLTP Application with some Reporting Queries

PROS

• If Application is stable, manually queries are verified. Developer DB queries can be used to verify data rendered is corrrect. Since we reuse developer query there is not exactly any data validation done by automation in this case.

• If a query is changed and automation results does not match then it means developer DB Query is changed

CONS

• If the underlying Developer query is incorrect and we reuse the same in automation. Then we are not catching those defects

• If the QA team member rewrites another set of queries for validating the data. There is a possibility this query also can be incorrect

Personal Opinion

• I have done web service automation. I have written custom queries to validate web service results

• I support reuse of developer queries if they are validated by manual testing

My Question - I would like to hear from your automation experience. Do you write custom queries / reuse developer queries in your automation framework.

Many thanks in advance for your opinion and sharing your experience

Answer 1

Siva,

In addition to user246's excellent list of suggestions, I'd at these factors to consider:

• You've probably already validated the developer queries: by the time you start automating you know they do what they should.
• The data you need to look at when testing is not necessarily the same data the developers need to use. That means you need different queries. For instance, a lot of the baseline checks I do are a simple SELECT ColumnA, ColumnB... FROM TableName, sometimes with a WHERE clause, sometimes not depending on what needsto be checked against.
• If your tests are data driven, you need your own queries to retrieve your data, particularly since it's likely to be stored and structured differently than the application database.
• If you're using the automated tests to validate developer queries, you're going to want to use them, and check the results they retrieve against some known baseline.

I personally wouldn't rule out either option - it's much more a matter of being aware of the limitations of each, and using what's appropriate to your circumstances.

Answer 2

Here are some points to consider regarding the first question.

All automation requires maintenance. Every line of test code you write will have to be maintained. That is a compelling reason to try to use someone else's code.

Automated tests have bugs too. In my experience, developers distrust automated tests because they are written by non-developers. Automated tests (and tester-written queries) have bugs too, and every time you detect a problem, you have to ask whether the bug is in the test or in the application.

The developer queries are not necessarily written with your testing requirements in mind. The application may never need to query the database the way your test wants to. Whether you (or someone else) should add your test-specific queries to your persistence layer depends upon your organization. If your organization prefers to leave test-specific features out of production code, you are presumably forced to write and maintain your own queries.

You cannot test everything. Your application depends not only on properly-written queries but also on all the rest of the code, on the run-time environment, the operating system, and many other things. You don't have time to test everything you depend on, so you have to trust something. If it is convenient to use developer's queries, and if you don't have time to test them, it is reasonable to evaluate the risk and decide to trust the developer's queries.

Do not reinvent the application. We as an industry like to construct software out of disjoint components that can be developed, tested, and debugged in isolation. Techniques like stubbing and mocking (and unit testing) facilitate this style of development. As a tester, your primary job is presumably to test the application as a whole. If you trust a component, and using it does not impede how you test, you should use it. If you do not trust it, you (or someone) should test it.

If you really want to be a developer, get a developer job. We need testers who have solid development skills. However, a tester's primary mission is to measure and improve quality. If your primary reason for writing test code (or queries or whatever) is to hone your developer skills, you should find a developer job.

Answer 3

We generally manually validate a query (usually a stored procedure, not just a random query) first and do any testing related to it. Once we have validated it, then we use some tools to compare results from old versions and new versions. The tools we use are from Redgate. It has a few tools to validate database schemas as well as validate results of queries. It has an API and we have created a wrapper that we can use to exercise our queries and compare them build over build.

I also found this to be a useful, though high level article about data validation. It's more about how to approach the testing than specific tests, but it does contain some specific tests and has a lot of useful information: http://msdn.microsoft.com/en-us/library/gg261774.aspx