Today’s complex software systems access heterogeneous data from a variety of backend databases. The intricate mix of client-server and Web-enabled database applications are extremely difficult to test productively. Testing at the data access layer is the point at which your application communicates with the database. Tests at this level are vital to improve not only your overall test strategy, but also your product’s quality. In this presentation you’ll find out what you need to know to test the SQL database engine, stored procedures, and data views, find out how to design effective automated tests that exercise the complete database layer of your applications. You’ll learn about the most common and vexing defects related to SQL databases and the best tools available to support your testing efforts.

A strong foundation in Structured Query Language is becoming increasingly necessary and even required by some companies in today’s data environments. Many test professionals are being asked to bridge the gap between database administrators and database developers by testing the integrity between the database application and it’s data stores. Understanding the primary database manipulation language – Structured Query Language – for Relational databases is key to being able to perform effectively in this arena.

Most applications are data-driven in some way in today’s environments. The complex systems we deal with have many different layers. Testing experience has found that we cannot effectively test the application without being aware of these layers and what issues and bugs they can introduce into the system. A data-based application has a data access layer which is the code written to manipulate and perform actions on the database. The databases and data stores used by the application present a multi-layered system in and of itself.

A given database may have to allow for more than one client application, for example, an application for internet user and those for intranet or client/server applications, perhaps also an administrative application. These additional client applications may use the database in unpredictable ways. Certainly an “advanced user” application or even a DBA going in behind the scenes could touch the data in unforeseen and potentially harmful ways. Hence the database component may need to be tested for uses beyond those of the current application and further it should be regularly checked for data integrity and consistency. This is particularly true if, for scalability reasons, the business rules are not kept on the database backend but in the database application front-end (presentation layer) and/or middle-tier layers.

With the increasing use of data on the internet and in every application, the need for skilled testers in databases is growing quickly. Stored procedures are increasingly used by experienced database developers to ease the complexity of calls to the database backend. These procedures contain critical tasks such as inserting customer records and sales. They need to be tested at several levels. Black box testing of the front-end application is important, but makes it difficult to isolate the problem. Testing at the backend can increase the robustness of the data.

Don’t the major test tool vendors cover this? Not that we’ve seen! They seem to have enough to do as it is providing the support for GUI testing and performance. Testing database objects can be done by pretty much all the tools in some limited fashion, but you don’t necessarily have all the flexibility you need in the IDE of these tools and therefore have to rely on coding using their macro language anyway. As much as possible, we recommend bypassing their programming macro language and writing your tests in SQL script code directly in your RDBMS or using a variety of relatively simple scripting languages – VBScript, PERL, Ruby, Javascript. Why, you ask again? Because they’re more reusable and portable in terms of your database, of course, and also because the skill set for using these languages will be more available in the personnel you’re able to hire, train, and retain. We can think of other reasons too, but those are pretty good, don’t you think?