Should a Tester Have Knowledge of the Database?

Most greenfield software projects start with a database design, usually followed by an API implementation that often happens in parallel with the user interface (UI) implementation. Before the UI is ready to wire up to the API with real data, the API needs integration testing between it and the database layer. This is where knowledge of SQL and databases can help a QA tester take their testing to the next level.

Advatages of Database Access

Below are some examples of the kinds of testing capabilities that are unlocked when a QA also has knowledge of the data store:

  • Developers have a nasty habit of implementing all the GET endpoints first. In this case, it can be convenient to use the database directly to create data when the creation API endpoints are not ready yet. For example, you could pre-populate the tables if data creation APIs are not implemented, and this will allow you to start testing the GET endpoints that are implemented.
  • Testing things that cannot be done through the UI (or even though the API). For example, some buggy code has accidentally deleted a user and there is no way to restore it through the UI or APIs. If your database uses soft delete, you can easily do this directly in the database.
  • The ability to compare real data in the database with the data returned by the API. Because data retrieval is an additional layer and the query in the code (store procedures) can be implemented incorrectly. One example of this is an API that does time zone conversions incorrectly, and you can only see this if you can see the data in the database to confirm it.
Comparing data returned from an API to the data stored in the database
  • The ability to quickly create a large amount of test data. For example, you need to test pagination or infinite scrolling on a UI with a default pageSize of 25 items. With a little bit of SQL knowledge, you will be able to semi-automate this task and speed up testing. You can insert the test data directly into the database with a minimum number of 26 items to test the pagination.
  • The ability to delete and edit data directly in the database. For example, if you are testing filtering.
  • Checking for items directly in the database which were created, changed, or deleted via the UI or APIs. Sometimes the API often uses fewer fields than are stored in the relevant tables. In this case it makes sense to check what default values the record was created with, and see if any fields are missing in the mapping.
  • Working with deleted items. When testing by the black-box method, it may look like the delete has worked – you call DELETE /api/Users/1 and try to GET /api/Users/1 and it returns nothing. However, with access to the database, you will have additional information about what has actually happened to the entity that you deleted.

The following examples relate more to the database testing and the ability to keep the data clean, but also allow testers to understand how things work at the back-end and improve test case coverage:

  • The record can be soft or hard deleted (soft delete uses a flag e.g. IsDeleted, IsActive to mark the data as deleted without erasing the data itself from the DB).
  • The database can have cascading deletes enabled for foreign key relationships. This makes sense to use when a child entity requires a parent entity and is no longer valid if the parent is deleted. If this is not the case, other options for handling deletes across foreign keys should be discussed.
  • Assign NULL values to the foreign keys. Breaking the link between the dependent entities is only suitable for optional references where the database column to which the foreign key is mapped can allow NULL values. For example, if more than one table is involved – this means that the record to be deleted has dependencies or in use. So it makes sense to try to remove the entity with different variations.
  • Analyzing the data directly in the database can help QA find the true cause of the bug. Sometimes there are non-obvious bugs (especially if it is a custom bug) for which it is difficult to trace the dependency through GUI/API checking. In this case tables store more information to investigate the bug.
  • If you are developing an application using a legacy DB, it is useful to analyze the existing data in the tables that may raise some questions (e.g. new application is going to have some data validation that is different from what is currently stored in DB)
  • Knowing data types and length/precision of fields.
  • Ability to directly access the database for automated testing.
  • Being able to read a database schema and understanding the relationship between tables, keys, and indexes can provide more knowledge when creating test cases.

Conclusion

As you can see, it can be very helpful for a QA tester to have access to the database for the application they are testing. It requires the QA engineer to learn some things they may not know about database design and SQL queries, but when in the hands of a skilled QA engineer, this knowledge unlocks many capabilities to better test the application.

Related Blog Posts

We hope you’ve found this to be helpful and are walking away with some new, useful insights. If you want to learn more, here are a couple of related articles that others also usually find to be interesting:

Our Gear Is Packed and We're Excited to Explore with You

Ready to come with us? 

Together, we can map your company’s tech journey and start down the trails. If you’re set to take the first step, simply fill out the form below. We’ll be in touch – and you’ll have a partner who cares about you and your company. 

We can’t wait to hear from you! 

This field is for validation purposes and should be left unchanged.

Together, we can map your company’s tech journey and start down the trails. If you’re set to take the first step, simply fill out the form below. We’ll be in touch – and you’ll have a partner who cares about you and your company. 

We can’t wait to hear from you! 

Montage Portal

Montage Furniture Services provides furniture protection plans and claims processing services to a wide selection of furniture retailers and consumers.

Project Background

Montage was looking to build a new web portal for both Retailers and Consumers, which would integrate with Dynamics CRM and other legacy systems. The portal needed to be multi tenant and support branding and configuration for different Retailers. Trailhead architected the new Montage Platform, including the Portal and all of it’s back end integrations, did the UI/UX and then delivered the new system, along with enhancements to DevOps and processes.

Logistics

We’ve logged countless miles exploring the tech world. In doing so, we gained the experience that enables us to deliver your unique software and systems architecture needs. Our team of seasoned tech vets can provide you with:

Custom App and Software Development

We collaborate with you throughout the entire process because your customized tech should fit your needs, not just those of other clients.

Cloud and Mobile Applications

The modern world demands versatile technology, and this is exactly what your mobile and cloud-based apps will give you.

User Experience and Interface (UX/UI) Design

We want your end users to have optimal experiences with tech that is highly intuitive and responsive.

DevOps

This combination of Agile software development and IT operations provides you with high-quality software at reduced cost, time, and risk.

Trailhead stepped into a challenging project – building our new web architecture and redeveloping our portals at the same time the business was migrating from a legacy system to our new CRM solution. They were able to not only significantly improve our web development architecture but our development and deployment processes as well as the functionality and performance of our portals. The feedback from customers has been overwhelmingly positive. Trailhead has proven themselves to be a valuable partner.

– BOB DOERKSEN, Vice President of Technology Services
at Montage Furniture Services

Technologies Used

When you hit the trails, it is essential to bring appropriate gear. The same holds true for your digital technology needs. That’s why Trailhead builds custom solutions on trusted platforms like .NET, Angular, React, and Xamarin.

Expertise

We partner with businesses who need intuitive custom software, responsive mobile applications, and advanced cloud technologies. And our extensive experience in the tech field allows us to help you map out the right path for all your digital technology needs.

  • Project Management
  • Architecture
  • Web App Development
  • Cloud Development
  • DevOps
  • Process Improvements
  • Legacy System Integration
  • UI Design
  • Manual QA
  • Back end/API/Database development

We partner with businesses who need intuitive custom software, responsive mobile applications, and advanced cloud technologies. And our extensive experience in the tech field allows us to help you map out the right path for all your digital technology needs.

Our Gear Is Packed and We're Excited to Explore with You

Ready to come with us? 

Together, we can map your company’s tech journey and start down the trails. If you’re set to take the first step, simply fill out the contact form. We’ll be in touch – and you’ll have a partner who cares about you and your company. 

We can’t wait to hear from you! 

This field is for validation purposes and should be left unchanged.

Thank you message.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.