Connection pooling concept in Node.js

Introduction

Connection pooling helps your app reuse database connections instead of opening a new one every time. This makes your app faster and uses less resources.

When your app talks to a database many times quickly.

When you want to avoid delays caused by opening new connections.

When you want to limit how many connections your app uses at once.

When you want to improve app performance under heavy load.

Syntax

Node.js

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

const pool = new Pool({
  user: 'username',
  host: 'localhost',
  database: 'mydb',
  password: 'password',
  port: 5432,
  max: 10, // max connections
  idleTimeoutMillis: 30000 // close idle connections after 30 seconds
});

This example uses the pg library for PostgreSQL in Node.js.

The max option sets how many connections can be open at once.

Examples

Basic pool setup with default max connections.

Node.js

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

const pool = new Pool({
  user: 'user1',
  host: 'localhost',
  database: 'testdb',
  password: 'pass123',
  port: 5432
});

Pool with max 5 connections and closes idle ones after 10 seconds.

Node.js

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

const pool = new Pool({
  max: 5,
  idleTimeoutMillis: 10000
});

Using the pool to run a query and then closing all connections.

Node.js

pool.query('SELECT NOW()', (err, res) => {
  if (err) throw err;
  console.log(res.rows[0]);
  pool.end();
});

Sample Program

This program creates a pool with up to 3 connections. It runs a simple query to get the current time from the database, prints it, and then closes the pool.

Node.js

import { Pool } from 'pg';

const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'testdb',
  password: 'secret',
  port: 5432,
  max: 3,
  idleTimeoutMillis: 20000
});

async function fetchTime() {
  try {
    const res = await pool.query('SELECT NOW()');
    console.log('Current time from DB:', res.rows[0].now);
  } catch (err) {
    console.error('Error running query', err);
  } finally {
    await pool.end();
  }
}

fetchTime();

Important Notes

Always close the pool with pool.end() when your app stops to free resources.

Using a pool avoids the overhead of connecting to the database every time you run a query.

Set max carefully to avoid overloading your database with too many connections.

Summary

Connection pooling reuses database connections to improve speed and resource use.

It is useful when your app runs many database queries quickly.

Always configure and close your pool properly to keep your app healthy.

Practice

1. What is the main purpose of connection pooling in Node.js applications?

easy

A. To encrypt database connections automatically

B. To create a new database connection for every query

C. To reuse database connections and improve performance

D. To store user session data in memory

Solution

1. Step 1: Understand connection pooling concept

   Connection pooling keeps a set of open database connections ready to use.

2. Step 2: Identify the benefit of reusing connections

   Reusing connections avoids the overhead of opening and closing connections repeatedly, improving speed.

3. Final Answer:

   To reuse database connections and improve performance -> Option C

4. Quick Check:

   Connection pooling = reuse connections [OK]

Hint: Pooling means reuse connections, not create new each time [OK]

Common Mistakes:

• Thinking pooling creates new connections every time
• Confusing pooling with session storage
• Assuming pooling encrypts connections automatically

2. Which of the following is the correct way to create a connection pool using the popular 'mysql2' package in Node.js?

easy

A. const pool = mysql.createPool({ host: 'localhost', user: 'root' });

B. const pool = mysql.connectPool({ host: 'localhost', user: 'root' });

C. const pool = mysql.newPool({ host: 'localhost', user: 'root' });

D. const pool = mysql.poolCreate({ host: 'localhost', user: 'root' });

Solution

1. Step 1: Recall mysql2 pool creation syntax

   The correct method is createPool with configuration object.

2. Step 2: Match method names with options

   Only const pool = mysql.createPool({ host: 'localhost', user: 'root' }); uses createPool correctly; others use invalid method names.

3. Final Answer:

   const pool = mysql.createPool({ host: 'localhost', user: 'root' }); -> Option A

4. Quick Check:

   Use createPool() to make a pool [OK]

Hint: Remember method is createPool, not connectPool or others [OK]

Common Mistakes:

• Using wrong method names like connectPool
• Forgetting to pass config object
• Confusing pool creation with direct connection

3. Given this code snippet using a connection pool, what will be logged if the pool is properly configured and the query succeeds?

const pool = mysql.createPool({ host: 'localhost', user: 'root', database: 'test' });
pool.query('SELECT 1 + 1 AS solution', (err, results) => {
  if (err) throw err;
  console.log(results[0].solution);
});

medium

A. undefined

B. 2

C. Error

D. 1 + 1

Solution

1. Step 1: Understand the SQL query result

   The query 'SELECT 1 + 1 AS solution' returns a result with solution = 2.

2. Step 2: Check the console.log output

   results[0].solution accesses the first row's solution field, which is 2.

3. Final Answer:

   2 -> Option B

4. Quick Check:

   Query result = 2 [OK]

Hint: SELECT 1 + 1 returns 2, not string or error [OK]

Common Mistakes:

• Expecting string '1 + 1' instead of result 2
• Accessing wrong property of results
• Not handling async callback properly

4. Identify the error in this Node.js code using a connection pool:

const pool = mysql.createPool({ host: 'localhost', user: 'root' });
pool.getConnection((err, connection) => {
  if (err) throw err;
  connection.query('SELECT NOW()', (err, results) => {
    if (err) throw err;
    console.log(results);
    connection.release();
  });
});

medium

A. The pool is created with wrong method

B. The callback functions are missing error handling

C. The query syntax is invalid

D. The connection is not released back to the pool after query

Solution

1. Step 1: Check connection usage in pool

   After using a connection from pool, it must be released with connection.release().

2. Step 2: Verify code for releasing connection

   The code does not call connection.release(), so connection stays checked out, causing leaks.

3. Final Answer:

   The connection is not released back to the pool after query -> Option D

4. Quick Check:

   Always release connection after use [OK]

Hint: Always call connection.release() after query [OK]

Common Mistakes:

• Forgetting to release connection causing pool exhaustion
• Assuming pool auto-releases connections
• Ignoring error handling in callbacks

5. You want to optimize a Node.js app that runs many database queries quickly. Which approach best uses connection pooling to improve performance and resource use?

hard

A. Create a single pool at app start and reuse it for all queries

B. Create a new pool for each query and close it immediately

C. Use direct connections without pooling for faster queries

D. Open one connection and never close it or use a pool

Solution

1. Step 1: Understand best practice for pooling

   Creating one pool at app start and reusing it avoids overhead and manages resources well.

2. Step 2: Evaluate other options

   Creating new pools per query wastes resources; direct connections lack reuse; never closing connections risks leaks.

3. Final Answer:

   Create a single pool at app start and reuse it for all queries -> Option A

4. Quick Check:

   One shared pool = best practice [OK]

Hint: One pool shared app-wide is efficient and safe [OK]

Common Mistakes:

• Making new pools per query causing overhead
• Not using pooling at all
• Leaving connections open without management