✦ CRUD Operations in Node.js with Express and a Database

❖ Introduction

CRUD is one of the most important concepts in backend development. Almost every database-driven application uses CRUD operations to manage information.

CRUD stands for:

• Create: Add new records to the database.

• Read: Retrieve existing records.

• Update: Modify saved records.

• Delete: Remove records from the database.

For example, a Student Management System needs CRUD operations to register students, display student details, edit their information, and delete records when required.

Node.js provides the runtime environment for backend JavaScript applications, while Express.js makes it easier to create API routes. MySQL stores application data permanently.

In this tutorial, you will build a practical Student Management API using Node.js, Express.js, and MySQL.

✦ 1. Prerequisites

Before starting, install:

• Node.js and npm

• MySQL Server or XAMPP

• Visual Studio Code

• Postman for API testing

You should understand basic JavaScript, functions, objects, and HTTP methods.

✦ 2. Create a Node.js Project

Open your terminal:

mkdir node-crud-project
cd node-crud-project
npm init -y
npm install express mysql2 dotenv

Create this project structure:

node-crud-project/
├── server.js
├── .env
├── .gitignore
└── package.json

Package explanation:

• express: Creates the backend server and API routes.

• mysql2: Connects Node.js to MySQL.

• dotenv: Loads database credentials from environment variables.

✦ 3. Create a MySQL Database

Open phpMyAdmin, MySQL Workbench, or your MySQL command-line client.

Execute:

CREATE DATABASE crud_db;

USE crud_db;

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    mobile VARCHAR(15) NOT NULL,
    course VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Your database now contains a students table.

The table has four important fields:

Field	Purpose
id	Unique student ID
name	Student name
mobile	Student mobile number
course	Student course

The id field automatically generates a unique number whenever a new student is inserted.

✦ 4. Configure Database Connection

Create a .env file:

PORT=3000
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=
DB_NAME=crud_db
DB_PORT=3306

These are example local settings. If your MySQL account has a password or uses another port, update the values accordingly.

Create .gitignore:

node_modules/
.env

This helps keep private database credentials out of your Git repository.

✦ 5. Connect Node.js to MySQL

Open server.js and add the following complete code:

require("dotenv").config();

const express = require("express");
const mysql = require("mysql2/promise");

const app = express();

app.use(express.json());

// MySQL connection pool
const db = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    port: Number(process.env.DB_PORT || 3306),
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// Check API and database connection
app.get("/", async (req, res) => {
    try {
        await db.query("SELECT 1");

        res.json({
            success: true,
            message: "Node.js CRUD API connected to MySQL"
        });
    } catch (error) {
        console.error(error.message);

        res.status(500).json({
            success: false,
            message: "Database connection failed"
        });
    }
});

// CREATE: Add Student
app.post("/students", async (req, res) => {
    const { name, mobile, course } = req.body;

    if (
        typeof name !== "string" ||
        typeof mobile !== "string" ||
        typeof course !== "string" ||
        !name.trim() ||
        !mobile.trim() ||
        !course.trim()
    ) {
        return res.status(400).json({
            success: false,
            message: "Name, mobile and course are required"
        });
    }

    if (
        name.trim().length > 100 ||
        mobile.trim().length > 15 ||
        course.trim().length > 100
    ) {
        return res.status(400).json({
            success: false,
            message: "One or more fields exceed the allowed length"
        });
    }

    try {
        const [result] = await db.execute(
            "INSERT INTO students (name, mobile, course) VALUES (?, ?, ?)",
            [name.trim(), mobile.trim(), course.trim()]
        );

        res.status(201).json({
            success: true,
            message: "Student added successfully",
            studentId: result.insertId
        });
    } catch (error) {
        console.error("Insert error:", error.message);

        res.status(500).json({
            success: false,
            message: "Unable to add student"
        });
    }
});

// READ: Get All Students
app.get("/students", async (req, res) => {
    try {
        const [students] = await db.execute(
            "SELECT * FROM students ORDER BY id DESC"
        );

        res.json({
            success: true,
            data: students
        });
    } catch (error) {
        console.error("Fetch error:", error.message);

        res.status(500).json({
            success: false,
            message: "Unable to fetch students"
        });
    }
});

// READ: Get Student By ID
app.get("/students/:id", async (req, res) => {
    const id = Number(req.params.id);

    if (!Number.isInteger(id) || id < 1) {
        return res.status(400).json({
            success: false,
            message: "Valid student ID is required"
        });
    }

    try {
        const [students] = await db.execute(
            "SELECT * FROM students WHERE id = ?",
            [id]
        );

        if (students.length === 0) {
            return res.status(404).json({
                success: false,
                message: "Student not found"
            });
        }

        res.json({
            success: true,
            data: students[0]
        });
    } catch (error) {
        console.error("Fetch error:", error.message);

        res.status(500).json({
            success: false,
            message: "Unable to fetch student"
        });
    }
});

// UPDATE: Edit Student
app.put("/students/:id", async (req, res) => {
    const id = Number(req.params.id);
    const { name, mobile, course } = req.body;

    if (!Number.isInteger(id) || id < 1) {
        return res.status(400).json({
            success: false,
            message: "Valid student ID is required"
        });
    }

    if (
        typeof name !== "string" ||
        typeof mobile !== "string" ||
        typeof course !== "string" ||
        !name.trim() ||
        !mobile.trim() ||
        !course.trim()
    ) {
        return res.status(400).json({
            success: false,
            message: "Name, mobile and course are required"
        });
    }

    if (
        name.trim().length > 100 ||
        mobile.trim().length > 15 ||
        course.trim().length > 100
    ) {
        return res.status(400).json({
            success: false,
            message: "One or more fields exceed the allowed length"
        });
    }

    try {
        const [result] = await db.execute(
            `UPDATE students
             SET name = ?, mobile = ?, course = ?
             WHERE id = ?`,
            [name.trim(), mobile.trim(), course.trim(), id]
        );

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

        res.json({
            success: true,
            message: "Student updated successfully"
        });
    } catch (error) {
        console.error("Update error:", error.message);

        res.status(500).json({
            success: false,
            message: "Unable to update student"
        });
    }
});

// DELETE: Remove Student
app.delete("/students/:id", async (req, res) => {
    const id = Number(req.params.id);

    if (!Number.isInteger(id) || id < 1) {
        return res.status(400).json({
            success: false,
            message: "Valid student ID is required"
        });
    }

    try {
        const [result] = await db.execute(
            "DELETE FROM students WHERE id = ?",
            [id]
        );

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

        res.json({
            success: true,
            message: "Student deleted successfully"
        });
    } catch (error) {
        console.error("Delete error:", error.message);

        res.status(500).json({
            success: false,
            message: "Unable to delete student"
        });
    }
});

// Start server after checking database
async function startServer() {
    try {
        await db.query("SELECT 1");

        const PORT = Number(process.env.PORT || 3000);

        app.listen(PORT, () => {
            console.log(`Server running at http://localhost:${PORT}`);
        });
    } catch (error) {
        console.error("Database connection failed:", error.message);
        process.exitCode = 1;
    }
}

startServer();

This single file contains all five API routes: one connection check and four CRUD operations.

✦ 6. Understand the Four CRUD Operations

❖ A. CREATE – Insert a New Student

The CREATE operation adds a new record to the database.

API method: POST

URL:

http://localhost:3000/students

Postman settings:

1. Select POST.

2. Open Body.

3. Select raw.

4. Choose JSON.

5. Enter the following data:

{
    "name": "Aman Verma",
    "mobile": "9123456780",
    "course": "Node.js"
}

The server inserts the data using:

INSERT INTO students (name, mobile, course)
VALUES (?, ?, ?);

A successful request returns a response similar to:

{
    "success": true,
    "message": "Student added successfully",
    "studentId": 1
}

The generated ID depends on the existing database records.

❖ B. READ – Display Student Records

The READ operation retrieves records from MySQL.

API method: GET

URL:

http://localhost:3000/students

The database query is:

SELECT * FROM students;

Example response:

{
    "success": true,
    "data": [
        {
            "id": 1,
            "name": "Aman Verma",
            "mobile": "9123456780",
            "course": "Node.js"
        }
    ]
}

You can also retrieve a single student using:

GET http://localhost:3000/students/1

❖ C. UPDATE – Edit Student Details

The UPDATE operation modifies an existing record.

API method: PUT

URL:

http://localhost:3000/students/1

Send the updated JSON:

{
    "name": "Aman Verma",
    "mobile": "9123456780",
    "course": "Full Stack Development"
}

The SQL query is:

UPDATE students
SET name = ?, mobile = ?, course = ?
WHERE id = ?;

The WHERE id = ? condition ensures that the intended student record is updated rather than every record in the table.

❖ D. DELETE – Remove Student Details

The DELETE operation removes an existing record.

API method: DELETE

URL:

http://localhost:3000/students/1

The SQL query is:

DELETE FROM students WHERE id = ?;

A successful response:

{
    "success": true,
    "message": "Student deleted successfully"
}

Be careful when deleting records. In a real application, you may want authentication, authorization, confirmation, or a soft-delete approach.

✦ 7. Start and Test Your Project

Run the application:

node server.js

You should see:

Server running at http://localhost:3000

Open this URL:

http://localhost:3000/

You can now test the API using Postman.

Operation	Method	Endpoint
Create	POST	/students
Read all	GET	/students
Read one	GET	/students/:id
Update	PUT	/students/:id
Delete	DELETE	/students/:id

✦ 8. Important Concepts Used in This Project

❖ Express.js Routes

Routes define how the application responds to HTTP requests.

app.get("/students", async (req, res) => {
    // Retrieve students
});

❖ Request Body

req.body contains data sent by the client. The middleware express.json() parses JSON request bodies.

const { name, mobile, course } = req.body;

❖ Route Parameters

req.params retrieves values from the URL.

app.get("/students/:id", (req, res) => {
    console.log(req.params.id);
});

❖ Async and Await

Database queries take time to complete. async and await allow you to handle these operations without blocking the entire application.

❖ Parameterized Queries

Using ? placeholders separates user values from SQL query structure and helps prevent SQL injection.

✦ 9. Common CRUD Errors

❖ Cannot POST /students

Check the API URL and HTTP method. Make sure the POST route is present in server.js.

❖ Database connection refused

Check whether MySQL is running and whether your host and port are correct.

❖ Unknown database

Create crud_db and verify the database name in .env.

❖ req.body is undefined

Make sure app.use(express.json()) appears before your routes and that Postman sends JSON with the appropriate content type.

❖ Student not found

Check whether the requested ID exists in the database.

❖ Invalid input

Send all required fields using the expected data types and ensure they are not empty.

✦ 10. Best Practices for CRUD Applications

• Validate all incoming data on the server.

• Use parameterized database queries.

• Keep database credentials in environment variables.

• Use authentication and role-based authorization for sensitive operations.

• Return appropriate HTTP status codes.

• Avoid exposing internal database errors to users.

• Add pagination for large datasets.

• Back up the database regularly.

• Separate routes, controllers, and database logic as the application grows.

✦ Frequently Asked Questions (FAQs)

❖ 1. What does CRUD stand for?

CRUD stands for Create, Read, Update, and Delete. These are the four basic operations used to manage database records.

❖ 2. Can I create CRUD APIs using Node.js?

Yes. Node.js with Express.js can create REST APIs that perform database operations using MySQL, MongoDB, PostgreSQL, and other supported databases.

❖ 3. Which HTTP methods are used for CRUD?

POST is commonly used for Create, GET for Read, PUT or PATCH for Update, and DELETE for Delete.

❖ 4. Is MySQL required for a Node.js CRUD application?

No. MySQL is one option. You can also use MongoDB or another database depending on your project requirements.

❖ 5. Can I connect a frontend form to this CRUD API?

Yes. HTML, CSS, JavaScript, Bootstrap, React, or another frontend can send HTTP requests to these API endpoints using fetch() or an HTTP client.

✦ Conclusion

CRUD operations are the foundation of database-driven backend applications. In this tutorial, you learned how to create a Node.js project, connect it to MySQL, and implement Create, Read, Update, and Delete operations using Express.js.

You can use this foundation to build practical applications such as Student Management Systems, School Management Software, Inventory Management Systems, Billing Software, and CRM applications.

To learn practical programming and backend development, visit Param Classes. For website development and digital solutions, visit ParamWebInfo.