Master Node.js From Scratch

Clear, interactive, and structured Node.js lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to Node.js
Introduction to Node.js What Is Node.js? Node.js Architecture Features of Node.js Node.js vs Browser JavaScript Node.js Use Cases
2. Installation and Setup
Installation Introduction LTS and Current Versions npm Setup Environment Setup Verify Installation Node.js REPL
3. Node.js Fundamentals
Fundamentals Introduction Running JavaScript Global Objects Process Object Command-Line Arguments Environment Variables
4. JavaScript Basics
JavaScript Basics Introduction Variables: var, let and const JavaScript Data Types Comparison and Logical Operators If, Else If, Else and Switch Loops and Iteration Functions and Arrow Functions JavaScript Array Methods
5. Node.js Modules
Modules Introduction CommonJS Modules ES Modules Export and Import Built-in Modules Creating Custom Modules Path Module OS Module
6. Working with JSON
JSON Introduction JSON Syntax JSON.stringify() JSON.parse() JSON Array of Objects JSON User Database
7. Node Package Manager
npm Introduction package.json Installing Packages Dependencies and DevDependencies npm Scripts Package Versions and Semantic Versioning npm init and Project Setup
8. File System
File System Introduction Reading Files Writing Files Appending Files Deleting Files Working with Directories
9. CLI Applications
CLI Applications Introduction Command-Line Input Task Manager Project Structure Reading todos.json Adding, Updating and Deleting Tasks Saving Tasks to JSON
10. Events and EventEmitter
Events Introduction EventEmitter Registering Events Emitting Events Removing Event Listeners Custom Events
11. Asynchronous Programming
Async Programming Introduction Callbacks Promises async and await setTimeout() and setInterval() Event Loop and Non-Blocking I/O
12. Native HTTP Module
HTTP Module Introduction Creating an HTTP Server Handling Requests Sending Responses HTTP Request Methods Status Codes and Headers Handling POST Request Body Parsing Incoming JSON Products JSON API
13. Express.js
Express.js Introduction Installing Express Creating an Express Server Request and Response Objects Route Parameters and Query Strings Serving Static Files
14. Express Middleware
Middleware Introduction Built-in Middleware Application-Level Middleware Router-Level Middleware Custom Middleware Error-Handling Middleware
15. REST API Development
REST API Introduction GET Endpoints POST Endpoints PUT and PATCH Endpoints DELETE Endpoints Request Validation and API Responses
16. Database Integration
Database Introduction MySQL Integration MongoDB Integration Database CRUD Operations Database Queries and Filtering Connection Management
17. Authentication and Authorization
Authentication Introduction Password Hashing User Registration User Login JWT Authentication Authorization and Protected Routes
18. Advanced Node.js
Advanced Node.js Introduction Error Handling Debugging Node.js Applications Streams Buffers Performance and Security

Database CRUD Operations in Node.js

Learn how to Create, Read, Update, and Delete database records using Node.js, Express.js, and MySQL.

CRUD represents the four basic operations performed on database records. Almost every real-world application uses CRUD operations to manage information such as students, employees, products, and users.

In this tutorial, we will create a simple Product API using Node.js, Express.js, and the mysql2 package. We will use parameterized SQL queries to interact safely with MySQL.

1. What Are CRUD Operations?

CRUD stands for Create, Read, Update, and Delete. Each operation performs a specific action on database records.

Operation Purpose SQL Command HTTP Method
Create Add a new record INSERT POST
Read Retrieve existing records SELECT GET
Update Modify an existing record UPDATE PUT / PATCH
Delete Remove a record DELETE DELETE
Remember: CRUD describes database operations. HTTP methods are commonly used by REST APIs to expose those operations to clients.

2. Install Required Packages

First, create a Node.js project and install Express.js and mysql2. Express handles HTTP requests, while mysql2 connects Node.js to MySQL.

Terminal
npm init -y
npm install express mysql2

Create a file named server.js.

3. Create the Database and Table

Before writing the API, create a database named crud_demo and a table named products. Each product will have an ID, name, and price.

SQL
CREATE DATABASE crud_demo;

USE crud_demo;

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

The id column automatically generates a unique number for every new product.

4. Configure the MySQL Connection

Import Express and mysql2, create an Express application, and configure a connection pool to manage database connections.

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

const app = express();

app.use(express.json());

const pool = mysql.createPool({
    host: "localhost",
    user: "root",
    password: "YOUR_MYSQL_PASSWORD",
    database: "crud_demo",
    waitForConnections: true,
    connectionLimit: 10
});
Important: Replace YOUR_MYSQL_PASSWORD with your local MySQL password. In production, store database credentials in environment variables instead of hardcoding them.

The express.json() middleware allows Express to read JSON data sent in the request body.

5. CREATE Operation — Insert a Record

The CREATE operation adds a new product to the database. In a REST API, we commonly use the HTTP POST method.

JavaScript
app.post("/api/products", async (req, res) => {
    try {
        const { name, price } = req.body;

        if (
            typeof name !== "string" ||
            name.trim() === "" ||
            price === undefined ||
            price === null ||
            price === "" ||
            !Number.isFinite(Number(price)) ||
            Number(price) < 0
        ) {
            return res.status(400).json({
                message: "Valid product name and price are required."
            });
        }

        const [result] = await pool.execute(
            "INSERT INTO products (name, price) VALUES (?, ?)",
            [name.trim(), Number(price)]
        );

        res.status(201).json({
            message: "Product created successfully",
            productId: result.insertId
        });
    } catch (error) {
        console.error(error);

        res.status(500).json({
            message: "Unable to create product."
        });
    }
});

Here, INSERT INTO adds the record. The question marks are placeholders, and the values are supplied separately to prevent user input from being treated as SQL code.

Example request:

JSON
{
    "name": "Keyboard",
    "price": 1200
}

Example response:

JSON
{
    "message": "Product created successfully",
    "productId": 1
}

6. READ Operation — Retrieve Records

The READ operation retrieves information from the database. We can create one endpoint to get all products and another endpoint to get a product by its ID.

A. Get All Products

JavaScript
app.get("/api/products", async (req, res) => {
    try {
        const [products] = await pool.execute(
            "SELECT id, name, price FROM products ORDER BY id"
        );

        res.status(200).json(products);
    } catch (error) {
        console.error(error);

        res.status(500).json({
            message: "Unable to retrieve products."
        });
    }
});

The SELECT statement retrieves product records. The API returns the records as a JSON array.

B. Get a Product by ID

JavaScript
app.get("/api/products/:id", async (req, res) => {
    try {
        const id = Number(req.params.id);

        if (!Number.isSafeInteger(id) || id <= 0) {
            return res.status(400).json({
                message: "A valid product ID is required."
            });
        }

        const [products] = await pool.execute(
            "SELECT id, name, price FROM products WHERE id = ?",
            [id]
        );

        if (products.length === 0) {
            return res.status(404).json({
                message: "Product not found."
            });
        }

        res.status(200).json(products[0]);
    } catch (error) {
        console.error(error);

        res.status(500).json({
            message: "Unable to retrieve product."
        });
    }
});

The :id route parameter identifies the requested product. If the record does not exist, the API returns 404 Not Found.

7. UPDATE Operation — Modify a Record

The UPDATE operation changes an existing product. PUT is commonly used when replacing the editable fields of a resource, while PATCH is used for partial updates.

A. PUT — Replace Editable Fields

JavaScript
app.put("/api/products/:id", async (req, res) => {
    try {
        const id = Number(req.params.id);
        const { name, price } = req.body;

        if (!Number.isSafeInteger(id) || id <= 0) {
            return res.status(400).json({
                message: "A valid product ID is required."
            });
        }

        if (
            typeof name !== "string" ||
            name.trim() === "" ||
            price === undefined ||
            price === null ||
            price === "" ||
            !Number.isFinite(Number(price)) ||
            Number(price) < 0
        ) {
            return res.status(400).json({
                message: "Valid product name and price are required."
            });
        }

        const [result] = await pool.execute(
            "UPDATE products SET name = ?, price = ? WHERE id = ?",
            [name.trim(), Number(price), id]
        );

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

        res.status(200).json({
            message: "Product updated successfully."
        });
    } catch (error) {
        console.error(error);

        res.status(500).json({
            message: "Unable to update product."
        });
    }
});

This endpoint expects both name and price. If a record exists but the submitted values are identical to its current values, MySQL may report zero changed rows; for a strict not-found check, verify the record's existence separately.

B. PATCH — Update Selected Fields

PATCH allows the client to send only the fields it wants to change. The following example supports updating the name, the price, or both.

JavaScript
app.patch("/api/products/:id", async (req, res) => {
    try {
        const id = Number(req.params.id);

        if (!Number.isSafeInteger(id) || id <= 0) {
            return res.status(400).json({
                message: "A valid product ID is required."
            });
        }

        const fields = [];
        const values = [];

        if (Object.prototype.hasOwnProperty.call(req.body, "name")) {
            if (
                typeof req.body.name !== "string" ||
                req.body.name.trim() === ""
            ) {
                return res.status(400).json({
                    message: "Name must be a non-empty string."
                });
            }

            fields.push("name = ?");
            values.push(req.body.name.trim());
        }

        if (Object.prototype.hasOwnProperty.call(req.body, "price")) {
            const price = req.body.price;

            if (
                price === null ||
                price === "" ||
                !Number.isFinite(Number(price)) ||
                Number(price) < 0
            ) {
                return res.status(400).json({
                    message: "Price must be a valid non-negative number."
                });
            }

            fields.push("price = ?");
            values.push(Number(price));
        }

        if (fields.length === 0) {
            return res.status(400).json({
                message: "Provide a valid name or price to update."
            });
        }

        const [existing] = await pool.execute(
            "SELECT id FROM products WHERE id = ?",
            [id]
        );

        if (existing.length === 0) {
            return res.status(404).json({
                message: "Product not found."
            });
        }

        values.push(id);

        await pool.execute(
            `UPDATE products SET ${fields.join(", ")} WHERE id = ?`,
            values
        );

        res.status(200).json({
            message: "Product updated successfully."
        });
    } catch (error) {
        console.error(error);

        res.status(500).json({
            message: "Unable to update product."
        });
    }
});

The column names used in the SQL statement are selected from predefined strings in the application. User-provided values are still passed through placeholders. Never insert arbitrary client-provided column names directly into SQL.

8. DELETE Operation — Remove a Record

The DELETE operation removes a product from the database. We commonly use the HTTP DELETE method and identify the record by ID.

JavaScript
app.delete("/api/products/:id", async (req, res) => {
    try {
        const id = Number(req.params.id);

        if (!Number.isSafeInteger(id) || id <= 0) {
            return res.status(400).json({
                message: "A valid product ID is required."
            });
        }

        const [result] = await pool.execute(
            "DELETE FROM products WHERE id = ?",
            [id]
        );

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

        res.status(200).json({
            message: "Product deleted successfully."
        });
    } catch (error) {
        console.error(error);

        res.status(500).json({
            message: "Unable to delete product."
        });
    }
});

The DELETE statement removes the matching record. If the ID does not exist, the API returns a 404 response.

9. Start the Server

Add this code at the bottom of server.js to start the Express server.

JavaScript
const PORT = 3000;

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

Run the application from your terminal:

Terminal
node server.js

Use Postman, another API client, or your frontend to test the endpoints. The server must be running and the MySQL connection settings must be correct.

10. CRUD API Endpoints

Method Endpoint Operation
POST /api/products Create a product
GET /api/products Get all products
GET /api/products/1 Get product with ID 1
PUT /api/products/1 Replace editable product fields
PATCH /api/products/1 Update selected product fields
DELETE /api/products/1 Delete product with ID 1

11. CRUD Operation Flow

The client sends an HTTP request to the Express API. The route validates the input, executes a database query, and returns a response to the client.

Client

Postman or Frontend Application

↓
Express.js API

Receive request and validate input

↓
Node.js + mysql2

Execute parameterized SQL query

↓
MySQL Database

Create, Read, Update, or Delete records

↓
JSON Response

Return the result or an error message

Important Points to Remember

  • CRUD stands for Create, Read, Update, and Delete.
  • POST is commonly used to create a record.
  • GET retrieves records from the database.
  • PUT replaces the editable fields of a resource.
  • PATCH updates selected fields of a resource.
  • DELETE removes a record.
  • Use parameterized queries for user-supplied SQL values.
  • Validate request data before executing database queries.
  • Return suitable HTTP status codes, such as 201, 400, 404, and 500.
  • Keep database credentials outside source code in production.

Summary

CRUD operations are the foundation of database-driven applications. Using Node.js, Express.js, and MySQL, we created API endpoints to insert, retrieve, update, and delete product records.

In the next tutorial, we will learn how to retrieve and filter database records using queries, conditions, sorting, and limits.