Database CRUD Testing Through UI with Sample Test Cases

All about CRUD Operations and How to Perform CRUD Testing Through UI:

Human beings started storing information even several decades ago! And in those days, Flat file databases were used in the computing history where all the data was stored in a file.

Later, in the early 1970s IBM developed the first Relational Database Model, which introduced “indexes” to easily fetch the data.

Database CRUD Testing Through UI

CRUD Operations Overview

Currently, the most common data storage system is the Database where the software creates, reads, updates and deletes the data through Queries. Computer Software can respond to user requirements in a faster and more effective manner through properly designed databases and queries.

This implies that testing and verification of databases is a much more important factor.

Graphical User Interface (GUI) responses like error messages, success messages, etc. are considered to be very important by almost all the test managers. This is because GUI happens to be the visible part of an application, which the users can see. However, database testing is equally as important as one.

Out of my experience, I have seen many manual testers who consider this a tedious job, but that is not the case.

In this tutorial, we will discuss black box database functionality testing through User Interface and MySQL queries in a simple manner with easy examples.

Why is Database Testing Important?

The points given below will explain the importance of Database testing in a much more brief way.

• Data is an important asset and it needs to be saved and protected.
• Databases are getting more complex with new technologies and platforms. Therefore, the chances of bugs increase.
• There can be critical functionalities associated with the values stored in the database.
• Problems with Databases or queries can lead to major functionality issues.
• Ensure Data is mapped properly or not.

Database testing can be done as a unit test, black box test, white-box test, and grey box text.

4 Basic Database Functionalities

Database-based software generally has four major functionalities which will be clear from the examples below.

Example 1:

Facebook is the most famous social networking website.

• You can create a new account
• View your account details
• Edit Account details
• Delete the account
• You can create comments
• View them
• Edit them
• Delete them

Example 2: 

LinkedIn, the famous Job Search website:

• You can create your profile
• View it
• Edit it
• Delete it
• You can add posts
• View them
• Edit them
• Delete them

Did you notice a set of common activities here?

Yes! You’re right.

Most of the software supports these functionalities of creating, viewing, editing, and deleting from where the term CRUD comes from.

Definition of CRUD

In computer programming, CRUD stands for Create, Read, Update and Delete. These are the four major and basic functions of persistent storage. This is often done in software applications through forms.

• Create – INSERT an entry in the Database.
• Read or Retrieve – SELECT the entry from the Database and View it.
• Update – UPDATE the entry completely or partially.
• Delete or Destroy – DROP/DELETE the entry.

Based on software requirements, CRUD cycles can vary.

For Example: sometimes, a salesperson creates an account and the user views it. The user may not have the privilege to edit or delete it. On the other hand, the requirement could be: the user creates his account and the salesperson verifies and approves it. These cycles are very important from a tester’s point of view.

For the functionalities discussed above, there is a corresponding query running in the database.

Given below are Examples of MYSQL queries for each action

Three variations of CRUD are BREAD (Browse, Read, Edit, Add, Delete), DAVE (Delete, Add, View, Edit), and CRAP (Create, Replicate, Append, Process).

CRUD Testing

CRUD testing is black-box testing of the database functionality.

As we know, Black box testing considers the test software system as a “black box, and the tests are conducted through the UI.

We understand that every front-end action like account registration, editing personal details, viewing details, deleting accounts, etc. has a corresponding CRUD action in the database. CRUD testing is done to check if these actions reflect in the Database properly or not.

CRUD testing is different from common black box front-end testing where we check for success messages like “Account successfully created” after a user registration, etc. Here we have to check if the account details are getting entered into the Database or not.

There are two ways in which a manual tester can do this:

#1) Execute queries on our own – Testers with a good understanding of SQL language and software requirements can make their own queries to test the database. This way all possible cases can be verified with good queries.

#2) Execute queries with the help of a developer – Testers can begin with verifying the user interface of the application and get queries from the developer.

The following questions should be considered while writing test cases for CRUD operations:

• What kind of CRUD action is valid and what is invalid for a test application?
• What kind of a relation do/should the CRUD actions have with each other?
• When will the CRUD actions be executed?
• Who is accessing the CRUD functionality? Does the system set different privileges for different users?

The general test process for DB testing is not very different from the usual manual black box GUI testing.

Like,

Step 1: Prepare the test environment.

Step 2: Execute the test steps.

Step 3: Check the test result.

Step 4: Validate actual results according to the expected results.

Step 5: Report bugs and other findings.

In order to check the test result, GUI response and Query result are used. For CRUD black box testing, we just need a query, which is SELECT.

As we all know, databases store data. SELECT query used when you need to retrieve the data. For black-box testing, we just have to use this query to see if the actions through UI show proper reflections in the database or not.

‘SELECT’ can be used in the following ways:

#1) If a tester wants to check and verify all the data, he can use the start symbol (*) in the SELECT query. This is the simplest form of SELECT query.

SELECT * FROM table_name;

The above statement selects all the fields from all the rows from the table_name table.

#2) In some cases, the first query result can be messy. If a tester is interested only in some fields only, then the following script can be used.

 SELECT ‘field_1’, ‘field_2’, ‘field_3’ FROM table_name;

The above statement selects the fields, ‘field_1’, ‘field_2’, and ‘field_3’ from all the rows from the table_name table.

#3) If a tester wants to see the results based on any criteria, then the WHERE clause can be used.

SELECT ‘field_1’ FROM table_name WHERE field_2 = ‘success’;

The above statement selects the field, ‘field_1’from all the rows from the table_name table, where ‘field2’ is ‘success’.

How to Test the CRUD Functionality of a Software?

Consider the following Example of a User Interface, which allows certain user privileges for using MySQL Database to store the data.

Check how CRUD functionality can be checked for this case.

Note: Always conduct CRUD testing in the database dumps because this testing can cause changes in the database. The command below can be used to take a dump of the entire database.

$ mysqldump -u [uname] -p [pass] Shopping_DB > Shopping_DB_backup.sql

#1) CREATE Functionality

To add a new product item, the following steps can be followed:

1. Load ‘Product Add’ form.
2. Enter the product name and say “test name”.
3. Enter the product description, and say ‘this is test product detail’.
4. Enter product price, say ‘100’.
5. Submit the form.

Checking the results:

• The tester manually verifies if the product is displayed with all the details on the front end of the software application.
• Tester executes the query in the MYSQL database server to check if a particular row is present

Query:

SELECT * FROM products WHERE product_name = ‘test name’;

Query Result from MySQL:

A row with the corresponding details should be displayed like,

Other cases to consider:

1. For some systems, different users will have different privileges. In that case, testers might have to check the response for each user role.
2. If duplicate products are not allowed, a tester can check that by adding a product with the same details once again. This time the database should not have a second entry corresponding to the same product.
3. If the software allows multiple product creations at a time, then the tester can check if all the details of all products in the submission were entered into the database properly or not.
4. Try different input combinations.
5. Check what happens during server downtime.

#2) READ Functionality

To check if the created entry is readable, the following steps can be followed:

1. Create products with different input combinations through CREATE functionality, say test name 1, test name 2, test name 3.
2. Try searching for the products.

Checking the results:

• The tester manually verifies if the product details are correct.
• The tester compares the details with the ones saved in the database.

Query:

Query Result from MySQL:

The details of the selected products must be displayed. The tester can verify and compare this with the results in UI.

Other cases to consider:

1. View items one at a time.
2. View multiple items at a time.
3. Trying to view an item that doesn’t exist.
4. Try searching with different conditions.
5. Try checking the functionality for different user roles.
6. Check what happens during server downtime.

#3) UPDATE Functionality

To edit or update existing entries, the following steps can be followed:

1. Create a product using CREATE functionality.
2. Edit the different fields of the product, say “test name” to “V Neck Top”.
3. Submit

Checking the results:

• The tester manually verifies that the product details have changed
• Tester executes MYSQL query and sees the details

Query:

SELECT * FROM products WHERE product_name = ‘V Neck Top’;

Query Result from MySQL:

Rows with corresponding details should be displayed.

If you search with an old product name, the database shouldn’t return the details.

Other cases to consider:

1. Update multiple items at a time.
2. Update to a key value that already exists.
3. Update all the details or partial details.
4. Update the fields with different input combinations.
5. Check for updated features for different Privileges.
6. Check what happens during server downtimes.

#4) DELETE Functionality

To check the delete functionality, you can follow the following steps:

1. Create a product with CREATE functionality.
2. Delete the product.

Checking the results:

• The tester manually checks if the product is removed from the UI
• The tester manually checks the MySQL database and confirms that the corresponding row has been deleted.

Query:

SELECT * FROM products WHERE product_name = ‘test name’;

Query Result from MySQL:

This should display the query results as shown below.

mysql>SELECT * FROM products WHERE product_name = ‘test name’;
Empty set (0.00 sec)

Other cases to consider:

1. Delete multiple items in a single request.
2. Delete the updated item.
3. Take two tabs and try to send a delete request for a product from both tabs altogether.

Conclusion

Database testing is very important as it confirms the security and reliability of a software application. Most software applications deal with creating, reading, updating, and deleting data to/from the database. According to software requirements, CRUD cycles can vary. Testers should plan test cases based on the CRUD cycle.

CRUD functionalities can be tested manually and verified from the User interface and Database. The SELECT query can be used to verify the changes in the database corresponding to each CRUD operation.

Are you an expert in CRUD testing? Do you have any interesting facts to share with our readers who are new to CRUD? Feel free to express your thoughts/suggestions in the comments section below!!