It can reveal performance issues that would otherwise go unnoticed until your app is released, improve the quality of your data, boost the application’s stability and give you full control over the test coverage.

Let’s dive deeper into backend testing including its benefits, types, and the common mistakes that can ruin your Quality Assurance efforts:

Chapter 1: Understand what backend testing is

Chapter 2: Perform database testing

2.1 Structural database testing

2.2 Functional database testing

2.3 Non-functional database testing

Chapter 3: Find the best database testing tools

Chapter 4: Deal with common issues


Chapter 1: Understand What Backend Testing Is

A typical web application has three layers: frontend, business logic, and a database.

Testing the frontend involves validating those parts of the application that are visible to the end-users (e.g. forms, menus, navigations, etc.). It’s also known as GUI testing.

Backend testing, on the other hand, deals with all those elements that users can’t see.  This means validating the application’s database and API that represent its business logic.

Application layers for backend testing

I have to mention, that backend testing increases QA costs.

When you utilize backend testing, you:

  1. Test database and API early in the development and discover many bugs when the cost of fixing them is at its lowest.
  2. Have complete control over the test coverage and therefore test your application more thoroughly.
  3. Avoid such problems as deadlock and data loss.
  4. Improve the system’s functional stability and robustness.
  5. Improve the quality of data and reveal performance issues that would get unnoticed until late in production.

API testing is a huge theme that requires a separate detailed article.

A backend database represents all the data that users input via the front-end. So, in addition to testing UI, you should give enough attention to database testing if your software solution is complex and data-sensitive.

Keep reading to learn how to do it…

Chapter 2: Perform Database Testing

The data that users input via the front-end is stored in a back-end database.

DB testing aims to ensure that the data contained in the database and its structure satisfy the project’s requirements.

DB testing can be grouped into three categories:

  • Structural testing deals with the inner structure of databases (i.e. metadata). It involves validation of Tables and all the other database objects that aren’t directly accessible by users, like Columns, Keys, Indexes, Schema, Triggers, Functions, etc. It can also involve testing your DB servers.
  • Functional testing focuses on the way data is mapped from the frontend to the database. (e.g. whether user clicking a button correctly affects the related tables/columns).
  • Non-functional testing checks how the database performs under the expected loads and extreme levels of stress. Security tests also belong here.

2.1 Structural database testing

A typical application has data moving back and forwards between the UI and database. Therefore, it’s important to ensure that data is mapped correctly between the frontend and the backend.

Schema testing

A schema represents the internal structure of the app database. It visualizes how the various objects like tables, columns, and fields, are organized and related in the database.

The main task for QA Engineer here is to validate that schemata are mapped correctly between the frontend and the database.

Schemata are tested using several methods:

  • An SQL Query DESC <table name> for schema validation.
  • Regular expressions for individual field names/values validation
  • SchemaCrawler and similar tools.

Tables and column validation

Relational databases organize data in tables and columns. While testing them, one mainly has to validate that:

  • DB fields support user inputs as outlined in the requirements.
  • The columns and fields have the required length and naming convention.
  • The columns and fields in the database are mapped correctly with the frontend.
  • The columns have the data types and field lengths that are compatible with the frontend, etc.

Testing the keys and indexes

Indexes are used to quickly locate a piece of data in a DB. Primary and foreign keys are used to uniquely identify a DB row. When testing keys and indexes, validate that:

  • All required constraints were created for the Primary and Foreign Keys.
  • All foreign key references are valid.
  • All indexes and keys observe the specified naming convention.
  • All fields and indexes have the required length and size, etc.

Testing the stored procedures

You can use the application’s UI to run the stored procedures and validate the results (black-box testing). Or you can use stubs (fake data) to execute the stored procedures in the backend (white-box testing).

While testing stored procedures, one has to mainly validate that:

  • The stored procedure modules are properly integrated.
  • The developers have followed the required error/exception handling and coding conventions.
  • They’ve correctly applied the trim commands when the data is retrieved from the required tables.
  • They’ve covered all the loops and conditions with input data.
  • Manually running a Stored Procedure results in the required outcome for the users, correctly updates the table fields, and fires all the required triggers.

You can validate the stored procedures using tools like SP Test or LINQ.

Validating the triggers

Triggers can be tested both during the white box and black box testing. In white box testing, you can create, update, or delete the simulated data invoking the triggers before integrating the DB with your frontend. After the integration, you can invoke triggers via the application’s UI.

When testing triggers, it’s important to validate that:

  • Developers observed the necessary coding conventions while writing the triggers.
  • Triggers have correct names.
  • Executing a trigger updates the data as required.
  • The triggers fired by DML statements have met the required conditions.
  • The Update/Insert/Delete triggers function correctly.
  • The data is rolled back upon a transaction failure, etc.

Validating the DB server

When testing the server validate that:

  • The server’s configurations meet the business requirements.
  • Authorized users can only perform the actions they were authorized to do.
  • The server supports the required maximum number of transactions, etc.

2.2 Functional database testing

When a user interacts with an application in a way that changes the data stored in a DB, we have a transaction. Creating an account in an online shop is a transaction and so does ordering a pair of sunglasses.

All database transactions should observe four properties:

ACID model for functional testing

  1. Atomicity: database transactions should succeed as a whole. If some part of a transaction fails, the whole transaction should be considered failed. To buy your sunglasses, you must fill all the necessary fields like credit card data and shipping info or get an error message.
  2. Consistency: The database should only save the valid transactions that don’t violate the rules for valid data. Your shipping info must be a real city and your credit card number must have 16 digits.
  3. Isolation: Transactions shouldn’t affect each other. If several transactions are performed simultaneously, the result should be the same as if they were run consequently. So if only 1 pair of sunglasses are left in stock, and you and your friend started the buying process, the one who completes the order first would get the glasses, the other one wouldn’t be able to finish the purchase.
  4. Durability: After a transaction is committed, the changes it brings to the database should persist in the system unaffected by crashes or power outages and such.

Integrity and consistency of data

After you Create, Read, Update, or Delete data in a database, the frontend should show the updated data on all the screens. There shouldn’t be cases where you can see updated data on some screens while others show old data.

The test cases should be written in a way to check all the locations where the data can be displayed and make sure it’s the same everywhere. When testing data integrity and consistency validate that:

  • The data is organized in a logical way.
  • The data in the tables is correct.
  • There is no unnecessary data.
  • The data is stored according to the requirements and correctly updates when users interact with the frontend.
  • Transactions have the required outcome.
  • That data is correctly committed after a successful transaction and rolled back in case of a failed transaction, etc.

2.3 Non-functional database testing

This group of testing activities includes Performance Testing (load/stress), Security testing, Usability Testing, etc.

Performance testing

Load testing can help you identify the performance-related risks and discover how your system would behave under the expected load. It can also help you identify the minimum system requirements for your servers, which can reduce server costs.

Additionally, stress tests allow you to find the system’s breakdown points by overloading the application until it fails.

So what kind of things should you include in your performance tests?

  • Most used transactions. If inefficient, they can affect the performance of all other transactions.
  • Transactions involved in the application’s core features. Their performance has the biggest influence on user experience.
  • At least one non-editing transaction + an editable transaction.
  • Transactions from multiple remote users to test response times.

Some of the tools you can use for performance testing:

  1. HammerDB – a popular open source benchmarking solution. It’s automated, multi-threaded, and supports dynamic scripting as well as a wide range of databases.
  2. Apache JMeter – another great open-source tool with a modular structure and a user-friendly GUI. It can be integrated with your CI/build tools and become a part of your development lifecycle.
  3. Oracle SLOB – a free toolkit that allows you to generate input/output in Oracle DB and devise various custom performance tests.

Validating user login and security

When testing your security, check whether:

  • The system keeps the user from advancing if he/she entered any combination of correct/incorrect usernames and passwords.
  • The system has user roles with different privileges.
  • All users have the required privileges.
  • Users are only permitted to perform authorized actions.
  • Data is protected from unauthorized users.

And finally, ensure that the sensitive data such as, passwords, or medical records, is encrypted. Storing such data as a plain text is a recipe for disaster.

Chapter 3: Find The Best Database Testing Tools

TOAD – one of the most popular DB management and testing tools. It allows you to connect and send queries to your DB. The tool has a free and a paid version, both supporting a wide variety of databases and platforms.

phpMyAdmin – a free open-source application for managing and testing MySQL/MariaDB. It comes with a user-friendly GUI and has extensive documentation.

Data Factory – a free tool for generating and managing large amounts of data with complex relationships. It has an intuitive UI.

Chapter 4: Deal With Common Issues

While seeking to reduce the costs of DB testing, many companies make the same painful mistakes:

1.Insufficient testing approach

DB testing is a complex task that requires careful planning. But when your team lacks experience, the process can take a lot of time and have insufficient coverage.

Inexperienced teams often limit themselves to back-box testing. This means they only test the database by interacting with the app’s GUI. As the result, the team can’t accurately gauge the coverage of their tests.

White-box testing can eliminate this problem. It requires testers to have a good knowledge of SQL, so white-box testing can be rather expensive. But SQL experience is essential when validating triggers, functions, procedures, and other vital DB properties.

So hiring experienced testers can actually save you money.

2. Relying on mock data

In the effort to save time while testing a DB, testers often populate it with made up data. Doing so, you can miss certain bugs like having the wrong number of symbols permitted. Fixing them late in production is far costlier.

To detect such bugs early, you can use realistic datasets. Without including real names, you can have things like ZIP codes and phone numbers specific to your targeted area.

3. Overlooking the hidden costs of automation

Test automation can reduce QA costs and increase the test coverage. But without careful planning, you can spend more in the long run.

Many teams go for open-source test automation tools without considering the hidden costs. While free software can have excellent functionality, it often comes short in the stability department.

Depending on your situation, going for a commercial solution can actually be cheaper.

So remember to conduct a thorough cost/benefit analysis when choosing your test automation tools.

4. Poor communication

Communication between team members can be crucial to the success of DB testing. Testers are often left in the dark about the latest changes to data and schema. And when they finally learn about these changes, testers need to hastily rewrite their cases.

With agile development, there may be additional issues, like making changes to the code on the fly without testing or submitting tasks to the testers at the last moment.

This result is in under-testing and poor quality.

Communication issues are especially costly when migrating databases. This task requires a deep understanding of DB specifications which is only possible when all the team members communicate properly.

That’s why it’s important to pay enough attention to the in-team communication, especially in Agile projects.

Now It’s Your Turn

So those are the basics you need to know for getting the most out of Backend Testing.

The next step towards a flawless user experience is testing the API that represent the business logic of your application.

Now I’d like to hear from you:

Do you think your application requires Backend Testing?

Will the benefits of DB testing outweigh the increased costs? Or maybe you decided to skip it for now.

Either way, share your thoughts in the comments right now.

  • 12