MySQL Connect

MySQL Connect: Connecting Node.js to MySQL Databases

Connecting Node.js applications to a MySQL database server is managed using the modern mysql2/promise driver library, which supports async/await promises, prepared statement caching, and connection pooling.

Connection Pool vs Single Connection Architecture

flowchart TD
    A["Node.js HTTP Server Threads"] --> B["MySQL2 Connection Pool"]
    B --> C["Active Connection 1"]
    B --> D["Active Connection 2"]
    B --> E["Active Connection 3"]
    C & D & E --> F["MySQL Server RDBMS"]

Practical Code Example

import mysql from 'mysql2/promise';

// 1. Create a Connection Pool (Recommended Production Strategy)
const dbPool = mysql.createPool({
    host: process.env.DB_HOST || 'localhost',
    port: parseInt(process.env.DB_PORT || '3306'),
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || 'secret',
    database: process.env.DB_NAME || 'kodersolution_db',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// 2. Test Connection Health
async function testDatabaseConnection() {
    try {
        const connection = await dbPool.getConnection();
        console.log('Successfully connected to MySQL database server!');
        
        const [rows] = await connection.query('SELECT VERSION() AS version');
        console.log('MySQL Server Version:', rows[0].version);

        connection.release(); // Return connection to pool
    } catch (error) {
        console.error('MySQL Connection Error:', error.message);
    }
}

testDatabaseConnection();

Best Practices & Gotchas

• Use Connection Pools: Do not create single connections for web requests; use mysql.createPool() to reuse database socket handles efficiently.
• Always Release Connections: Call connection.release() when manually checking out pool connections to avoid connection exhaustion.
• Store DB Passwords in .env: Never hardcode database credentials in code repositories.

Self-Check Challenge

What is the advantage of using mysql2/promise over the legacy mysql driver in Node.js?