Connection Pool Setup

You are a database engineer. The user wants to configure database connection pooling to manage concurrent database connections efficiently and prevent connection exhaustion.

What to check first

• Verify your database driver is installed: npm list pg (for PostgreSQL) or npm list mysql2 (for MySQL)
• Check current Node.js version supports the pool library: node --version (v12+)
• Identify peak concurrent connection requirements from your application metrics

Steps

1. Install the connection pool library for your database: npm install pg for PostgreSQL (includes built-in pooling via pg.Pool) or npm install mysql2/promise for MySQL
2. Import the Pool class from your database driver at the top of your connection module
3. Create a new Pool instance with new Pool() and pass configuration object with host, port, database, user, and password keys
4. Set max property to limit concurrent connections (typically 10–20 for standard applications, 50+ for high-traffic)
5. Set min property to maintain idle connections ready (usually 2–5), reducing connection startup latency
6. Configure idleTimeoutMillis to close unused connections after N milliseconds (default 30000ms is usually fine)
7. Set connectionTimeoutMillis to define how long to wait for an available connection (default 0 = no timeout; use 10000–30000ms for safety)
8. Attach error handlers to the pool instance using .on('error', callback) to catch unexpected connection drops
9. Export the pool instance so your application queries use pool.query() instead of creating individual connections

Code

const { Pool } = require('pg');

// Create a connection pool
const pool = new Pool({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 5432,
  database: process.env.DB_NAME || 'myapp',
  user: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD || 'password',
  max: 20,                          // Maximum concurrent connections
  min: 2,                           // Minimum idle connections to maintain
  idleTimeoutMillis: 30000,         // Close idle connections after 30 seconds
  connectionTimeoutMillis: 10000,   // Wait max 10 seconds for available connection
});

// Handle pool errors
pool.on('error', (err) => {
  console.error('Unexpected error on idle client', err);
  process.exit(-1);
});

pool.on('connect', () => {
  console.log('New connection established');
});

// Example query using the pool
async function getUserById(userId) {
  try {
    const result = await pool.query(
      'SELECT id, name, email FROM users WHERE id = $1',
      [userId]
    );
    return result.rows[0];
  } catch (err) {

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change