Introduction
While MongoDB stores flexible documents, many applications still rely on relational databases like PostgreSQL or MySQL for structured, transactional data. Node.js connects to these databases through client libraries such as pg (PostgreSQL) or mysql2 (MySQL). Rather than opening a new connection for every query, production applications use a connection pool, which reuses a fixed set of open connections to reduce overhead and handle concurrent requests efficiently.
Cricket analogy: Opening a new database connection for every query is like sending a fresh new umpire out for every single ball instead of keeping a standing panel of umpires ready — a connection pool is like retaining the same panel of umpires (open connections) reused across the whole match for efficiency.
Syntax
const { Pool } = require('pg');
const pool = new Pool({
host: process.env.DB_HOST,
port: process.env.DB_PORT,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
max: 10,
idleTimeoutMillis: 30000,
});
pool.on('error', (err) => {
console.error('Unexpected idle client error', err);
});
async function getUserById(id) {
const result = await pool.query(
'SELECT id, name, email FROM users WHERE id = $1',
[id]
);
return result.rows[0];
}
module.exports = { pool, getUserById };Explanation
The Pool object manages a set of reusable client connections, configured with credentials pulled from environment variables and limits like max connections and idleTimeoutMillis. Crucially, the query uses a parameterized placeholder, $1 for pg (or ? for mysql2), instead of concatenating the id directly into the SQL string. This separates the query structure from user-supplied data, which the driver escapes safely, preventing SQL injection attacks. String concatenation of untrusted input into SQL is one of the most common and dangerous security mistakes in backend development.
Cricket analogy: Using a parameterized placeholder like $1 instead of concatenating the id into the query is like an umpire checking a player's ID card against an official pre-formatted registry field, rather than accepting whatever a spectator scribbles on a piece of paper claiming to be a player.
Example
const express = require('express');
const { getUserById } = require('./db');
const app = express();
app.get('/users/:id', async (req, res, next) => {
try {
const user = await getUserById(req.params.id);
if (!user) {
return res.status(404).json({ error: 'User not found' });
}
res.json(user);
} catch (err) {
next(err);
}
});
app.listen(3000, () => console.log('Server running on port 3000'));Output
A GET request to /users/5 executes the parameterized query with [5] bound to $1, returning JSON like {"id":5,"name":"Grace Hopper","email":"[email protected]"} with a 200 status if found, or {"error":"User not found"} with a 404 status otherwise. Because the id is bound as a parameter rather than interpolated into the string, passing something malicious like 5 OR 1=1 simply fails to match any row instead of altering the query logic.
Cricket analogy: Just as submitting "team score OR declare innings" as a scorecard entry would simply fail to match any valid delivery instead of altering the match record, passing "5 OR 1=1" as a bound parameter fails to match any row instead of manipulating the SQL query logic.
Key Takeaways
- Use a connection pool (pg.Pool or mysql2 pool) instead of opening a new connection per query.
- Always use parameterized queries with placeholders like $1 or ? — never string concatenation with user input.
- Store database credentials in environment variables, not in source code.
- Handle pool-level errors with an 'error' event listener to avoid crashing the process on idle client failures.
- Wrap query calls in try/catch and forward errors to Express's error-handling middleware via next(err).
Practice what you learned
1. Why do production Node.js apps typically use a connection pool instead of a single connection?
2. Which of the following is the SAFE way to include a user-supplied value in a SQL query with pg?
3. What is a primary danger of concatenating user input directly into a SQL query string?
4. In the pg library, what does pool.on('error', ...) protect against?
Was this page helpful?
You May Also Like
Connecting to MongoDB with Mongoose
Learn how to connect an Express app to MongoDB using Mongoose schemas, models, and connection management.
CRUD Operations in Express
Build complete Create, Read, Update, and Delete route handlers in Express using a database model.
Error Handling in Express
Master synchronous and asynchronous error handling in Express using 4-argument error middleware.
Related Reading
Related Study Notes in Web Development
Browse all study notesWebSockets Study Notes
Web Development · 30 topics
Web DevelopmentWebAssembly Study Notes
WebAssembly · 30 topics
Web DevelopmentgRPC Study Notes
Protocol Buffers · 30 topics
Web DevelopmentSpring Boot Study Notes
Java · 30 topics
Web DevelopmentFlask Study Notes
Python · 30 topics
Web DevelopmentDjango Study Notes
Python · 30 topics