All the basics (& more) you should know about CRUD operations
Let's get back to basics! CRUD operations are fundamental to computer programming, and even for experienced developers, a good refresher on best practices should be welcome. Also, for those with more experience, feel free to proceed directly to Part III for more advanced tips.
As a quick background : CRUD operations, which stand for Create, Read, Update, and Delete, are essential actions found in both database management systems (DBMS) and API design. These operations are the backbone of any data-centric application or system, allowing users to interact with and manipulate data stored within a database.
While the acronym CRUD was initially popularized in the early 1980s in relation to persistent storage, particularly SQL database management, it later expanded to encompass user interface conventions.
In this article, we will first provide a detailed definition of the CRUD actions. Then, we will proceed to discuss examples of actual CRUD operations for both database and RESTful API. Finally, we will conclude with more advanced advice. For those interested in leveraging advanced techniques for building CRUD APIs, you can read An expert's guide to CRUD APIs: how to build a robust one and we’ll soon publish an article specific to CRUD Apps.
I. Understanding CRUD operations
In order to manage data storage, it is necessary to insert data into the storage, access the stored data, and potentially update or delete it. These fundamental requirements of storage management form the four CRUD operations:
- Create:
The "Create" operation involves adding new data records to a database. This data needs to be allocated and initialized with content.
It typically entails inserting a new row or document into a database table or collection, respectively, with values for each of the columns or fields.
For example, in a user management system, a "Create" operation might involve adding a new user profile with details such as username, email address, and password.
- Read:
The "Read" operation involves retrieving data from a database. It allows users to query and fetch existing records from one or more tables or collections based on specified criteria.
For example, in an e-commerce application, a "Read" operation might involve retrieving product information such as name, price, and availability to display on a product listing page.
- Update:
The "Update" operation involves modifying existing data records within a database. It allows users to change the values of one or more fields in a particular record or set of records.
For example, in a content management system, an "Update" operation might involve editing the content of a blog post or article by modifying the title, body, or publication date.
- Delete:
The "Delete" operation involves removing data records from a database. Data is finalized and deallocated.
It allows users to permanently erase unwanted or obsolete data from the system.
For example, in a customer relationship management (CRM) application, a "Delete" operation might involve removing a customer record from the database when the customer requests to close their account.
II. CRUD operations commands and examples for databases and RESTful APIs
1) Databases
Databases are incredibly prevalent in the IT world and play a crucial role in storing, organizing, and managing data for various applications and systems. Here's a list of CRUD commands related to databases so you can get a better grasp of how CRUD operations actually look:
CRUD Operations | Corresponding SQL statement | Other databases | Corresponding NoSQL statement |
Create | Insert INSERT INTO users (id, username, email, birthdate) VALUES (1, 'john_doe', 'john.doe@email.com', '1990-05-15'); | Insert INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); There may be variations in specific features such as support for inserting multiple rows in a single statement or handling of default column values. | Insert "_id": 1, "username": "john_doe", "email": "john.doe@email.com", "birthdate": "1990-05-15" }) |
Read | Select SELECT username, email FROM users; | Select SELECT column1, column2, ... FROM table_name WHERE condition; There can be differences in advanced features such as window functions, common table expressions (CTEs), and specific SQL functions supported. | Select
|
Update | Update UPDATE users SET email = 'jane.doe@newemail.com' WHERE id = 1; | Update UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; There may be differences in features such as handling of JOINs, subqueries, and specific data types. | Update { "_id": 1 }, { $set: { "email": "jane.doe@newemail.com" } } ) |
Delete | Delete DELETE FROM users WHERE birthdate < '2000-01-01'; | Delete DELETE FROM table_name WHERE condition There may be differences in features such as support for limiting the number of rows affected or handling of cascading deletes for foreign key constraints. | Delete |
2) RESTful APIs
RESTful APIs provide a standardized approach to building scalable and interoperable web services. They are extensively used and CRUD operations on them are thus widely utilized. Below, you'll find the HTTP methods corresponding to each CRUD action:
CRUD Operations | Corresponding HTTP request | Corresponding javascript call |
Create | POST POST /api/users HTTP/1.1 Host: example.com Content-Type: application/json { "username": "john_doe", "email": "john.doe@email.com", "birthdate": "1990-05-15" } | POST const createUser = async (userData) => { const response = await fetch('/api/users', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify(userData) }); return await response.json(); }; |
Read | GET GET /api/users/123 HTTP/1.1 Host: example.com | GET const getUserById = async (userId) => { const response = await fetch(`/api/users/${userId}`); return await response.json(); }; const getAllUsers = async () => { const response = await fetch('/api/users'); return await response.json(); }; |
Update | PUT to fully update ie replace PUT /api/users/123 HTTP/1.1 Host: example.com Content-Type: application/json { "username": "john_doe_updated", "email": "john.updated@email.com", "birthdate": "1990-05-15" } ,
PATCH to partially update, ie modify PATCH /api/users/123 HTTP/1.1 Host: example.com Content-Type: application/json { "email": "john.updated@email.com" } | PUT const updateUser = async (userId, updatedUserData) => { const response = await fetch(`/api/users/${userId}`, { method: 'PUT', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify(updatedUserData) }); return await response.json(); }; PATCH const updateUserEmail = async (userId, newEmail) => { const response = await fetch(`/api/users/${userId}`, { method: 'PATCH', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ email: newEmail }) }); return await response.json(); }; |
Delete | DELETE DELETE /api/users/123 HTTP/1.1 Host: example.com | DELETE const deleteUser = async (userId) => { const response = await fetch(`/api/users/${userId}`, { method: 'DELETE' }); return await response.json(); }; |
3) Database restful API example
Let's consider the scenario of a new client being created and taking actions on a B2B retail website.
When a new user is CREATEd, such as John Doe, customer service can READ their information by browsing through a list of customers.
If John Doe then reaches out to customer service to update their personal information and place an order, the customer service team will READ then UPDATEs the user's details accordingly.
If John Doe then asks customer support to remove another item from their cart before completing the purchase, the backend system will DELETE the temporary record written in the sales table.
Finally, if John Doe validates with customer support the order of a specific item from the company catalog, the backend system UPDATEs the inventory to reflect the reduced stock available. Other potential customers READing the website will see the proper changes.
III. What to keep in mind for CRUD operations efficiency
Now that you're familiar with the fundamental functions of CRUD operations, let's delve deeper and explore below the best practices you should keep in mind to ensure the efficiency of your CRUD operations.
1) Data modeling
This one is a given. Still, it’s worth remembering that your CRUD operations can only be as good as your initial database structure is. So before implementing any CRUD operations, it is crucial to design the database schema or structure that defines the organization of data into tables, columns, and relationships. A well-designed schema ensures that CRUD operations can be performed efficiently and accurately. This is not easy fit and you should take the time to allocate enough resources to this task as it's fundamental to the success of your operations.
2) Data integrity and maintainability
Validating data input is essential to maintain data integrity and prevent errors in CRUD operations. Input validation ensures that only properly formatted and permissible data is accepted, reducing the risk of data corruption or security vulnerabilities.
CRUD operations often adhere to the ACID (Atomicity, Consistency, Isolation, Durability) properties of database transactions to ensure data integrity and reliability. ACID compliance guarantees that database operations are executed in a reliable and predictable manner, even in the presence of concurrent transactions or system failures.
Ensuring transactional integrity across multiple CRUD operations is critical to maintain data consistency and avoid partial updates or inconsistencies. Using database transactions or implementing compensating actions (in case of failure) helps preserve the integrity of related data during complex operations involving multiple CRUD operations.
3) Error handling
Don’t forget proper error handling mechanisms should be implemented to manage unexpected scenarios during CRUD operations, such as database connectivity issues, constraint violations, or data conflicts. Handling errors gracefully improves the robustness and reliability of the application.
You should actively anticipate scenarios such as the common following attempts:
- to CREATE or UPDATE a new user with a duplicate username (inform user or provide alternative username)
- to retrieve a user by ID, but the user does not exist in the database.(informing user gracefully)
- to update a user's email address, but the provided email address is invalid. (validate the input to ensure its integrity and consistency)
- to delete a user, but the user has associated data (e.g., orders in progress, transactions) that depend on it. (consider dependencies)
- to execute execute and operation but there is a database connection failure or timeout.
4) Security
Implementing appropriate security measures, such as authentication, authorization, and encryption, helps protect sensitive data and restrict unauthorized access to CRUD operations. Role-based access control (RBAC) can be used to define permissions for different user roles regarding CRUD operations on specific data entities.
Implementing proper role management is crucial to prevent casual restricted information browsing and change, whether it be from external users or internal users through an admin panel.
5) Performance optimization
Optimizing CRUD operations for performance involves techniques such as indexing, caching, and query optimization to enhance data retrieval and manipulation speed. Efficient database design, proper indexing
Performing CRUD operations in batches, rather than individually, can also significantly improve efficiency and reduce overhead, especially when dealing with large datasets. Batch processing techniques, such as bulk inserts or updates, help minimize the number of database round-trips and optimize resource utilization.
In conclusion, understanding CRUD operations is essential for designing and implementing data-centric applications effectively. By mastering the basics and adhering to best practices you can build robust, scalable, and performant systems that efficiently manage data throughout its lifecycle.