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 Connection Management in Node.js

Learn how to create, reuse, monitor, and safely close database connections in Node.js applications.

A database connection allows a Node.js application to communicate with a database such as MySQL or MongoDB. Applications need reliable connection management to execute queries, handle failures, and release resources properly.

In this tutorial, we will explore MySQL connection pools using the mysql2 package, understand connection errors, and learn how to close database connections gracefully.

1. What Is Database Connection Management?

Database connection management is the process of establishing, reusing, monitoring, and closing connections between an application and a database.

Whenever an application needs to read or modify database records, it must communicate with the database through a connection. Managing these connections correctly helps an application use resources efficiently.

Activity Description
Connect Establish communication with the database.
Reuse Reuse available connections for database queries.
Monitor Handle connection failures and query errors.
Release Return borrowed connections to the pool.
Close Shut down database resources when the application stops.

2. Single Database Connection

A single database connection can be created using mysql2/promise. This approach is useful for understanding how a database connection works.

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

async function main() {
    let connection;

    try {
        connection = await mysql.createConnection({
            host: "localhost",
            user: "root",
            password: process.env.DB_PASSWORD,
            database: "crud_demo"
        });

        console.log("Database connected successfully");

        const [rows] = await connection.execute(
            "SELECT id, name, price FROM products"
        );

        console.log(rows);
    } catch (error) {
        console.error("Database operation failed:", error.message);
    } finally {
        if (connection) {
            await connection.end();
            console.log("Database connection closed");
        }
    }
}

main();

The application creates a connection, executes a query, and closes the connection in the finally block.

Important: A single connection is not usually the best choice for an API that handles many concurrent requests. A connection pool is generally more suitable for long-running applications.

3. What Is a Connection Pool?

A connection pool maintains a collection of database connections that can be reused. Instead of creating a new connection for every query, the application obtains an available connection from the pool.

The pool can create connections when required, reuse available connections, and limit the number of connections managed at once.

Node.js Application

Multiple incoming requests

↓
MySQL Connection Pool

Manages reusable database connections

↓
Connection 1

Execute query

Connection 2

Execute query

Connection N

Execute query

↓
MySQL Database

Processes database queries

A pool improves connection reuse, but it does not make every database query faster automatically. Query design, indexes, pool size, and database capacity also matter.

4. Create a MySQL Connection Pool

Install the required packages if they are not already installed.

Terminal
npm install express mysql2

Create a file named db.js to configure and export the connection pool.

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

const pool = mysql.createPool({
    host: process.env.DB_HOST || "localhost",
    user: process.env.DB_USER || "root",
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME || "crud_demo",
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

module.exports = pool;

The pool configuration controls how connections are managed.

Option Purpose
host Database server address.
user Database username.
password Database password.
database Default database to use.
waitForConnections Wait for an available connection when the pool is busy.
connectionLimit Maximum number of connections in the pool.
queueLimit Maximum queued requests; zero means no queue limit.
Security note: Set your database credentials through environment variables. Never commit real database passwords to source control.

5. Reuse the Connection Pool

Once the pool has been created, import it wherever database queries are required. You do not need to create a new pool for every request.

Create a file named server.js.

JavaScript
const express = require("express");
const pool = require("./db");

const app = express();

app.use(express.json());

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("Query failed:", error.message);

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

const server = app.listen(3000, () => {
    console.log("Server running at http://localhost:3000");
});

The pool manages the connection used by the SELECT query. When the query completes, the pool makes that connection available for reuse.

6. Use a Connection Directly from the Pool

Most simple queries can be executed directly through pool.execute(). For transactions or multiple operations that must use the same connection, acquire a connection from the pool and release it in a finally block.

JavaScript
async function getProductCount() {
    let connection;

    try {
        connection = await pool.getConnection();

        const [rows] = await connection.execute(
            "SELECT COUNT(*) AS total FROM products"
        );

        return rows[0].total;
    } finally {
        if (connection) {
            connection.release();
        }
    }
}

The release() method returns the connection to the pool. It does not shut down the connection pool itself.

Remember: Use release() for a connection borrowed using pool.getConnection(). Avoid calling connection.end() on a borrowed pool connection when you intend to return it for reuse.

7. Handle Database Errors

Database operations may fail because of incorrect credentials, network interruptions, unavailable servers, invalid queries, or constraint violations. Use try/catch to handle rejected database operations.

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("Database error:", error.message);

        res.status(500).json({
            message: "An internal server error occurred."
        });
    }
});

This example validates the ID, handles a missing record with a 404 response, and catches database errors.

Do not return raw database error details, connection strings, or credentials to clients. Log appropriate details securely on the server instead.

8. Close the Connection Pool Gracefully

A connection pool is intended to remain available while the application is running. When the application shuts down, close the HTTP server and end the pool so database resources can be released.

Replace the simple server startup code in server.js with the following shutdown handling. Keep your existing route definitions above it.

JavaScript
const server = app.listen(3000, () => {
    console.log("Server running at http://localhost:3000");
});

let shuttingDown = false;

async function shutdown(signal) {
    if (shuttingDown) {
        return;
    }

    shuttingDown = true;
    console.log(`${signal} received. Shutting down...`);

    server.close(async () => {
        try {
            await pool.end();
            console.log("Database pool closed.");
            process.exitCode = 0;
        } catch (error) {
            console.error(
                "Error closing database pool:",
                error.message
            );
            process.exitCode = 1;
        }
    });
}

process.on("SIGINT", () => {
    void shutdown("SIGINT");
});

process.on("SIGTERM", () => {
    void shutdown("SIGTERM");
});

The server stops accepting new HTTP connections, waits for existing HTTP connections to close, and then ends the pool. For production applications, also consider a shutdown timeout so the process cannot wait indefinitely.

9. Connection Management Best Practices

Practice Why It Matters
Use a connection pool Reuses connections across database operations.
Set a suitable pool size Helps control database resource usage.
Release borrowed connections Prevents connections from remaining checked out.
Handle errors Prevents unhandled promise rejections and unclear failures.
Protect credentials Reduces the risk of exposing database access.
Close the pool on shutdown Allows database resources to be released cleanly.
Monitor connection usage Helps identify saturation and performance issues.

10. Database Connection Lifecycle

Application Starts

Configure the database connection pool

↓
Request Arrives

Execute a query using the pool

↓
Database Processes Query

Return the query result or an error

↓
Connection Becomes Available

The pool reuses the connection

↓
Application Shuts Down

Close the server and end the pool

Important Points to Remember

  • Database connections allow applications to communicate with databases.
  • Connection pools reuse connections and manage concurrent requests.
  • Create a pool once and reuse it throughout the application.
  • Use try/catch to handle asynchronous database errors.
  • Release borrowed connections in a finally block.
  • Use pool.end() when the application is shutting down.
  • Never expose database credentials or raw internal errors to clients.
  • Choose pool limits according to application and database capacity.
  • For transactions, use one acquired connection for the entire transaction.

Summary

Database connection management helps Node.js applications communicate with databases reliably. We learned about single connections, connection pools, query execution, error handling, releasing borrowed connections, and graceful application shutdown.

With these practices, you can build applications that manage database resources more efficiently and handle database failures more safely.