Working with SQL Databases

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

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

// Create a connection pool
// ONE pool for the whole application, created once when this module loads
const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,   // never a literal in source code
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10
});

module.exports = pool;   // module caching means every file shares this one pool

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

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

const pgPool = new Pool({
  connectionString: process.env.DATABASE_URL,   // postgres://user:pass@host/dbname
  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();

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
    )
  `);
}

// THE VULNERABILITY, written out so you recognise it on sight:
//   const sql = "SELECT * FROM users WHERE email = '" + email + "'";
//   email = "' OR '1'='1"              ->  the condition is true for every row
//   email = "'; DROP TABLE users; --"  ->  a second statement runs as well

// 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;
}

// Column names and sort direction CANNOT be placeholders.
// Use an allow-list rather than concatenating whatever the client sent.
const SORTABLE = { name: 'name', newest: 'created_at' };

async function listUsers(pool, sortKey, dir) {
  const column = SORTABLE[sortKey] || 'created_at';   // never the raw input
  const order = dir === 'asc' ? 'ASC' : 'DESC';
  const [rows] = await pool.query(
    `SELECT id, name, email, created_at FROM users ORDER BY ${column} ${order} LIMIT ?`,
    [50]
  );
  return rows;
}

-- schema/001_init.sql — versioned, committed, applied in order
CREATE TABLE users (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(80)  NOT NULL,
  email      VARCHAR(255) NOT NULL UNIQUE,   -- enforced by the database itself
  password   VARCHAR(255) NOT NULL,          -- a bcrypt hash, never a password
  role       ENUM('user','admin') NOT NULL DEFAULT 'user',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE books (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  title     VARCHAR(200) NOT NULL,
  author    VARCHAR(120) NOT NULL,
  owner_id  INT NOT NULL,
  CONSTRAINT fk_books_owner
    FOREIGN KEY (owner_id) REFERENCES users(id)
    ON DELETE CASCADE                        -- delete a user, delete their books
);

CREATE INDEX idx_books_owner ON books(owner_id);   -- you will filter by this constantly


// In Node: a duplicate-key violation is a 409, not a 500
try {
  await createUser(pool, name, email);
} catch (err) {
  if (err.code === 'ER_DUP_ENTRY' || err.code === '23505') {   // MySQL / PostgreSQL
    return res.status(409).json({ error: 'That email is already registered' });
  }
  throw err;
}

// A transaction must run on ONE connection, not on the pool
async function placeOrder(pool, userId, bookId) {
  const conn = await pool.getConnection();   // borrowed — you MUST give it back
  try {
    await conn.beginTransaction();

    const [rows] = await conn.query(
      'SELECT stock FROM books WHERE id = ? FOR UPDATE', [bookId]
    );
    if (!rows.length || rows[0].stock < 1) {
      throw new Error('OUT_OF_STOCK');
    }

    await conn.query('UPDATE books SET stock = stock - 1 WHERE id = ?', [bookId]);
    await conn.query(
      'INSERT INTO orders (user_id, book_id) VALUES (?, ?)', [userId, bookId]
    );

    await conn.commit();      // both changes, or neither of them
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();           // the ONLY place this is safe to put
  }
}

// If you never call getConnection(), nothing can leak:
const [rows] = await pool.query(
  'SELECT id, title FROM books WHERE owner_id = ?', [userId]
);

// The same read, three ways

// 1. Raw SQL — you can see exactly what will run
const [rows] = await pool.query(
  'SELECT id, title FROM books WHERE owner_id = ? ORDER BY created_at DESC LIMIT 20',
  [userId]
);

// 2. A query builder — composable, and still obviously SQL
// const rows = await knex('books')
//   .select('id', 'title')
//   .where({ owner_id: userId })
//   .orderBy('created_at', 'desc')
//   .limit(20);

// 3. An ORM — convenient, and one more step away from the query that runs
// const rows = await prisma.book.findMany({
//   where: { ownerId: userId },
//   select: { id: true, title: true },
//   orderBy: { createdAt: 'desc' },
//   take: 20
// });

// Still injectable, in every ORM ever written, if you do this:
// await prisma.$queryRawUnsafe(`SELECT * FROM books WHERE title = '${input}'`);