CRUD: The four essential moves every database loves

Databases & system design

When you interact with any app - whether it’s browsing products, updating your profile, or deleting a post - you’re relying on the core operations known as CRUD: Create, Read, Update, and Delete. These four actions are the foundation of how apps manage and manipulate data, making them essential for software development. Let’s break down each operation and explore why they’re so important.

What is CRUD?

At its core, CRUD lets apps store, access, and manage data effectively. Whether you’re adding a new user, displaying product listings, editing order details, or removing outdated data, CRUD operations keep everything running smoothly. Here’s a closer look at what each operation does:

Create

The Create operation allows applications to add new records to the database. This operation usually corresponds to an SQL INSERT command in relational databases or an insert() method in NoSQL databases.

In an e-commerce application, when a new product is added, the system uses a Create operation to insert product details like name, description, price, and stock quantity into the database.

SQL Example:

INSERT INTO products (product_id, name, description, price, stock) VALUES (1, 'Laptop', 'High-performance laptop', 1200, 30);

NoSQL Example:

db.products.insert({product_id: 1, name: "Laptop", description: "High-performance laptop", price: 1200, stock: 30});

Read

The Read operation retrieves data from the database without modifying it. This operation typically corresponds to an SQL SELECT command or a find() method in NoSQL databases. The Read operation is crucial for displaying data to users, such as viewing a user profile, browsing product listings, or viewing transaction histories.

In a social media app, when users view their friends' profiles, the system uses a Read operation to retrieve and display profile details, photos, and posts.

SQL Example:

SELECT * FROM users WHERE user_id = 1;

NoSQL Example:

db.users.find({user_id: 1});

Update

The Update operation modifies existing records in the database. This is commonly achieved using the SQL UPDATE command or the update() method in NoSQL databases. The Update operation is essential for changing data that already exists, such as editing a user’s profile information, adjusting a product’s price, or updating order status.

In a content management system (CMS), when an author edits an article, the Update operation modifies the article’s title, body, and publication date in the database.

SQL Example:

UPDATE products SET price = 1100 WHERE product_id = 1;

NoSQL Example:

db.products.update({product_id: 1}, {$set: {price: 1100}});

Delete

The Delete operation removes records from the database. In SQL, this is done with the DELETE command, while NoSQL databases often use a remove() method. Delete operations are important for maintaining a clean and efficient database, allowing for the removal of outdated, incorrect, or unnecessary data.

In a project management tool, when a user deletes a completed or canceled project, the Delete operation removes the project data from the database.

SQL Example:

DELETE FROM projects WHERE project_id = 10;

NoSQL Example:

db.projects.remove({project_id: 10});

CRUD in Web Application Development

CRUD is fundamental to application development across various types of applications, enabling efficient data interaction, storage, and display.

Web Applications often rely on CRUD operations to interact with databases, particularly in user interface interactions where users can create, view, update, and delete data.

A blog application where users can create posts, view other posts, edit their posts, and delete unwanted posts.

RESTful APIs use HTTP methods to map CRUD operations directly:

• POST for Create

• GET for Read

• PUT or PATCH for Update

• DELETE for Delete

An e-commerce API allows clients to manage product listings, where POST /products adds a new product, GET /products/{id} retrieves product details, PUT /products/{id} updates product information, and DELETE /products/{id} removes a product.

CRUD vs. Other Models

While CRUD is foundational, some alternatives or enhancements exist, particularly for complex or distributed systems:

• CQRS (Command Query Responsibility Segregation): Separates the responsibilities of read and write operations, often used in microservices and distributed architectures.

• Event Sourcing: Instead of directly updating data, event sourcing records state changes as events, which can be replayed to reconstruct the current state, offering an auditable history of changes.

• RESTful and GraphQL APIs: While RESTful APIs align directly with CRUD, GraphQL provides flexible data querying, allowing clients to request exactly the data they need.

Conclusion

CRUD operations are everywhere, powering the apps and services we use daily. They help systems add, view, update, and remove data efficiently, making them critical to web, mobile, and enterprise applications. As systems grow, techniques like CQRS and event sourcing help handle larger workloads and maintain performance. Whether you’re a beginner or an experienced developer, understanding CRUD is key to building scalable and reliable software.