Advanced Node.js

Chapter 9: Database Optimization

9.1 Connection Pooling

Connection pooling reuses database connections, reducing overhead and improving performance.

// MySQL connection pool
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: 'localhost',
    user: 'user',
    password: 'password',
    database: 'mydb',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

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

// PostgreSQL connection pool
const { Pool } = require('pg');
const pgPool = new Pool({
    host: 'localhost',
    database: 'mydb',
    max: 20,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000
});

9.2 Query Optimization

// Use indexes
CREATE INDEX idx_email ON users(email);

// Avoid N+1 queries
// Bad
const users = await User.findAll();
for (const user of users) {
    const posts = await Post.findAll({ where: { userId: user.id } });
}

// Good - Use joins or eager loading
const users = await User.findAll({
    include: [{ model: Post }]
});

9.3 Prepared Statements

// Prevents SQL injection and improves performance
const stmt = await pool.prepare('SELECT * FROM users WHERE id = ?');
const [rows] = await stmt.execute([userId]);
stmt.close();

9.4 Database Transactions

// Transaction example
const connection = await pool.getConnection();
try {
    await connection.beginTransaction();
    
    await connection.query('INSERT INTO orders ...');
    await connection.query('UPDATE inventory ...');
    
    await connection.commit();
} catch (error) {
    await connection.rollback();
    throw error;
} finally {
    connection.release();
}