← Back to Examples

CRUD Operations with Node.js and MySQL

Complete examples of Create, Read, Update, and Delete operations.

Database Setup

First, create the database and table:

-- Run this in MySQL
CREATE DATABASE IF NOT EXISTS school;
USE school;

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,
    grade VARCHAR(10),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO students (name, email, age, grade) VALUES
    ('Alice Johnson', 'alice@school.com', 20, 'A'),
    ('Bob Smith', 'bob@school.com', 22, 'B+'),
    ('Charlie Brown', 'charlie@school.com', 21, 'A-');

Database Connection Module

// db.js
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'school',
    waitForConnections: true,
    connectionLimit: 10
});

module.exports = pool;

const pool = require('./db');

async function createStudent(studentData) {
    const { name, email, age, grade } = studentData;

    try {
        const [result] = await pool.execute(
            'INSERT INTO students (name, email, age, grade) VALUES (?, ?, ?, ?)',
            [name, email, age, grade]
        );

        console.log('Student created with ID:', result.insertId);
        return {
            id: result.insertId,
            ...studentData
        };
    } catch (error) {
        if (error.code === 'ER_DUP_ENTRY') {
            throw new Error('Email already exists');
        }
        throw error;
    }
}

// Usage
createStudent({
    name: 'David Wilson',
    email: 'david@school.com',
    age: 19,
    grade: 'B'
}).then(student => console.log('Created:', student))
  .catch(err => console.error('Error:', err.message));

async function createManyStudents(studentsArray) {
    const values = studentsArray.map(s => [s.name, s.email, s.age, s.grade]);

    const [result] = await pool.query(
        'INSERT INTO students (name, email, age, grade) VALUES ?',
        [values]
    );

    console.log('Inserted', result.affectedRows, 'students');
    return result.affectedRows;
}

// Usage
createManyStudents([
    { name: 'Eve Davis', email: 'eve@school.com', age: 20, grade: 'A' },
    { name: 'Frank Miller', email: 'frank@school.com', age: 21, grade: 'B+' }
]);

async function getAllStudents() {
    const [rows] = await pool.execute('SELECT * FROM students ORDER BY name');
    return rows;
}

// Usage
getAllStudents().then(students => {
    console.log('All students:');
    students.forEach(s => console.log(`- ${s.name} (${s.email})`));
});

async function getStudentById(id) {
    const [rows] = await pool.execute(
        'SELECT * FROM students WHERE id = ?',
        [id]
    );

    if (rows.length === 0) {
        return null;
    }
    return rows[0];
}

// Usage
getStudentById(1).then(student => {
    if (student) {
        console.log('Found:', student.name);
    } else {
        console.log('Student not found');
    }
});

async function searchStudents(filters) {
    let query = 'SELECT * FROM students WHERE 1=1';
    const params = [];

    if (filters.name) {
        query += ' AND name LIKE ?';
        params.push(`%${filters.name}%`);
    }

    if (filters.minAge) {
        query += ' AND age >= ?';
        params.push(filters.minAge);
    }

    if (filters.maxAge) {
        query += ' AND age <= ?';
        params.push(filters.maxAge);
    }

    if (filters.grade) {
        query += ' AND grade = ?';
        params.push(filters.grade);
    }

    query += ' ORDER BY name';

    const [rows] = await pool.execute(query, params);
    return rows;
}

// Usage - Find students named "A*" with grade "A"
searchStudents({ name: 'A', grade: 'A' })
    .then(results => console.log('Found:', results.length, 'students'));

async function getStudentsPaginated(page = 1, limit = 10) {
    const offset = (page - 1) * limit;

    // Get total count
    const [countResult] = await pool.execute(
        'SELECT COUNT(*) as total FROM students'
    );
    const total = countResult[0].total;

    // Get paginated data
    const [rows] = await pool.execute(
        'SELECT * FROM students ORDER BY id LIMIT ? OFFSET ?',
        [limit, offset]
    );

    return {
        data: rows,
        pagination: {
            page,
            limit,
            total,
            totalPages: Math.ceil(total / limit)
        }
    };
}

// Usage - Get page 2 with 5 items per page
getStudentsPaginated(2, 5).then(result => {
    console.log('Page', result.pagination.page, 'of', result.pagination.totalPages);
    console.log('Students:', result.data.map(s => s.name));
});

async function updateStudentEmail(id, newEmail) {
    const [result] = await pool.execute(
        'UPDATE students SET email = ? WHERE id = ?',
        [newEmail, id]
    );

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

    return true;
}

// Usage
updateStudentEmail(1, 'alice.new@school.com')
    .then(() => console.log('Email updated'))
    .catch(err => console.error(err.message));

async function updateStudent(id, updates) {
    // Build dynamic query
    const fields = [];
    const values = [];

    if (updates.name !== undefined) {
        fields.push('name = ?');
        values.push(updates.name);
    }
    if (updates.email !== undefined) {
        fields.push('email = ?');
        values.push(updates.email);
    }
    if (updates.age !== undefined) {
        fields.push('age = ?');
        values.push(updates.age);
    }
    if (updates.grade !== undefined) {
        fields.push('grade = ?');
        values.push(updates.grade);
    }

    if (fields.length === 0) {
        throw new Error('No fields to update');
    }

    values.push(id); // Add id for WHERE clause

    const query = `UPDATE students SET ${fields.join(', ')} WHERE id = ?`;
    const [result] = await pool.execute(query, values);

    return result.affectedRows > 0;
}

// Usage - Update name and grade
updateStudent(2, { name: 'Robert Smith', grade: 'A' })
    .then(updated => console.log('Updated:', updated));

async function upsertStudent(student) {
    const { email, name, age, grade } = student;

    const [result] = await pool.execute(
        `INSERT INTO students (name, email, age, grade)
         VALUES (?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE
         name = VALUES(name),
         age = VALUES(age),
         grade = VALUES(grade)`,
        [name, email, age, grade]
    );

    // affectedRows: 1 = inserted, 2 = updated
    return {
        action: result.affectedRows === 1 ? 'inserted' : 'updated',
        id: result.insertId || null
    };
}

// Usage - Will update if email exists, insert if not
upsertStudent({
    email: 'alice@school.com',
    name: 'Alice Johnson Updated',
    age: 21,
    grade: 'A+'
});

async function deleteStudent(id) {
    const [result] = await pool.execute(
        'DELETE FROM students WHERE id = ?',
        [id]
    );

    if (result.affectedRows === 0) {
        return { deleted: false, message: 'Student not found' };
    }

    return { deleted: true, message: 'Student deleted successfully' };
}

// Usage
deleteStudent(5).then(result => console.log(result.message));

-- First, add a deleted_at column to your table
-- ALTER TABLE students ADD COLUMN deleted_at TIMESTAMP NULL;

async function softDeleteStudent(id) {
    const [result] = await pool.execute(
        'UPDATE students SET deleted_at = NOW() WHERE id = ? AND deleted_at IS NULL',
        [id]
    );

    return result.affectedRows > 0;
}

// Get only active students
async function getActiveStudents() {
    const [rows] = await pool.execute(
        'SELECT * FROM students WHERE deleted_at IS NULL ORDER BY name'
    );
    return rows;
}

// Restore deleted student
async function restoreStudent(id) {
    const [result] = await pool.execute(
        'UPDATE students SET deleted_at = NULL WHERE id = ?',
        [id]
    );
    return result.affectedRows > 0;
}

async function deleteOldRecords(daysOld) {
    const [result] = await pool.execute(
        `DELETE FROM students
         WHERE created_at < DATE_SUB(NOW(), INTERVAL ? DAY)`,
        [daysOld]
    );

    console.log(`Deleted ${result.affectedRows} old records`);
    return result.affectedRows;
}

// Usage - Delete records older than 365 days
deleteOldRecords(365);

Complete CRUD Module

// studentModel.js - Complete CRUD module
const pool = require('./db');

const StudentModel = {
    // CREATE
    async create(data) {
        const { name, email, age, grade } = data;
        const [result] = await pool.execute(
            'INSERT INTO students (name, email, age, grade) VALUES (?, ?, ?, ?)',
            [name, email, age, grade]
        );
        return { id: result.insertId, ...data };
    },

    // READ
    async findAll() {
        const [rows] = await pool.execute('SELECT * FROM students ORDER BY name');
        return rows;
    },

    async findById(id) {
        const [rows] = await pool.execute('SELECT * FROM students WHERE id = ?', [id]);
        return rows[0] || null;
    },

    async findByEmail(email) {
        const [rows] = await pool.execute('SELECT * FROM students WHERE email = ?', [email]);
        return rows[0] || null;
    },

    // UPDATE
    async update(id, data) {
        const { name, email, age, grade } = data;
        const [result] = await pool.execute(
            'UPDATE students SET name = ?, email = ?, age = ?, grade = ? WHERE id = ?',
            [name, email, age, grade, id]
        );
        return result.affectedRows > 0;
    },

    // DELETE
    async delete(id) {
        const [result] = await pool.execute('DELETE FROM students WHERE id = ?', [id]);
        return result.affectedRows > 0;
    }
};

module.exports = StudentModel;

← Back to Examples