Connection pooling for serverless in NextJS - Step-by-Step Execution

Concept Flow - Connection pooling for serverless

Serverless Function Start

↓

Check for Existing DB Connection

↓

Reuse Connection

↓

Execute DB Query

↓

Return Response

↓

Function Ends, Connection stays open or closed based on pooling

This flow shows how serverless functions check for existing database connections to reuse them, or create new ones if none exist, to efficiently manage connections.

Execution Sample

NextJS

import { createPool } from 'mysql2/promise';

const pool = createPool({
  host: 'localhost',
  user: 'user',
  database: 'db',
  waitForConnections: true,
  connectionLimit: 5
});

export default async function handler(req, res) {
  const [rows] = await pool.query('SELECT * FROM users');
  res.json(rows);
}

This code creates a connection pool and reuses connections for each serverless function call to query users.

Execution Table

Step	Action	Connection Pool State	Query Execution	Response Sent
1	Function invoked	Pool created with 0 active connections	No query yet	No response yet
2	Check pool for free connection	0 active, 0 used	No query yet	No response yet
3	Acquire connection from pool	1 active, 1 used	No query yet	No response yet
4	Execute 'SELECT * FROM users'	1 active, 1 used	Query running	No response yet
5	Query completes	1 active, 1 used	Query result ready	No response yet
6	Release connection back to pool	1 active, 0 used	Query done	No response yet
7	Send JSON response with user data	1 active, 0 used	Query done	Response sent
8	Function ends, pool keeps connections alive	1 active, 0 used	Query done	Response sent

💡 Function ends but pool keeps connections alive for reuse in next invocation

Variable Tracker

Variable	Start	After Step 3	After Step 6	Final
pool.activeConnections	0	1	1	1
pool.usedConnections	0	1	0	0
query.status	none	running	done	done
response.status	none	none	none	sent

Key Moments - 2 Insights

Why does the pool keep connections alive after the function ends?

The pool keeps connections alive to reuse them in future function calls, avoiding the overhead of creating new connections each time, as shown in execution_table steps 7 and 8.

What happens if no free connection is available in the pool?

The pool waits for a connection to be released or creates a new one up to the connectionLimit, ensuring queries can run without errors, as implied in the pool configuration in the code sample.

Visual Quiz - 3 Questions

Look at the execution table, how many active connections are there after step 3?

A 2

B 1

C 0

D None

💡 Hint

Check the 'Connection Pool State' column at step 3 in the execution_table.

At which step is the connection released back to the pool?

A Step 6

B Step 4

C Step 7

D Step 2

💡 Hint

Look for 'Release connection back to pool' in the Action column of execution_table.

If the connectionLimit was set to 1 and two functions run simultaneously, what would happen?

A Both functions run with separate connections

B Second function creates a new connection anyway

C Second function waits for connection to be free

D Error occurs immediately

💡 Hint

Refer to the pool configuration and the meaning of connectionLimit in the code sample.

Concept Snapshot

Connection pooling in serverless:
- Create a pool once outside handler
- Reuse connections from pool on each call
- Pool limits max connections
- Connections stay alive after function ends
- Improves performance by avoiding reconnects

Full Transcript

This visual execution trace shows how connection pooling works in serverless Next.js functions. When a function runs, it checks the pool for an available database connection. If none is free, it creates one up to the limit. The function uses the connection to run queries, then releases it back to the pool. The pool keeps connections alive after the function ends, so future calls reuse them. This reduces connection overhead and improves speed. The execution table tracks each step, showing connection states and query progress. The variable tracker follows pool connections and response status. Key moments clarify why connections stay alive and how the pool manages limits. The quiz tests understanding of connection counts and pool behavior. This approach is essential for efficient database access in serverless environments.

Practice

1. What is the main benefit of using connection pooling in a Next.js serverless app?

easy

A. It automatically scales the number of serverless functions.

B. It reuses database connections to improve speed and avoid connection limits.

C. It caches API responses for faster loading.

D. It encrypts database connections for security.

Solution

1. Step 1: Understand connection pooling purpose

   Connection pooling allows reusing existing database connections instead of opening new ones each time.

2. Step 2: Identify benefits in serverless context

   This reuse improves speed and prevents hitting database connection limits common in serverless environments.

3. Final Answer:

   It reuses database connections to improve speed and avoid connection limits. -> Option B

4. Quick Check:

   Connection pooling = reuse connections [OK]

Hint: Pooling means reusing connections to avoid limits [OK]

Common Mistakes:

• Confusing pooling with caching data
• Thinking pooling scales serverless functions
• Assuming pooling encrypts connections

2. Which code snippet correctly creates a MySQL connection pool using mysql2/promise in Next.js?

easy

A. const pool = mysql2.createPool({ host: 'localhost', user: 'root', database: 'test' }).promise();

B. const pool = mysql2.promise.createPool({ host: 'localhost', user: 'root', database: 'test' });

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

D. const pool = mysql2/promise.createPool({ host: 'localhost', user: 'root', database: 'test' });

Solution

1. Step 1: Recall mysql2/promise usage

   When importing mysql from 'mysql2/promise', mysql.createPool() directly creates a promise-based pool.

2. Step 2: Match correct syntax

   const pool = mysql.createPool({ host: 'localhost', user: 'root', database: 'test' }); correctly uses the mysql2/promise import.

3. Final Answer:

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

4. Quick Check:

   mysql2/promise + mysql.createPool() = correct syntax [OK]

Hint: mysql from 'mysql2/promise'; mysql.createPool() [OK]

Common Mistakes:

• Trying to call createPool directly on mysql2/promise import
• Missing .promise() for async support
• Using wrong import or syntax

3. Given this Next.js API handler using a PostgreSQL pool, what will be the output if the database connection fails?

import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export default async function handler(req, res) {
  try {
    const client = await pool.connect();
    const result = await client.query('SELECT NOW()');
    client.release();
    res.status(200).json({ time: result.rows[0].now });
  } catch (error) {
    res.status(500).json({ error: 'Database connection failed' });
  }
}

medium

A. Returns JSON with error message 'Database connection failed'.

B. Returns empty JSON object {}.

C. Throws an unhandled exception crashing the server.

D. Returns JSON with current time from database.

Solution

1. Step 1: Analyze try-catch behavior

   If pool.connect() fails, the code jumps to the catch block.

2. Step 2: Check catch block response

   The catch block sends a 500 status with JSON error message 'Database connection failed'.

3. Final Answer:

   Returns JSON with error message 'Database connection failed'. -> Option A

4. Quick Check:

   Error caught = JSON error response [OK]

Hint: Errors in try send JSON error response [OK]

Common Mistakes:

• Assuming unhandled exception crashes server
• Expecting successful time JSON on failure
• Thinking empty JSON is returned

4. Identify the bug in this Next.js serverless function using MySQL connection pooling:

import mysql from 'mysql2/promise';
const pool = mysql.createPool({ host: 'localhost', user: 'root', database: 'test' });

export default async function handler(req, res) {
  const connection = await pool.getConnection();
  const [rows] = await connection.query('SELECT * FROM users');
  res.status(200).json(rows);
}

medium

A. Missing connection.release() after query.

B. Using getConnection() instead of connect().

C. Pool should be created inside the handler.

D. Query syntax is incorrect.

Solution

1. Step 1: Check connection usage

   The code gets a connection from the pool but never releases it back.

2. Step 2: Understand pooling best practice

   Connections must be released with connection.release() to avoid leaks and exhaustion.

3. Final Answer:

   Missing connection.release() after query. -> Option A

4. Quick Check:

   Always release pooled connections [OK]

Hint: Always release connections after use [OK]

Common Mistakes:

• Forgetting to release connections
• Thinking getConnection() is invalid
• Creating pool inside handler causing overhead

5. You want to optimize a Next.js serverless app connecting to PostgreSQL with connection pooling. Which approach best prevents exhausting database connections during high traffic?

hard

A. Close the pool after each query to free resources.

B. Create a new pool inside each API handler call.

C. Use a new client connection for every query without pooling.

D. Create a single global pool instance reused across requests.

Solution

1. Step 1: Understand serverless connection challenges

   Serverless functions can run many instances, so creating many pools wastes connections.

2. Step 2: Choose pooling strategy

   Creating a single global pool reused by all handlers limits total connections and improves reuse.

3. Final Answer:

   Create a single global pool instance reused across requests. -> Option D

4. Quick Check:

   Global pool reuse prevents connection exhaustion [OK]

Hint: Use one global pool, not new pools per request [OK]

Common Mistakes:

• Making new pool each request causing connection overload
• Not using pooling at all
• Closing pool too early causing errors