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.

Database CRUD Testing

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.

Database CRUD

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.

Database Testing- fb

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

Linked in

  • 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

ActionSample Query
CREATEINSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
READSELECT * from table;
UPDATEUPDATE table_name
SET column1 = value11, column2 = value22
WHERE condition;
DELETEDELETE FROM TABLE table_name where column1 = ‘value11’;

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 ownTesters 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.

EXAMPLEDATABASE DETAILS
1. Can CREATE a product with all details including, product name, product description, product price using ‘Product Add’ formDatabase : Shopping_DB
Table: product
Fields: product_name, product_details, product_price

Creates a row in the table ‘product’ with details added in corresponding fields
2. Can READ the details product name, product description, product price in ‘Product details page’.Database : Shopping_DB
Table: product
Fields: product_name, product_details, product_price

Selects all data or select a particular data from the table ‘products’
3. Can UPDATE the product name, product description, product price using ‘Product Edit’ form.Database : Shopping_DB
Table: product
Fields: product_name, product_details, product_price

Update all details or particular details in a particular row in the table ‘products’
4. Can DELETE the productDatabase : Shopping_DB
Table: product
Fields: product_name, product_details, product_price

Delete all details in ‘products’ table by dropping the table or delete a particular row from it.

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,

mysql> SELECT * FROM products WHERE product_name = ‘test name’;
+-------------+-----------------------------+-----------------+
|product_name | product_details | product_price |
+-------------+-----------------------------+-----------------+
| test name | this is test product details| 100 |

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:

SELECT * FROM products WHERE product_name = ‘test name 1’ OR product_name = ‘test name 12’ OR product_name = ‘test name 3’;

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.

mysql> SELECT * FROM products WHERE product_name = ‘test name 1’ OR product_name = ‘test name 12’ OR product_name = ‘test name 3’;
+-------------+-----------------------------+-----------------+
|product_name | product_details | product_price |
+-------------+-----------------------------+-----------------+
| test name 1 | this is test product detail1| 100 |
| test name 2 | this is test product detail2| 100 |
| test name 3 | this is test product detail3| 100 |

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.

mysql> SELECT * FROM products WHERE product_name = ‘V Neck Top’;
+-------------+-----------------------------+-----------------+
|product_name | product_details | product_price |
+-------------+-----------------------------+-----------------+
| V Neck Top | this is test product details| 100 |
+-------------+-----------------------------+-----------------+

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!!

10 thoughts on “Database CRUD Testing Through UI with Sample Test Cases

  1. Very informative article. Handy for the folks who are exploring around the DB testing. Thanks team for collating this info and sharing it with everyone.

  2. Thanks for transparent explanation. Noticed that under SELECT section the text .. “the start symbol (*) in SELECT query” to be corrected to ” star”. Please update us with more such info.

  3. Very Nice article. For my previous project, I had searched whole internet for an article like this one. Very helpful one. Saving the link. Requesting more articles like this.

  4. Hi,according to your your tutorial we do the databasebase testing from the frontend(UI) and from the backend(database).

    Is there any situations,that the CURD result is different in the above two option?

    Can you please give any examples?

  5. Great article on the importance of database testing. Every type of testing has its own importance. So it’s a great idea to seek a good IT firm that has expertise and experience in providing software testing solutions, including a comprehensive platform for automation testing, that are best suited to your business requirements.

Leave a Comment