Backend Testing: the Definitive Guide

With complex databases and large amounts of data, backend testing becomes a vital step towards high-quality software. While companies spend a lot of effort making sure everything works smoothly from the user point of view, they often forget that testing the backend is just as important.

Table of contents:

What is backend testing
How to perform database testing
Best database backend testing tools
Common issues

What is back end testing?

A typical web application has three layers: frontend (UI), 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.).

Back end testing, on the other hand, deals with all those elements that users can’t see.

Backend testing increases QA costs but provides numerous benefits:

Bugs discovered earlier when the cost of fixing them is at its lowest.
Complete control over the test coverage and test your application more thoroughly.
No deadlock and data loss.
Higher functional stability and robustness.
Better data quality and easier performance testing.

Backend API testing is incredibly important and forms the backbone of our test automation strategy. You can learn more about backend integration testing in our definitive guide to AP testing.

Read more: the list of top Node.js development companies.

How to perform database testing?

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

So in addition to testing your UI, you should give enough attention to database testing if your solution is complex and data-sensitive.

It ensures the data contained in your database and its structure satisfy the project’s requirements.

All DB tests can be grouped into three backend testing types:

Structural testing deals with the database inner structure (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 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.

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.
Columns and fields have the required length and naming convention.
Columns and fields in the database are mapped correctly with the frontend.
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 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.

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:

Atomicity: DB transactions should succeed as a whole. If some part of a transaction fails, the whole transaction should be considered failed. To buy sunglasses, you must fill all the necessary fields like credit card data and shipping info or get an error message.
Consistency: the database should only save 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.
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. Imagine there’s only 1 pair of sunglasses left in stock. If you and your friend start the buying process at the same time, the one who completes the order first would get the glasses. The other one wouldn’t be able to finish the purchase.
Durability: after a transaction is committed, the changes it brings to the database should persist in the system, unaffected by crashes or power outages.

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.

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 reducing their 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:

HammerDB – a popular open source benchmarking solution. It’s automated, multi-threaded, and supports dynamic scripting as well as a wide range of databases.
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.
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.

Best back end 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 with an intuitive UI for generating and managing large amounts of data with complex relationships.

Common issues

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

1.Insufficient testing approach

Inexperienced teams often limit themselves to back-box testing. This means they only test the database by interacting with the app’s GUI.

As a result, the team can’t accurately gauge their test coverage.

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

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. Even 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 dramatically 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 backend test automation tools without considering the hidden costs. While having excellent functionality, they often come short in the stability department.

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

4. Poor communication

Testers are often left in the dark about the latest changes to data and schema. When they finally learn about these changes, they need to hastily rewrite test 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 results in under-testing and poor quality.

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.

And if you need some help with testing your application, you can always rely on our QA experts. Just drop us a line.