Closing Database Pools — Complete Implementation Guide
In this tutorial, you will learn about Closing Database Pools. We cover key concepts, practical examples, and best practices to help you master this topic.
Closing database connection pools during graceful shutdown ensures all active queries complete, idle connections are released, and the database server is not left with hanging connections that consume resources.
What You'll Learn
By the end of this tutorial, you will know how to drain active database queries, close connection pools for PostgreSQL and MySQL, and handle pool shutdown errors.
Why It Matters
Leaving database connections open after shutdown wastes database resources and can exceed connection limits. A single deployment cycle can leave hundreds of orphaned connections if pools are not closed properly.
Real-World Use
DodaTech's user service closes its PostgreSQL pool during every deployment. The shutdown handler waits for active queries (max 5 seconds), then calls pool.end() to release all connections. Monitoring confirms zero orphaned connections after deployment.
Closing DB Pools Learning Path
flowchart LR
A[In-Flight Requests] --> B[Closing DB Pools]
B --> C[Active Queries]
B --> D[Pool.end()]
B --> E[Error Handling]
B --> F{You Are Here}
style F fill:#f90,color:#fff
PostgreSQL Pool Shutdown
The node-postgres library provides pool.end() which waits for all idle connections to close.
const { Pool } = require("pg");
class PostgresPoolManager {
constructor(config) {
this.pool = new Pool({
max: 20,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 5000,
...config
});
this.activeQueries = 0;
}
async query(text, params) {
this.activeQueries++;
try {
return await this.pool.query(text, params);
} finally {
this.activeQueries--;
}
}
async drain(timeoutMs = 5000) {
console.log("Starting PostgreSQL pool drain");
console.log(`Active queries: ${this.activeQueries}`);
const start = Date.now();
while (this.activeQueries > 0) {
if (Date.now() - start > timeoutMs) {
console.log("Timeout waiting for queries, forcing pool close");
break;
}
console.log(`Waiting for ${this.activeQueries} active queries...`);
await new Promise(r => setTimeout(r, 200));
}
try {
await this.pool.end();
console.log("PostgreSQL pool closed successfully");
} catch (err) {
console.error("Error closing PostgreSQL pool:", err.message);
}
}
}
const db = new PostgresPoolManager({ connectionString: "postgresql://localhost/mydb" });
console.log("PostgreSQL pool manager initialized");
MySQL Pool Shutdown
MySQL2 pool provides a similar end() method but with slightly different behavior.
const mysql = require("mysql2/promise");
class MySQLPoolManager {
constructor(config) {
this.pool = mysql.createPool({
host: "localhost",
user: "app",
password: "password",
database: "mydb",
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0,
...config
});
}
async query(sql, params) {
const [rows] = await this.pool.execute(sql, params);
return rows;
}
async drain(timeoutMs = 5000) {
console.log("Starting MySQL pool drain");
try {
const endPromise = this.pool.end();
const timeoutPromise = new Promise((_, reject) =>
setTimeout(() => reject(new Error("MySQL pool drain timeout")), timeoutMs)
);
await Promise.race([endPromise, timeoutPromise]);
console.log("MySQL pool closed successfully");
} catch (err) {
console.error("MySQL pool drain error:", err.message);
}
}
}
const mysqlDb = new MySQLPoolManager();
console.log("MySQL pool manager initialized");
Shutdown with Transaction Safety
Active transactions must complete or roll back before the pool closes.
class TransactionSafePool {
constructor(pool) {
this.pool = pool;
this.activeTransactions = new Set();
}
async beginTransaction() {
const client = await this.pool.connect();
await client.query("BEGIN");
const tx = { client, id: Date.now(), active: true };
this.activeTransactions.add(tx);
return tx;
}
async commit(tx) {
try {
await tx.client.query("COMMIT");
} finally {
tx.client.release();
this.activeTransactions.delete(tx);
}
}
async rollback(tx) {
try {
await tx.client.query("ROLLBACK");
} finally {
tx.client.release();
this.activeTransactions.delete(tx);
}
}
async drain(timeoutMs = 5000) {
console.log(`Draining ${this.activeTransactions.size} active transactions`);
const start = Date.now();
for (const tx of this.activeTransactions) {
try {
await tx.client.query("ROLLBACK");
console.log("Rolled back transaction:", tx.id);
} catch (err) {
console.error("Error rolling back transaction:", err.message);
}
tx.client.release();
this.activeTransactions.delete(tx);
}
await this.pool.end();
console.log("Transaction-safe pool closed");
}
}
const pgPool = new Pool({ max: 10 });
const txSafe = new TransactionSafePool(pgPool);
console.log("Transaction-safe pool manager ready");
Pool Health Verification Before Close
Before closing, verify the pool's health and log any anomalies.
class PoolHealthChecker {
static async checkBeforeClose(pool, poolName) {
const health = {
poolName,
totalCount: pool.totalCount,
idleCount: pool.idleCount,
waitingCount: pool.waitingCount,
timestamp: new Date().toISOString()
};
console.log("Pool health check:", JSON.stringify(health, null, 2));
if (health.waitingCount > 0) {
console.warn(`WARNING: ${health.waitingCount} queries waiting in queue`);
}
if (health.totalCount > health.idleCount) {
console.log(`${health.totalCount - health.idleCount} active connections to wait for`);
}
return health;
}
static async safeClose(pool, poolName, timeoutMs = 5000) {
await this.checkBeforeClose(pool, poolName);
await pool.end();
console.log(`${poolName} closed with ${pool.totalCount} connections released`);
}
}
const testPool = new Pool({ max: 5 });
PoolHealthChecker.safeClose(testPool, "test-pool", 3000);
// Pool health check: { "poolName": "test-pool", "totalCount": 0, ... }
// test-pool closed with 0 connections released
Common Mistakes
Calling pool.end() while queries are still running -- pool.end() closes idle connections immediately but may reject active queries or wait. Always drain active queries first or handle the rejection.
Not handling pool.end() errors -- pool.end() can reject if connections fail to close gracefully. Wrap it in try-catch to prevent unhandled promise rejections.
Closing the pool before stopping the HTTP server -- New requests arriving after pool closure will fail with connection errors. Stop accepting requests first, then close the pool.
Forgetting to close multiple pools -- If your application connects to multiple databases, each pool must be drained and closed individually.
Not releasing clients acquired with pool.connect() -- Every pool.connect() call must be paired with client.release(). Orphaned clients prevent pool.end() from completing.
Practice Questions
What does pool.end() do in node-postgres? It waits for all idle connections to close and prevents new connections from being created. Active connections are allowed to complete their queries.
Why must active transactions be handled before closing the pool? Active transactions hold a client connection. If the pool closes with open transactions, those changes may be lost or the connection may hang.
How do you handle a situation where pool.end() times out? Set a timeout wrapper around pool.end(). If it doesn't complete in time, log the error and allow the Process to exit. The database will clean up orphaned connections.
Challenge: Implement a multi-pool manager that drains and closes all pools in parallel during shutdown.
class MultiPoolManager {
constructor() {
this.pools = new Map();
}
register(name, pool) {
this.pools.set(name, pool);
}
async drainAll(timeoutMs = 10000) {
console.log(`Draining ${this.pools.size} database pools`);
const results = await Promise.allSettled(
Array.from(this.pools.entries()).map(([name, pool]) =>
Promise.race([
pool.end(),
new Promise((_, reject) =>
setTimeout(() => reject(new Error(`${name} drain timeout`)), timeoutMs)
)
]).then(() => ({ name, status: "ok" }))
)
);
results.forEach(r => {
if (r.status === "fulfilled") {
console.log(`Pool ${r.value.name}: closed`);
} else {
console.error(`Pool ${r.reason.message}`);
}
});
}
}
const multiPool = new MultiPoolManager();
multiPool.register("users", new Pool({ max: 5 }));
multiPool.register("analytics", new Pool({ max: 10 }));
multiPool.register("logs", new Pool({ max: 3 }));
console.log("Multi-pool manager ready with 3 pools");
FAQ
Mini Project
Build a database pool manager that handles PostgreSQL and MySQL pools, drains active queries with timeout, rolls back any open transactions, and reports pool health before closing.
class UniversalPoolManager {
constructor() {
this.pools = [];
}
addPool(name, pool, options = {}) {
this.pools.push({
name,
pool,
activeQueries: 0,
timeout: options.timeout || 5000
});
}
async query(poolName, queryText, params) {
const entry = this.pools.find(p => p.name === poolName);
if (!entry) throw new Error(`Pool ${poolName} not found`);
entry.activeQueries++;
try {
return await entry.pool.query(queryText, params);
} finally {
entry.activeQueries--;
}
}
async shutdown() {
for (const entry of this.pools) {
console.log(`Draining pool: ${entry.name}`);
const start = Date.now();
while (entry.activeQueries > 0) {
if (Date.now() - start > entry.timeout) break;
await new Promise(r => setTimeout(r, 100));
}
await entry.pool.end();
console.log(`Pool closed: ${entry.name}`);
}
}
}
const mgr = new UniversalPoolManager();
mgr.addPool("primary", new Pool({ max: 10 }));
mgr.addPool("secondary", new Pool({ max: 5 }));
mgr.shutdown();
What's Next
Now that you understand closing database pools, learn how to close message queue connections during shutdown. Then explore health check management during shutdown.
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro