Databases are used in almost all software applications and are central to any application containing data storage and retrieval functionality. Thus, when you test an application with a database behind it, you need to ensure that all your transactions (i.e., CRUD operations) are working well.
Why is Database Testing important?
Database Testing is critical for software applications because of the following reasons:
- Ensure Data Mapping. Data mapping is a way to work out the data elements, their data types, length, and the validation required for each field. If a user changes an attribute in the application, this change must reflect in the appropriate area.
- Maintain ACID Properties – ACID is an acronym and stands for Atomicity, Consistency, Isolation, Durability. ACID properties are the core of a robust database structure. Atomicity means that either all the operations execute in the database or none of them do. For example, when changing a record, then that record should be deleted and inserted again with the updated attributes, or nothing happens. Consistency ensures the database will be consistent at the end of every transaction. If a user tries to update one record twice, this operation must fail. Isolation enforces the data is available to all users at the end of each transaction. Durability means that the effects of the transaction are permanent in persistent storage, even when power failures occur. For example, if you purchase a ticket to your favorite sporting game, that ticket sale will remain even if there is a mishap in the system.
- Ensures accurate business rules – Databases usually store all the business rules, data transformations, and relationships between different entities. If any issues arise, there might be a database problem.
- Supports Audit Requirements – Implementing audit trails to track changes is a standard business requirement in critical applications. Thus, any change made to the database without the user knowing about it could be a severe security issue.
- Ensures Data Integrity – ensures that data remains valid and unchanged. It can be defined as “the state where all the data is correct and consistent.” In other words, if a system changes the data, it will be updated correctly and rendered accurately to all users.
- Application Critical – Databases store inputs that are displayed to users through a graphical user interface, API, reports, or in some cases, directly. As every piece of the tech stack depends on them, they should receive attention.
Due to the importance of databases, we need to test them thoroughly to ensure accurate data mapping, maintenance of the ACID properties, and all business rules are met. Let’s discuss CRUD’s four components in detail:
What does CRUD stand for in computer programming?
CRUD operations are the basic operations in applications that need an RDBMS (Relational DataBase Management System). CRUD stands for Create, Read, Update and Delete, and these are the essential functions of every application that leverages persistent storage. Testers perform these checks with ease when they know SQL.
| Action | Definition | Sample Query |
|---|---|---|
| Create | The create operation is when new objects are added to the table. |
INSERT INTO table_name (column1, column2, column3, columnN) VALUES (value1, value2, value, valueN); |
| Read | The read operation is when object data is displayed or printed, usually resulting in reading an individual record. | SELECT * FROM table_name WHERE condition; |
| Update | The update operation is when the object data is modified. |
UPDATE table_name SET column1 = value1, columnN = valueN WHERE condition; |
| Delete | The delete operation is when individual objects are removed from the table. | DELETE FROM table_name WHERE condition; |
What is CRUD Testing in Software Testing?
CRUD Testing in Software Testing is a form of black-box testing that validates data accuracy going into or coming out of software works as expected.
This could mean checking to see if the values are accurate if they render correctly on various screens and reports, and if there are hidden issues in the backend. Some examples would be checking that you can view data imported into the system, whether or not you can edit it as expected, and if those edits are reflected correctly on reports.
CRUD software testing can also include checking the proper configuration of interface screens. For example, in an application where users can update their information, you need to ensure the screens allow users to enter their information and not include or omit fields they should not see.
The two primary ways a software tester can perform this database testing are to either write and execute queries on their own or write and execute queries with the help of a teammate (developer, automation engineer, or another senior tester).
Executing your own queries
Writing queries against CRUD operations is a fundamental skill to hone in software testing. If you have a solid grasp of the SQL language and the business rules, you can make your queries to validate the database, allowing you to verify all aspects of a story.
Crafting queries with a teammate
Some testers are not as comfortable navigating their way through tables. Hence, they would need the help of a developer or another tester to write code. In this case, the manual tester would verify the user interface and pair with their teammate to craft SQL queries.
What are some SELECT query examples?
Often testers must validate data on-screen; the best way to validate the correct information is with SELECT statements.
Global SELECT statement returning all columns:
SELECT * FROM table_name;
The above statement retrieves all records and all rows from a specific table.
Global SELECT statement returning specific columns
SELECT column1, column2, column3 FROM table_name;
The above statement retrieves specific columns for all rows in a specific table. Retrieving specific columns is a best practice when querying a table.
SELECT statement for specific columns and rows
SELECT column1, column2 FROM table_name WHERE column3 = condition;
The above statement retrieves rows meeting the specific condition of column3 equaling a condition. Common examples might be where name = ‘john doe’ or active = true.
JOIN statements for fields from multiple tables
SELECT column1 FROM table_name INNER JOIN table_name2 ON table_name.columnN = table_name2.columnN WHERE condition;
The above statement will combine the records in table_name and table_name2, where the columns contain the same value. If your application’s database design incorporated best practices, there should be foreign keys between the tables to help identify the columns.
Why is CRUD testing necessary?
Any application must have a solid CRUD (Create, Read, Update, Delete) foundation to be tested or function. As testers, we break the application by verifying it does not work as intended. We need to ensure all aspects of the software are working, including handling database interactions. Here are some additional reason
ns:
• Conformance to standards (e.g., ISO/IEC 9126) requires testing against all features of an application, including the Data Manipulation Language (DML), which is part of the SQL standard;
• There are few limitations to the way you work with the database, so you can easily insert records, delete them or modify them. This flexibility makes this kind of database testing an easy-to-use methodology;
• Directly validating stored procedures is simpler.
Testers need to make sure they comprehend CRUD operations to understand the application they are working on. CRUD is a ubiquitous acronym among software testers, which is vital to understand.
What are the types of Testing for CRUD operation?
Data-Driven testing verifies CRUD actions. The UI or API code uses different data types, like numerical, alphanumeric, or date/time. So all those values must match with the values stored in the database. Here are some of the scenarios performed to ensure everything is working well:
Insertion Test Case
Used to ensure there are no errors after insertion, primary key constraints are okay, and no duplication.
Deletion Test Case
Delete should work as expected; all the data is removed from the table correctly.
Updating Test Case
Make sure that after the update operation, there are no errors or other problems
Retrieval Test Case
Confirm the retrieval of the correct records from the database and all the data (primary and foreign) is in sync.
What is the CRUD Test Cases Process?
As a tester, you should have a basic idea of how CRUD operations work in an application. Here are the steps you need to follow to test CRUD operations for any application:
Understanding the Data Model
Model data before commencing any work. Creating a data model helps decide what tables you need for the application and how they are connected. It will assist in determining the relationships between various tables and their primary keys, which is a good practice for relational databases.
Creating scenarios for each CRUD operation
Design an appropriate test case set based on the data model you developed. Each CRUD action in the application should have a different test, and the test scenarios should include the data used for the operation.
Verify the test results
Verify that each operation of CRUD works appropriately. Check to make sure you can insert new records, read, update, and delete them as expected.
Repeat steps 1 to 3 as needed
CRUD testing is a simple approach to follow for most applications. Remember that scenarios may become complex for large applications when more than one table is involved or testing many fields.
Should Our Team Automate CRUD Tests?
Data integrity can be tested through manual or automated means, whether through using SQL scripts for the client or encoding scripts for the server. The client-side of the application is not always responsible for data integrity. Thus, it is necessary to validate its integrity through the APIs, the Database layer, and the UI.
Manual checking inputs is usually the most advisable approach for quality control measures and ensuring the data quality is at its best. However, one good reason to automate checking data is to continually validate its integrity instead of only when building functionality.
A word of caution about using automation for this purpose is to make sure the code does not become bloated and produce an unrealistic QA environment. Recognize that data used in automated testing should be treated as static because creating an environment with actual data is usually not realistic for regression purposes.
What is CRUD API Testing?
CRUD API testing is precisely the same as CRUD database testing, except APIs are directly leveraged instead of the GUI. API stands for Application Programming Interface, which are sets of routines, protocols, and tools used to build software applications.
To successfully CRUD API Test, you need to have the correct tools such as Postman. If you are unfamiliar with APIs, collaborate with a developer to issue HTTP requests.
Example/Sample Test Cases for CRUD Operations
Consider the following scenario where a user can register an account, update their account details, view their current information, and close their account.
The scenarios would look like this:
| Example | Database Details |
|---|---|
| Construct an employee account with all details, including first name, last name, email address, password, role, country. |
Table: Employee Fields: first_name, last_name, email_address, password, role_id, country Creates a row in the table 'Employee' with details added in the corresponding form. |
| READ the user's first name, last name, email address, and country on the 'User Management' page. |
Table: Employee
Fields: first_name, last_name, email_address, role, country. Bonus opportunity: verify the password field is encrypted in the database. Select from the table 'Employee.' |
| UPDATE the user's first name, last name, email address, password, role, and country in the 'User Edit' form. |
Table: Employee Fields: first_name, last_name, email_address, password, role_id, country Updates a row in the table 'Employee' with details modified in the corresponding form in the 'Employee screen.' |
| DELETE the employee's account. |
Table: Employee
Fields: first_name, last_name, email_address, password, role, country Delete the corresponding row in the table 'Employee.' |
Let’s dive deep into how we verify each of the CRUD functionalities.
C – Create Functionality
Add a new employee account by following these steps:
- Open your application to the employee screen.
- Select add new employee option.
- Enter the following details:
-
- First name: Bug
- Last name: Banishers
- Email address: test-name@accelatest.com
- Password: tooSimplePassword
- Role: Software Tester
- Country: India
- Submit the form
Validate the results:
- The manual tester verifies the new employee’s account in the software application’s front-end Employee screen.
- The manual tester executes the following database query to validate the specific row exists.
Query:
SELECT * FROM Employee WHERE email=’test-name@accelatest.com’;
Query Result:
Additional cases to consider:
- For all databases, encrypt sensitive fields like passwords.
- If a duplicate email address exists in the MySQL database for a specific user, the test case should include validating an error message is displayed.
- If the application allows multiple employee creation simultaneously, consider including a multi-user scenario and validate the data persists properly.
- For some applications, different roles will have further access. For example, one position might have read-only access while another role may have edit permissions for the fields. The test case should verify the role is valid and whether it can perform any action.
- Attempt varying data combinations.
- Verify error handling when a system outage occurs.
R – Read Functionality
To see if the generated entry is readable, execute the following procedures:
- Add some employee records with various input combinations with the steps from the CREATE scenario. For our purposes, the email addresses will be test-name-7@accelatest.com, test-name-11@accelatest.com, test-name-13@accelatest.com.
- Try searching for the new record.
Validate the results:
- The tester manually validates the employee’s information.
- Compare the details to those saved in the database by the tester.
Query:
SELECT * FROM Employee WHERE email IN (‘test-name@accelatest.com’,’test-name-7@accelatest.com’,’test-name-11@accelatest.com’,’test-name-13@accelatest.com’);
Query Results:
The employee data entered must be returned, and the tester may check and compare it with the UI findings.
Additional cases to consider:
- View employee records on screen and in the MySQL database one at a time.
- View multiple employee records on screen and in the database.
- Try viewing an employee record that does not exist.
- Ensure search functionality works.
- Verify each of the user records has the correct access based on their role.
- Verify error handling when a system outage occurs.
U – Update Functionality
To see if the updated entry is readable, execute the following procedures:
- Generate an employee record with different inputs from the CREATE scenario.
- Modify the inputs of the employee record. For example, you could change the role from ‘Tester’ to ‘Automation Engineer’ and email from ‘test-name@accelatest.com’ to ‘new-email-address@accelatest.com.’
- Persist the changes to the database.
Validate the results:
- The tester manually validates the updated employee’s information.
- Compare the details to those saved in the database by the tester.
Query:
SELECT * FROM Employee WHERE email IN (‘new-email-address@accelatest.com’, ‘test-name@accelatest.com’);
Query Result:
Verify the email address test-name@accelatest.com is not returned in the query.
Additional cases to consider:
- Update multiple employee records at once.
- Attempt to violate unique key constraints by updating the value to one that already exists.
- Update any necessary or wanted information.
- Update the fields with various input combinations to devise more complex scenarios.
- Check the updated employee record can perform only their role’s assignment.
- Verify error handling when a system outage occurs.
D – Delete Functionality
To see if the delete functionality is working, execute the following procedures:
- Create a new employee record by leveraging the CREATE scenario.
- Delete the employee record.
Validate the results:
- The tester manually validates the deleted employee’s information is no longer available in the UI.
- Compare the details to those saved in the MySQL database by the tester.
Query:
SELECT * FROM Employee WHERE email = ‘test-name@accelatest.com’;
Query Result:
Additional cases to test CRUD functionality:
- Delete multiple employee records at once.
- Add the Update scenario between the Create and Delete.
Conclusion for CRUD Testing in Software Testing
Completing black-box testing to validate the integrity of the stored user’s data is vital to releasing quality applications. It may involve the process of verifying all records within a database or checking all four major components of data: creating, reading, updating, and deleting.
Perform CRUD operations tests either manually or through automation. Either way, validate the data through the API Layer, User Interface, and the database. The SELECT query is critical for manual and automated testers to master as it is the basis of all CRUD validation.
Image Credits:
Database Vectors by Vecteezy
