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 Queries and Filtering in Node.js

Learn how to retrieve, filter, sort, and limit database records using SQL queries in Node.js applications.

When an application stores thousands of records, retrieving every record is not always useful. We often need specific information, such as products within a price range, employees from a particular department, or the newest records in a database.

SQL provides clauses such as WHERE, ORDER BY, LIMIT, and OFFSET to retrieve the required data. In this tutorial, we will use Node.js, Express.js, mysql2, and MySQL to understand these concepts.

1. What Are Database Queries?

A database query is a request to retrieve or manipulate information stored in a database. A SELECT query retrieves records from a table.

SQL
SELECT * FROM products;

This query retrieves all columns and all rows from the products table.

Remember: A query defines what information the database should return. Filtering and sorting help us retrieve that information more precisely.

2. Select Specific Columns

Instead of retrieving every column, we can select only the columns required by the application.

SQL
SELECT id, name, price
FROM products;

This query returns only the product ID, name, and price. Selecting the necessary columns can reduce unnecessary data transfer.

Clause Purpose
SELECT Specifies the columns to retrieve.
FROM Specifies the table to query.
WHERE Filters records based on a condition.
ORDER BY Sorts the returned records.
LIMIT Restricts the number of returned records.
OFFSET Skips a specified number of records.

3. Filter Records Using WHERE

The WHERE clause retrieves only the records that match a specified condition. For example, we can find products whose price is greater than or equal to 1000.

SQL
SELECT id, name, price
FROM products
WHERE price >= 1000;

We can also filter records using a specific product ID.

SQL
SELECT id, name, price
FROM products
WHERE id = 1;

Common WHERE Operators

Operator Meaning Example
= Equal to price = 1000
> Greater than price > 1000
< Less than price < 1000
>= Greater than or equal to price >= 1000
<= Less than or equal to price <= 5000
AND Both conditions must be true price >= 1000 AND price <= 5000
OR At least one condition must be true price < 1000 OR price > 5000
LIKE Matches a text pattern name LIKE 'Key%'

4. Sort Records Using ORDER BY

ORDER BY sorts records in ascending or descending order. ASC means ascending, while DESC means descending.

A. Ascending Order

SQL
SELECT id, name, price
FROM products
ORDER BY price ASC;

The products are sorted from the lowest price to the highest price.

B. Descending Order

SQL
SELECT id, name, price
FROM products
ORDER BY price DESC;

The products are sorted from the highest price to the lowest price.

5. Limit the Number of Records

LIMIT specifies the maximum number of records returned by a query. It is useful when an application needs only a small number of records.

SQL
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 5;

This query returns up to five products, ordered by ID.

Tip: Use ORDER BY when a consistent record order is important. LIMIT without a deterministic sort does not guarantee which records will appear first.

6. Pagination Using LIMIT and OFFSET

Pagination divides a large result set into smaller pages. For example, a product listing might display ten products per page.

SQL
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 0;

The query retrieves the first ten products. To retrieve the next ten, change the offset to 10.

SQL
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 10;

Pagination Formula

Page Number

The page requested by the client

↓
Calculate OFFSET

(Page Number - 1) × Page Size

↓
Execute Query

LIMIT Page Size OFFSET Calculated Offset

For page 3 with 10 records per page, the offset is (3 - 1) × 10 = 20.

7. Connect Database Queries to Node.js

Now let's create an Express endpoint that filters products by minimum and maximum price. It also supports sorting and pagination.

This example assumes that the app and MySQL connection pool named pool have already been created, as explained in the previous CRUD tutorial.

JavaScript
app.get("/api/products/search", async (req, res) => {
    try {
        const minPrice = req.query.minPrice === undefined
            ? 0
            : Number(req.query.minPrice);

        const maxPrice = req.query.maxPrice === undefined
            ? 1000000
            : Number(req.query.maxPrice);

        const page = req.query.page === undefined
            ? 1
            : Number(req.query.page);

        const pageSize = req.query.pageSize === undefined
            ? 10
            : Number(req.query.pageSize);

        const sort = req.query.sort === undefined
            ? "asc"
            : req.query.sort.toLowerCase();

        if (
            !Number.isFinite(minPrice) ||
            !Number.isFinite(maxPrice) ||
            minPrice < 0 ||
            maxPrice < minPrice
        ) {
            return res.status(400).json({
                message: "Invalid price range."
            });
        }

        if (
            !Number.isSafeInteger(page) ||
            page < 1 ||
            !Number.isSafeInteger(pageSize) ||
            pageSize < 1 ||
            pageSize > 100
        ) {
            return res.status(400).json({
                message: "Invalid page or page size."
            });
        }

        if (!["asc", "desc"].includes(sort)) {
            return res.status(400).json({
                message: "Sort must be asc or desc."
            });
        }

        const offset = (page - 1) * pageSize;

        if (!Number.isSafeInteger(offset)) {
            return res.status(400).json({
                message: "Requested page is too large."
            });
        }

        const orderDirection = sort === "desc" ? "DESC" : "ASC";

        const [products] = await pool.execute(
            `SELECT id, name, price
             FROM products
             WHERE price >= ? AND price <= ?
             ORDER BY price ${orderDirection}, id ASC
             LIMIT ? OFFSET ?`,
            [minPrice, maxPrice, pageSize, offset]
        );

        res.status(200).json({
            page,
            pageSize,
            minPrice,
            maxPrice,
            products
        });
    } catch (error) {
        console.error(error);

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

Notice that the sort direction is chosen from a predefined allowlist. SQL placeholders are used for the filter values, page size, and offset.

8. Test the API

Start the Node.js server and send a GET request from Postman or another API client.

URL
http://localhost:3000/api/products/search?minPrice=500&maxPrice=5000&page=1&pageSize=10&sort=asc

Example response:

JSON
{
    "page": 1,
    "pageSize": 10,
    "minPrice": 500,
    "maxPrice": 5000,
    "products": [
        {
            "id": 1,
            "name": "Keyboard",
            "price": "1200.00"
        }
    ]
}

This is an illustrative response. The actual records depend on the data stored in your MySQL database. Depending on the mysql2 configuration, DECIMAL values may be returned as strings.

9. Use Parameterized Queries

When query values come from a user, use parameterized queries instead of concatenating input into SQL statements.

Unsafe Example

JavaScript
// Do not build SQL using untrusted input.
const sql =
    "SELECT * FROM products WHERE name = '" +
    req.query.name +
    "'";

Safer Example

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

Parameterized queries help prevent SQL injection by keeping supplied values separate from the SQL statement.

10. Database Query Flow

Client Request

Price, sort, and pagination parameters

↓
Express.js

Validate query parameters

↓
SQL Query

WHERE, ORDER BY, LIMIT, OFFSET

↓
MySQL Database

Find and return matching records

↓
JSON Response

Send filtered records to the client

Important Points to Remember

  • SELECT retrieves records from a database table.
  • WHERE filters records according to specified conditions.
  • ORDER BY sorts records in ascending or descending order.
  • LIMIT restricts the number of records returned.
  • OFFSET skips records and is useful for pagination.
  • Validate query parameters before executing database queries.
  • Use parameterized queries for user-supplied values.
  • Allowlist dynamic SQL identifiers and sort directions.
  • Use a stable ORDER BY when implementing pagination.
  • Handle database errors and return appropriate HTTP status codes.

Summary

Database queries help applications retrieve exactly the information they need. We learned how to select specific columns, filter records with WHERE, sort with ORDER BY, and implement pagination with LIMIT and OFFSET.

We also connected these concepts to an Express.js API using mysql2 and learned why parameterized queries are important for security.