How to test CRUD operations in Database Testing?

Question

C: Create
R: Retrieve
U: Update
D: Delete

How can a tester verify that CRUD operations are working fine in Web App/Database testing? Please explain with real examples!

Answer 1

CRUD stands for Create, Read, Update and Delete. (Retrieve may occasionally be substituted for Read.)

Different users may have different CRUD cycles based upon the requirements of the system. A customer, for instance, might have the ability to create an account, retrieve it upon return to a website, update billing information, or delete it if necessary. An operations manager, by contrast, might create product records, (Read) call them up as needed, (Update) modify packaging or raw materials details or delete it if the product was discontinued.

Live Example:

You should test such operations from both Front-end (User Interface) and Backend (Database queries) e.g. you have a web application where you as an Admin create users and maintain their data. So,

1. Login to application as the Admin user
2. Open the Create user page, enter all necessary information for creating user like Firstname, Lastname, Email ID, User name, Phone no. etc. -- This is the create operation
3. Hit the save button
4. Now, go back to the user list and search for that user record and open its detail page, verify all the information is as it has been entered by you -- This is the Retrieve operation
5. Go to Database, fire a query on the User table and search for your created record, verify all the values are as it has been entered by you on the UI, also they are in the same field column where they should be e.g. sometimes Last name on UI is wrongly mapped with First name in DB, so you should verify these things too.
6. From the UI, edit that user record and update the Email ID or any other preferences. Save the changes and again verify the changes on UI and DB too.
7. Now, Update the value directly from the DB for any field of the user record, and see that change should be reflected on the UI too (be sure that all referenced tables are also updated by you if there is no such trigger to update reference tables) --- This is the Update Operation
8. Delete the record from the UI and verify that it should no more be visible on the User List and it should not be in the DB too, but here you need to validate the data according to the Business requirements e.g. you client says on Delete user data should be there in DB but it should be Inactive (like Active = 0, for the deleted user). Then you need to verify it accordingly that data exists in DB with Active = 0 and it should not be shown over the UI.
9. Delete the User record from the DB directly (as per Business requirements) and see the changes are reflected on UI or not. Here double-check your query which you are going to execute, else you have delete whole table or multiple users or their data might get corrupted. So, always verify you delete commands before executing them. Now, when deleting from the DB directly, delete from the referenced tables, else data will be corrupted and there can be some errors shown over the UI.
10. Verify the formats of the data being stored in Database e.g. Date format, Time Format (if your application supports multiple time zone then it should be verified for different time zones). Validate where your application saves NULL and where it saves ' ' (i.e. Blank). This NULL and Blank sometimes seems to be same but during retrieve it may cause issues.

For the next level, you can create your own Stored Procedures (SP) for verifying these CRUD operations, once you know about the queries and tables being used, you can create these SP where on firing the Delete SP, it will itself verify the main and referenced tables and provide you with the results.

For the DB testing, you need to read about the queries, syntax and keywords required for creating and executing queries. Syntax of different DB differs from each other in one or more respect, so you should first read about the DB on which you want to perform the DB testing.

Answer 2

CRUD operations are the root of Database testing qa services and we need to understand the meaning of the same with a common example.

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

First we try to understand it more with a day to day example:

Facebook, the most famous social networking website.
We can create a new account
We can View the account details
We can Edit account details
We can Delete the account
We can create comments
We can View them
We can Edit them
We can Delete them

So we are using CRUD in day to day life.