Full-Stack CRUD Walkthrough

Complete code for a Student Management System with Express backend, MySQL database, and vanilla JavaScript frontend.

Architecture Overview

Project Structure

1 setup.sql - Database Setup

-- Run this in MySQL Workbench or command line
CREATE DATABASE IF NOT EXISTS student_manager;
USE student_manager;

CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    age INT CHECK (age > 0 AND age < 150),
    course VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Sample data
INSERT INTO students (name, email, age, course) VALUES
    ('Alice Johnson', 'alice@university.com', 20, 'Computer Science'),
    ('Bob Smith', 'bob@university.com', 22, 'Information Technology'),
    ('Charlie Brown', 'charlie@university.com', 21, 'Electronics'),
    ('Diana Prince', 'diana@university.com', 19, 'Computer Science'),
    ('Edward Norton', 'edward@university.com', 23, 'Mathematics');

2 backend/package.json

{
    "name": "student-manager-backend",
    "version": "1.0.0",
    "scripts": {
        "start": "node server.js",
        "dev": "nodemon server.js"
    },
    "dependencies": {
        "express": "^4.18.2",
        "mysql2": "^3.6.0",
        "cors": "^2.8.5",
        "dotenv": "^16.3.1"
    }
}

Run: cd backend && npm install

3 backend/.env

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_password
DB_NAME=student_manager
PORT=3000

4 backend/server.js - Complete Backend

// server.js - Complete Express backend
require('dotenv').config();
const express = require('express');
const cors = require('cors');
const mysql = require('mysql2/promise');

const app = express();
const PORT = process.env.PORT || 3000;

// Middleware
app.use(cors());
app.use(express.json());

// Database connection pool
const pool = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    waitForConnections: true,
    connectionLimit: 10
});

// Test database connection
pool.getConnection()
    .then(conn => { console.log('Database connected'); conn.release(); })
    .catch(err => { console.error('DB Error:', err.message); process.exit(1); });

// ============================
// ROUTES
// ============================

// GET /api/students - List all students
app.get('/api/students', async (req, res) => {
    try {
        let query = 'SELECT * FROM students';
        const params = [];

        // Search functionality
        if (req.query.search) {
            query += ' WHERE name LIKE ? OR email LIKE ? OR course LIKE ?';
            const term = `%${req.query.search}%`;
            params.push(term, term, term);
        }

        query += ' ORDER BY id DESC';
        const [rows] = await pool.execute(query, params);

        res.json({ success: true, count: rows.length, data: rows });
    } catch (error) {
        console.error('GET /students error:', error);
        res.status(500).json({ success: false, error: 'Failed to fetch students' });
    }
});

// GET /api/students/:id - Get single student
app.get('/api/students/:id', async (req, res) => {
    try {
        const [rows] = await pool.execute('SELECT * FROM students WHERE id = ?', [req.params.id]);

        if (rows.length === 0) {
            return res.status(404).json({ success: false, error: 'Student not found' });
        }

        res.json({ success: true, data: rows[0] });
    } catch (error) {
        res.status(500).json({ success: false, error: 'Failed to fetch student' });
    }
});

// POST /api/students - Create student
app.post('/api/students', async (req, res) => {
    try {
        const { name, email, age, course } = req.body;

        // Validation
        if (!name || !email) {
            return res.status(400).json({ success: false, error: 'Name and email are required' });
        }

        const [result] = await pool.execute(
            'INSERT INTO students (name, email, age, course) VALUES (?, ?, ?, ?)',
            [name, email, age || null, course || null]
        );

        res.status(201).json({
            success: true,
            message: 'Student created',
            data: { id: result.insertId, name, email, age, course }
        });
    } catch (error) {
        if (error.code === 'ER_DUP_ENTRY') {
            return res.status(400).json({ success: false, error: 'Email already exists' });
        }
        res.status(500).json({ success: false, error: 'Failed to create student' });
    }
});

// PUT /api/students/:id - Update student
app.put('/api/students/:id', async (req, res) => {
    try {
        const { name, email, age, course } = req.body;
        const id = req.params.id;

        // Check if exists
        const [existing] = await pool.execute('SELECT * FROM students WHERE id = ?', [id]);
        if (existing.length === 0) {
            return res.status(404).json({ success: false, error: 'Student not found' });
        }

        const student = existing[0];
        const [result] = await pool.execute(
            'UPDATE students SET name = ?, email = ?, age = ?, course = ? WHERE id = ?',
            [
                name || student.name,
                email || student.email,
                age !== undefined ? age : student.age,
                course !== undefined ? course : student.course,
                id
            ]
        );

        res.json({ success: true, message: 'Student updated' });
    } catch (error) {
        if (error.code === 'ER_DUP_ENTRY') {
            return res.status(400).json({ success: false, error: 'Email already exists' });
        }
        res.status(500).json({ success: false, error: 'Failed to update student' });
    }
});

// DELETE /api/students/:id - Delete student
app.delete('/api/students/:id', async (req, res) => {
    try {
        const [result] = await pool.execute('DELETE FROM students WHERE id = ?', [req.params.id]);

        if (result.affectedRows === 0) {
            return res.status(404).json({ success: false, error: 'Student not found' });
        }

        res.json({ success: true, message: 'Student deleted' });
    } catch (error) {
        res.status(500).json({ success: false, error: 'Failed to delete student' });
    }
});

// Health check
app.get('/health', (req, res) => {
    res.json({ status: 'OK', timestamp: new Date().toISOString() });
});

// Start server
app.listen(PORT, () => {
    console.log(`Server running at http://localhost:${PORT}`);
});

5 frontend/index.html - User Interface

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Student Manager</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <nav class="navbar navbar-dark bg-dark">
        <div class="container">
            <span class="navbar-brand">Student Manager</span>
        </div>
    </nav>

    <div class="container mt-4">
        <!-- Alert for messages -->
        <div id="alertBox" class="alert d-none" role="alert"></div>

        <!-- Add Student Form -->
        <div class="card mb-4">
            <div class="card-header bg-primary text-white">Add New Student</div>
            <div class="card-body">
                <form id="studentForm">
                    <div class="row g-3">
                        <div class="col-md-3">
                            <input type="text" id="name" class="form-control" placeholder="Name" required>
                        </div>
                        <div class="col-md-3">
                            <input type="email" id="email" class="form-control" placeholder="Email" required>
                        </div>
                        <div class="col-md-2">
                            <input type="number" id="age" class="form-control" placeholder="Age">
                        </div>
                        <div class="col-md-2">
                            <input type="text" id="course" class="form-control" placeholder="Course">
                        </div>
                        <div class="col-md-2">
                            <button type="submit" class="btn btn-primary w-100">Add</button>
                        </div>
                    </div>
                </form>
            </div>
        </div>

        <!-- Search -->
        <div class="input-group mb-3">
            <input type="text" id="searchInput" class="form-control" placeholder="Search students...">
            <button class="btn btn-outline-secondary" onclick="loadStudents()">Search</button>
        </div>

        <!-- Student Table -->
        <div class="table-responsive">
            <table class="table table-striped table-hover">
                <thead class="table-dark">
                    <tr>
                        <th>ID</th><th>Name</th><th>Email</th>
                        <th>Age</th><th>Course</th><th>Actions</th>
                    </tr>
                </thead>
                <tbody id="studentList"></tbody>
            </table>
        </div>
    </div>

    <script src="app.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

6 frontend/app.js - Frontend Logic

// app.js - Frontend API integration
const API_URL = 'http://localhost:3000/api/students';

// Load on page ready
document.addEventListener('DOMContentLoaded', () => {
    loadStudents();
    document.getElementById('studentForm').addEventListener('submit', addStudent);
    document.getElementById('searchInput').addEventListener('keypress', (e) => {
        if (e.key === 'Enter') loadStudents();
    });
});

// Show alert messages
function showAlert(message, type = 'success') {
    const alertBox = document.getElementById('alertBox');
    alertBox.className = `alert alert-${type}`;
    alertBox.textContent = message;
    alertBox.classList.remove('d-none');
    setTimeout(() => alertBox.classList.add('d-none'), 3000);
}

// GET - Load all students
async function loadStudents() {
    const tbody = document.getElementById('studentList');
    const search = document.getElementById('searchInput').value;

    tbody.innerHTML = '<tr><td colspan="6" class="text-center">Loading...</td></tr>';

    try {
        let url = API_URL;
        if (search) url += `?search=${encodeURIComponent(search)}`;

        const response = await fetch(url);
        const result = await response.json();

        if (result.data.length === 0) {
            tbody.innerHTML = '<tr><td colspan="6" class="text-center text-muted">No students found</td></tr>';
            return;
        }

        tbody.innerHTML = result.data.map(s => `
            <tr>
                <td>${s.id}</td>
                <td>${escapeHtml(s.name)}</td>
                <td>${escapeHtml(s.email)}</td>
                <td>${s.age || '-'}</td>
                <td>${escapeHtml(s.course || '-')}</td>
                <td>
                    <button class="btn btn-warning btn-sm" onclick="editStudent(${s.id})">Edit</button>
                    <button class="btn btn-danger btn-sm" onclick="deleteStudent(${s.id})">Delete</button>
                </td>
            </tr>
        `).join('');
    } catch (error) {
        tbody.innerHTML = '<tr><td colspan="6" class="text-center text-danger">Error loading data</td></tr>';
        showAlert('Failed to load students: ' + error.message, 'danger');
    }
}

// POST - Add new student
async function addStudent(e) {
    e.preventDefault();

    const data = {
        name: document.getElementById('name').value,
        email: document.getElementById('email').value,
        age: document.getElementById('age').value || null,
        course: document.getElementById('course').value || null
    };

    try {
        const response = await fetch(API_URL, {
            method: 'POST',
            headers: { 'Content-Type': 'application/json' },
            body: JSON.stringify(data)
        });

        const result = await response.json();

        if (!response.ok) {
            showAlert(result.error, 'danger');
            return;
        }

        showAlert('Student added successfully!');
        document.getElementById('studentForm').reset();
        loadStudents();
    } catch (error) {
        showAlert('Failed to add student', 'danger');
    }
}

// PUT - Edit student
async function editStudent(id) {
    const name = prompt('Enter new name:');
    if (!name) return;

    const email = prompt('Enter new email:');
    if (!email) return;

    try {
        const response = await fetch(`${API_URL}/${id}`, {
            method: 'PUT',
            headers: { 'Content-Type': 'application/json' },
            body: JSON.stringify({ name, email })
        });

        const result = await response.json();

        if (!response.ok) {
            showAlert(result.error, 'danger');
            return;
        }

        showAlert('Student updated!');
        loadStudents();
    } catch (error) {
        showAlert('Failed to update student', 'danger');
    }
}

// DELETE - Remove student
async function deleteStudent(id) {
    if (!confirm('Are you sure you want to delete this student?')) return;

    try {
        const response = await fetch(`${API_URL}/${id}`, { method: 'DELETE' });
        const result = await response.json();

        if (!response.ok) {
            showAlert(result.error, 'danger');
            return;
        }

        showAlert('Student deleted');
        loadStudents();
    } catch (error) {
        showAlert('Failed to delete student', 'danger');
    }
}

// Prevent XSS
function escapeHtml(text) {
    const div = document.createElement('div');
    div.textContent = text;
    return div.innerHTML;
}

Full-Stack CRUD Application Walkthrough

Architecture Overview

Frontend

Backend

Database

Project Structure

Running the Application

Summary