Node.js Relational Databases - MySQL & PostgreSQL Connection Pooling & Safe Queries

Relational databases like PostgreSQL and MySQL remain the gold standard for enterprise financial systems, e-commerce, and structured relational data. In this tutorial, you will master connection pooling, parameterized queries, and ACID transactions in Node.js.

PostgreSQL with pg Connection Pool

npm install pg

// db/postgres.js
const { Pool } = require('pg');

const pool = new Pool({
  host: process.env.PG_HOST || 'localhost',
  port: 5432,
  user: process.env.PG_USER || 'postgres',
  password: process.env.PG_PASSWORD || 'secret',
  database: 'veewom_university',
  max: 20,                  // Maximum 20 simultaneous connections
  idleTimeoutMillis: 30000, // Close idle clients after 30 seconds
  connectionTimeoutMillis: 2000
});

// Safe Parameterized Query Helper
const query = (text, params) => pool.query(text, params);

module.exports = { pool, query };

Executing Parameterized Queries (100% SQL Injection Safe)

const db = require('./db/postgres');

async function getStudentByEmail(userEmail) {
  // CRITICAL: Use $1, $2 placeholders! NEVER concatenate strings!
  const sql = 'SELECT id, full_name, email, gpa FROM students WHERE email = $1 LIMIT 1';
  
  const result = await db.query(sql, [userEmail]);
  return result.rows[0]; // Returns matching record
}

Executing ACID Transactions (Rollback on Failure)

async function transferCourseEnrollment(studentId, oldCourseId, newCourseId) {
  const client = await db.pool.connect(); // Acquire dedicated client

  try {
    await client.query('BEGIN'); // Start Transaction

    // 1. Remove from old course
    await client.query(
      'DELETE FROM enrollments WHERE student_id = $1 AND course_id = $2',
      [studentId, oldCourseId]
    );

    // 2. Add to new course
    await client.query(
      'INSERT INTO enrollments (student_id, course_id, enrolled_at) VALUES ($1, $2, NOW())',
      [studentId, newCourseId]
    );

    await client.query('COMMIT'); // Commit all changes!
    console.log('[SUCCESS] Enrollment transfer completed atomically.');
  } catch (error) {
    await client.query('ROLLBACK'); // Revert all changes if any query failed!
    console.error('[TRANSACTION ROLLED BACK] Error:', error.message);
    throw error;
  } finally {
    client.release(); // Release client back to pool
  }
}