Working with SQL Databases — Node.js Tutorial

Connecting to SQL Databases

Node.js can connect to SQL databases like MySQL and PostgreSQL using dedicated driver packages. The mysql2 package is used for MySQL, and pg is used for PostgreSQL. Both support connection pooling for efficient database access.

Connection pools maintain a set of reusable connections, avoiding the overhead of opening and closing connections for every query. This is essential for production applications.

Example

// --- MySQL ---
// npm install mysql2

const mysql = require('mysql2/promise');

// Create a connection pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 10
});

// --- PostgreSQL ---
// npm install pg

const { Pool } = require('pg');

const pgPool = new Pool({
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  password: 'password',
  database: 'myapp',
  max: 10
});

// Test the connection
async function testConnection() {
  try {
    const [rows] = await pool.query('SELECT 1 + 1 AS result');
    console.log('MySQL connected:', rows[0].result); // 2
  } catch (err) {
    console.error('Connection failed:', err.message);
  }
}

testConnection();

mysql2/promise — MySQL driver with Promise support
pg (Pool) — PostgreSQL driver with connection pooling
Connection pool — Reuse connections for better performance
connectionLimit / max — Maximum number of connections in the pool
Always use environment variables for database credentials

Notes

Always use the promise-based version of mysql2 (mysql2/promise) to use async/await instead of callbacks.

Parameterized Queries and CRUD

Never build SQL queries by concatenating user input — this creates SQL injection vulnerabilities. Always use parameterized queries where the database driver safely escapes values for you.

Parameterized queries use placeholders (? for MySQL, $1 for PostgreSQL) that the driver replaces with properly escaped values.

Example

const mysql = require('mysql2/promise');

// Assume pool is already created

// CREATE TABLE
async function createTable(pool) {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100) NOT NULL,
      email VARCHAR(255) UNIQUE NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `);
}

// INSERT — Use ? placeholders (NEVER string concatenation)
async function createUser(pool, name, email) {
  const [result] = await pool.query(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    [name, email]
  );
  return { id: result.insertId, name, email };
}

// SELECT
async function getUsers(pool) {
  const [rows] = await pool.query('SELECT * FROM users ORDER BY created_at DESC');
  return rows;
}

// SELECT by ID
async function getUserById(pool, id) {
  const [rows] = await pool.query('SELECT * FROM users WHERE id = ?', [id]);
  return rows[0] || null;
}

// UPDATE
async function updateUser(pool, id, name, email) {
  const [result] = await pool.query(
    'UPDATE users SET name = ?, email = ? WHERE id = ?',
    [name, email, id]
  );
  return result.affectedRows > 0;
}

// DELETE
async function deleteUser(pool, id) {
  const [result] = await pool.query('DELETE FROM users WHERE id = ?', [id]);
  return result.affectedRows > 0;
}

Notes

SQL injection is one of the most dangerous web vulnerabilities. ALWAYS use parameterized queries — never build SQL strings with user input.