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.
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.
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.
Multiple incoming requests
Manages reusable database connections
Execute query
Execute query
Execute query
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.
npm install express mysql2
Create a file named db.js to configure and export
the connection pool.
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. |
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.
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.
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.
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.
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.
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.
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
Configure the database connection pool
Execute a query using the pool
Return the query result or an error
The pool reuses the connection
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.