Connection pooling concept in Node.js - Mini Project: Build & Apply

Connection Pooling Concept in Node.js

📖 Scenario: You are building a simple Node.js app that connects to a database. To make your app faster and handle many users, you want to reuse database connections instead of opening a new one every time.

🎯 Goal: Build a basic connection pool setup using Node.js to manage multiple database connections efficiently.

📋 What You'll Learn

Create an array to hold connection objects

Set a maximum number of connections allowed

Write a function to get a connection from the pool

Add a function to release a connection back to the pool

💡 Why This Matters

🌍 Real World

Connection pooling helps web servers handle many users efficiently by reusing database connections instead of opening new ones each time.

💼 Career

Understanding connection pooling is important for backend developers working with databases to improve app performance and resource management.

1

Create the connection pool array

Create an empty array called connectionPool to hold the database connections.

Node.js

// Create an empty array called connectionPool
// Your code here

Hint

Use const connectionPool = []; to create an empty array.

2

Set the maximum pool size

Create a constant called MAX_POOL_SIZE and set it to 5 to limit the number of connections in the pool.

Node.js

const connectionPool = [];
// Create a constant MAX_POOL_SIZE and set it to 5
// Your code here

Hint

Use const MAX_POOL_SIZE = 5; to set the pool size limit.

3

Write a function to get a connection

Write a function called getConnection that returns a connection from connectionPool if available. If the pool is empty and the pool size is less than MAX_POOL_SIZE, add a new connection string 'new_connection' to the pool and return it.

Node.js

const connectionPool = [];
const MAX_POOL_SIZE = 5;

// Write function getConnection to return a connection from connectionPool or add 'new_connection' if pool size allows
// Your code here

Hint

Use connectionPool.pop() to get a connection and check pool size with connectionPool.length.

4

Add a function to release connections

Write a function called releaseConnection that takes a conn parameter and adds it back to connectionPool only if the pool size is less than MAX_POOL_SIZE.

Node.js

const connectionPool = [];
const MAX_POOL_SIZE = 5;

function getConnection() {
    if (connectionPool.length > 0) {
        return connectionPool.pop();
    } else if (connectionPool.length < MAX_POOL_SIZE) {
        const newConn = 'new_connection';
        return newConn;
    } else {
        return null;
    }
}

// Write function releaseConnection that adds conn back to connectionPool if pool size is less than MAX_POOL_SIZE
// Your code here

Hint

Use connectionPool.push(conn) to add the connection back to the pool.

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