CRUD Architecture

CRUD represents the four fundamental operations performed on persistent data: Create, Read, Update, and Delete. Most applications, especially web applications, are built around these core operations. Understanding CRUD is essential because it forms the foundation for data management across databases, APIs, and user interfaces.

The Four Operations

Create - Adding New Data

The Create operation adds new records or entities to a data store. This is typically the first operation users perform when starting to use an application.

// Database Create
async function createUser(userData) {
    const newUser = {
        id: generateId(),
        name: userData.name,
        email: userData.email,
        createdAt: new Date(),
        updatedAt: new Date()
    };

    // SQL Database
    const result = await db.query(
        'INSERT INTO users (id, name, email, created_at, updated_at) VALUES (?, ?, ?, ?, ?)',
        [newUser.id, newUser.name, newUser.email, newUser.createdAt, newUser.updatedAt]
    );

    return newUser;
}

// Array Create (in-memory)
const users = [];
function createUserInMemory(userData) {
    const newUser = {
        id: users.length + 1,
        ...userData,
        createdAt: new Date()
    };
    users.push(newUser);
    return newUser;
}

// LocalStorage Create
function createUserInStorage(userData) {
    const users = JSON.parse(localStorage.getItem('users') || '[]');
    const newUser = {
        id: Date.now(),
        ...userData
    };
    users.push(newUser);
    localStorage.setItem('users', JSON.stringify(users));
    return newUser;
}

Read - Retrieving Existing Data

The Read operation retrieves data from storage. This is the most frequently used operation, as users often need to view data before modifying or deleting it.

// Read All
async function getAllUsers() {
    const result = await db.query('SELECT * FROM users ORDER BY created_at DESC');
    return result.rows;
}

// Read One by ID
async function getUserById(id) {
    const result = await db.query('SELECT * FROM users WHERE id = ?', [id]);
    return result.rows[0];
}

// Read with Filtering
async function getUsersByRole(role) {
    const result = await db.query('SELECT * FROM users WHERE role = ?', [role]);
    return result.rows;
}

// Read with Search
async function searchUsers(searchTerm) {
    const result = await db.query(
        'SELECT * FROM users WHERE name LIKE ? OR email LIKE ?',
        [`%${searchTerm}%`, `%${searchTerm}%`]
    );
    return result.rows;
}

// Read with Pagination
async function getUsersPaginated(page = 1, pageSize = 10) {
    const offset = (page - 1) * pageSize;
    const result = await db.query(
        'SELECT * FROM users LIMIT ? OFFSET ?',
        [pageSize, offset]
    );
    return result.rows;
}

Update - Modifying Existing Data

The Update operation changes existing records. Updates can be partial (only some fields) or complete (replacing the entire record).

// Full Update (Replace)
async function updateUser(id, userData) {
    const result = await db.query(
        'UPDATE users SET name = ?, email = ?, updated_at = ? WHERE id = ?',
        [userData.name, userData.email, new Date(), id]
    );

    if (result.affectedRows === 0) {
        throw new Error('User not found');
    }

    return getUserById(id);
}

// Partial Update (Merge)
async function patchUser(id, partialData) {
    // Get existing user
    const existingUser = await getUserById(id);
    if (!existingUser) {
        throw new Error('User not found');
    }

    // Merge changes
    const updatedUser = {
        ...existingUser,
        ...partialData,
        updatedAt: new Date()
    };

    // Build dynamic UPDATE query
    const fields = Object.keys(partialData);
    const setClause = fields.map(field => `${field} = ?`).join(', ');
    const values = [...fields.map(f => partialData[f]), id];

    await db.query(
        `UPDATE users SET ${setClause}, updated_at = ? WHERE id = ?`,
        [...values, new Date(), id]
    );

    return updatedUser;
}

// Update with Validation
async function updateUserEmail(id, newEmail) {
    // Validate email format
    if (!isValidEmail(newEmail)) {
        throw new Error('Invalid email format');
    }

    // Check if email is already in use
    const existingUser = await db.query(
        'SELECT id FROM users WHERE email = ? AND id != ?',
        [newEmail, id]
    );

    if (existingUser.rows.length > 0) {
        throw new Error('Email already in use');
    }

    // Perform update
    await db.query(
        'UPDATE users SET email = ?, updated_at = ? WHERE id = ?',
        [newEmail, new Date(), id]
    );

    return getUserById(id);
}

Delete - Removing Data

The Delete operation removes records from storage. Deletes can be hard (permanent) or soft (marked as deleted but retained in the database).

// Hard Delete (Permanent)
async function deleteUser(id) {
    const result = await db.query('DELETE FROM users WHERE id = ?', [id]);

    if (result.affectedRows === 0) {
        throw new Error('User not found');
    }

    return { success: true, message: 'User deleted' };
}

// Soft Delete (Mark as deleted)
async function softDeleteUser(id) {
    const result = await db.query(
        'UPDATE users SET deleted_at = ?, is_active = false WHERE id = ?',
        [new Date(), id]
    );

    if (result.affectedRows === 0) {
        throw new Error('User not found');
    }

    return { success: true, message: 'User deactivated' };
}

// Delete with Cascade (Remove related data)
async function deleteUserWithPosts(userId) {
    // Start transaction
    await db.query('START TRANSACTION');

    try {
        // Delete user's posts
        await db.query('DELETE FROM posts WHERE user_id = ?', [userId]);

        // Delete user's comments
        await db.query('DELETE FROM comments WHERE user_id = ?', [userId]);

        // Delete user
        await db.query('DELETE FROM users WHERE id = ?', [userId]);

        // Commit transaction
        await db.query('COMMIT');

        return { success: true, message: 'User and related data deleted' };
    } catch (error) {
        // Rollback on error
        await db.query('ROLLBACK');
        throw error;
    }
}

// Bulk Delete
async function deleteInactiveUsers(daysInactive = 365) {
    const cutoffDate = new Date();
    cutoffDate.setDate(cutoffDate.getDate() - daysInactive);

    const result = await db.query(
        'DELETE FROM users WHERE last_login < ? AND is_active = false',
        [cutoffDate]
    );

    return {
        success: true,
        deletedCount: result.affectedRows
    };
}

CRUD in Web Applications

HTML Forms for CRUD

Traditional web applications use HTML forms to perform CRUD operations. Each operation typically has its own form or page.

<!-- Create Form -->
<form action="/users" method="POST">
    <h2>Create New User</h2>
    <input type="text" name="name" placeholder="Name" required>
    <input type="email" name="email" placeholder="Email" required>
    <button type="submit">Create User</button>
</form>

<!-- Read (Display) -->
<div class="user-list">
    <h2>Users</h2>
    <!-- This would be populated by server-side rendering -->
    <div class="user-card">
        <h3>Alice Johnson</h3>
        <p>alice@example.com</p>
        <a href="/users/1">View Details</a>
        <a href="/users/1/edit">Edit</a>
        <form action="/users/1" method="POST" style="display: inline;">
            <input type="hidden" name="_method" value="DELETE">
            <button type="submit">Delete</button>
        </form>
    </div>
</div>

<!-- Update Form -->
<form action="/users/1" method="POST">
    <input type="hidden" name="_method" value="PUT">
    <h2>Edit User</h2>
    <input type="text" name="name" value="Alice Johnson" required>
    <input type="email" name="email" value="alice@example.com" required>
    <button type="submit">Update User</button>
</form>

<!-- Delete Confirmation -->
<form action="/users/1" method="POST">
    <input type="hidden" name="_method" value="DELETE">
    <h2>Delete User</h2>
    <p>Are you sure you want to delete Alice Johnson?</p>
    <button type="submit">Confirm Delete</button>
    <a href="/users">Cancel</a>
</form>

Server-Side CRUD Routes

Web servers expose CRUD operations through routes. While traditional forms only support GET and POST, servers often use method overrides or accept different HTTP methods.

// Express.js CRUD Routes
const express = require('express');
const app = express();

// CREATE - Handle form submission
app.post('/users', async (req, res) => {
    try {
        const newUser = await createUser(req.body);
        res.redirect(`/users/${newUser.id}`);
    } catch (error) {
        res.status(400).send(error.message);
    }
});

// READ - List all users
app.get('/users', async (req, res) => {
    const users = await getAllUsers();
    res.render('users/index', { users });
});

// READ - Get single user
app.get('/users/:id', async (req, res) => {
    const user = await getUserById(req.params.id);
    if (!user) {
        return res.status(404).send('User not found');
    }
    res.render('users/show', { user });
});

// UPDATE - Show edit form
app.get('/users/:id/edit', async (req, res) => {
    const user = await getUserById(req.params.id);
    if (!user) {
        return res.status(404).send('User not found');
    }
    res.render('users/edit', { user });
});

// UPDATE - Handle form submission
app.post('/users/:id', async (req, res) => {
    if (req.body._method === 'PUT') {
        try {
            await updateUser(req.params.id, req.body);
            res.redirect(`/users/${req.params.id}`);
        } catch (error) {
            res.status(400).send(error.message);
        }
    }
});

// DELETE - Handle deletion
app.post('/users/:id', async (req, res) => {
    if (req.body._method === 'DELETE') {
        try {
            await deleteUser(req.params.id);
            res.redirect('/users');
        } catch (error) {
            res.status(400).send(error.message);
        }
    }
});

Client-Side CRUD with JavaScript

Modern single-page applications perform CRUD operations using JavaScript and AJAX, providing a more dynamic user experience without page reloads.

// CRUD Manager Component
class UserManager {
    constructor(apiUrl) {
        this.apiUrl = apiUrl;
    }

    // CREATE
    async create(userData) {
        const response = await fetch(this.apiUrl, {
            method: 'POST',
            headers: { 'Content-Type': 'application/json' },
            body: JSON.stringify(userData)
        });

        if (!response.ok) {
            throw new Error('Failed to create user');
        }

        const newUser = await response.json();
        this.renderUser(newUser);
        return newUser;
    }

    // READ (all)
    async readAll() {
        const response = await fetch(this.apiUrl);
        const users = await response.json();
        this.renderUserList(users);
        return users;
    }

    // READ (one)
    async readOne(id) {
        const response = await fetch(`${this.apiUrl}/${id}`);
        if (!response.ok) {
            throw new Error('User not found');
        }
        return response.json();
    }

    // UPDATE
    async update(id, userData) {
        const response = await fetch(`${this.apiUrl}/${id}`, {
            method: 'PUT',
            headers: { 'Content-Type': 'application/json' },
            body: JSON.stringify(userData)
        });

        if (!response.ok) {
            throw new Error('Failed to update user');
        }

        const updatedUser = await response.json();
        this.renderUser(updatedUser);
        return updatedUser;
    }

    // DELETE
    async delete(id) {
        const confirmed = confirm('Are you sure you want to delete this user?');
        if (!confirmed) return;

        const response = await fetch(`${this.apiUrl}/${id}`, {
            method: 'DELETE'
        });

        if (!response.ok) {
            throw new Error('Failed to delete user');
        }

        this.removeUserFromUI(id);
        return { success: true };
    }

    renderUserList(users) {
        const container = document.getElementById('user-list');
        container.innerHTML = users.map(user => `
            <div class="user-card" data-id="${user.id}">
                <h3>${user.name}</h3>
                <p>${user.email}</p>
                <button onclick="userManager.edit(${user.id})">Edit</button>
                <button onclick="userManager.delete(${user.id})">Delete</button>
            </div>
        `).join('');
    }

    renderUser(user) {
        // Update or add user in the UI
    }

    removeUserFromUI(id) {
        const element = document.querySelector(`[data-id="${id}"]`);
        if (element) {
            element.remove();
        }
    }
}

// Usage
const userManager = new UserManager('/api/users');

// Create new user
document.getElementById('create-form').addEventListener('submit', async (e) => {
    e.preventDefault();
    const formData = new FormData(e.target);
    await userManager.create(Object.fromEntries(formData));
    e.target.reset();
});

// Load users on page load
userManager.readAll();

CRUD and REST

CRUD operations map directly to HTTP methods in RESTful APIs. This standardized mapping makes web APIs predictable and easy to understand.

See the REST Architecture page for more details on how REST implements CRUD over HTTP.

Benefits of CRUD

• Simplicity: Four operations cover most data management needs
• Predictability: Developers know what operations are available
• Standardization: Common vocabulary across different systems
• Completeness: Covers the entire data lifecycle
• Database Independence: Works with any storage system
• User Interface Clarity: Easy to design UIs around these operations

Challenges and Considerations

• Oversimplification: Not all operations fit neatly into CRUD (e.g., "approve", "publish", "archive")
• Business Logic: CRUD doesn't capture complex business rules
• Permissions: Different users may have different CRUD permissions
• Validation: Each operation needs appropriate validation
• Atomicity: Some operations need to be atomic (all-or-nothing)
• Audit Trail: Need to track who did what and when
• Soft vs Hard Delete: Decide whether deletions are permanent

Beyond Simple CRUD

While CRUD is fundamental, real-world applications often need additional operations:

// Extended CRUD operations
class ExtendedUserManager {
    // Standard CRUD
    async create(data) { /* ... */ }
    async read(id) { /* ... */ }
    async update(id, data) { /* ... */ }
    async delete(id) { /* ... */ }

    // Additional operations
    async archive(id) {
        // Not quite delete, not quite update
        await db.query(
            'UPDATE users SET status = ?, archived_at = ? WHERE id = ?',
            ['archived', new Date(), id]
        );
    }

    async restore(id) {
        // Undo soft delete or archive
        await db.query(
            'UPDATE users SET status = ?, archived_at = NULL WHERE id = ?',
            ['active', id]
        );
    }

    async activate(id) {
        // Business-specific state change
        await db.query(
            'UPDATE users SET is_active = ?, activated_at = ? WHERE id = ?',
            [true, new Date(), id]
        );
    }

    async search(criteria) {
        // More complex read operation
        const { name, email, role, minAge, maxAge } = criteria;
        // Build dynamic query
    }

    async bulkUpdate(ids, data) {
        // Update multiple records at once
        await db.query(
            'UPDATE users SET ? WHERE id IN (?)',
            [data, ids]
        );
    }
}

Key Takeaways

• CRUD represents the four fundamental operations: Create, Read, Update, Delete
• Most applications are built around CRUD operations on data
• CRUD applies to databases, files, in-memory storage, and APIs
• HTML forms naturally map to CRUD operations
• REST APIs use HTTP methods to implement CRUD over the web
• Real applications often extend CRUD with business-specific operations
• Consider soft deletes, validation, permissions, and audit trails
• CRUD is simple but powerful - it's the foundation of data management

See Also

• REST Architecture - How CRUD maps to HTTP methods
• Multi-Page Application - Traditional CRUD with server-side forms
• Single Page Application - JavaScript-based CRUD operations
• Event-Driven Architecture - CRUD operations can trigger events