Database CRUD Testing Through UI with Sample Test Cases

What are CRUD Operations and How to Perform CRUD Testing Through UI:

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

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

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 the user requirements in a faster and effective manner through properly designed database and queries.

This implies that testing and verification of database is a much 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 important an one.

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

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 brief way.

• Data is an important asset and it needs to be saved and protected.
• Databases are getting complex with new technologies and platforms. Therefore, chances of bugs increase.
• There can be critical functionalities associated with the values stored in the database.
• Problems in Database or queries can lead to major functionality issues.
• To 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.

The 4 Basic Database Functionalities

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

Example 1:

Facebook, 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 are right.

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

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. These are often done in software applications through forms.

• Create – INSERT an entry in 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 the software requirements, the CRUD cycles can vary.

For Example: sometimes, salesperson creates an account and the user views it. The user might 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 a 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 understood that every front-end action like account registration, editing personal details, viewing details, deleting account 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 actually 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 requirement can make their own queries to test the database. This way all the 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 relation does/should the CRUD actions have with each other?
• When are the CRUD actions 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 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. When we need to retrieve the data, the SELECT query is used. In 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 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 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 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.

Let’s 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

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

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

Checking the result:

• Tester manually verifies if the product is displayed with all the details in the front end of the software application.
• Tester executes the query in MYSQL database server to check if the 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 roles.
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 some 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 result:

• Tester manually verifies if the product details are correct.
• 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, following steps can be followed:

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

Checking the result:

• 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:

A row 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 the updated feature for different Privileges.
6. Check what happens during server downtimes.

#4) DELETE Functionality

To check the delete functionality, following steps can be followed:

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

Checking the result:

• Tester manually checks if the product is removed from the UI
• 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 result 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 an updated item.
3. Take two tabs and try to give delete request for a product from both tabs altogether.

Conclusion

Database testing is very important as it confirms security and reliability of a software application. Most software applications deal with creating, reading, and updating and deleting data to/from the database. According to software requirement, CRUD cycles can vary. A tester should plan test cases based on 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!!