CRUD Database Testing

What is CRUD?

Organizations that maintain customer records, payment information, health data, and more need persistent data storage platforms. This data is usually organized into a database, which is simply an organized collection that can be viewed electronically. The most common type of database is a relational database, which consists of rows and columns linked to other tables with additional information using a keyword system.

CRUD is an acronym that refers to the four main functions required to implement a persistent storage software: create, read, update, and delete.

CRUD is widely used in databases. This includes relational database management systems (RDBMS) such as Oracle, MySQL and PostgreSQL. It also includes NoSQL databases such as MongoDB, Apache Cassandra, and AWS DynamoDB.

Users can call four basic CRUD functions to perform different types of operations on selected data in the database. This can be done through code or through a graphical user interface.

Typically, CRUD consists of 3 parts: an API (or server), a database, and a user interface (UI). The API contains code and methods, the database stores and helps the user retrieve information, while the user interface helps users interact with the application.

CRUD operations

CRUD operations are used in persistent storage systems, that is, the data does not disappear even after the system is turned off. These are different from operations on data stored in volatile storage such as RAM or cache files.

CRUD is widely used in database software programs. This includes relational database management systems (RDBMS) such as Oracle, MySQL and PostgreSQL. It also includes NoSQL databases such as MongoDB, Apache Cassandra, and AWS DynamoDB.

Users can call four basic CRUD functions to perform different types of operations on selected data in the database. This can be done through code or through a graphical user interface.

CRUD-like operations can also be performed on persistent data structures such as files. For example, you can create a Microsoft Word document, update it, read it, and even delete it from File Explorer. However, files are not record-oriented (or document-oriented in the case of MongoDB or Couchbase). CRUD terminology specifically refers to record-oriented operations, not flat file operations.

CREATE

This function allows users to create a new record in the database. In a SQL relational database program, it is called insert. In Oracle HCM Cloud, it is create. Remember that a record is a row and columns are attributes. A user can create a new row and populate it with data corresponding to each attribute, but only an administrator can add new attributes to the table itself

READ

The read function is similar to the search function, allowing users to retrieve specific table entries and read their values. You can find the records you need using keywords or by filtering data based on individual criteria. The READ function does not change any information.

UPDATE

The update function is used to modify records that already exist in the database. Like READ, UPDATE can be applied to one, all, or just a few records, depending on the criteria.

DELETE

The delete function allows users to delete records from the database that are no longer needed. Some relational database applications can perform hard or soft deletes. Some relational database applications can perform hard or soft deletes. A hard delete permanently removes records from the database, while a soft delete can simply update the status of the row to indicate that it has been deleted, leaving the data present and intact.

What is CRUD testing?

CRUD testing in software testing is the check of data that comes in or out of the system. This can mean checking the accuracy of the values, how they are displayed, whether there are hidden problems in the backend, etc. Some examples can be: checking the ability to view data imported into the system; the ability to edit this data appropriately; whether the reports reflect these changes correctly.

There are two main ways in which a software tester can perform database testing - either by writing and executing queries on their own or with the help of a partner (a developer, automation engineer, or other senior tester).

Let's consider the main functions with examples and the cases in which they are used.

C – Create Functionality.  

For example, add a new employee account. The tester checks the new employee account on the application's external employee screen and queries the database to check for a specific row.

Additional cases to consider:

• encryption of confidential fields, such as passwords;
• displaying an error message e.g. for repetitive email;
• adding  a multi-user scenario;
• different access levels for some roles. For example, one job  may have read-only access while another has edit-field permissions;
• different data combinations;
• error handling in case of system failure.

R – Read Functionality.

For example, the tester checks whether the generated record of a new employee can be read and compares the data with those stored in the database.

             Additional Cases to Consider:

• view records one by one;
• view multiple records;
• view a record that does not exist;
• check the search function;
• make sure that each entry has the correct access according to its role;
• error handling in case of system failure.

U – Update Functionality.

For example, change the input data in the employee's accounting file and save the changes in the database, check the results.

Additional cases to consider: 

• update records of several employees simultaneously;
• break a unique key constraint by updating a value to one that already exists;
• update fields with different input combinations to develop more complex scenarios;
• verify that the updated record fulfills only the purpose of its role;
• error handling in case of system failure.

D – Delete Functionality. 

For example, delete a record about an employee, check the results in the database.

   Additional Cases to Consider:

• deleting records of several employees simultaneously;
• deleting a part of the information;
• add the Update script between Create and Delete, check the result in the database;
• execute the Delete – Create – Delete script with one record;
• error handling in case of system failure.

Conclusion.

CRUD operations are widely used in many systems supported by relational databases. Their testing is very important to ensure that information integrity, consistency, security, reliability, etc. are maintained. After all, the preservation of user data and their confidentiality is the basis of working with databases.