CodeWithDhanian

Posted on Aug 13

CRUD Operations with a Relational Database (SQL)

#backend

CRUD stands for Create, Read, Update, and Delete—the four fundamental operations performed on data in almost every backend application. Whether building an e-commerce platform, banking system, hospital management system, social media application, or inventory management system, every backend revolves around storing, retrieving, modifying, and removing structured data efficiently.

A Relational Database Management System (RDBMS) stores information inside tables, where data is organized into rows and columns. Relationships between tables are established using Primary Keys and Foreign Keys, allowing complex applications to maintain data consistency, integrity, and normalization.

Core CRUD Operations

1. CREATE — Inserting New Records

The Create operation inserts new information into a database.

Example Users table:

id	name	email
1	Alice	alice@example.com

Adding another user:

INSERT INTO users (name, email)
VALUES ('John Doe', 'john@example.com');

Explanation

INSERT INTO specifies the target table.
(name, email) lists the columns receiving values.
VALUES contains the actual data.
The id is usually generated automatically using AUTO_INCREMENT, SERIAL, or IDENTITY depending on the database.

2. READ — Retrieving Data

The Read operation fetches stored information.

Retrieve every user:

SELECT *
FROM users;

Retrieve only specific columns:

SELECT id, name, email
FROM users;

Retrieve a single user:

SELECT *
FROM users
WHERE id = 1;

Retrieve users sorted alphabetically:

SELECT *
FROM users
ORDER BY name ASC;

Limit returned rows:

SELECT *
FROM users
LIMIT 10;

Important SQL keywords

SELECT chooses columns.
FROM specifies the table.
WHERE filters records.
ORDER BY sorts results.
LIMIT restricts the number of returned rows.

3. UPDATE — Modifying Existing Data

The Update operation changes existing records.

UPDATE users
SET email = 'newemail@example.com'
WHERE id = 1;

Explanation

UPDATE specifies the table.
SET assigns new values.
WHERE identifies which row to modify.

Without a WHERE clause:

UPDATE users
SET email = 'changed@example.com';

Every row in the table would be updated, making WHERE one of the most important clauses in SQL.

4. DELETE — Removing Records

Delete a specific record:

DELETE FROM users
WHERE id = 1;

Delete every record:

DELETE FROM users;

Completely remove the table:

DROP TABLE users;

These commands are very different:

DELETE removes rows.
DROP TABLE removes the entire table structure and its data.

Complete Database Structure

Database
│
├── users
│   ├── id (Primary Key)
│   ├── name
│   ├── email
│   └── created_at
│
├── products
│   ├── id (Primary Key)
│   ├── name
│   ├── price
│   └── stock
│
└── orders
    ├── id (Primary Key)
    ├── user_id (Foreign Key)
    ├── product_id (Foreign Key)
    ├── quantity
    └── order_date

Here:

users.id uniquely identifies every user.
products.id uniquely identifies every product.
orders.user_id references users.id.
orders.product_id references products.id.

This relationship prevents duplicate information and keeps data consistent across multiple tables.

Creating Tables

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Column breakdown

INT stores whole numbers.
PRIMARY KEY uniquely identifies each row.
AUTO_INCREMENT automatically generates IDs.
VARCHAR(100) stores variable-length text.
NOT NULL requires a value.
UNIQUE prevents duplicate emails.
TIMESTAMP records creation time automatically.

Typical Backend CRUD Flow

Client
   │
   ▼
HTTP Request
   │
   ▼
Backend API
   │
   ▼
Validation
   │
   ▼
Business Logic
   │
   ▼
SQL Query
   │
   ▼
Relational Database
   │
   ▼
Query Result
   │
   ▼
Backend Response
   │
   ▼
Client

Each request follows this sequence:

Client sends an HTTP request.
Backend API receives it.
Input is validated.
Business logic determines what should happen.
SQL performs the required CRUD operation.
Database executes the query.
Results are returned.
Backend formats the response as JSON.
Client displays the updated information.

Example CRUD API (Node.js + Express)

import express from "express";

const app = express();

app.use(express.json());

const users = [];

// CREATE
app.post("/users", (req, res) => {
  users.push(req.body);
  res.status(201).json(req.body);
});

// READ
app.get("/users", (req, res) => {
  res.json(users);
});

// UPDATE
app.put("/users/:id", (req, res) => {
  users[req.params.id] = req.body;
  res.json(users[req.params.id]);
});

// DELETE
app.delete("/users/:id", (req, res) => {
  users.splice(req.params.id, 1);
  res.status(204).send();
});

app.listen(3000);

This example demonstrates the four CRUD endpoints:

POST /users creates a new resource.
GET /users retrieves resources.
PUT /users/:id updates an existing resource.
DELETE /users/:id removes a resource.

In production, the users array would be replaced by SQL queries against a relational database.

Best Practices

Always validate incoming data before executing SQL.
Use Primary Keys to uniquely identify records.
Enforce Foreign Keys to maintain relationships.
Use transactions when multiple related operations must either all succeed or all fail.
Avoid SELECT * when only specific columns are required.
Always include WHERE when updating or deleting individual records.
Parameterize queries to prevent SQL Injection attacks.
Create indexes on frequently searched columns to improve query performance.
Normalize tables to eliminate redundant data while preserving consistency.

Backend Engineering eBook

Build a stronger foundation in backend development with the complete Backend Engineering eBook:

https://codewithdhanian.gumroad.com/l/ungqng

DEV Community