Connecting to SQL Databases in Node.js

Analogy categoryAnalogies

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

javascript

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

javascript

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":"grace@example.com"} 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).