Born in the early eighties, databases can be considered IT dinosaurs. But unlike dinosaurs, databases are far from dying out. Today over 90% of businesses having only 10 employees use CRM systems and other business solutions relying on databases. However, you need to see the other side of the coin: massive losses caused by faulty databases. So how to prevent them? The answer is in thorough database testing. But how to push quality database testing into an Agile project? Let’s dive in to learn more.

Database testing: Why bother?

That’s quite a logical question. You might think, “We thoroughly check the application GUI and everything works as clockwork. Database? Yeah, we’ve tested the back end and the product has worked well since then. No need to waste time”. Watch out! In fact, poor data quality annually accounts for 20% of losses ($611 billion) in U.S. companies. Database testing can detect faulty data and its faulty rendering, preventing these cosmic losses. But how to fit this complex effort in an Agile project? Skeptics may say it’s impossible, but we’ll try to bring them over.

Database testing: What to test?

Databases are complex software systems, so a tester may feel overwhelmed by the overall scope of work in front of them. Do not fear! Testing databases runs around three focal points: data flows, business logic and database performance. These focal points, in their turn, can be grouped under the two well-known types of testing:

• Functional testing for data flows and business logic.
• Non-functional testing for database performance.

We consider the specifics of each testing type in database testing below.

Functional testing: Black or white-box?

Database functional testing provides for two implementations: black-box and white-box testing. Following a black-box testing approach, testing engineers act as real system users. This is a good way to test data processing and some business logic. Black-box testing mainly requires the knowledge of the application and basic manual testing skills.  Nevertheless, it has a serious drawback: with even quality functional tests, the team never know how much of the app they’ve actually tested. This is where white-box testing can help.

White-box testing requires good knowledge of Structured Query Language (SQL). Using SQL, testing engineers write queries to the database to compare the expected output result with the actual one.

So what type of testing to apply? To have a well-tested database, you’ll need to use both, as the two types of testing complement each other.

Types of testing now clear, we turn to the actual testing process.

Focus 1: Data flows

Correct data flows ensure seamless work of the UI and the database and hence, positive user experience. The key areas for data flow testing are:

• CRUD operations (create-retrieve-update-delete). This is the simplest, and yet a very important point for testing. The acronym describes the transactions between the UI and the backend performed by the user. The best way to test CRUD is to mock the user’s behavior and check if the actual results match the expected ones. Interestingly, this set of possible user actions remains the same regardless of the application complexity. While testing CRUD validity, the team will also want to check data mapping. Correct data mapping ensures that the relevant tables and records are evoked and/or updated when a user performs CRUD transactions.
• ACID properties of transactions. This acronym describes a set of requirements to database transactions. These requirements include:
  • Atomicity – aka “all or nothing” rule, ensuring that a transaction either fully happens, or doesn’t happen at all.
  • Consistency – ensuring that the database stays valid regardless of the pass or fail state of the transaction.
  • Isolation – ensuring that different transactions don’t influence one another when they are performed one by one or at once.
  • Durability – ensuring that once a transaction passes, its result is stable regardless of the external conditions (e.g. connection or power outage).

These properties assure reliable information processing in the database.

As for testing ACID, the most productive way is to follow black-box testing. Luckily, the ACID properties determine the details you need to write the relevant test cases, both positive and negative. For example:

Consistency, ensuring that the database stays valid regardless of the pass or fail state of the transaction.

Data integrity This component features the changes done to the data and their processing in the system. There are four basic types of data integrity:

• • Row integrity requires that all rows in the table have a unique ID.
  • Column integrity requires that all columns in the table follow the same format and definitions.
  • Referential integrity creates relationships between tables.
  • User-defined integrity is present in complex applications and relies on customized code logic (triggers, stored procedures, functions, programming languages other than SQL.

The main purpose of testing data integrity is to make sure that the user’s actions hold true for the same data throughout the whole system and that the database prevents changes. So, test cases for data integrity should involve major “incorrect” combinations: negative test cases that contradict business logic, wrong data input, ignoring input rules for a particular row/column, deleting data referenced in another table and more. In all these cases the database under test should report errors and prevent the required changes.

For verifying data flows, communication with the developers can be of great help. The testing team will be able to better understand data flows, get ideas about possible reasons for failures and plan testing activities with special attention to potentially weak points.

Focus 2: Business logic

Business logic determines the rules for creating, storing, changing and displaying data and describes how business objects should interact. And all that with the help of code.

Both white-box and black-box efforts help to test business logic. White box testing is run when the backend alone is not yet ready. It concentrates on the correct operation of triggers, relational constraints and stored procedures using test SQL queries. White-box testing helps detect and address possible code issues rapidly.

When white-box testing is successfully complete, the testing team can proceed with black-box testing to check how the business logic verified during white-box testing work in the frontend. For this matter, regression testing suits best.

Database regression testing

The function of database regression testing is making sure that recent database changes haven’t altered or broken the verified functionality. Just note that database changes don’t occur often, and it’s enough to run regression testing once in six-twelve months. Regression testing for databases usually follows good Agile testing practices to make this effort less time-consuming.

Non-functional testing: Database performance

Database performance testing implies stress and load testing. Load testing checks the amount of user queries the database can process at a time. A key performance indicator (KPI) here is the time a database needs to respond to user queries (the less, the better). Stress testing means loading the database to the point it breaks to see how many users can access the database at the same time.

To test database performance, you should choose between manual and automated testing. To make the right choice, you should consider the number of users of the database under test. While it may be possible to test the performance of a CRM system for a small business manually, certain web applications may number hundreds of users, which renders manual testing unfeasible. Luckily, there are a number of tools for database performance testing. However, the majority of them, including open-source tools, may turn pricey. According to Forbes, the lack of understanding of the actual tool prices resulted in severe under-budgeting for DevOps teams in 2017.

Summing up

At first glance, thorough database testing in Agile projects seems like pushing a mighty bull into a tiny china shop. However, with due planning, the effort is justified.

First of all, you can break critical database testing points into the two well-known testing types– functional and non-functional testing. Functional database testing covers data flows and business logic. Non-functional database testing is about database performance. To make testing efficient, you should take into account the following database testing specifics:

• For functional testing: the need to run both white-box and black-box testing to assure full test coverage and positive user experience.
• For non-functional testing: the need to choose between manual and automated testing that depends on the prospective user audience (small, medium or large). In the case of automated testing, you should mind additional costs induced by the use of automated testing tools to prevent under-budgeting.

Good understanding of the scope of testing and the related risks helps enable thorough and efficient testing within the Agile project time and budget limits.

Other popular articles:

• What is black-box, Specification-based, also known as behavioral testing techniques?
• What are the categories of test design techniques?
• What is white-box or Structure-based or structural testing techniques?
• What is Acceptance Test-Driven Development in Agile Methodology?
• What is Volume testing in software testing?